How Much to Accumulate for Retirement? (Excel analysis)

Graeme7777

Hi,

I'm trying to figure out how much I need to accumulate from my workplace pension and other investments to reach a target level of annual retirement income.

I've seen some great, freely-available Excel workbooks with calculators for mortgage amortisation and affordability.

Is anyone aware of something similar for pension calculations?

Thanks! 

Notepad_Phil

I think most people create their own as the calculations needed to work out the sums are very dependant on factors that are personal to the individual.

Are you aware of the term SWR (safe withdrawal rate) as that is maybe the most important decision that you will take if your retirement is mainly based on your own investments and you don't want to run out of money due to taking too much. If you've not come across the term then have a search through this forum for the term as it is frequently discussed.

Once you know how much yearly pre-tax income you want from your investments and know your SWR then the amount you need to accumulate is very simple i.e. amount = income / SWR. For example if you need to realise an initial pre-tax income of £10,000 from the investments and have decided that your investments are capable of a SWR of 3.5% then amount = 10000/0.035 i.e. just under £300,000.

jim8888

Again, trying to keep it simple, there's another general rule of thumb that says you need 25 times your annual expenditure level to think about retiring. On that measure, if you were spending £10k a year, you'd need a pot of £250k. It's fun creating your own spreadsheets, and tweaking them forever, but the "big picture" targets give a good guideline when starting out and help you to see the wood from the trees.

justwhat

jim8888 said:

Again, trying to keep it simple, there's another general rule of thumb that says you need 25 times your annual expenditure level to think about retiring. On that measure, if you were spending £10k a year, you'd need a pot of £250k. It's fun creating your own spreadsheets, and tweaking them forever, but the "big picture" targets give a good guideline when starting out and help you to see the wood from the trees.

Surely that's age dependant. i am 50 and spend under 10k.  All depends on the life style you want. i do intend to live off the state when the savings run out. lol

El_Torro

I use a combination of Excel and online tools. There are many places to find these online tools, personally I tend to use the ones on the Hargreaves Lansdown website, especially the Pension Calculator and the Regular Investing Calculator.

Whichever method you use the important thing is the assumptions you are making. If you are say 30 years away from retirement then the assumption of how much you are going to be putting in every month and how much above inflation your investments grow every year is important. It's very easy to use a figure that is too optimistic or pessimistic. 

jim8888

justwhat said:

jim8888 said:

Again, trying to keep it simple, there's another general rule of thumb that says you need 25 times your annual expenditure level to think about retiring. On that measure, if you were spending £10k a year, you'd need a pot of £250k. It's fun creating your own spreadsheets, and tweaking them forever, but the "big picture" targets give a good guideline when starting out and help you to see the wood from the trees.

Surely that's age dependant. i am 50 and spend under 10k.  All depends on the life style you want. i do intend to live off the state when the savings run out. lol

That's really true! I once heard Dave Ramsey (American podcast finance guru) advise a sixty year old about what he should do in order to have a 2 million dollar pot when he reached 80! And I often wonder why SWR calculations assume you want the same withdrawal income at 65, 75, 85.....

squirrelpie

jim8888 said:

Again, trying to keep it simple, there's another general rule of thumb that says you need 25 times your annual expenditure level to think about retiring. On that measure, if you were spending £10k a year, you'd need a pot of £250k. It's fun creating your own spreadsheets, and tweaking them forever, but the "big picture" targets give a good guideline when starting out and help you to see the wood from the trees.

What that factor of 25 times does is just bake in an assumed safe withdrawal rate of 4%. Surely it's better to make it explicit like Notepad_Phil did? The 4% figure was derived in the USA I believe and may be optimistic in the UK.

enthusiasticsaver

If you just have a DC pension (defined contribution) and state pension then it is easy to work out on a spreadsheet what your income will be based on maybe 3%  or 4% drawdown from the DC pot each year. 

We had various pensions all paying out at different times and most of them DB so we just did a spreadsheet recording the estimated income from each pension for the 8 years from our proposed retirement date to the date of our last pension paying out which in our case is my state pension and updated it each year.

How much you save each month depends on how much income you want in retirement and how old you are when you start saving plus of course how much spare money you have available and other priorities. We aimed initially for 10%  of our pre tax income (including employer contributions) and increased later on when our children were older and the mortgage paid off.  Some people prioritise their pensions above their mortgage though so it is whatever works for you. 

justwhat

think you also have to consider what you will do with excess money on your death or if you are even intending to leave anything.

tacpot12

I used cFIREsim a lot when planning my retirement. I found it very useful as it allow me to incorporate my state pension entitlement, other DB pensions, and rental income as well as allowing me to test the likely success of my likely pension pot lasting as long as I want it to.

SouthCoastBoy

It seems most people take the swr as the way forward. My understanding of swr is that the pot will be preserved at the swr? Is that incorrect?