Retired at 48 Spreadsheet Calculators

In our book Retired at 48, we described how we used an online retirement planner provided by GlobeInvestorGold to predict how much money we needed to retire and how long that money would last.  It accepted a comprehensive set of input parameters including current and retirement ages, current investment total, current salary, projected annual savings and retirement expenditures. Taking these inputs, it created a chart showing how the money would grow during the saving years and draw down during the retirement years.  It basically told you whether you were on target to make your retirement goals with your current set of parameters, or whether you needed to save more, work longer or spend less in order to achieve these goals.

This tool provided an excellent starting point, but lacked some capabilities that we desired.  The biggest gap was in trying to plan our retirement as a couple.  We wanted to be able to represent our ages (current, retirement, life expectancy) and income sources (salaries, pensions) separately, but still treat the overall saving and spending targets as a couple.  The tool also did not reflect recent new legislation that increased the old age security payout age to 67, and did not allow for the options of taking Canada pension plan at an age earlier or later than 65.

To meet our needs, I reverse engineered the functionality of the tool, reproduced it in a spreadsheet and added the additional features that I desired.  Simulating the results of the online tool, the spreadsheet started with a year beginning balance, estimated investment growth plus additional savings to project a year end total during the saving years.  In the retirement or spending years, the spreadsheet tracked the retirement spending, offset by company or government pensions where applicable.  I added a column to update actual year ending balance, so that at the end of each year, the projection could recalculate with more accurate data to start the next year.  I also provided a column for entering actual savings each year, so that the actual investment growth could be tracked.  With these extra features, we were able to simulate a wide variety of scenarios, in order to land on the one that allowed us to achieve our early retirement.

Even after publishing the book, I have continued to enhance the spreadsheet to make it more easily adaptable to different situations.  My spreadsheet now provides a full set of input parameters for each spouse, including specifying the existence of a company pension, the age when it would start paying out and whether or not that pension is indexed for inflation. We will continue to use this spreadsheet to verify year after year whether our original retirement projections are still on track.  If they are not, we will have to take remedial actions such as reducing spending until we fall back in line.

As discussed in detail in the book, we use several other spreadsheets to track our investments and dividend payouts on a monthly basis. Filters are used to provide a quick summary of our investment holdings per account, per market sector, per market capitalization, etc. to ensure that we remain properly diversified in order to reduce risk in our portfolio.

There are a few extra worksheets that we use regularly, which were not included in the book:

This worksheet summarizes the dividend payouts per month or quarter per account.  This is useful for ensuring that there is enough cash flow to pay our bills each month.  If the dividend payout of any stock either increases or decreases, these tables highlight the impact to our monthly cash flow.

We keep one worksheet like this per year, to provide a snapshot of our portfolio by account, month by month, and year by year.  This helps us understand whether or not our portfolio is growing as expected.  We also keep a running total of the dividends paid out per month to our non-registered account, to aid us during tax season.

GET YOUR OWN COPY OF THESE SPREADSHEETS
I have created a sample set of my spreadsheet calculators and worksheets, including ones represented in the book and those that are not.  I will make them available to anyone who has purchased my book and wants to try using them.  Simply send an email to retiredat48book@gmail.com and specify the full name of the person to whom the book is dedicated.  I will email you back with the spreadsheet.  If you have questions about the how to use spreadsheets, you can comment on this blog post.