The RATE function
Returns the interest rate per period of a loan or investment, from the number of payments, the payment and the amount. Multiply by 12 to get the annual rate on monthly payments.
Syntax
RATE(nper, pmt, pv, [fv], [type], [guess])- nper
- The number of payments.
- pmt
- The payment each period, negative for money paid out. Use 0 for none.
- pv
- The amount today, such as the loan amount.
- fv (optional)
- The amount at the end. Left out, 0.
- type (optional)
- 0 or left out if payments are made at the end of each period, 1 if at the start.
- guess (optional)
- A starting guess for the rate. Left out, 10%. Only needed when RATE cannot find an answer.
Examples
B1=RATE(60,-300,15000)*12
| A | B | |
|---|---|---|
| 1 | 0.074200958 |
B1
=RATE(110,20,3-10000,420000)| A | B | |
|---|---|---|
| 1 | 0.071773463 |
In plain words: The yearly rate that turns 310,000 paid in today into 420,000 after 110 years, 2with no payments along the way.
D1=RATE(B3*12,-B2,B1)*12
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan amount | 18000 | 0.062396772 | |
| 2 | Monthly payment | 350 | ||
| 3 | Years | 5 |
Questions
Why is the rate from RATE so small?
RATE returns the rate per period. With monthly payments that is a monthly rate; multiply by 12 for the annual rate, and format the cell as a percentage.
Why does RATE return
RATE searches for the answer step by step and can give up. Check that the payment and the amount have opposite signs, and give a guess, such as 0.01, as the last argument.