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

CPP Benefit Calculator Spreadsheet

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.  

Wednesday, 7 December 2016

Financial Studies 2 - Start CPP Earlier or Later than 65?


This is a long Blog post, so a summary of the results is in order, to keep your attention. 

The chart below are results calculated using my Retirement Forecaster spreadsheet and shows the optimum age to take CPP (blue line). The Retirement Forecaster spreadsheet takes into account effects of income tax, investment return and OAS clawback when determining the optimal strategy. You can estimate your life expectancy and use this chart to make an informed decision on when to start CPP. The red line is the result, just using a simple Total Cash calculation, only considering how much cash you receive as CPP payments. This comes to a different conclusion as the more accurate results from the Retirement Forecaster spreadsheet. 

Note that the assumptions that went into this calculation are specific and may not represent your situation. You can estimate the best age to start CPP by using my Retirement Forecaster spreadsheet for your specific situation. 

Okay, on with the Blog Post

Why should I write about this? Much has already been written on this subject, and by very competent and capable people. I want to look at this to see if it is possible to simplify the conclusions. 

Many of the articles do not address the tax implications or the OAS clawback of a lower or higher CPP benefit for a different period of time. Nor do many consider the time value of money. Using my Retirement Forecaster spreadsheet, we can account for these. 

Monday, 8 August 2016

Financial Basics 13 - Tracking your Financial Performance

When you decide that you want to track how something is performing, usually this is done by measuring something actual and comparing against a target. An Athlete's performance is measured by such things as time, distance, height or score. But what do they measure against? In their competitions they are trying to get the fastest time, longest distance, lowest or highest score depending on the sport. In training and warm-up competitions they may be measuring their performance against something else. They may have a target time they want to achieve, or maybe they just want to continually improve over their personal best time.

So when you want to measure your performance your need to:
  • Decide on what you are measuring.
  • Decide on what targets or progression are considered success.
The simplest form in term of finances is to measure your Net Worth and compare it to a target amount that you want to achieve. 

Net Worth Tracking Spreadsheet

Net Worth Tracking v1p0

This spreadsheet is designed to help you keep track of your Net Worth, those you designate as Retirement Assets and compare your assets to forecasts created from the Retirement Forecast spreadsheet.

You can download the blank spreadsheet here. If you want one with some example data already filled in, download that here

Tuesday, 7 June 2016

Experimental Design for Reservoir Simulation

ED4Sim v2p2

ED4Sim is a spreadsheet tool designed to assess uncertainty in petroleum reservoirs using reservoir simulation.  It is a very powerful spreadsheet, that has state of the art capabilities in an easy to use flexible package.

This spreadsheet was developed mainly while working at Nexen, and along with the RSMPlots spreadsheet, the company has allowed it to be released publicly. 

Update June 7th, 2016

The link to the Essential regression add-in is broken and I am unable to find this tool elsewhere. This is surprising as I consider it the best free regression tool and as good or better than commercial (not free) tools. I have provided links below to my copies of this tool.