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.
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.
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).
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
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".
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.
CPP Calculation v1p2
This spreadsheet will estimate your CPP Benefit based on past pensionable earning and future earnings. You can vary the age you retire and the age you elect to take CPP benefits. Download the spreadsheet in excel format here and in Google Sheet format here.
Updated to v1.2 on December 11th, 2016
- Added the over 65 dropout provision.
- Updated the government YMPE values for 2016 and 2017.
Updated to v1.1 on April 23rd, 2015
- The spreadsheet has been updated to fix some bugs and update some values that have changed.
