One for the excel folk

SpeedSouth

I know a lot on here are proficient in excel, so wonder who can pinpoint the error in my formula. I googled various sites to get it this far, so have not come up with it myself.

First formula is: To calculate POT in so many years
=FV(B8/12,B9*12,-B7,-B6)
Where B8 = rate of return, B9 = Number of Years, B7 = Monthly contributions, B6 = Starting Pot

This appears to give a valid pot size after the period of years.

2nd Formula, I have used to then generate the annual contributions required to reach my required pot is:
=((B10-B3)*B8)/((1+B8)^B9-1)
Where B10 = Pot size, B3 = Current amount in 20 years (In this case property equity), B8&9 as above.

The idea being I can have the 2nd formula show me the amount of annual contributions required to hit my pot size, then work out the shortage/over payment as it stands on a monthly basis.

However when I add the 3 pots up (ISAs & both pensions), I can see that it will equal my required pot size, but the annual contributions still shows a shortage.

I'm guessing it has to be something to do with the initial balances, and how I factor those. I have tried to remove the initial ISA and pension balances into the property equity cell but that still does not seem to fit.

Happy to share the sheet if necessary.
Thanks

alanq

I am not an Excel expert but notice that the first formula is about monthly contributions and the second about annual contributions.

Is the problem that, for a given gross rate, monthly contributions will give a greater amount of interest after a year?

SpeedSouth

alanq wrote: »

I am not an Excel expert but notice that the first formula is about monthly contributions and the second about annual contributions.

Is the problem that, for a given gross rate, monthly contributions will give a greater amount of interest after a year?

Very well could be I suppose, but given the difference in values I am seeing I would doubt it. As I said I'm no expert either so have used these formulas rather than derived them.

Temrael

Not really a solution to the second formula but perhaps a simpler approach (using that same natty FV calculation you are though).

What I do in my sheet is to say I have X in the pot currently (ISA, Pension or whatever)
I need this pot to be Y in the future
I'm therefore Z% towards my desired total (Y)
I'm currently contributing A per month to this pot
This means the pot will reach a total of B (using the same FV calculation as you do)
This will be C % above/below what I need (Y)

I can then see the effect of my monthly contributions and what any increase or decrease would do. It doesn't cleverly tell me what my contributions ought to be, but it's quick to see by just altering A.

As an aside, for how big I think the pot needs to be, I use a multiple of our current annual outgoings, which therefore goes up as inflation does. So (for example) I am targeting one of my pension pots as needing to be 7.5 times our current annual outgoings. This means, so long as I keep our outgoings updated in my spreadsheet, that I can largely forget about inflation (since the projected returns I've built in to my FV calc are what I expect to get above inflation).

I figure that should mean that I don't end up falling into a trap of targeting (today) an absolute figure that then is eroded by inflation in 5 or 10 years time.

Jibeddy

Could it be the difference between B6 (starting pot) and B3 (current pot amount after 20 years)?

TheTracker

Not sure I'd do it this way.

You know how much you have to contribute to achieve your goals, with your assumptions. Say £1k pm or a little under £12k pa.

At any point you know how much behind or ahead you are in contributions. You're trying to work out the effect of being behind so that you can amend future contributions to account for the deviation off your path.

So you miss a payment of £1000 in January. How much do you have to contribute in February to stay on track? Well, on paper its £1000 plus the missed rate of return. But do you use your assumed rate of growth (say 5% making it £1000+1000*.05/12=£1004.17). But what if the markets went up 10% in the month? Do you now say you have to account for the 10% vs 5% growth by contributing more (you missed more growth) or less (since the growth wiped out the missed gain and you can stay on track by just re-contributing £1000) or the same as modelled (the £1004.17).

The approach I would take - I do take - is to do a stocktake annually not monthly. How on track am I now? Do market conditions for the last year mean I need to contribute more now? (I never say this). Did I simply not contribute enough last year? eg the market has rocketed in the last 2 years but i'm not decreasing my contributions because it may fall for the next 2 years. I simply knew at the outset I needed to contribute £X pm for Y years to get Z pot size. It sounds to me like the model you are trying to create would say contribute less during a bull run and more during a bear period, which loses the point of incentivising your regular payments.

My view is you're going in too deep and getting lost amongst the noise. Catch up with your contributions if you can, and maybe add a little more, up to whatever growth you assumed, if you want to catch up on the missed hypothetical 5% growth. Doing that is a very simple spreadsheet with 12 actual monthly contributions compounding each month by the estimated rate, vs flat contributions through the year.

SpeedSouth

@ Temrel, Yeah I can work it that way on my sheet already which is how I noticed the error.

As far as pot size versus inflation goes. My sheet is all in today's money, so I've factored a growth of 2.5% per year which I would hope to achieve after inflation.

The Pot size comes from the required income (based on today's spending) and withdrawal rate

SpeedSouth

Jibeddy wrote: »

Could it be the difference between B6 (starting pot) and B3 (current pot amount after 20 years)?

On looking through it all again, I think this is the issue. Initial pots are factored with an annual growth and equity pot is simply 1.5 times so throwing the maths out.

SpeedSouth

TheTracker wrote: »

Not sure I'd do it this way.....

The approach I would take - I do take - is to do a stocktake annually not monthly. How on track am I now? Do market conditions for the last year mean I need to contribute more now? (I never say this). Did I simply not contribute enough last year? eg the market has rocketed in the last 2 years but i'm not decreasing my contributions because it may fall for the next 2 years. I simply knew at the outset I needed to contribute £X pm for Y years to get Z pot size.

My view is you're going in too deep and getting lost amongst the noise. Catch up with your contributions if you can, and maybe add a little more, up to whatever growth you assumed, if you want to catch up on the missed hypothetical 5% growth. Doing that is a very simple spreadsheet with 12 actual monthly contributions compounding each month by the estimated rate, vs flat contributions through the year.

You're probably right, and as you say can work it all out this way. Whilst I've tracked accounts before I've only done this sheet very recently to see projected pots. Never looked at where I am in relation to the "fictional" plan really previously.