How to convert an annual interest rate to a monthly rate in Excel
Formula (Excel and Google Sheets)
=B2/12For a quoted annual rate (APR), divide by 12: =B2/12. For an effective annual rate, take the 12th root: =(1+B2)^(1/12)-1.
- Result on the example
- 0.600% in
B3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B3. Change any input and the result updates, or edit the formula to experiment.
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.
=B2/12Result0.006
B27.2%The value in B2.
B2/120.006The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =B2/12. We ran it in Google Sheets mode on the same data and it returns 0.600%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =(1+B2)^(1/12)-1 | 0.581%Different result | 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%.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Convert an annual interest rate to a monthly rate
B3, convert the quoted annual rate (APR) in B2 into the monthly rate a lender charges.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.