Wednesday, 22 February 2017

Histograms Etc - Easy Histograms, Probability Plots and Statistics



This Spreadsheet...
  • This spreadsheet allows you to quickly create professional looking histograms, probability plots and statistics of data.
  • Plots histograms and cumulative probability plots for up to 10 distributions of data.  
  • Calculates statistics for each data set.
  • Calculates best fit normal and lognormal distribution.  

Wednesday, 15 February 2017

X-Y Plots Spreadsheet - An Easy Way To Explore Your Data

This Spreadsheet...

  • This spreadsheet allows you to quickly create professional looking plots of data.
  • This spreadsheet is useful for looking for trends and relationships in your data.
  • You just paste in the data to the data sheet, name the columns and select which columns you want to plot in the Chart sheet.
  • With this spreadsheet it is easy to:
    • plot separate series (different colour points) categorized by one of your data fields.
    • filter the data you want to plot.
    • plot regression lines through the different series.
    • plot bin averages for scattered data.
    • annotate the data with labels automatically.


Wednesday, 8 February 2017

Regression Modeling Spreadsheet (automatic stepwise multiple linear regression)


This spreadsheet does:
  • Multiple linear regression.
  • Can use linear, square and interaction terms for all the variables you enter.
  • Stepwise multiple linear regression to find the best model automatically.
  • Provides results and many useful diagnostic plots.
  • Provides a suggestion of which independent terms may be confounded (useful for regression of experimental design data).

Tuesday, 31 January 2017

My Philosophy On The Use Of Spreadsheet Tools

Spreadsheets are great tools. As much as computers have transformed the way we work (and play to some extent) the spreadsheet has been as important to transform the way the technical professional works. Many professionals use spreadsheets to do things that were once extremely time consuming. When I went to University in the early 1980s, we did plotting, data analysis, statistics and regression by hand or with the use of calculators. It took a long time and was easy to make mistakes, which then would require the calculation or plot to be re-done. By the time I started working in the mid 1980s, personal computers were making their way into the office and spreadsheets arrived shortly thereafter. First was Lotus 1-2-3, which was ground breaking, but eventually Excel became the standard. 

Good old Lotus 1-2-3

Saturday, 31 December 2016

Financial Studies 2 - CPP Early or Late? Part 3


In the Part 1 of my blog posts discussing whether to start CPP early or late, I talked about articles which had presented the break-even point analysis and I pointed out that this was the wrong question to ask. The break-even is the age at which the cash received from starting CPP early is the same as starting CPP at 65. 

The analysis is usually done on a spreadsheet, but there is actually a much easier way to calculate this. 

The result is:

The number of months from when your start CPP to the break-even age is the reciprocal of the actuarial adjustment discount rate. If you start CPP early the discount is 0.6% per month, or 0.006. The reciprocal of this (1/0.006) is 167 months or 13.9 years. For starting CPP at 60, the break-even is at age 74. If you live to less than 74, starting CPP at 60 is better than starting it at 65. 

So next time you're at a cocktail party and someone is going on about their spreadsheet to calculate the break-even for taking CPP early, you can one-up them by saying "Yeah, it's just the reciprocal of the actuarial adjustment discount rate".


Monday, 19 December 2016

Financial Studies 2 - CPP Early or Late? Part 2



After publishing the post on whether to take CPP early or late, a friend pointed out to me that if the situation was that the person retired earlier than the example, the conclusion may change. It occurs when the person has less than 40 years of contributions to CPP which can occur if you retire before 58, are unemployed for some years, or work outside Canada for part of your career. In the previous post the example person retired at 60 and had 40 years of CPP contributions. 

Just a note on terminology. When I say retire, I mean when the employment stops and stop CPP contributions. This is not the same as the age they start CPP benefits. 

When calculating your CPP benefit, the benefit is based on your average (inflation adjusted) pensionable earnings (on which your contributions are made) over your working life. If you earned more than the maximum pensionable earnings in a year the value is capped at that maximum, which is about $55k now. In the calculation you get to drop your 17% of lowest earning years, or in other words keep 83% of your highest pensionable earning years. 


Sunday, 11 December 2016

Retirement Planning and Forecasting Spreadsheet 2.0 (for Canada)

Retirement Forecaster 2p6

Updated to version 2.6 on December 11th, 2016. 

In this version I have made some enhancements including:
  • Updated the OAS comments and functionality. With federal budget in March 2016, normal date for taking OAS is fixed at 65. 
  • Spreadsheet will now calculate the increase in OAS benefits if you defer starting OAS after 65, up to the latest of 70.
  • In the "Top up to Tax Bracket" experimental functionality, have updated the tax brackets to include provincial ones. Selection is now done by drop-down.
Download new version here.  

Updated to version 2.5 on November 16th, 2016. 

In this version I have made some enhancements including:
  • In Annual Inputs you may now input "Non-eligible Canadian Dividends" to allow input of income from a Canadian controlled private corporation.
  • Made clawback of CPP from a DB benefit able to be a fraction. This allows you to better model a DB plan of CPP clawback if you have worked at the company with the DB for only part of your career.
  • CPP Benefit amount is now automatically discounted or increased if you start taking CPP at an age before or after 65.
  • CPP and EI payroll deduction is now estimated to more accurately calculate a net income.
  • LIF maximum withdrawal tables have been updated for 2016 values.
  • LIF maximum withdrawal is now dependent on whether the LIF is federally or provincially governed. This changes the maximum withdrawal percentage.
  • Tax rate iteration was optimized to make the convergence faster.
  • You may now plot Age on the x-axis of plots as an option. Year is still also available. (Look under "Other Settings".

Updated to version 2.4 on April 5th, 2016. 

In this version I have updated all the tax rates for federal and all provinces (the previous version had just the major changes to federal and Alberta rates). I have also added the feature to fund TFSA contributions from RRSP withdrawals in retirement (previous version did this from savings).

Updated to version 2.3 on February 8th, 2016. 

In this version I have updated the TFSA limits for 2016, added a message if either person has an income shortfall in retirement, added a report of total tax lifetime income tax paid on the Inputs_Summary sheet, included the federal tax abatement for Quebec and changed how the tax rate iteration works to remove a bug.

Updated to version 2.0 on May 21st, 2015. 

In this version the inputs during employment phase have changed. Instead of specifying a percentage of income saved, you now specify an income and expenses. After deducting taxes and expenses from income the difference is the amount saved. This version allows the potential withdrawal of savings during employment if expenses and taxes are higher than income. This version handles the tax payable on investment income more accurately than the previous one. 

This spreadsheet allows you to make an accurate financial plan for your retirement, starting at any time in your life.  Unlike many retirement tools, this spreadsheet shows a cash flow forecast vs time and identifies which account money is saved into (during employment) and which account or source the retirement income is withdrawn from.  It handles the different account types (RRSP, LIRA, TFSA and a taxable investment account), the limits imposed on them and does income tax calculations for any of the provinces.  

The picture below shows the main page of the spreadsheet which contains all the inputs you need (in yellow) and provides a cash flow forecast and how the savings accounts increase and decline.  


I hope you find the tool useful and I am always willing to hear of suggestions to improve it.