Retirement Planning Spreadsheet example

BogStandardBob

I have searched but can't find one. Can anyone please share your retirement planning spreadsheet or recommend a free download of one? Thanks a lot.

MallyGirl

maybe start by looking at a free product like guiide

Exodi

Since you asked for a spreadsheet specifically, you could check out Microsofts templates, e.g: https://create.microsoft.com/en-us/template/retirement-financial-planner-9fd421b1-67bb-4a95-a054-84eee7f21062

Depending on your excel knowledge (good knowledge on the FV function) you could create one yourself.

If you wanted a more detailed report you could register for a £1 trial of timeline - https://www.timeline.co/

It's typically used by financial advisers (so make sure you cancel the trial after) but it's intuitive to use.

tacpot12

You can try out a web-based application for retirement planning for free at https://evolvemyretirement.com/ 

This might be sufficient. 

You might also look at cFIREsim if you want to model the outcomes of different approaches to retirement using historical stockmarket data, e.g. to find your Safe Withdrawal Rate if you are planning to draw down from any private/Defined Contribution pensions  

Notfarfromtheborder

Not sure what you mean re retirement planning spreadsheet, however, this may be worth a look.

Make your own retirement income cashflow model, stress test and capacity for loss on Excel. (youtube.com)


Pretty easy to follow should you wish to build a cashflow model

sgx2000

Notfarfromtheborder said:

Not sure what you mean re retirement planning spreadsheet, however, this may be worth a look.

Make your own retirement income cashflow model, stress test and capacity for loss on Excel. (youtube.com)


Pretty easy to follow should you wish to build a cashflow model

This is a brilliant starting point

barnstar2077

ChatGPT, or bottom right on windows (if it is supported on your version), is very good at solving spreadsheet problems. 

For instance, you can paste in a cells formula, explain what you are trying to do and ask it why it won't work.  It is great at picking up simple formatting errors etc.

I dare say you could probably have it help out a lot more if needed.  As in, "When making a spreadsheet, If F3 is 100 and G4 is 6, what do I need to type into D5 to have that cell divide F3 by G4" etc.

Using the before mentioned video tutorial and an AI to answer any specific questions would be a great place to start.

Edit:  I use Googlesheets, accessed through Google Drive, as it is free and I can access it from my phone and my computer easily.

barnstar2077

Notfarfromtheborder said:

Not sure what you mean re retirement planning spreadsheet, however, this may be worth a look.

Make your own retirement income cashflow model, stress test and capacity for loss on Excel. (youtube.com)


Pretty easy to follow should you wish to build a cashflow model

Doesn't take income tax, personal allowance etc into account, but by the time someone has finished doing the above they should be capable of adding in those details themselves.

sgx2000

barnstar2077 said:

Notfarfromtheborder said:

Not sure what you mean re retirement planning spreadsheet, however, this may be worth a look.

Make your own retirement income cashflow model, stress test and capacity for loss on Excel. (youtube.com)


Pretty easy to follow should you wish to build a cashflow model

Doesn't take income tax, personal allowance etc into account, but by the time someone has finished doing the above they should be capable of adding in those details themselves.

Just exactly what I did.....
He also did a drawdown spreadsheet i.e.  inflation / returns effects etc

The only thing that is missing would be a historical stocks return calculator i.e. monte carlo simulation
But that would require a vast amount of info from historical returns...... 
lol....Quite a bit past my  very amateur spreadsheet skill level.......

Moonwolf

sgx2000 said:

barnstar2077 said:

Notfarfromtheborder said:

Not sure what you mean re retirement planning spreadsheet, however, this may be worth a look.

Make your own retirement income cashflow model, stress test and capacity for loss on Excel. (youtube.com)


Pretty easy to follow should you wish to build a cashflow model

Doesn't take income tax, personal allowance etc into account, but by the time someone has finished doing the above they should be capable of adding in those details themselves.

Just exactly what I did.....
He also did a drawdown spreadsheet i.e.  inflation / returns effects etc

The only thing that is missing would be a historical stocks return calculator i.e. monte carlo simulation
But that would require a vast amount of info from historical returns...... 
lol....Quite a bit past my  very amateur spreadsheet skill level.......

I built my scenarios and put my basic assumptions in a spreadsheet but do that sort of historical return stuff on guiide.

My first tab is just a collection of my pensions with projections for how they will grow. Deferred DB don't change, my 2015 NHS pension I add 1/54th my current salary and revalue at 1.5% for the years I am working. My DC uses my equity growth figures.  All are corrected/adjusted when I get my annual statements and any payrise information.

I ignore inflation on the grounds that my DB will grow by CPI, predicting inflation and growth separately gives me two things to get wrong, and in any event my personal inflation might be different again.  Much easier to just predict growth over inflation so I have a global growth over inflation figure which I reply to equities and a separate negative growth figure for cash.  Generally I set these at 2.5% and -1% but I test with different combinations.

I then have a tab for retiring at 59, retiring at 60 and so on.  I've deleted the ones from 55 where I have already got older than that.  Each retirement year has a column for each age to 90.  Income is read from my pension predictions tab for the year I retire, most are flat from then but for the DC, the first column reads the start value, each subsequent column reduces the pot by what I have drawn down and revalues the remainder by growth over inflation.

I have income lines for each pension including state pension so for some the income is partial in the first year.  Annual age columns could use financial year, calendar year, age year or year from retirement.  I've gone with financial year as that is when I think most fixed numbers will change 

I have a line for cash savings which go down by my -ve growth figure. 

I have a drawdown line. My drawdown plan is segmented across years out depending how much DB I expect in that year, so from 68 onwards all the years are the same.

This gives me a gross income line.

My drawdown will be UFPLS or UFPLS style so I have a line for the 25% tax free and then a line which is taxable income.  I subtract the personal allowance from that which I have assumed grows by inflation from 2028 but reduces by 2% in real terms until then, a line for tax paid and a line for net "take home" calculated from the above.

I then have a line for annual expenditure that is just 12 x the total from a monthly budget tab.  Any difference between income and expenditure in a year is added to (or subtracted from) my savings.

I also have lines for what my partner will get if I die in any year, spouses pensions rom my DB and the remaining DC.

I can change my growth assumptions and see when I would run out of DB pension, I can also slash my DC starting figure.  But putting the numbers in guiide with different retirement ages gives me more confidence. I can also trim my monthly budget.

There are no complex calculations or fancy spreadsheet functions, just a few core numbers at key points that I can change at will.  For my job I've developed complex macro driven spreadsheets using advanced functions but I struggle to see the utility here.

I can see that if I retire next March and draw down enough to match my current take home income, which is about 5% more than my budget, I will run out of DC by age 90 at 2.5% growth, 86 at 2% growth and 84 at 1.5% growth. Fortunately I have enough DB and state pension to live comfortably so I'm almost certainly going then.

There are all sorts of small holes in the calculations, assumptions about tax allowances and growth calculations that are too big or little as they don't cover in year changes, but in my opinion the plans and predictions are pretty noisy anyway so don't sweat the small stuff.

MallyGirl

Moonwolf said:

sgx2000 said:

barnstar2077 said:

Notfarfromtheborder said:

Not sure what you mean re retirement planning spreadsheet, however, this may be worth a look.

Make your own retirement income cashflow model, stress test and capacity for loss on Excel. (youtube.com)


Pretty easy to follow should you wish to build a cashflow model

Doesn't take income tax, personal allowance etc into account, but by the time someone has finished doing the above they should be capable of adding in those details themselves.

Just exactly what I did.....
He also did a drawdown spreadsheet i.e.  inflation / returns effects etc

The only thing that is missing would be a historical stocks return calculator i.e. monte carlo simulation
But that would require a vast amount of info from historical returns...... 
lol....Quite a bit past my  very amateur spreadsheet skill level.......

I built my scenarios and put my basic assumptions in a spreadsheet but do that sort of historical return stuff on guiide.

My first tab is just a collection of my pensions with projections for how they will grow. Deferred DB don't change, my 2015 NHS pension I add 1/54th my current salary and revalue at 1.5% for the years I am working. My DC uses my equity growth figures.  All are corrected/adjusted when I get my annual statements and any payrise information.

I ignore inflation on the grounds that my DB will grow by CPI, predicting inflation and growth separately gives me two things to get wrong, and in any event my personal inflation might be different again.  Much easier to just predict growth over inflation so I have a global growth over inflation figure which I reply to equities and a separate negative growth figure for cash.  Generally I set these at 2.5% and -1% but I test with different combinations.

I then have a tab for retiring at 59, retiring at 60 and so on.  I've deleted the ones from 55 where I have already got older than that.  Each retirement year has a column for each age to 90.  Income is read from my pension predictions tab for the year I retire, most are flat from then but for the DC, the first column reads the start value, each subsequent column reduces the pot by what I have drawn down and revalues the remainder by growth over inflation.

I have income lines for each pension including state pension so for some the income is partial in the first year.  Annual age columns could use financial year, calendar year, age year or year from retirement.  I've gone with financial year as that is when I think most fixed numbers will change 

I have a line for cash savings which go down by my -ve growth figure. 

I have a drawdown line. My drawdown plan is segmented across years out depending how much DB I expect in that year, so from 68 onwards all the years are the same.

This gives me a gross income line.

My drawdown will be UFPLS or UFPLS style so I have a line for the 25% tax free and then a line which is taxable income.  I subtract the personal allowance from that which I have assumed grows by inflation from 2028 but reduces by 2% in real terms until then, a line for tax paid and a line for net "take home" calculated from the above.

I then have a line for annual expenditure that is just 12 x the total from a monthly budget tab.  Any difference between income and expenditure in a year is added to (or subtracted from) my savings.

I also have lines for what my partner will get if I die in any year, spouses pensions rom my DB and the remaining DC.

I can change my growth assumptions and see when I would run out of DB pension, I can also slash my DC starting figure.  But putting the numbers in guiide with different retirement ages gives me more confidence. I can also trim my monthly budget.

There are no complex calculations or fancy spreadsheet functions, just a few core numbers at key points that I can change at will.  For my job I've developed complex macro driven spreadsheets using advanced functions but I struggle to see the utility here.

I can see that if I retire next March and draw down enough to match my current take home income, which is about 5% more than my budget, I will run out of DC by age 90 at 2.5% growth, 86 at 2% growth and 84 at 1.5% growth. Fortunately I have enough DB and state pension to live comfortably so I'm almost certainly going then.

There are all sorts of small holes in the calculations, assumptions about tax allowances and growth calculations that are too big or little as they don't cover in year changes, but in my opinion the plans and predictions are pretty noisy anyway so don't sweat the small stuff.

are you actually me? My spreadsheet has a huge number of similarities the the above. The only difference is that I don't model death of one of us - almost everything is in DC so it will go to the other one automatically.
I have also used guiide and am looking forward to investigating the couples support