# RATE function in Excel

> RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested.

Source: https://spreadsheetpractice.com/functions/rate · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested.
- **Syntax:** `=RATE(nper, pmt, pv, [fv], [type], [guess])`
- **Most common use:** `=RATE(B4,-B3,B2)` returns 0.54% (The monthly rate on the loan)
- **Available in:** Every current version
- **Category:** [Financial functions](https://spreadsheetpractice.com/functions/category/finance)
- **Returns:** The interest rate per period, as a decimal. Format it as a percentage.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested. Use it to find the real rate on a loan or lease when you only know the payment, or the annual growth rate of an investment. Multiply a monthly result by 12 for an annual rate.

## Syntax

`=RATE(nper, pmt, pv, [fv], [type], [guess])`

| Argument | Required? | What it is |
|---|---|---|
| `nper` | Required | The total number of payment periods. |
| `pmt` | Required | The payment each period, negative for money paid out. Use 0 for a lump sum. |
| `pv` | Required | The amount today: positive for money received, such as a loan. |
| `fv` | Optional | The amount at the end. Defaults to 0. |
| `type` | Optional | 0 or omitted for payments at the end of each period, 1 for the beginning. |
| `guess` | Optional | A starting guess for the rate. Defaults to 10%; change it only if RATE fails to find an answer. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | `=RATE(B4,-B3,B2)` | 0.54% | The monthly rate on the loan |
| D3 | `=RATE(B4,-B3,B2)*12` | 6.5% | Times 12 for the annual rate: 6.5% |
| D4 | `=RATE(B7,0,-B5,B6)` | 8.1% | Yearly growth that turned $10,000 into $16,000 in 6 years |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Input | Value |  | Result |
| 2 | Amount borrowed | $25,000 |  | 0.54% |
| 3 | Monthly payment | $489.15 |  | 6.5% |
| 4 | Payments | 60 |  | 8.1% |
| 5 | Invested | $10,000 |  |  |
| 6 | Grew to | $16,000 |  |  |
| 7 | Years | 6 |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| A rate that looks tiny, like 0.5% | RATE returns the rate per period. For monthly payments, multiply by 12 to get the annual rate. |
| #NUM! | The payment and the loan have the same sign, so there is no rate that works. Make the payment negative and the loan positive. |
| #NUM! on an unusual loan | RATE solves by trial and error and can fail to converge. Supply a guess close to the expected rate, such as 0.01 for 1% a month. |

## Tips

- A dealer quoting only the monthly payment? RATE tells you the interest rate you are actually paying.

## Related functions

- [PMT](https://spreadsheetpractice.com/functions/pmt): PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments.
- [NPER](https://spreadsheetpractice.com/functions/nper): NPER calculates how many payment periods it takes to pay off a loan or reach a savings goal, at a fixed payment and interest rate.
- [IRR](https://spreadsheetpractice.com/functions/irr): IRR calculates the internal rate of return: the discount rate at which a series of cash flows has a net present value of zero.
- [FV](https://spreadsheetpractice.com/functions/fv): FV calculates what an investment will be worth in the future, given regular deposits, a starting balance and a constant rate of return.

## Questions

### How do I find the interest rate on a loan in Excel?

Use =RATE(number_of_payments, -payment, loan_amount)*12 for the annual rate on a monthly loan.

### How do I calculate an annual growth rate in Excel?

Use =RATE(years, 0, -start_value, end_value). It returns the compound annual growth rate.

### Why does RATE return #NUM!?

Usually the signs are wrong (payment and loan both positive) or RATE couldn't converge. Fix the signs or add a guess argument.

## Practice

A graded RATE exercise ("What rate is this loan?") is on [the RATE page](https://spreadsheetpractice.com/functions/rate). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
