Rate(nper, pmt, pv, fv, type, guess)
Returns the interest rate (per period) for an annuity. The value returned is the interest rate that results in equal payments of «pmt» per period over the «nper» of the annuity.
In general, there may be zero or multiple solutions. Rate uses an iterative search algorithm. If no solution is found within 30 iterations, it returns NaN.
- The number of periods for the annuity.
- The payment per period.
- If you make payments, this is a negative number.
- If you receive payments, this is a positive number.
- The present value of the annuity.
- If you are receiving a loan this would be the loan amount as a positive number.
- If you are giving someone a loan, this would be a negative number.
- (Optional)The future value at the completion of «NPer» periods.
- If you received a loan, «Fv» would be your balloon payment as a negative number.
- If you receive money back at the end, this would be a positive number.
- (Optional) Indicates whether payments are at the beginning of the period.
True= Payments due at beginning of period, with first payment due immediately.
False= Payments due at end of period. (default)
- (Optional) An estimate of the solution to seed the iteration.
- Remember that this is expressed as a rate per period.
- Defaults to 10%
Your friend needs a loan. He proposes that he will pay you $50 per month for 24 months if you will loan him $1,000. What annual rate of interest would this amount to?
Before we do this, annual interest rates resulting from monthly payments are often expressed in two different ways. A straight interest rate is just 12 times the monthly interest rate. An Annual Percentage Rate (APR) is the compounded rate, and although the less often quoted rate, is actually the more honest rate.
Expressed as straight interest rate:
12*Rate(24, $50, -$1000) → 18.16%
Expressed as an annual percentage rate:
(1 + Rate(24, $50, -$1000))^12 - 1 → 19.75%