Page 1 of 1

Planning it out!

Posted: January 10th, 2017, 10:40 am
by monabri
I decided (many years ago) to set up a spreadsheet that would help me plan my financial requirements until the age of 90. At the top of the spreadsheet I have the years laid out horizontally. Somewhat schematically, it is laid out like this (i won't draw the vertical lines in!) :-

2017 2018 2019 2020 2021 2022 etc etc
-------------------------------------------------------------------------------------------------------------------------------------------------
Pension 1
Pension 2
State Pension 1
State Pension 2
-------------------------------------------------------------------------------------------------------------------------------------------------
Initial Savings amount
Income from savings
Income from "other" (eg BTL)
-------------------------------------------------------------------------------------------------------------------------------------------------
Drawdown - Person 1 this is personal spending money [these sums reduce the balance]
Drawdown - Person 2
-------------------------------------------------------------------------------------------------------------------------------------------------
Treat money - holidays/new car/whatever you want [these sums reduce the balance]
------------------------------------------------------------------------------------------------------------------------------------------------
House running costs including food (calculated in a separate sheet with the number fed through to this sheet)
-------------------------------------------------------------------------------------------------------------------------------------------------
Tax allowance
Tax due -Person 1
Tax due Person 2
--------------------------------------------------------------------------------------------------------------------------------------------------
Net Balance to take forward to the top of the next column.
-------------------------------------------------------------------------------------------------------------------------------------------------

I also have included that facility to increase any of these lines by an inflationary amount. This provided me with a discipline which I have have held to for the last 15 years.

The planning tool then allows you to say "what happens if inflation reaches x %", "I buy a new car in year X", I spend £ x thousands on holidays etc. It allows you to model different scenarios to ensure you don't run out of money but - just as important for me - we don't die leaving loads of money in IHT.

Ok, I might not live to 90 (none of my parents have) but my wife has a better chance. Of course, you could also include the injection of lump sums into your spreadsheet for unexpected/expected windfalls (inheritance perhaps).

I think it is important to plan it out so you have some idea of what is possible - there is no point working until 70 to discover that you might have had sufficient funds 5 years earlier.

Re: Planning it out!

Posted: January 10th, 2017, 1:12 pm
by StepOne
This looks great monabri.

As I approach 50 it's the sort of thing I have been doing now and again in temporary spreadsheets, but I like the look of yours and am excited about the thought of setting up something similar (that's how sad my life is!)

I did something very roughly a couple of weeks ago which kind of implied I might not need to be saving as much as I am. This could be a bit liberating for me, as I do prefer to do things such as save, and overpay the mortgage, instead of spending it on things like expensive holidays with my family. My wife keeps reminding me that these kids are only young once. Of course I remind her that I never went further than Glasgow on a Summer holiday, and even then we stayed with my Dad's cousins rather than fork out for accommodation! This line of reasoning has yet to win her over. Perhaps another spreadsheet in my armoury might convince me she's been right all along!

Cheers
StepOne

Re: Planning it out!

Posted: January 10th, 2017, 3:31 pm
by monabri
That's another way of looking at it (saving too much). I also used it for ensuring that I paid the minimum tax when it comes to taking pensions but that's fine tweaking.

I would suggest adding a graph (x-axis = year : Y axis = fund balance) so you can tweak things (like how much you will drawdown to spend each year) and visually see what the effect is. You could then model different scenarios (what if inflation is x%) etc.

Re: Planning it out!

Posted: January 11th, 2017, 3:57 pm
by uryjm
Hi Monabri
I like the look of your plan!
I'd love a copy of the spreadsheet - not with your personal details of course! - but just to give me an idea how it works. No doubt my wife and I have relatively simple pensions, but they're a mixture of DB and DCs, some we can take at 55, some at 60, some at 62 and then the state pension.....plus you can take a lump sum, or all of it as a plain monthly income....
I have two years until I hit 55 and am relatively well provided for (I think!). I am struggling with they myriad of options though. What did you find best as a starting point? Was it to decide a target annual income? Or the maximum tax efficient you could take?
Anyway, I love planning and you have inspired me to do a bit more :D

Re: Planning it out!

Posted: January 11th, 2017, 5:32 pm
by genou
uryjm wrote:Hi Monabri
I like the look of your plan!
I'd love a copy of the spreadsheet - not with your personal details of course! - but just to give me an idea how it works. No doubt my wife and I have relatively simple pensions, but they're a mixture of DB and DCs, some we can take at 55, some at 60, some at 62 and then the state pension.....plus you can take a lump sum, or all of it as a plain monthly income....



If you are running Windows on your machine, you might want to take a look at the sunset version of MS Money ( which is free ) from here
http://money.mvps.org/downloads/files/2 ... K-QFE2.exe

It has an inbuilt Lifetime Planner / Budget and portfolio manager. The planner allows for "what if I did this....."

Re: Planning it out!

Posted: January 14th, 2017, 10:22 am
by monabri
Hi uryjm, I've sent you an email - if you reply with your email address I will send you a "clean" copy. You will need EXCEL to play around with it.

Re: Planning it out!

Posted: January 14th, 2017, 1:51 pm
by staffordian
monabri wrote:Hi uryjm, I've sent you an email - if you reply with your email address I will send you a "clean" copy. You will need EXCEL to play around with it.


It sounds very much as though you have created something which many here might find useful. In fact after your initial post I had a bash at concocting something similar, which even in it's very crude state is better than I had before, so many thanks for the post!

It's very presumptuous of me, but if it's something you would consider sharing, I wonder if Itsallaguess and kiloran might add it to the software available from their weebly site?

http://lemonfoolfinancialsoftware.weebly.com

Staffordian

Re: Planning it out!

Posted: January 14th, 2017, 1:56 pm
by monabri
Of course I'd share it....no problem. I'll send you a private msg with my email so you can have a shufty!

Re: Planning it out!

Posted: January 14th, 2017, 1:58 pm
by staffordian
monabri wrote:Of course I'd share it....no problem. I'll send you a private msg with my email so you can have a shufty!


Much appreciated!

Staffordian

Re: Planning it out!

Posted: March 19th, 2017, 4:29 pm
by Fromdusktildawn
Monabri
This looks really useful, thank you for sharing. May I have a copy ? Liz

Re: Planning it out!

Posted: March 20th, 2017, 12:08 pm
by FanciThat
monabri wrote:I decided (many years ago) to set up a spreadsheet that would help me plan my financial requirements until the age of 90. At the top of the spreadsheet I have the years laid out horizontally. Somewhat schematically, it is laid out like this (i won't draw the vertical lines in!) :-

2017 2018 2019 2020 2021 2022 etc etc
-------------------------------------------------------------------------------------------------------------------------------------------------
Pension 1
Pension 2
State Pension 1
State Pension 2
-------------------------------------------------------------------------------------------------------------------------------------------------
Initial Savings amount
Income from savings
Income from "other" (eg BTL)
-------------------------------------------------------------------------------------------------------------------------------------------------
Drawdown - Person 1 this is personal spending money [these sums reduce the balance]
Drawdown - Person 2
-------------------------------------------------------------------------------------------------------------------------------------------------
Treat money - holidays/new car/whatever you want [these sums reduce the balance]
------------------------------------------------------------------------------------------------------------------------------------------------
House running costs including food (calculated in a separate sheet with the number fed through to this sheet)
-------------------------------------------------------------------------------------------------------------------------------------------------
Tax allowance
Tax due -Person 1
Tax due Person 2
--------------------------------------------------------------------------------------------------------------------------------------------------
Net Balance to take forward to the top of the next column.
-------------------------------------------------------------------------------------------------------------------------------------------------

I also have included that facility to increase any of these lines by an inflationary amount. This provided me with a discipline which I have have held to for the last 15 years.

The planning tool then allows you to say "what happens if inflation reaches x %", "I buy a new car in year X", I spend £ x thousands on holidays etc. It allows you to model different scenarios to ensure you don't run out of money but - just as important for me - we don't die leaving loads of money in IHT.

Ok, I might not live to 90 (none of my parents have) but my wife has a better chance. Of course, you could also include the injection of lump sums into your spreadsheet for unexpected/expected windfalls (inheritance perhaps).

I think it is important to plan it out so you have some idea of what is possible - there is no point working until 70 to discover that you might have had sufficient funds 5 years earlier.


Your spreadsheet is eerily close to mine! I guess that's because all those lines are exactly what you need to model the incomes and outgoings for retirement. This is what I used to confirm that I could indeed retire 3 years early (from my NRD of 60 to receive my company pension) though I did have additional versions to enable modelling of different 'what if' scenarios (eg. getting CETV instead of FS, buying additional NI years, etc). Based on my modelling I stop work in 2 weeks time :-)

Cheers, FT

Re: Planning it out!

Posted: March 20th, 2017, 3:03 pm
by JMN2
I've been constructing a SS too, a slightly simpler though, in it I don't go into the spending too much, just wanted to see how much to take out every year, and then with that cash drawn out I plan the expenses elsewhere in another SS.

The following are the columns from which the numbers go into the next row for next year and so on.

ISA pot (formula : (ISA pot Previous year + drawdown isa + subscr isa )* return
SIPP pot ( formula : similar as above)
Total pot
Age
Tax Year
Drawdown ISA
Drawdown SIPP
Drawdown Total
Return (4%)
Subscription ISA
Subsription SIPP
Subsription SIPP TAX

I could do each row semi-annual, basically raid the pot twice a year and plan expenses.

Re: Planning it out!

Posted: March 20th, 2017, 7:04 pm
by monabri
FT
Have a good and long retirement :D !!!!!!! feet up , G&Ts in the summer, throw away the alarm clock ...chillax!!!

The one thing I will say is that time goes REALLY quickly and you'll wonder how the heck you fitted the 9-5pm "W" word in.

monabri