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
Topic:Finance basicsExcel & Google Sheets
=EFFECT(A2,B2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Nominal annual rate | Periods per year |
| 0.12 | 12 |
=EFFECT(A2,B2)→0.1268250301
Both expressions are checked against an independently calculated expected result in HyperFormula. This is a sample execution check, not native Excel or Google Sheets certification.
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
- 1Put the nominal annual rate in A2 and a positive whole-number compounding frequency in B2.
- 2Use 0.12 or 12% for the nominal rate and 12 for monthly compounding.
- 3Enter the formula outside the sample. Apply Percentage formatting to its decimal output.
- 4Reconcile with (1 + 0.12/12)^12 - 1, which is approximately 0.1268250301.
Tips
- EFFECT requires a positive nominal rate and at least one compounding period. Excel truncates fractional period counts, so validate whole-number input instead of relying on truncation.
- This excludes fees, taxes and changing rates. An effective rate is not automatically the legally defined APR for a product.
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
Why does the cell show 0.1268250301 instead of 12.68%?
The result is a ratio. Percentage formatting changes its display without multiplying the stored value by 100.
Why does EFFECT return #NUM!?
Check that the nominal rate is greater than zero and the compounding-period count is at least one.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow 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 net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel 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 cost per unit
Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.
Sources and compatibility
Reviewed 2026-09-16