Disclaimer: Any advertising links on words in this post will have been placed there by TMF after it was posted. I have no knowledge of whether or where they will appear, so please do not read any emphasis or approval by me into them. If you have any complaints about them, complain to TMF, not me.

--------------------------------------------------------------

To be more specific, I need a calculation for the following:

"say with a starting HYP portfolio of value V, assuming monthly savings of M, an average annual dividend yield of D, plus an average annual growth rate of G, after Y years, how much will my portfolio pension pot be worth?"

Or put another way:

"how much will I have to invest per month into my SIPP in order to reach a portfolio value of V in Y years?"

Enter the following captions into cells:

A1: Initial sum
A2: Dividend yield
A3: Growth rate
A4: Inflation
A5: Growth in monthly savings
A6: Years

A9: Monthly savings
A10: Final year monthly savings
A11: Final value
A12: Final real value

A14: Target real value
A15: Target value
A16: Required final year monthly savings
A17: Required monthly savings

Widen column A so that all of these fit. Format cells B1, B9, B10, B11, B12, B14, B15, B16, B17 as "Currency" and cells B2, B3, B4, B5 as "Percentage".

Enter the following formulae into cells (the value produced in B7 isn't of great interest - it's just a useful value to simplify the other formulae):

B7: =(1+B2+B3)/(1+B5)
B10: =B9*((1+B5)^(B6-1))
B11: =B1*B7^B6+12*B10*IF(B7=1,B6,(B7^B6-1)/(B7-1))
B12: =B11/((1+B4)^B6)
B15: =B14*(1+B4)^B6
B16: =(B15-B1*B7^B6)/(12*IF(B7=1,B6,(B7^B6-1)/(B7-1)))
B17: =B16/((1+B5)^(B6-1))

Enter the following numbers into the input cells:

B1: 10000
B2: 5%
B3: 4%
B4: 3%
B5: 5%
B6: 20

B9: 250

If you've done everything correctly, cells B10, B11 and B12 will show £631.74, £242,455.91 and £134,241.96 respectively. Cells B9-B12 are set up to tell you how much you would end up with for the monthly savings you enter into B9, on the assumptions entered in B1-B6. I.e. basically to answer your first question.

Cells B14-B17 are set up to answer your second question - i.e. to go from a target amount entered into B14 to the required monthly savings. If you enter 134241.96 into B14, they should produce the reverse of the calculation in cells B9-B12.

You will note that I've added inflation to your list of input variables. This is important, as you can see from the difference between the final value and the final real (i.e. inflation-adjusted) amount. Politicians might like telling us that inflation is dead - but over typical saving-for-retirement timescales, it's still very significant...

I've also added an input variable for the percentage you think you'll be able to increase your monthly savings by each year. Be realistic about this!

If you don't want to use either or both of these two extra inputs, just input 0% for the ones you don't want.

Of course, monthly investments into a HYP do not exactly make sense because of the brokers fees (although there may be some Foolish ways around this also), so I am expecting new HYP investments will most likely be made on a six-monthly or yearly basis.

The formulae above basically assume all savings and dividends are saved up for a full year, then invested together, and that interest on those accumulated savings pays your costs precisely. They could be adjusted for other investment schedules and to take interest rates and dealing costs into account more precisely, but it's not worth bothering. There are far greater sources of error in this sort of calculation, such as the facts that stockmarkets have never yet been known to go up smoothly year after year after year, and even though HYPs have low volatility as stockmarket investments go, your HYP will still get noticably lucky or unlucky performance from time to time...

Because there are these errors, use the spreadsheet conservatively. E.g. if you want to retire on real HYP capital of £200,000, set yourself a noticably higher target, such as £250,000 or even £300,000. Or alternatively, set the target at £200,000, but then do your best to save significantly more than the monthly savings indicated.

Finally, to anyone who reckons the capital level of a HYP doesn't matter and it's the income level you should be looking at: you're quite right normally. But this question is about a HYP invested in a SIPP, which means that it is quite likely it will have to be converted back into cash at some point, and then invested in an annuity or (under the likely new rules) some alternative safe investment. I rather doubt that governments will allow people's pensions to stay in individual share portfolios, no matter how conservatively constructed...

Gengulphus