How to use RATE function?

[Eric]

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

 

[Bob Umlas]

Does this do it?:
=RATE(20,600,,-20000)

 

[PatJennings]

You may be heading in the wrong direction if, in fact, you are trying to
determine the rate for an insurance (life?) plan. You should be using the
IRR function first off. From it you will see how the "investment rate of
return" functions within a life insurance policy. To do this (quite
cumbersome, unless you use an offset column, which I won't try to explain
here) is to enter 600 in twenty rows. Then, create a waterfall such that in
column 1 there are 20 600s, in column 2 19 600s, column 3 18 600s. Get my
drift? In the blank cells beneath the final 600s, enter (minus sign) 20000.
In the cells below the -20000, enter the IRR formula. The answer at 20 years
is 4.65%. The answer at year 1 is 3233.33%. Try earning that in an
"investment" account. remember, life insurance is NOT an investment. It is
designed to protect investments, most notably your lifetime earning power.
If it does throw off an investment, which it may not, depending on the
"investment vehicle", ask the insurer if future premiums will remain at 600
or not. Just for fun, calculate an IRR for escalating premiums after year
20. I'd guess 8% annually would not be inappropriate. The insurer will tell
you that you can use the build up in your "investment account" to offset the
rising premium costs. Unless the policy is a guaranteed premium with a
guaranteed death benefit and a guaranteed rate of return on your
"investment", you may be in for a shock. How well the "investment" performs
is your responsibility, not the insurance company's. True Whole Life
Insurance will guarantee the premium and death benefit. Some Whole Life
issuers will include a non-guaranteed dividend. The beauty of it is that
while premiums appear relatively high in the early years, they remain
constant to age 100. Your death benefit provided to your heirs will never be
less than the guaranteed amount and may in fact grow as a result of the
addition of automatically purchasing of additional insurance (without you
having to monitor rates of return).

 

[Eric]

Thank everyone for suggestion
Eric

 

[Eric]

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

 

[Eric]

Thank you for suggestion
Eric

 

[Niek Otten]

See answer in other newsgroup

Please do not post in separate posts to different newsgroups

--
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
|