RATE Function in Excel: Find the Interest Rate on a Loan
By ExcelReady TeamUpdated 7 min read
The short answer
The RATE function in Excel finds the interest rate per period of a loan or investment. Write =RATE(nper, pmt, pv), for example =RATE(60,-200,10000) for a 10,000 loan repaid with 60 monthly payments of 200. It returns a monthly rate of about 0.62%, so multiply by 12 for the yearly rate: about 7.42%.
Key takeaways
- The shape is =RATE(nper, pmt, pv, [fv], [type], [guess]), and the result is a rate per period.
- Money you receive is positive and money you pay is negative, so pmt and pv need opposite signs.
- For monthly payments, multiply the result by 12 for the nominal yearly rate, or use (1+rate)^12-1 for the effective rate.
- With no regular payments, RATE gives the growth rate: =RATE(4,0,-10000,12000) returns about 4.66%.
- #NUM! usually means the signs are wrong or Excel couldn't find a rate in 20 tries; check the inputs or add a guess.
On this page+
The RATE function in Excel finds the interest rate hidden in a loan or investment. You give it the number of payments, the payment amount and the amount borrowed or invested, and it returns the rate per period. It is the reverse of PMT: PMT finds the payment from the rate, RATE finds the rate from the payment. This guide is for anyone comparing loan offers, checking a lease quote or answering a finance question in an Excel test.
This guide explains the calculation only. It is not advice on any loan or investment.
What does the RATE function do?#
RATE finds the interest rate that makes a series of equal payments exactly repay an amount. The syntax is:
=RATE(nper, pmt, pv, [fv], [type], [guess])| Argument | What it means | Example |
|---|---|---|
| nper | Total number of payments | 60 (5 years of monthly payments) |
| pmt | The payment each period. Money you pay is negative | -200 |
| pv | Present value: the amount you receive or invest today | 10000 |
| fv | Optional. Value left at the end. Default 0 (fully repaid) | 0 |
| type | Optional. 0 = payments at the end of each period (default), 1 = at the start | 0 |
| guess | Optional. A starting point for Excel's search. Default 10% | 0.01 |
The result is a rate per period. If the payments are monthly, the rate is monthly. RATE works in every version of Excel for Windows and Mac.
How to find the interest rate on a loan#
Say you borrow 10,000 and repay 200 a month for 60 months. Put the inputs in cells:
| A | B | |
|---|---|---|
| 1 | Loan amount | 10,000 |
| 2 | Monthly payment | 200 |
| 3 | Number of payments | 60 |
In B4, type:
=RATE(B3,-B2,B1)The result is 0.0062, or 0.62% per month. The payment has a minus sign because it is money leaving your pocket, while the loan amount is money you receive.
To see a yearly figure, multiply by 12:
=RATE(B3,-B2,B1)*12That gives about 7.42% a year. This is the nominal annual rate, the kind usually quoted on loans. The effective annual rate, which includes interest on interest, is =(1+RATE(B3,-B2,B1))^12-1, or about 7.68%. The compound interest guide explains why the two differ.
Format the result as a percentage with Home > Percent Style (Windows: Ctrl+Shift+%; Mac: Control+Shift+%), then add decimal places. Without decimals, 0.62% shows as 1%.
How to round the result#
A rate of 0.0742009... is accurate but hard to read. Round it to the precision you need:
=ROUND(RATE(B3,-B2,B1)*12,4)returns 0.0742, which shows as 7.42% in percent format. Four decimal places in the formula means two decimal places as a percentage. The ROUND function guide explains the digits argument, and the rounding lesson lets you practise ROUND, ROUNDUP and ROUNDDOWN.
Only round the final figure you report. If you round a rate and then feed it into another calculation, the small error grows.
How to check RATE with PMT#
You can prove the answer by running it backwards with PMT:
=PMT(7.42%/12,60,10000)returns about -200.00, the payment you started with. If the check doesn't match, an input is wrong. The PMT function guide covers payments in detail, and a loan amortization schedule shows how each payment splits into interest and principal at that rate.
More RATE examples#
The same function handles many kinds of questions. Change the inputs, not the formula:
| Question | Formula | Result |
|---|---|---|
| Car loan: 18,000, 48 monthly payments of 420, yearly rate | =RATE(48,-420,18000)*12 | 5.67% |
| Loan: 20,000, 5 yearly payments of 4,800 | =RATE(5,-4800,20000) | 6.40% |
| Same 10,000 loan, but a 300 fee is taken off at the start | =RATE(60,-200,9700)*12 | 8.71% |
| 10,000 grows to 12,000 in 4 years, no payments | =RATE(4,0,-10000,12000) | 4.66% |
The fee example shows a useful trick. You only receive 9,700, but you still repay 200 a month, so the true cost of borrowing is higher than the 7.42% on the full amount.
The last example uses fv, the future value. With no regular payments, RATE gives the yearly growth rate, the same as this formula:
=(12000/10000)^(1/4)-1This is the compound annual growth rate. The percent change lesson covers simple growth, and the FV function guide goes the other way, from a known rate to a future value.
When payments are made at the start of each period#
Most loans are paid at the end of each month, which is RATE's default. Leases and rent are often paid in advance, at the start of each period. Set the type argument to 1 for that case:
=RATE(60,-200,10000,0,1)*12The result is about 7.69% a year, higher than the 7.42% for end-of-month payments. Paying each instalment a month earlier means you hold the borrowed money for less time, so the same payments imply a higher rate. When you compare two offers, check when each one's payments fall, because the timing changes the true rate.
Why RATE returns #NUM! or a strange result#
Most RATE problems come from signs and units. Check these:
- Same signs.
=RATE(60,200,10000)returns #NUM!, because receiving 10,000 and also receiving 200 a month can never balance. Make the payment negative. - No answer found. RATE searches for the answer by trial. If it doesn't settle within 20 tries, it returns #NUM!. Add a guess close to the expected rate, such as
=RATE(60,-200,10000,0,0,0.01). The guide to the #NUM! error covers other causes. - Mixed units. If the payments are monthly, nper must be in months.
=RATE(5,-200,10000)treats the loan as only 5 payments of 200, which gives #NUM! or a meaningless rate. Use 60 for five years of monthly payments. - Forgetting to annualize. 0.62% a month is not 0.62% a year. Multiply by 12 or compound it.
- Rounding too early. A cell format with no decimals can hide the real rate. Widen the format before you trust the number.
Practise it#
RATE gives you a rate; reading and rounding it correctly is the part most people get wrong. Try the growth-rate exercise on this page, which matches =RATE(4,0,-10000,12000). Then practise percent change in a live spreadsheet and practise rounding with ROUND. Each step checks your formula instantly.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
With no regular payments, RATE returns the same answer as a growth formula. In B4, find the yearly growth rate from B1 to B2 over B3 years: =ROUND(POWER(B2/B1,1/B3)-1,4).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | Rate per year | |||
| 5 | ||||
| 6 | ||||
| 7 |
Tip: B2/B1 is the total growth (1.2). POWER with 1/B3 spreads it over the years. Subtract 1, then ROUND to 4 decimals.
Practise this in a lesson
Frequently asked questions
What does the RATE function do in Excel?
RATE works out the interest rate per period when you know the number of payments, the payment amount and the loan or investment amount. It is the reverse of PMT, which finds the payment from the rate.
How do I get an annual rate from RATE?
RATE returns the rate per payment period. For monthly payments, multiply by 12 for the nominal annual rate, for example =RATE(60,-200,10000)*12. For the effective annual rate, use =(1+RATE(60,-200,10000))^12-1.
Why does RATE return #NUM!?
Usually the payment and the loan amount have the same sign, so there is no rate that balances them. Make one negative. It also happens when Excel can't find an answer within 20 tries; adding a guess such as 0.01 can help.
Why does RATE show 0% or 1%?
The cell is formatted with no decimal places. A monthly rate such as 0.0062 rounds to 1% or 0%. Format the cell as a percentage with two decimals, or use ROUND to show the precision you need.