Help with this growth formula

[kak]

I'm trying to calculate a yearly fee that will decrease by 10% per year (as measured each year against the prior year's fee). Is there a formula I can use that will help me avoid manualy changing the exponent or that will simplfy the equation? Sometimes I will be using 3 years and sometimes I will be using 4 or 5 years.

My Formula using #s
=0.025*((0.9^3)+(0.9^2)+(0.9^1))

The way my formula looks in my spreadsheet
=(B5*(((1-B7)^(B8-1))+((1-B7)^(B8-2))+((1-B7)^B8)))

I'm using Windows XP

Thanks

 

[tactps]

Try:
=FV(b7,b8,0,B5)
Where:
b7 = interest rate
b8 = no of payments
b5 = amount

 

[Jay Petrulis]

Hi,

If you want the 0th power included, the formula for the geometric series is:

a*(1-(r^n)) / (1-r)

where
a represents the constant term (B5 in your example)
r represents the scaling factor (1-B7) in your formula
n represents the number of periods (B8).

Note that if you define r to be (1-B7), 1-r = B7

Since your example does not have the 0th power term, multiply by (1-B7) to get your desired results.

a*r*(1-(r^n)) / (1-r)

With
0.025 in B5
0.1 in B7
3 in B8

=B5*(1-B7)*(1-(1-B7)^B8)/(B7)

will give you the desired result

=B5*(1-(1-B7)^B8)/(B7)

will give the more standard result with the 0th term included.

EDIT: Tactps, the straight FV formula returns incorrect results. This is an annuity-type problem and the formula needs to be altered to reflect that (too late for me to figure it out with the FV function right now). Unless of course I made a mistake.

EDIT2:

=B5*SUM((1-B7)^ROW(INDIRECT("1:" & B8)))

array-entered also works, but is a brute force approach.

 

[tactps]

If it an annuity, you can replace the ",0," in my formula with the amount each year:

say:
Try:
=FV(b7,b8,b5,B5)
Where:
b7 = interest rate
b8 = no of payments
b5 = amount

 

[Jay Petrulis]

If it an annuity, you can replace the ",0," in my formula with the amount each year:

say:
Try:
=FV(b7,b8,b5,B5)
Where:
b7 = interest rate
b8 = no of payments
b5 = amount

The rate used in the FV function is calculated as a discount rate, while the OP's specs want it a factor multiple (or some better term).

The discount rate is in the denominator (1+rate)^periods, while the OP wants the (1-rate)^period in the numerator,

=FV(-B7,B8,-B5)*(1-B7) would be the correct formula for the OP's specs.

 

[kak]

Thank you both for the help.

I decided to use the geometric series formula solution b/c it is easier for me to understand. This will help me complete my repeated task so much faster!

 

[tactps]

Jay, you are the master - I stand corrected.