# How to calculate the interest rate on a loan in Excel

Use RATE with the number of payments, the payment and the amount, then multiply by 12: `=RATE(B4,-B3,B2)*12`.

**Excel:** `=RATE(B4,-B3,B2)*12` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Loan offer | Value |
| Amount borrowed | 28500 |
| Monthly payment | 562 |
| Number of payments | 60 |
| Annual interest rate |  |

Formula in B5: `=RATE(B4,-B3,B2)*12`

Result: 6.83%

## How it works

RATE searches for the interest rate that makes 60 payments of $562 pay off $28,500 exactly. Because the payments are monthly, the answer is a monthly rate, and multiplying by 12 gives the annual rate a lender would quote. As with PMT, the payment is negative and the amount borrowed positive.

## Other ways

- `=(1+RATE(B4,-B3,B2))^12-1`: The effective annual rate, which includes monthly compounding. It is a little higher than the quoted rate. (Effective rate: 7.04%.)

## Common mistakes

- Forgetting the *12 shows a monthly rate of about 0.57%, which looks deceptively cheap.
- Same signs on the payment and the amount make RATE return #NUM!.
- If RATE can't find an answer it returns #NUM!; give it a starting guess as the sixth argument, such as 0.01.

## Questions

**How do I include upfront fees?**

Borrow less in the formula: `=RATE(B4,-B3,B2-B6)*12` with the fees in B6. The higher rate is closer to the APR.

**Can RATE find the return on an investment?**

Yes: `=RATE(years,0,-start,end)` gives the yearly growth rate between two values.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-the-interest-rate-on-a-loan