Updating Retirement Spreadsheet to Reflect Current Realities

Those who follow this blog or have read my book Retired at 48 - One Couple's Journey to a Pensionless Retirement know that we use a Retirement Spreadsheet which both estimated and tracked our portfolio growth progress during the savings phase prior to retirement, and created a spending plan to track against during the "draw-down" phase (post retirement) to ensure we do not prematurely run out of funds.  At the beginning of every new year, I update our actual year end portfolio balance in the "Actual Ending Balance" column for the previous year.  This causes the rest of the projections going forward to recalculate, thus providing a more accurate view of the future.  In creating my spreadsheet, I reverse-engineered from a online small app and then expanded upon it to support the extra data and functionality that I desired in my retirement plan.

Since we first started using this spreadsheet and in the sample spreadsheet that I distribute for anyone who requests it from me, I have used 2% as the estimated average inflation rate within cell J9.  In the spreadsheet's predictive estimate, the following fields are increased annually by that rate of inflation.

• Income Sources
• Canada Pension Plan (CPP) - Indexed to Inflation
• Old Age Security (OAS) - Indexed to Inflation
• Expenses
• Retirement Spending

This rate had been relatively stable for so many years (especially during our retirement years) that I not really given it much thought. Unfortunately in 2021, driven by stresses caused by the pandemic which show no sign of easing, the rate of inflation has risen to almost 5%.  It seems short-sighted not to reflect this in our ongoing projections for the future.

I have updated the latest version of the spreadsheet that I distribute to reflect this by increasing the predicted rate of inflation to 4% in cell J9 (couples examples) or I9 (individual example).  This is an input field that you can update to whatever you want, but I thought I would put something more realistic in the sample for now.  Given that the same rate will be used to predict all future years until you change it, I did not use 4.8% which is the current rate as of January 19, 2022.  I tempered the prediction a bit with the hopes that after the pandemic is done, the rate will ease a bit.  Anyone requesting my retirement spreadsheet going forward will get the version with this new rate (unless things radically change again).  

Anyone who already has my spreadsheet but would like to address this issue themselves have the following options:

1. Update Inflation rate in Cell J9 (or I9 for individuals) to 4%.  
• Unfortunately this will affect all the previous years' calculations which in turn will affect the estimates going forward
• This does not matter to anyone still in the savings phase since you will not have started using CPP/OAS/Retirement savings yet.
• For those in retirement spending phase, you may want to use the next option although if you have been updating the spreadsheet with your actual ending balance each year, the impact is negligible.
2. Update inflation rate only the years going forward starting from a given year
• Make a copy of the spreadsheet before you start so you can try again if you make a mistake
• Enter 1.04 (or whatever you want your going forward inflation estimate to be) in cell AD4
• Click on the first Retirement Spending calculation cell (e.g. Cell AF21 for couples examples, AB21 for singles example)
• Change the last part of the formula in that cell from $AC$4 to $AD$4 (couples) or from $AB$4 to $AD$4 (singles)
• Click and hold from the bottom right corner of your starting cell and drag down to the end to update the formula for all subsequent Retirement Spending years.
• Repeat the previous 3 steps for CPP and OAS (if desired)
3. I can send you a new spreadsheet but you would have to re-input all of your previous data.

In order to have an even more conservative estimate in our own personal spreadsheet to make sure we don't run out of money, I only updated the retirement spending estimate to use the higher inflation rate but left the OAS and CPP estimates with the lower rate.  By increasing our spending estimates but keeping our income estimates lower, I generate an extra buffer to make sure we don't overspend.

As Rich and I creep closer to the age where we might start taking OAS, I realize that based on the amounts remaining in each of our RRIF accounts, we may qualify for different amounts of OAS.  I have therefore updated my spreadsheet to split out the OAS estimate for Person 1 vs Person 2 in the couples examples.  Currently there is only one field to represent both people.  I should have done this from the start.  I also found an error in the starting year of taking OAS for Person 1 which I have now fixed.  It was previously hardcoded at age 67 but now uses the age entered in the input field.

Again, for anyone who already has a spreadsheet but would like these changes, email me to let me know and I can help you modify yours or send you a new one (but you would have to replicate the inputs from your current spreadsheet).