Spreadsheet to monitor pensions

cfw1994

ScreamingLordCrutch said:

I've found this one:
https://whatapalaver.co.uk/retirement-planning-couples

I think I'll give it a go.

I'm going to re-quote this one - I've spent some time today on this one - & I think it is excellent!

I like the way the outgoings lay things out - regular & variable obligations, regular & variable discretionary, & annual subscriptions.   Made me run through our spending over 2020 again (I use Quicken, so easily done).  Also the option to put splurges, so major car refresh plans, major birthdays, etc can all be dropped in.  

Obviously this kind of thing is always 'crystal ball gazing' to some degree, but the spreaddie kind of confirmed the numbers I was working to should work fine for us....reassuring!
Not so sure on the order of 'where' the money is taken from - it looks like it treats the DC Pot last, taking from other investments first.   From an inheritance tax that might make sense, but I think it needs some tweaking.....

It is also avoiding complex inflation/growth questions by just treating all savings at a rate above inflation that you chose - I prefer to be able to vary that (eg, cash/PBonds might only be 0.5%, or 1% if we include cautious funds, main DC pension might be a cautious 3%, but stock outside that might hope to get 6-7%) & more easily see things year by year.

Overall, however, as a tool, I'd rate this spreaddie right up there with mine in terms of usefulness & usability - thanks for the find, @ScreamingLordCrutch !

Notepad_Phil

cfw1994 said:
It is also avoiding complex inflation/growth questions by just treating all savings at a rate above inflation that you chose - I prefer to be able to vary that (eg, cash/PBonds might only be 0.5%, or 1% if we include cautious funds, main DC pension might be a cautious 3%, but stock outside that might hope to get 6-7%) & more easily see things year by year.

I take it that you can enter negative values too - e.g. I tend to plot cash as losing a minimum of 1% to inflation ever year. Historically that has underestimated the returns I actually get, but I think going forward it will be a lot harder to get cash savings to match inflation.

cfw1994

Notepad_Phil said:

cfw1994 said:
It is also avoiding complex inflation/growth questions by just treating all savings at a rate above inflation that you chose - I prefer to be able to vary that (eg, cash/PBonds might only be 0.5%, or 1% if we include cautious funds, main DC pension might be a cautious 3%, but stock outside that might hope to get 6-7%) & more easily see things year by year.

I take it that you can enter negative values too - e.g. I tend to plot cash as losing a minimum of 1% to inflation ever year. Historically that has underestimated the returns I actually get, but I think going forward it will be a lot harder to get cash savings to match inflation.

Only if you apply that to all investments on that spreaddie, so far as I can see: looks like there is one rate for all.
That is one area I prefer mine, I can tweak and adjust different pots as I wish.   
BUT, nonetheless, a decent sheet.   All unprotected too, so if you are handy with google sheets, you could probably hack away to your heart's content!

ScreamingLordCrutch

Thanks for all the replies - I'm going to try the one I referred to, but it doesn't take mortgages into account so I'll need to tweak it a bit for that.

whatapalaver_codes

ScreamingLordCrutch said:

Thanks for all the replies - I'm going to try the one I referred to, but it doesn't take mortgages into account so I'll need to tweak it a bit for that.

Hi - I created the spreadsheet whatapalaver.co.uk/retirement-planning-couples 
I'm interested in tweaking it so meets other people needs as well as my own. I forgot about mortgages as we recently paid ours off. It is a good one to add though on the basis that people are likely to finish repayments before they plan to take retirement so can't just handle it in the pre-retirement budget.

laxlau

whatapalaver_codes said:

ScreamingLordCrutch said:

Thanks for all the replies - I'm going to try the one I referred to, but it doesn't take mortgages into account so I'll need to tweak it a bit for that.

Hi - I created the spreadsheet whatapalaver.co.uk/retirement-planning-couples 
I'm interested in tweaking it so meets other people needs as well as my own. I forgot about mortgages as we recently paid ours off. It is a good one to add though on the basis that people are likely to finish repayments before they plan to take retirement so can't just handle it in the pre-retirement budget.

Well, this is serendipitous - I was going to post a comment on your blog to thank you and point out a small error, and then also add a note here for anyone else that started using it; can kill two birds with one stone now.

first up - thank you!!  Your spreadsheet has helped me figure out how to keep an eye on our retirement approach without having to fudge two people’s numbers into a single sheet, and now that I can do that it has kickstarted getting a grip on the constituent parts (existing pension provision; understanding our spending etc)... I don’t think I’m exaggerating to say I’ll be able to retire earlier because of you putting this spreadsheet out there (at the very least I’ll be more confident I have enough when I do go, which means I will go earlier!!)

The error I think is that the pension columns on the networth sheets (N-P) don’t correctly reference the age column (C).  They are using column D and E to compare to the selected retirement age on the config sheets in the IF statement... basically missing the absolute reference $ in front of the C column reference when the formula was copied across to the Pension 2 - 4 calcs.

Thanks again.

cfw1994

whatapalaver_codes said:

ScreamingLordCrutch said:

Thanks for all the replies - I'm going to try the one I referred to, but it doesn't take mortgages into account so I'll need to tweak it a bit for that.

Hi - I created the spreadsheet whatapalaver.co.uk/retirement-planning-couples 
I'm interested in tweaking it so meets other people needs as well as my own. I forgot about mortgages as we recently paid ours off. It is a good one to add though on the basis that people are likely to finish repayments before they plan to take retirement so can't just handle it in the pre-retirement budget.

Welcome to the forum!
You've made a really great sheet: thanks for sharing (& thanks to ScreamingLordCrutch for finding it!).

One tiny useful addition would be to enable the different investments to be shown with differing returns - cash/premium-bonds would (IMHO!) not warrant more than 1% pa, which would lose out to inflation, but equity might be expected to average perhaps 5-7% (depending how bullish you feel!).   Maybe over years some might get some fixed funds in between.  
BUT: overall, for me (mere months from ending my day job!), & having worked on my own spreaddie for some years, I found it has been really useful (& I have looked at a few, believe me!).  

My version tries to show the effects of inflation.....I think yours doesn't?     So mine shows the pension amounts rising, as well as the income desired going up.   My goal being that each row (each year) can be updated with actual numbers (including any 'real world' DC Pot Year end numbers, etc), and the future numbers roll down from there....who knows if it will work?  Only time will tell!

whatapalaver_codes

laxlau said:

 I don’t think I’m exaggerating to say I’ll be able to retire earlier because of you putting this spreadsheet out there (at the very least I’ll be more confident I have enough when I do go, which means I will go earlier!!)

Well thank you - that's very nice to read.
Someone has already pointed out that error with cols N:P and I have corrected. Unfortunately there isn't a way to inform people of revisions if I've updated the document. I have now added a version history tab to explain/date any changes but it would require people to go back to the original shared copy.

I have also just created a V3 revision which has the option to add a monthly repayment value for a mortgage with an end date that differs from your retirement age.

whatapalaver_codes

cfw1994 said:

One tiny useful addition would be to enable the different investments to be shown with differing returns - cash/premium-bonds would (IMHO!) not warrant more than 1% pa, which would lose out to inflation, but equity might be expected to average perhaps 5-7% (depending how bullish you feel!).   Maybe over years some might get some fixed funds in between.  

Hi. I have now posted an updated link to the post which enables you to alter the assumptions for different categories of investments.

You can now add an 'Assumed Rate for Investments' for two categories of investments and for 5 different pension pots.

Thanks to everyone who has offered feedback and suggestions.

cfw1994

Right: as your admin secretary, I can tell you the change log date has the wrong year for v5.....
.......but I'll forgive that error - thanks for making those changes, it is a VeryGoodSheet™