Finance

Convert APR to APY in Excel (EFFECT)

A 6% rate compounded monthly earns more than 6% a year. EFFECT gives the true annual rate.

All Excel versions and Google Sheets

The formula

APY / effective rateExcelGoogle Sheets
=EFFECT(B1,B2)
In D1 → 6.17%
Same without EFFECTExcelGoogle Sheets
=(1+B1/B2)^B2-1
In D2 → 6.17%
Back to APRExcelGoogle Sheets
=NOMINAL(D1,B2)
In D3 → 6%
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.

Worked example

Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.

ABCD
1Nominal APR6%6.17%
2Compounding periods/yr126.17%
36%

How it works

B1
Nominal annual rate.
B2
Compounding periods per year: 12 monthly, 365 daily.

Common errors and fixes

Why does the formula return #NUM!?

Rate ≤ 0 or periods < 1.

Confusing APR with APY?

Loan APRs in some countries include fees; EFFECT only converts compounding.

Does this work in Google Sheets?

Yes. The formulas on this page work the same way in Google Sheets.