The Automagical Financial Planning Ballparkinator

April 14th, 2014 by Potato

TLDR: A single, straight-line calculation of invest $X/yr at Y% returns is a little too simplistic for your retirement planning, but a Monte Carlo or exhaustive simulation can be overwhelming. I think a good middle-ground is to find a ballpark number of how much you need to save, and to see where that puts you under good, decent, and relatively poor investment outcomes so you can figure out if you’re in the right ballpark. Download Potato’s Automagical Financial Planning Ballparkinator here.

I think hands-down the most common two questions I get related to finances are “how do I…” (for which I’ve written a whole book and have a course) and “how much do I need to save, anyway?” There are a host of tools available on the internet to try to answer this, but they’re all fairly simplistic — as few as three factors are considered, with many hard-coded variables. If you’re still decades away from retirement then you really just need a ballpark number to get started, and for that those tools are pretty good — heck the “save 10% of your income” rule-of-thumb is not terrible, and it doesn’t even specify whether it refers to pre-tax or after-tax income, or to what age range it applies.

But of course I wanted something a little more detailed with more things to tweak. More importantly, I wanted to put up side-by-side comparisons of some important scenarios so people didn’t have to refresh their web browser a dozen times to get an idea of where to go. May I present Potato’s Automagical Financial Planning Ballparkinator. Available in Excel.

There are many possible shapes to the future, so how much you need to save decades in advance will only ever be a rough estimate. This will help you figure out what the general ballpark estimate of that number should be. It’s based on my retirement planning spreadsheet calculator — I added a more robust tax calculation (including OAS clawbacks), and of course the whole soup-to-nuts saving through retirement component, but have removed some of the finer features (like non-flat budgets and personal inflation rates). It does include a separate field for your investing fees (MERs) so you can see the impact of those without having to directly adjust the returns in the scenarios (and more directly, to put that factor front-of-mind).

It calculates forward based on your current savings rate (and a bunch of other assumptions) to find out how long your money will last under that plan, and also estimates backwards from your budget needs, accumulation years investment returns, and a sustainable withdrawal rate to rough out how much you should be saving (annually). Note that the backwards calculation bundles all account types together for the calculation and does not account for taxes — a much rougher estimate and more similar to the simplistic web-based tools common on many bank websites.

The results are presented in a little table to examine three scenarios for future returns (a base, worst, and best case), which you can of course define yourself, and for four risk profiles/asset allocations: ultra-conservative (all fixed income), balanced (50/50), risk tolerant (mostly stocks), and all stocks. This is important as the rules of thumb like “save 10%” are based on having enough of a balance to get something close to equity-like returns on your savings. If you are like many people today, scarred by 2008 and unable to contemplate anything more volatile than a package of bonds, then you will have to cut your spending budgets and save substantially more every year to make up for that ultra-conservatism in your investments. Similarly, if you don’t start investing until you’re in your 50’s, then you’ll have to put away substantially more than 10% of your income.

Figuring out your future spending needs may be the most difficult part. For your future budget you can start with your current spending needs, and take your best guess as to how they will change in the future. More travel, but less commuting? No more mortgage payments, but added expenses for lawn care & snow removal that you used to handle yourself? The default in the spreadsheet is to take 75% of your current spending budget, but definitely put careful consideration into this figure — and try out a few iterations.

Note that this is not a full financial plan, not by a long shot. There’s nothing here about contingency plans, goals, motivations, asset allocation, rebalancing plans, insurance, emergency contacts1, taxes and tax shelters, short-term savings goals, or really much of anything else. I’m hoping one day someone will get around to showing us what a good, complete financial plan looks like. Until then: ballpark, get started, evaluate, adjust.

Also, if you need help estimating how much you’ll get from CPP, I have a tool for that here.

1 – no, not poison control. Who will calm you down when markets are roiling? Who will your family call if you’re dead or incapacitated, who has your will?

Update, April 16, 2014: Thanks to Spudd at CMF (no relation) for pointing out that there was a problem with early retirement scenarios and the RRIF table. I’ve provided rough guidance for that going back to age 20 — it might not be the right amount to start withdrawing from an RRSP right away, but it should be reasonably close and at least returns something for early retirement scenarios now. I’ve replaced the sheet on the site, the link remains the same.

Update, July 2017: I’ve fixed the “backwards” calculation with a better approximation, one that uses a few different ranges of sustainable withdrawal rates. There was also a pretty bad bug when RRIF withdrawals satisfied cashflow needs, but money was still being pulled from a TFSA anyway that’s now fixed.

2 Responses to “The Automagical Financial Planning Ballparkinator”

  1. gammj Says:

    Not to pressure you or anything (even though I will proceed to sort of do so anyway)but any ballpark on when the fix might be in? :P

    I like your calculator in that it seems to be the most complete out of most of them out there. That is, unless you want to pay for one. And even some of the pay ones don’t seem to work on anything beyond Windows XP and/or very old copies of Excel in some cases.

  2. Potato Says:

    Hi gammj, thanks for the gentle push! I’ve (long) had fixing this as one of the things I have to do before the book launch (I mention it in there). Unfortunately I got halfway through and had to shelve it to meet some deadlines. There are only a few weekends left between now and Dec 1, so soon! It is literally the top of my to-do list at the moment.