Compound Rate Function

[BL]

Hi, there,

I am looking for a function to calculate the compound rate for a annual
payment which can give a total amount of $ in a given period.

P = Annual Payment
R = Interest rate
N = the total of year
$ = Total

so P + P*(1+R) + P*(1+R)^2 + P*(1+R)^3 + ...P*(1+R)^n = $

e.g. What is the growth rate required for an initial payment of $100
required to get total $610.51 payment in 5 years? the answer to this question
is 10% as

$100 +$100*(110%)^1+$100*(110%)^2+$100*(110%)^3+$100*(110%)^4 = $610.51

I search but I cannot find the function required. Please help.

Thank you.

BL

 

[JE McGimpsey]

Take a look at the RATE() function

 

[BL]

Thank JE,

Unfortunately, the RATE() Function requires the PV as input key, I am
looking the rate to discount the amount to PV. Any other suggestion? I am
building up a table and use the Goal Seek Function of Excel to help me find
the rate; however, the process is time-consuming.

BL

 

[Ron Coderre]

Actually, I believe JE is correct is suggesting the RATE function:

A1: RATE =RATE(A4,A3,A2,A5,A6)--->Which calcs to 10%
A2: PV 0
A3: PMT -100
A4: TERM 5
A5: FV 610.51
A6: TYPE 0

 

[JE McGimpsey]

Huh? You give the PV in your problem statement!

Your example shows an initial payment (PV) of $100, compounded for 4
years, with subsequent annual $100 payments at the end of each period,
compounding annually.

You can either model that as

=RATE(4,100,100,-610.51,0) ===> 10%

or, use a PV of 0 and stretch the period to 5 years:

=RATE(5,100,0,-610.51,0) ===> 10%

In neither case is the PV unknown.

 

[BL]

You are right, the RATE() works exactly what I need. Thank you very much for
your time in explaining to me.

BL