Help with percentage forumla please

Originally Posted by snowy2006

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/199*M11)+D16 in cell D17
=(D17/199*M11)+D17 in cell D18 etc etc...

This works just fine. I only want to show the increased fee for the first 10 years sequentially. At the end of the list of 10 years I then want to show what the fee will be in years 25 and 50. (IE: in cells D26 and D27).

Any ideas how to calculate this please ? Many Thanks.

Si.

Hi,

a mathematician will happen along and give you the formula, I couldn't even work out why you /199

however, in the meantime you could put

=(OFFSET(D17,-1,0)/199*M$11)+OFFSET(D17,-1,0)

in D17 and formula fill that down 50 rows, and hide the rows that you are not interested in at this time.

hth
---
formula fill is described at
http://www.mvps.org/dmcritchie/excel/fillhand.htm

You might find it easier to project years 25/50 if you rewrite your formula in D17, D18, etc:

D17: = $D$16 * (1+($M$11/199))
D18: = $D$16 * (1+($M$11/199))^2
D19: = $D$16 * (1+($M$11/199))^3

For 25 years:
= $D$16 * (1+($M$11/199))^24

For 50 years:
= $D$16 * (1+($M$11/199))^49

Scott

Edit:
If you have the years in another column, such as column C, you could probably
put:

D17: = $D$16*(1+($M$11/199))^(C17-1)
etc.