How to use RATE function?

[Guest]

Could anyone give me any suggestion on how to use RATE function?

F=A*((1+r)^n-1)/r

r = interest rate
n = number of installment for a fixed investment plan
A = fixed amount for each installment
F = Final capital value

For example, an insurance saving plan offers $20,000 return in the
next 20 years by paying $600 every year. How to determine the interest
rate?
((1+r)^n-1)/r = $20,000 / $600 = 33.333, when n is 20, then r will
equal to approximate 5%.

0% < r, r is a real number for the interest rate, which could be 10%
or 300%
n is a positive integer for any number of term.

There is a RATE function in excel, could anyone give me any suggestion on
how to determine the interest rate based on the above example please?

RATE(nper,pmt,pv,fv,type,guess)
RATE(20,600,20000,fv,type,guess)

What are the value fv, type, guess for this example?

Thank you in advance
Eric

 

[Niek Otten]

Hi Eric,

=RATE(20,600,0,-20000)

Gives you 5.0762%

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Could anyone give me any suggestion on how to use RATE function?
|
| F=A*((1+r)^n-1)/r
|
| r = interest rate
| n = number of installment for a fixed investment plan
| A = fixed amount for each installment
| F = Final capital value
|
| For example, an insurance saving plan offers $20,000 return in the
| next 20 years by paying $600 every year. How to determine the interest
| rate?
| ((1+r)^n-1)/r = $20,000 / $600 = 33.333, when n is 20, then r will
| equal to approximate 5%.
|
| 0% < r, r is a real number for the interest rate, which could be 10%
| or 300%
| n is a positive integer for any number of term.
|
| There is a RATE function in excel, could anyone give me any suggestion on
| how to determine the interest rate based on the above example please?
|
| RATE(nper,pmt,pv,fv,type,guess)
| RATE(20,600,20000,fv,type,guess)
|
| What are the value fv, type, guess for this example?
|
| Thank you in advance
| Eric

 

[Guest]

Thank you very much
Eric

 

[Guest]

I get mortage loan $63000, and I need to pay $2276 every 3 months for 15 years.
Does anyone know on how to determine the interest rate using RATE function?

=RATE(60,2276,0,-63000) = -3%, which don't seem right

Does anyone know how to apply RATE function in this case?
Could anyone please give me any suggestion?
Thank for any suggestion
Eric

 

[Niek Otten]

Hi Eric,

=RATE(60,2276,-63000)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I get mortage loan $63000, and I need to pay $2276 every 3 months for 15 years.
| Does anyone know on how to determine the interest rate using RATE function?
|
| =RATE(60,2276,0,-63000) = -3%, which don't seem right
|
| Does anyone know how to apply RATE function in this case?
| Could anyone please give me any suggestion?
| Thank for any suggestion
| Eric
|

 

[Guest]

Thank you very much
Eric