How to convert APR to APY in Excel
Formula (Excel and Google Sheets)
=(1+B2/B3)^B3-1Compound the periodic rate for a year: =(1+B2/B3)^B3-1, with the APR in B2 and the compounding periods per year in B3.
- Result on the example
- 4.594% in
B4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B4. Change any input and the result updates, or edit the formula to experiment.
How it works
APR is the periodic rate times the number of periods; it ignores compounding. APY is what you actually earn in a year: each month pays 4.5%/12, and that interest earns interest the following months, so 4.5% compounded monthly yields 4.594%. Excel's EFFECT function gives the same: EFFECT(B2,B3).
=(1+B2/B3)^B3-1Result0.04593982504
B24.50%The value in B2.
B312The value in B3.
B2/B30.00375The / operator divides.
1+B2/B31.00375The + operator adds.
1+B2/B3)^B31.045939825The ^ operator raises to a power.
1+B2/B3)^B3-10.04593982504The - operator subtracts.
In Google Sheets
The same formula works in Google Sheets: =(1+B2/B3)^B3-1. We ran it in Google Sheets mode on the same data and it returns 4.594%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =EXP(B2)-1 | 4.603%Different result | Continuous compounding, the upper limit as periods get shorter. 4.603%, slightly more than monthly compounding. |
| =(1+B2/365)^365-1 | 4.602%Different result | Daily compounding, as many savings accounts use. 4.602%. |
Common mistakes
- APY and APR are only equal with yearly compounding. Compare accounts on APY.
- To go back from APY to APR, reverse it:
=B3*((1+B4)^(1/B3)-1), which is what NOMINAL does.
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 APR to APY
B4, convert the APR in B2 into the annual percentage yield, with B3 compounding periods per year.Questions
What is the difference between APR and APY?
APR is the simple yearly rate; APY includes the effect of compounding, so it is the true yearly return.
How do I convert APY to APR?
Use =B3*((1+B4)^(1/B3)-1), or NOMINAL(B4,B3) in Excel.
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.