Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate the effective annual interest rate in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=EFFECT(A2,B2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Nominal Rate | Periods/Year |
| 0.12 | 12 |
=EFFECT(A2,B2)→0.1268250301
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In A2 enter the nominal annual interest rate as a decimal (e.g. 0.12 for 12%). In B2 enter the number of compounding periods per year (12 for monthly, 4 for quarterly, 365 for daily).
- 2In C2 enter =EFFECT(A2,B2). For 12% compounded monthly, EFFECT(0.12,12) returns approximately 0.1268 — meaning the true annual cost is 12.68%.
- 3Format C2 as a percentage to display the result clearly.
- 4Verify the result manually with =(1+A2/B2)^B2-1. It should match EFFECT exactly.
- 5Build a comparison table with different compounding frequencies in B2:B6 (1, 2, 4, 12, 365) and copy the EFFECT formula down to show how EAR increases with compounding frequency.
Tips
- EFFECT takes the nominal annual rate as input — do not pass the periodic rate. The nominal rate is stated on the loan or product documents.
- The inverse is NOMINAL(effective_rate, periods) — useful when a lender quotes an EAR and you need the nominal rate to feed into PMT or FV.
- For continuous compounding, use =EXP(A2)-1 instead of EFFECT. Daily compounding (365 periods) approaches but never quite reaches the continuous limit.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
When should I use EAR vs the nominal rate?
Use EAR to compare products with different compounding frequencies — savings accounts, mortgages, credit cards. Use the periodic nominal rate as the input to payment functions like PMT and FV.
Does EFFECT work for continuous compounding?
No — EFFECT handles discrete compounding only. For continuous compounding, use =EXP(nominal_rate)-1.
More on Finance basics
See all →How-to
How to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.
How-toHow to calculate compound interest
Compute a compounded future value with principal × (1 + rate)^years using POWER. Identical in Excel and Google Sheets.
How-toHow to calculate cost per unit
Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.
How-toHow to calculate a selling price from a target margin
Use cost and target gross margin percentage to back-calculate the required selling price in Excel or Google Sheets.
How-toHow to calculate simple interest
Multiply principal, rate, and time to compute simple interest using a straightforward formula in Excel or Sheets.