Help with formula for yearly rising cost spreadsheet please.

Hi all,

Just wondering if anyone could please help with the following :

I have a spreadsheet that has a starting value for a yearly payment in cell D16.
This yearly payment is going to increase by a percentage specified in cell M11 over the next fifty years.

I want to display a column showing the figures increasing over the first ten years and have done so using cells D16 to D25 and having the following formula in them :

=D16*(1+(M11/100))^2
=D16*(1+(M11/100))^3
=D16*(1+(M11/100))^4

etc...

This forumla was suggested by some kind users in a previous post and works just fine.

However I now have another problem! At the end of the list of 10 figures I want have a cell that lists the TOTAL paid over 10 years - that is easy to do using the following forumla :

@SUM(D16..D25)

Now the tricky bit :

Underneath the 10 year total I then want to display the TOTAL amount that would be paid over both 25 and then 50 years. I can easily calculate what the yearly fee will be in 25 (and 50) years by using

=D16*(1+(M11/100))^24 and
=D16*(1+(M11/100))^49

but not how much will have been paid in TOTAL at 25 and 50 year points.

Please note : I do not want to show the individual yearly amounts for the next 50 years (Only for the first 10 as I am currently doing).

Any ideas please !?!??

Si.

Hi Duane,

Thank you very much for your quick reply. The formula doesn't seem to be quite right.

Assuming a starting value of 600 pounds sterling and an annual increase of 15 percent then if I calculate the 10 year total using the @sum(D16..D25) method I get a total of £12182.23. If I use your formula truncated to calculate the first ten year total as follows =SUM(D16*(1+(M11/100))^{1,2,3,4,5,6,7,8,9,10}) then the result is £14010.

As you can see the two figures are different. The first ten years figures work out as follows :

600.00 690.00 793.50 912.53 1049.40 1206.81 1387.84 1596.01 1835.41 2110.73
When added up by calculator the figure is 12182.23

Any more ideas please - I am sure it is something simple :-)

Thanks,
Si.

Thanks all, it worked a treat!!

Si.