This is where a lot of the previous sections all come together into one integrated model.
This page is packed with stuff and it’s heavy going, I’m afraid. But totally worth it.
How I use these models
I had a few simpler versions of this spreadsheet, mostly to consider a particular decision such as whether to transfer a DB scheme. Once I got it how you see it now, I loved it. It will show me how to move money around tax efficiently, how much should be in each account at the end of each year, and if in reality I find the value is high or low vs target, I can edit the future spend a little so that I get to the same end point.
To really do a good job of minimising tax, I need to understand the maths so I can adjust the various payments myself. It doesn’t seem sensible to me to ask a financial adviser to recalculate this every year. I’d expect an adviser to get it roughly right (and flag any new rules), but I have a chance to do better as long as I understand the latest rules each year. When I’m age 54 I’m thinking of checking my plans with an adviser for a fee, and then age 55 lots starts happening.
At the moment I have several of these sheets modelling different retirement dates and different investment yields, and that’s much easier to do in “today’s money”. When I’m 55 this sheet will be converted to an “Actual Money” sheet with past years being over-typed with actual numbers. The “Rough Min Assets After Tax” (SIPP x 85%, plus drawdown x 80%, plus ISA x 100%) will be a guide each year so I can see whether we’re overspending or underspending. This is the same principle as our “long term budget” where we compare our ISA value to the expected year-end ISA value.
Leaving aside some inheritance tax reasons (but see the “inheritance tax” page because it’s important to understand), I want to move money across to my ISAs in as tax-efficient a way as possible. The (impossible) dream is to get my SIPP/DC money moved untaxed into my ISA where I can access it tax-free each month.
So I will start to move from SIPP/DC into Drawdown from age 55. I will take 25% of that tax free each time, and put it in an ISA (mine and/or my wife’s).
When I retire from my main job, and I start to take money from my drawdown account, I will try to keep income in the 20% bracket, not 40%. As explained in the IHT section, there can be good reasons to leave some money in drawdown anyway. The tax-free sums provide a buffer that means I can have a spend-profile higher than the annual taxable pension withdrawals for several years.
Over the 50 years that the model shows, it’s certain that tax rules will change. The model shows current rules – what else can I do?
Important – Most of of the numbers here are not mine. I have changed retirement planned dates and significantly changed the values of SIPP funds and DB pensions to make some points.
The model automatically maximises the amount taken from drawdown/DB/State/employment to get taxed at 20% (i.e. just below the 40% threshold). At the extreme right (red rectangle) is a calculation of the surplus being added to ISAs. The amount moved from SIPP/DC to drawdown has been manually adjusted (red rectangle) to make the ISA additions total about 40k (the limit for 2 people). Nice theory, but for myself I suspect I will over-withdraw tax free so that a couple of boom years for equities don’t take me over the lifetime allowance.
As the spouse is going to have income below the personal allowance, if only for a few years until the state pension starts, then they can transfer 10% of their allowance to the main wage earner (as long as that person will not be at 40% tax). In the spreadsheet I’ve shown that.
In this sheet you can see the main wage earner on the left side headed purple, then a lower-taxed spouse headed in blue, and finally some analysis on the right in green.
White cells have maths (including “equals the row above”).
Orange cells are a typed-in value.
Top left you see it’s a “today’s money” sheet with no inflation.
Then you see in blue the sum being budgeted to spend out of current accounts each year up to age 74.
In light blue are some totals up to age 90: taxable pension, tax-free pension, tax paid. It’s just to show that these 2 people’s pensions will be taxed at about 12% and 2% overall.
The sheet goes to age 105 and the final Drawdown & ISA monies are reflected in blue cells at the top of the sheet.
In dark green cells you see the Lifetime Allowance. I’ve picked a start value for the SIPP to show how easy it is to use 100% of the allowance, even though in this model I’ve done most of the things to minimise how much of the allowance I use. Moving into drawdown late or getting greater growth will put the model further over the lifetime allowance limit. (I know LTA is accumulated as a percentage, not in pounds, but this spreadsheet is approximately correct and the error will be swamped by equity variability.)
You can see that if growth is RPI+3%, the main earner can stay at 20% marginal tax throughout, and the spouse at 0% until the state pension kicks in.
I’ve started the model at age 54 because it shows one baseline year and then a flurry of activity at age 55.
You can increase the spouse’s SIPP start value to see what level consumes all the 0% tax allowance between retirement and age 67 (highlighted in a red rectangle), so you can see what extra SIPP contributions could be worthwhile. That money comes back out after the planned withdrawals. You can see that if the main earner adds to their SIPP then they get the tax-free part when they’re about 61, and the taxable part at age 88. Whereas if the spouse adds to a SIPP, then they can get the tax-free part out when they’re about 56, and the taxable part when they’re about 62-66.
The main earner needs to move more money from SIPP/DC to drawdown earlier. If the spouse delays moving from SIPP/DC to drawdown then the main earner can move more sooner. Of course, they could ignore trying to get money into an ISA and just move more sooner anyway. They might also have other costs in mind that would consume this money, but then we should boost the spend profile.
When the main earner starts to take payments from the DB pension, the lifetime allowance impact is 20 times the annual payment, plus any income-tax free lump sum. If it is taken earlier than the default date (red highlight rectangle) and therefore get an actuarially reduced pension, then it’s 20 times the reduced amount, plus the reduced lump sum. The reduction also reduces the use of the lifetime allowance. BUT taking this pension also triggers the annual allowance which restricts pension contributions, so it’s painful to start it before retirement (before 58 in this model).
Once the main wage earner stops withdrawing from drawdown, their income is below the 0% tax allowance. They can transfer up to 10% of allowance (red rectangle) to their spouse who has the bigger income now.