Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use RATE function?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use RATE function?
Does this do it?:
=RATE(20,600,,-20000) "Eric" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use RATE function?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use RATE function?
Thank everyone for suggestion
Eric |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use RATE function?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use RATE function?
Thank you for suggestion
Eric |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use RATE function?
See answer in other newsgroup
Please do not post in separate posts to different newsgroups -- Kind regards, Niek Otten Microsoft MVP - Excel "Eric" wrote in message ... |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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use RATE function? | Excel Discussion (Misc queries) | |||
Rate function with #Num! result | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
a function giving the interest rate for inv x for period y & bal z | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) |