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.

Rebalancing Spreadsheet

April 4th, 2014 by Potato

Canadian Capitalist and Squawkfox have created rebalancing spreadsheets to help you when it comes time to rebalance your portfolio. They are somewhat simplistic and hard-coded with the funds — this is a good thing if you’re following the Sleepy Portfolio or one of the Couch portfolios: just enter the current value, the money you have to add, and see how to split your new purchase up.

I wanted one with a bit more flexibility: one that would allow for a few broad categories of investments, with sub-investments. For instance, if I had small bits of cash left over at various points through the year I might throw them into a TD e-series fund, and as long as my overall Canadian/US/International split was ok I wouldn’t worry about rebalancing the e-series versus ETF splits. Or similarly if I had several sub-products to make up one sector, like counting BRK.B and VTI together for US exposure, but not caring too much whether that split was 50/50 or 60/40. Also rather than just entering the current value, the sheet lets you enter the price and the number of units in your various accounts. With the units entered, half the work is done for the next time you want to rebalance (just update the unit prices).

The sheet calculates and displays the variances (how much you are off by), and then also calculates how many units of each fund you need to buy to get back into balance. Note that you should generally round down so that you don’t over-spend the funds in your brokerage account. In the top row you can enter how much new cash you have to invest, or enter a negative value to withdraw cash from your portfolio.

The spreadsheet is available here in Excel, or through Google Docs* here. Enjoy!

* – Google can try to call it Drive, but Docs has stuck with me.