# How to convert an annual interest rate to a monthly rate in Excel

For a quoted annual rate (APR), divide by 12: `=B2/12`. For an effective annual rate, take the 12th root: `=(1+B2)^(1/12)-1`.

**Excel:** `=B2/12` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Rate | Value |
| Annual rate (APR) | 0.072 |
| Monthly rate |  |

Formula in B3: `=B2/12`

Result: 0.600%

## How it works

Loan and card rates are quoted as nominal annual rates: the monthly rate times 12. So the monthly rate is simply 7.2% / 12 = 0.6%, which is the rate to give PMT, NPER and FV for monthly payments.

Investment returns are usually effective annual rates, where the monthly rate compounds up to the annual one. Then use the 12th root, shown as the alternative.

## Other ways

- `=(1+B2)^(1/12)-1`: The monthly rate that compounds to 7.2% over a year: for annual returns and effective rates. (0.581%, a little less than 0.6%.)

## Common mistakes

- Using the annual rate with monthly periods in PMT or FV overstates interest twelve-fold.
- Dividing an effective annual return by 12 slightly overstates the monthly rate; compounding 0.6% a month gives 7.44%, not 7.2%.

## Questions

**How do I convert a monthly rate to an annual rate?**

Multiply by 12 for the nominal rate, or use `=(1+B3)^12-1` for the effective rate.

**How do I get a daily rate?**

Divide the APR by 365: `=B2/365`.

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/convert-an-annual-interest-rate-to-monthly