# Help with this growth formula

#### kak

##### New Member
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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try:
=FV(b7,b8,0,B5)
Where:
b7 = interest rate
b8 = no of payments
b5 = amount

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.

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

tactps said:
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.

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!

Jay, you are the master - I stand corrected.

Replies
12
Views
326
Replies
3
Views
351
Replies
5
Views
158
Replies
3
Views
129
Replies
1
Views
208

1,196,517
Messages
6,015,682
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back