Quick Navigation
EFFECT Function
Summary
The Excel EFFECT function converts a nominal annual interest rate and compounding frequency into the true effective annual interest rate, accounting for the power of compound interest.
Syntax
EFFECT(nominal_rate, npery)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| nominal_rate | Number |
Yes | Nominal annual interest rate as decimal (e.g., 5% = 0.05) |
| npery | Number |
Yes | Compounding periods per year (e.g., 12 for monthly, 4 for quarterly) |
Using the EFFECT Function
EFFECT reveals the actual return you earn when interest compounds multiple times per year. A quoted 5% rate compounded monthly actually delivers higher returns than simple 5% annual interest.
Common EFFECT Examples
Quarterly Compounding Example
=EFFECT(0.0525,4)Returns 0.0535427 or 5.35% - true rate for 5.25% nominal quarterly compounding
Monthly Credit Card Rate
=EFFECT(0.18,12)Shows true 19.56% APR for 18% nominal monthly compounded credit card
Daily Compounding CD
=EFFECT(0.04,365)Converts 4% nominal daily compounding to true 4.08% effective yield
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: Non-numeric input for either argument
Solution: Ensure both parameters contain valid numbers
#NUM!
Cause: nominal_rate ≤ 0 or npery < 1
Solution: Use positive nominal_rate > 0 and npery ≥ 1
Notes
- Formula: (1 + nominal_rate/npery)^npery - 1
- Inverse of NOMINAL function
- Perfect for mortgage, loan, and investment analysis
- npery truncates to nearest integer down
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+