The After-Tax Spending Plan

Asset allocation, risk, diversification and rebalancing. Pros/cons of hiring a financial advisor. Seeking advice on your portfolio?
Post Reply
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

The After-Tax Spending Plan

Post by longinvest »

I've built an after-tax spending plan. Here are its permanent links:

ONLINE AND DOWNLOAD LINKS:
IMPORTANT: To modify the spreadsheet, you can either download it and use your favorite software, or:
  • Log into your Google account.
  • Select the "File -> Create a copy..." menu item.
  • The copy will be yours to play with and modify.
It's an online and downloadable spreadsheet to calculate how much one can spend during the upcoming year*. It aims at adapting one's current standard of living and savings to market returns, while taking taxes into account. Instead of targeting a specific "retirement number", it targets a user-selected chosen retirement age and puts accumulation and retirement spending on an equal footing.

* At the time this post is being written, it only works for people in their accumulation period. Future versions are likely to also work for retirees.

It's very easy to use.

Inputs include: current age, retirement age, current salary, current portfolio balance, the "current amount (montlhy) at age 65" found on one's last QPP statement, and the portfolio's asset allocation.
input.png
input.png (17.48 KiB) Viewed 2974 times
The spreadsheet automatically calculates this year's approximate contributions and taxes as well as a projected retirement plan based on VPW with OAS and QPP delayed until age 70.
taxes-and-plan.png
Most importantly, the spreadsheet determines this year's monthly budget (after tax).
savings-and-spending.png
savings-and-spending.png (18.04 KiB) Viewed 2974 times
The goal of the spreadsheet is to determine a sustainable monthly budget every year.

Market returns are unpredictable. The portfolio will fluctuate. It is thus important to update the inputs every year (and update the Rate sheet) to recalculate a new budget based on the new reality at that point. Similar to VPW, it's a flexible spending plan. If the portfolio has grown more during the year, spending will increase; if it has grown less, spending will decrease.

I initially developed it for myself, so it's currently using QC tax parameters and QPP. It could be improved to be usable during retirement, too.

1) Would there be some interest for such improvements and getting this spreadsheet to support other provinces and CPP?
2) Is there any other comment on the spreadsheet?
Last edited by longinvest on 23 Sep 2017 23:55, edited 4 times in total.
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

Here's a complete screenshot of the main sheet (click on the image to enlarge it):
screenshot.png
Even though many members don't live in QC, they can still give it a try. Tax rates are often somewhat similar in many other provinces (depending on salary). Of course, they would use their CPP monthly estimate at age 65 (assuming no future contribution) as the "current amount (monthly)" QPP entry. (Does the CRA provide this information? If not, one could just put $0.)

Comments are welcome!
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
User avatar
adrian2
Veteran Contributor
Veteran Contributor
Posts: 13333
Joined: 19 Feb 2005 08:42
Location: Greater Toronto Area

Re: The After-Tax Spending Plan

Post by adrian2 »

longinvest wrote: 23 Sep 2017 11:41 Of course, they would use their CPP monthly estimate at age 65 (assuming no future contribution) as the "current amount (monthly)" QPP entry. (Does the CRA provide this information? If not, one could just put $0.)
Unfortunately, the CPP website estimate is (sometimes) very misleading. It's not clear from the verbiage, but the estimate assumes that you would continue to contribute to CPP, from now until retirement, at the same average rate you have contributed from age 18 until now. For some, it may be a reasonable assumption; for others, not so much.
Imagefiniki, the Canadian financial wiki
“It doesn't matter how beautiful your theory is, it doesn't matter how smart you are. If it doesn't agree with experiment, it's wrong.” [Richard P. Feynman, Nobel prize winner]
steves
Veteran Contributor
Veteran Contributor
Posts: 3200
Joined: 01 Mar 2005 15:02
Location: Hornby Island BC
Contact:

Re: The After-Tax Spending Plan

Post by steves »

I don't acknowledge a fixed retirement date.... there is just one continuum:- periods when there is more money coming in than can be spent and periods when the reverse occurs.

Say you plan for a 3 year sabatical in 4 years and come back into the workforce for ten years (at a lower salary). Or you expect a cash surprise such as selling the cottage in 10 years or receiving an inheritance in the future.

It can get complimacated.
Live Rich, Die Broke (but not too soon).
User avatar
adrian2
Veteran Contributor
Veteran Contributor
Posts: 13333
Joined: 19 Feb 2005 08:42
Location: Greater Toronto Area

Re: The After-Tax Spending Plan

Post by adrian2 »

longinvest wrote: 23 Sep 2017 05:35 I've built an after-tax spending plan.
[,,,]
I initially developed it for myself, so it's currently using QC tax parameters and QPP. It could be improved to be usable during retirement, too.

1) Would there be some interest for such improvements and getting this spreadsheet to support other provinces and CPP?
Yes, please!
Imagefiniki, the Canadian financial wiki
“It doesn't matter how beautiful your theory is, it doesn't matter how smart you are. If it doesn't agree with experiment, it's wrong.” [Richard P. Feynman, Nobel prize winner]
User avatar
adrian2
Veteran Contributor
Veteran Contributor
Posts: 13333
Joined: 19 Feb 2005 08:42
Location: Greater Toronto Area

Re: The After-Tax Spending Plan

Post by adrian2 »

steves wrote: 23 Sep 2017 15:14 It can get complimacated.
Complimacated, indeed! :)
Imagefiniki, the Canadian financial wiki
“It doesn't matter how beautiful your theory is, it doesn't matter how smart you are. If it doesn't agree with experiment, it's wrong.” [Richard P. Feynman, Nobel prize winner]
steves
Veteran Contributor
Veteran Contributor
Posts: 3200
Joined: 01 Mar 2005 15:02
Location: Hornby Island BC
Contact:

Re: The After-Tax Spending Plan

Post by steves »

Even more so when you account for the fact that income tax jumps all over the map as various forms of capital and sources of income come in and out of play over time.
Live Rich, Die Broke (but not too soon).
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

Adrian,
adrian2 wrote: 23 Sep 2017 15:12
longinvest wrote: 23 Sep 2017 11:41 Of course, they would use their CPP monthly estimate at age 65 (assuming no future contribution) as the "current amount (monthly)" QPP entry. (Does the CRA provide this information? If not, one could just put $0.)
Unfortunately, the CPP website estimate is (sometimes) very misleading. It's not clear from the verbiage, but the estimate assumes that you would continue to contribute to CPP, from now until retirement, at the same average rate you have contributed from age 18 until now. For some, it may be a reasonable assumption; for others, not so much.
One obvious solution would be to ask the user for his full CPP contribution history, but this would be painful for everybody.

I was quite proud of my trick of using QPP's "current amount (monthly)" at age 65 from one's last QPP statement (as opposed to the "projected amount (monthly)" at age 65, also provided on the statement) to make a pretty accurate age 70 projection combining this single number with the current age, target retirement age, and current salary.

I could try to reconstruct an approximate "current amount" at age 65 based on CPP's "projected amount", and then use it like I do with the QPP's current amount. I'll have to think about it to see how much precision would be lost by such a reconstruction.

Added: OK, I found my answers. I could work with the CPP's "projected amount"; it's actually pretty easy to convert it back into a good-enough "current amount".
Last edited by longinvest on 23 Sep 2017 23:21, edited 5 times in total.
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

Steve,
steves wrote: 23 Sep 2017 15:14 I don't acknowledge a fixed retirement date.... there is just one continuum:- periods when there is more money coming in than can be spent and periods when the reverse occurs.

Say you plan for a 3 year sabatical in 4 years and come back into the workforce for ten years (at a lower salary). Or you expect a cash surprise such as selling the cottage in 10 years or receiving an inheritance in the future.

It can get complimacated.
Obviously, this spreadsheet is a broad strokes tool. Yet, it can be useful. Being a free and open spreadsheet, it can be very easily customized by its users.

Let's be clear. It's in no way a replacement for a full-fledged software such as RRIFmetic, which can do full precision tax calculations, Monte-Carlo simulations, generate detailed cash-flow projections, etc. RRIFmetic can be an extremely useful tool. It can also be a learning tool for people who naively expect markets to work like a savings account, having never been exposed to the complexity of financial planning with uncertain markets returns.

Markets don't care about our personal objectives and plans. They'll behave however they want. So, even if the spreadsheet had accurate projections to the fifth decimal, markets would just make fun of such false precision.

I think that the proposed spreadsheet is much better than flying by the seat of one's pants, or using crude calculations which ignore QPP/CPP/OAS pensions and taxes.
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
User avatar
Quebec
Veteran Contributor
Veteran Contributor
Posts: 1645
Joined: 24 Oct 2009 16:49
Location: Quebec City

Re: The After-Tax Spending Plan

Post by Quebec »

longinvest wrote: 23 Sep 2017 05:35 (...) calculate how much one can spend during the upcoming year (...) for people in their accumulation period. (...)
I understand the VPW strategy during retirement. You don't want to make a large withdrawal right after a crash.

But I don't quite get the logic of saving more after a bad year in the markets, during accumulation: is that what you're going with this spreadsheet, like VPW in reverse (variable percentage contributions)? Or are you trying to equalize consumption throughout the life cycle?

That's not how I think about saving money for retirement. I aim to save a fixed percentage of gross income. Now life happens, so reality is not a fixed percentage, but I aim to save a fixed %, cause it's easy to understand. If I don't save enough this year relative to my goal, I'll try to save more next year. A negative return this year does not mean my plan is ruined and have to save way more next year: I just keep saving as much as possible, and eventually things should be fine. To make sure I'm on track, I can do a retirement projection (spending and income) for different ages, etc. and revisit this every five years. If I'm not on track, I can then revisit the constant savings rate, projected retirement age, etc.

Anyway my point is I don't understand what you're trying to do here, can you please explain the philosophy behind the spreadsheet.
Imagefiniki, the Canadian financial wiki: a knowledge base of financial subjects written from a Canadian perspective
User avatar
AltaRed
Veteran Contributor
Veteran Contributor
Posts: 33398
Joined: 05 Mar 2005 20:04
Location: Ogopogo Land

Re: The After-Tax Spending Plan

Post by AltaRed »

Ditto thoughts/questions from me. Thought maybe I was not 'getting it' but maybe I am not alone.

Added: The way I look at it is during accumulating years, I do what I can to save and that, together with investment performance/returns, get me to a point somewhere...at some age whereby i can run retirement withdrawal scenarios. There is so much variabilty in one's earning power through the years, as well as the curve balls life throws one's way, and the market roller coaster ride, that doing much in the way of analysis in one's 30s (for example) seems pretty back of envelope.
Imagefiniki, the Canadian financial wiki The go-to place to bolster your financial freedom
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

The spreadsheet informs us about the "equilibrium" monthly spending amount, given the current state of things: current age, planned retirement age, current salary, current portfolio balance, and currently accrued QPP (and eventually CPP) pension.

When we say that one must live "below one's means", this is the "means" number. If one spends anymore than this, one is living above his means.

But, it goes beyond that. The spreadsheet goes beyond the typical "if I save $X per year for Y years at Z%, how much will I accumulate?"

For one thing, it automatically detects how much of the savings can go into an RRSP, and realizes that any additional saving has to be done after paying taxes on the residual income (income - RRSP contribution). It also automatically estimates the mandatory contributions to EI, QPIP, QPP based on one's salary, and also projects the QPP contribution, along with the already accrued amount, into an effective pension amount when delayed to age 70. Of course, one interesting feature is that it automatically calculates taxes to display after-tax budget projections.

Another thing that I wanted to show is that the "minimal savings rate" goes up after bad market years. When you think about it, it's actually a very efficient thing to do. Buy more of the stuff when it is cheap. When markets are expensive and you're ahead of your goals, you could plan for an earlier retirement, but that could be riskier (because of possible reversal) than to simply take advantage of the situation to go ahead with some of the bigger occasional expenses that we have to assume (change an older car needing too many repairs, etc).

I could add a parameter to target a different percentage of after-tax income, in retirement, than work years, such as 200% to double spending in retirement. But, that wasn't really the goal, here. The goal was to show the equilibrium budget.

It's a tool to dispell myths. A median single worker with a $40K salary from age 30 to age 65 doesn't need to accumulate $1M at age 65 to retire in dignity. Of course, he can aim to do so, if he wants, sacrificing current spending in favor of higher retirement spending. But, he only needs to accumulate $337K to preserve his current standard of living, as shown in http://www.financialwisdomforum.org/for ... &mode=view.

Another example: The spreadsheet can serve to expose the practical impossibility of plans which are sometimes touted on the internet. "Save 70% of your salary and retire in 10 years". RIght... Let's see:
retire-at-35.png
retire-at-35.png (27.87 KiB) Viewed 2585 times
OK, a person age 25 with a $40K salary could retire at 35, at the price of living on a $502 per month budget. The good news: the actual savings rate is 64%, lower than the claimed 70%. The bad news: I think that QC Social Assistance is more than $502 per month; why bother with work? And, I'm not done, yet! One will have accrued $4,445 in annual QPP pension, if delayed to age 70. At age 35, one won't have accumulated a big enough portfolio to bridge the gap in CPP and OAS payments from age 35 to 69. Myth busted!

Does that make any sense?
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

I forgot to say: it's a free and open spreadsheet that doesn't use macros. It's easy to modify.

It could be modified in less than 2 minutes to estimate an after-tax retirement budget given a specific savings rate, if one wanted to do such projections, for example.

For the record, I'm not a fan of fixed savings rate. I'm more of a "spend as little as you can and send everything else into the portfolio" type of person (and my wife too, of course). If I ever needed to spend money on a big item, I would do it and not fret about not meeting an artificial "savings rate" objective. Of course, both my wife and I are mostly allergic to spending unless we absolutely have to; so big spending events don't tend to happen much for us. There was such an event, years ago: we put a big down payment on our home. It also happened when we replaced our 10+ years car that was starting to need too many repairs, more than 10 years ago. But, nothing since then...
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

I could easily add an additional parameter:
  • Annual spending budget [ $ XXX ] ← This should be kept as low as reasonably possible
And, from that, find out how much is going into savings to project a Retirement annual spending budget.

The problem, though, is that others will insist on setting the savings rate, or the savings amount. There are as many ways to plan things as there are people.

The "equilibrium budget" idea was kind of neutral. Those who think in terms of spending saw a neutral spending amount. Those who think in terms of savings saw neutral savings amounts and rate.

The neutral budget can be useful. Some people might plan to spend more in retirement; they need to spend less than the neutral budget during accumulation years. Others might plan to spend less in retirement (children gone, mortgage burned); they can spend more than the neutral budget during accumulation years.

Building a spreadsheet which meets all possible needs would make the spreadsheet more complex and, as a consequence, harder to understand and modify.

So I'll leave the neutral budget calculations in place and let users modify their own copy of the spreadsheet to match their own specific plan.

I'm more interested into expanding the spreadsheet to support CPP/other-provinces.
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
User avatar
Quebec
Veteran Contributor
Veteran Contributor
Posts: 1645
Joined: 24 Oct 2009 16:49
Location: Quebec City

Re: The After-Tax Spending Plan

Post by Quebec »

longinvest wrote: 25 Sep 2017 20:43 For the record, I'm not a fan of fixed savings rate. I'm more of a "spend as little as you can and send everything else into the portfolio" type of person (and my wife too, of course).
Yes, in practice, this is what we do too.

But a fixed savings rate target (calculated in such a way that you will be able to retire at age X) is easy to understand. It gives you a goal. You can compare how much you've saved last year and see if you are on track. If not, maybe have a cheaper vacation next year, etc. Or maybe you're saving too much and you can afford a fantastic vacation next year. I guess this is sort of what you're doing here, but taking market returns into account.

I'll play with the spreadsheet when I get a chance.
Imagefiniki, the Canadian financial wiki: a knowledge base of financial subjects written from a Canadian perspective
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

Quebec,
Quebec wrote: 28 Sep 2017 20:30 But a fixed savings rate target (calculated in such a way that you will be able to retire at age X) is easy to understand. It gives you a goal. You can compare how much you've saved last year and see if you are on track. If not, maybe have a cheaper vacation next year, etc. Or maybe you're saving too much and you can afford a fantastic vacation next year. I guess this is sort of what you're doing here, but taking market returns into account.
Here's how I use the spreadsheet.

I target financial independence as early as reasonably possible (while not living like a pauper). I put the target financial independence age into the spreadsheet's "target retirement age" field*. After that age, I'll let my (fluctuating**) living standard increase in a sustainable manner, so that I could leave my job at any point without consequences despite the cumulative increases. In other words, once financial independence is reached, my monthly budget will be determined by setting the "target retirement age" equal to the "current age" (updating the spreadsheet's inputs every year), and I'll continue sending any residual incoming money into savings.

* To tell the truth, I cheat; I change the target age until the monthly budget matches our "as little spending a reasonably possible" budget. It lets me know when we'll approximately reach financial independence.
** The spreadsheet determines a monthly budget which fluctuates every year.

Of course, it would be nice to be super optimistic and put 65 as target retirement age (as I really like my job), but that would be quite imprudent. While it would allow for much higher spending today, it could leave me in a vulnerable position where I would realize that I had overspent, if I were to lose my job before 65. I prefer to target an earlier financial independence age.

In my personal copy, I added fields to deal with my DB pension, like contributions (which significantly reduce RRSP contribution space) and pension projections (which automatically adjust according to the selected retirement year, based on the specific rules of my DB plan). Here's a post I wrote about dealing with the lack of inflation indexing: Any way to "translate" a fixed pension/annuity to an inflation adjusted one? - Bogleheads.org
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
User avatar
Quebec
Veteran Contributor
Veteran Contributor
Posts: 1645
Joined: 24 Oct 2009 16:49
Location: Quebec City

Re: The After-Tax Spending Plan

Post by Quebec »

longinvest wrote: 28 Sep 2017 21:49 In my personal copy, I added fields to deal with my DB pension, like contributions (which significantly reduce RRSP contribution space) and pension projections (which automatically adjust according to the selected retirement year, based on the specific rules of my DB plan).
Yes. I've tried the spreadsheet but since the PV of the DB pension isn't factored in the "current portfolio balance", I get an artificially high required savings rate. I could use the transfer value of the pension as a 1st approximation. But then the "Projected retirement" calculations also have to be changed. Maybe a project for a rainy weekend.

Basic question: I have two monthly amounts at age 65 on my QPP "Relevé de participation", one if I stop working, the other if I continue. Which number do I want to input on the spreadsheet?
Imagefiniki, the Canadian financial wiki: a knowledge base of financial subjects written from a Canadian perspective
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: The After-Tax Spending Plan

Post by longinvest »

Quebec wrote: 12 Mar 2018 17:20 Basic question: I have two monthly amounts at age 65 on my QPP "Relevé de participation", one if I stop working, the other if I continue. Which number do I want to input on the spreadsheet?
I enter the amount at 65 if I stop working. The spreadsheet then adjusts the amount based on the chosen retirement age and the current salary.
Quebec wrote: 12 Mar 2018 17:20
longinvest wrote: 28 Sep 2017 21:49 In my personal copy, I added fields to deal with my DB pension, like contributions (which significantly reduce RRSP contribution space) and pension projections (which automatically adjust according to the selected retirement year, based on the specific rules of my DB plan).
Yes. I've tried the spreadsheet but since the PV of the DB pension isn't factored in the "current portfolio balance", I get an artificially high required savings rate. I could use the transfer value of the pension as a 1st approximation. But then the "Projected retirement" calculations also have to be changed. Maybe a project for a rainy weekend.
I created a formula that estimates my pension at age 65 based on the age I retire. I discount the amount by 2% for each year between retirement and 65, to account for inflation. I also calculate the required bridge between retirement and 65, which is: discounted-pension X (65 - retirement age).

I reduce the RRSP percentage from 18% to the percentage I can effectively contribute.

In other words, in the on the main sheet, the right column lines are:
(*** xx *** indicates a modification)

Code: Select all

EI contribution: 
QPIP contribution: 
QPP contribution: 
*** Pension contribution: *** 
Taxes: 

Portfolio balance: 
Part of portfolio transferred, at retirement, into a high-interest savings account or a GIC ladder 
as a bridge to fill the gap in OAS, QPP, *** and pension *** payments from retirement to pension start: 
Remaining portfolio (for VPW withdrawals): 
OAS (delayed to age 70 with bridge): 
QPP (delayed to age 70 with bridge): 
*** pension (at age 65 with bridge): *** 
VPW portfolio withdrawal: 
Total income: 
OAS clawback: 
Taxable income: 
Taxes: 
Net income: 
The OAS and QPP bridges run until age 70, and the pension bridge runs until age 65.
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
Post Reply