The Excel RATE function is used to return the interest rate per period of a loan or investment.
The syntax for the RATE function is:
=RATE(nper, pmt, pv, [fv], [type], [guess])
| Argument | Purpose |
|---|---|
| nper | The number of payments for the loan or investment |
| pmt | The payment made each period |
| pv | The present value, or total amount a number of future payments is worth now |
| fv | The future value, or total remaining after the last payment has been made.This argument is optional, and if omitted the total is assumed to be 0 |
| type | When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 00 – Payments are made at the end of the period1 – Payments are made at the beginning of the period |
| guess | Your guess at what the rate may be. It is optional and if omitted, the guess is assumed to be 10% |
The examples below show the RATE function being used to return the interest rate dependent upon different parameters.
| Function | Result |
|---|---|
| =RATE(B4*12,-C4,A4) | 3.49%Interest rate returned on a £5,000 loan paid in £200 monthly payments for 5 years. Payments are made at the end of each period |
| =RATE(B4*12,-C4,A4,,1) | 3.67%Interest rate returned on a £5,000 loan paid in £200 monthly payments for 5 years. Payments are made at the beginning of each period |
| =RATE(B4*52,-C4,A4) | 4.00%Interest rate returned on a £5,000 loan paid in £200 weekly payments for 5 years. Payments are made at the end of each period |
