Retirement calculator/spreadsheet advice

davilown

SO after all your advice last week, this weekend I want to set up a spreadsheet to see how much I need to retire and when I can retire.

What do I need to include? I've got regular household bills etc and some income.

Has anyone got an example spreadsheet they'd be willing to share?

Linton

I suggest you do not start off with household bills. Far more relevent is the total of what you are actually spending now/year perhaps minus major items that wont be needed when you retire. After all you will presumably want to maintain the standard of living you have been used to whilst working. Working from a list of expenditure headings makes it almost certain you will miss things out or lie to yourself about your real expenditure.

If you just know the basics of spread sheets it is easy to set up a simple but useful financial model with:
- the inflation rate and investment rate of return as changeable parameters
- a column for each year
- a row each for assets at start of year, income, expenditure
- a row for withdrawal of assets calculated as expenditure - income.


Then for the following year

- assets at start of year = (assets at start of previous year - previous years withdrawal)*(1+InvReturn/100)
- income = last years income possibly multiplied by (1+inflation/100) if inflation linked
- expenditure = last years expenditure * (1+inflation/100)
- withdrawal = expenditure-income

Repeat for as many years as you want.

This is just a start. You can then easily extend it by eg having one off expenditures, multiple assets with different rates of return etc etc. I find it difficult to stop adding more bells and whistles to my spreadsheet. It is highly specific to my circumstances and somewhat messy and now calculates tax on SIPP withdrawals and gross income and deducts it to give net income. It also calculates SP from an assumption of number of years deferred.

Audaxer

Linton wrote: »

I suggest you do not start off with household bills. Far more relevent is the total of what you are actually spending now/year perhaps minus major items that wont be needed when you retire. After all you will presumably want to maintain the standard of living you have been used to whilst working. Working from a list of expenditure headings makes it almost certain you will miss things out or lie to yourself about your real expenditure.

If you just know the basics of spread sheets it is easy to set up a simple but useful financial model with:
- the inflation rate and investment rate of return as changeable parameters
- a column for each year
- a row each for assets at start of year, income, expenditure
- a row for withdrawal of assets calculated as expenditure - income.


Then for the following year

- assets at start of year = (assets at start of previous year - previous years withdrawal)*(1+InvReturn/100)
- income = last years income possibly multiplied by (1+inflation/100) if inflation linked
- expenditure = last years expenditure * (1+inflation/100)
- withdrawal = expenditure-income

Repeat for as many years as you want.

This is just a start. You can then easily extend it by eg having one off expenditures, multiple assets with different rates of return etc etc. I find it difficult to stop adding more bells and whistles to my spreadsheet. It is highly specific to my circumstances and somewhat messy and now calculates tax on SIPP withdrawals and gross income and deducts it to give net income. It also calculates SP from an assumption of number of years deferred.

Linton, interested to know what estimated increase in income required you make for inflation each year going forward? On my spreadsheet I've estimated my income needs increasing at 2.5% a year due to inflation, but of course inflation could be lower or much higher than that for some of these years, so not sure if there is a better way of estimating your inflation-adjusted income requirements going forward?

I've also used the current Personal Tax Allowance of £12,500 each year going forward, but probably/hopefully this allowance will increase over the years. I think it has increased quite a lot in recent years, but that will possibly not continue, so do you just estimate that the Personal Tax Allowance will rise roughly in line with inflation?

Linton

Audaxer wrote: »

Linton, interested to know what estimated increase in income required you make for inflation each year going forward? On my spreadsheet I've estimated my income needs increasing at 2.5% a year due to inflation, but of course inflation could be lower or much higher than that for some of these years, so not sure if there is a better way of estimating your inflation-adjusted income requirements going forward?

I've also used the current Personal Tax Allowance of £12,500 each year going forward, but probably/hopefully this allowance will increase over the years. I think it has increased quite a lot in recent years, but that will possibly not continue, so do you just estimate that the Personal Tax Allowance will rise roughly in line with inflation?

I assume an inflation rate of 3% and that my income needs and all tax limits increase accordingly. This should be pessimistic in the long term given the BoEs inflation remit. Future inflation is completely unknown so it is difficult to see where a better guess could come from.

squirrelpie

I suggest you do not start off with household bills. Far more relevent is the total of what you are actually spending now/year perhaps minus major items that wont be needed when you retire.

I agree with Linton's suggestion and it is what I did.


There are so many unknowns that I don't feel it's worth trying to make any kind of detailed plan. Inflation is one big unknown - I try to deal with it by making sure my income is index-linked so far as possible. Future health care needs is another - apart from being prepared to see a lot of savings disappear I don't know there's much else to do. Then there are political uncertainties both here and elsewhere and other things as well.

SMcGill

I’m going to be a tiny voice of dissent here. I’ve been tracking my monthly spend in detail for the last year and it’s made me much more aware of my pattern of spending (way too many of my family were born in summer, it’s very inconvenient!) and what I’m spending it on. If I hadn’t looked at this detail and just relied on total spend then I might not have fully realised what a horrifying and pointless amount of money I spend on personal styling.

For me at least, it’s absolutely worth getting into the detail, at least once, before stepping back to look at the overall picture.

davilown

Thanks everyone for your responses - I think I get the gist of what you were saying Linton. I'm not sure if this is possibly the best idea but I wasn't going to invest in anything except maybe another property.

Thanks again

kangoora

SMcGill wrote: »

I’m going to be a tiny voice of dissent here. I’ve been tracking my monthly spend in detail for the last year and it’s made me much more aware of my pattern of spending (way too many of my family were born in summer, it’s very inconvenient!) and what I’m spending it on. If I hadn’t looked at this detail and just relied on total spend then I might not have fully realised what a horrifying and pointless amount of money I spend on personal styling.

For me at least, it’s absolutely worth getting into the detail, at least once, before stepping back to look at the overall picture.

Not the only one. My wife runs an extremely detailed spreadsheet with all our accounts, pensions & savings on and reconciles it weekly (even down to shopping budget, dental, medical, entertainment categories etc). From that she ends up with a yearly spend on everything. Takes about 15 minutes (max 30) on a Sunday morning to reconcile - she is a bookkeeper though .

I have a much higher level one which does projections out for 20 years. This includes very high level spending categories on an annual basis so we can see when mortgage ends, pensions kick in, lump sums arrive, forecasted big expenses (car replacements etc).

This shows our 'expected' annual income & savings for the next 20 years. At the end of a year I correct for the actuals my wife has recorded and adjust going forward if needed - again, doesn't take very long although it did take ages to build.

Of the two I'd say the 20 year one is more important for retirement planning BUT unless you have accurate figures to base it on it will be GIGO (garbage in, garbage out).

We've been running both for about 5-6 years now.

Linton

SMcGill wrote: »

I’m going to be a tiny voice of dissent here. I’ve been tracking my monthly spend in detail for the last year and it’s made me much more aware of my pattern of spending (way too many of my family were born in summer, it’s very inconvenient!) and what I’m spending it on. If I hadn’t looked at this detail and just relied on total spend then I might not have fully realised what a horrifying and pointless amount of money I spend on personal styling.

For me at least, it’s absolutely worth getting into the detail, at least once, before stepping back to look at the overall picture.

No dissent at all. Collecting data on actual spending is what I did whilst working to provide the spending figures on which to base the retirement plan. But "pointless" spending is part of what provides an acceptable standard of living. You could say that bottles of decent wine and foreign holidays are pointless, but you might not be happy without them for perhaps 30 years of retirement.


My disagreement was with the idea that if you dont know your current overall spending pattern, you can get a good idea of your expenditure needs simply by making a list of bills. You dont want your plans to limit you to what you would consider a very basic existance from the day you stop work. If your looking at details does uncover really pointless spending then I suggest you change your lifestyle now well before retirement so you have chance to get used to it.

jsinc

For expenditure list - https://www.moneysavingexpert.com/banking/Budget-planning/#bplanner