EFFECT function
EFFECT converts a stated annual interest rate into the true yearly rate once compounding is included. It helps you compare loans or savings that compound at different frequencies.
Example
Read it as a sentence: turn a 5.25% yearly rate compounded 4 times a year into the rate you really earn over a full year.
A
B
C
D
1
0.0535426673707584
2
3
Syntax
EFFECT(nominal_rate, npery)
-
nominal_rate(required) - Stated annual rate.
-
npery(required) - Compounding periods per year (whole number).
Returns: Effective annual interest rate.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=EFFECT(0.05,12) | 0.051161898 | Nominal 5% monthly |
=EFFECT(0.05,1) | 0.05 | Npery 1 |
=EFFECT(0.05,12.9) | 0.051161898 | Npery 12.9 truncates |
=EFFECT("0.05","12") | 0.051161898 | Text number |
=EFFECT({0.05,0.1},12) | 0.051161898 | Spill |
=EFFECT(0.05,0) | #NUM! | Npery 0 |
=EFFECT(0,12) | #NUM! | Nominal 0 |
=EFFECT(-0.05,12) | #NUM! | Nominal negative |
Watch out for
- npery is truncated to a whole number.
- Rates are decimals, so 5.25% is 0.0525.
Where it works
- Excel
- EFFECT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .