EFFECT Function

Converts a quoted (nominal) rate into the true annual rate after compounding.

Category

Financial

Available in

All versions, Microsoft 365

Returns

An effective annual rate

Want to practice EFFECT in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

A 6% rate compounded monthly earns more than 6% a year, because interest earns interest. EFFECT returns that true annual rate.

Use it to compare offers that compound at different frequencies.

Syntax

=EFFECT(nominal_rate, npery) Explain

Arguments

ArgumentRequired?What it does
nominal_rateYesThe quoted annual rate.
nperyYesCompounding periods per year (12 = monthly). Decimals are cut to whole numbers.

Examples

Example 1 — Same 6%, different compounding

How much does compounding frequency matter?

C2fx=EFFECT(6%,B2)
ABC
1CompoundingPer yearEffective rate
2Annual16.00%
3Quarterly46.14%
4Monthly126.17%
5Daily3656.18%

Common mistakes

  • #NUM! error. The rate must be above 0 and npery at least 1.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.