# How to convert APR to APY in Excel

Compound the periodic rate for a year: `=(1+B2/B3)^B3-1`, with the APR in B2 and the compounding periods per year in B3.

**Excel:** `=(1+B2/B3)^B3-1` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Savings account | Value |
| APR (nominal rate) | 0.045 |
| Compounding periods per year | 12 |
| APY |  |

Formula in B4: `=(1+B2/B3)^B3-1`

Result: 4.594%

## 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)`.

## Other ways

- `=EXP(B2)-1`: Continuous compounding, the upper limit as periods get shorter. (4.603%, slightly more than monthly compounding.)
- `=(1+B2/365)^365-1`: 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.

## 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.

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-apr-to-apy