Financial
EFFECT function: convert stated APR into effective annual interest rate (APY)
Compare loan or savings offers with different compounding frequencies on an apples-to-apples annual basis using EFFECT.
Why 12% compounded monthly costs more than 12% annually
A stated 12% nominal annual rate (APR) compounded 12 times per year charges 1% each month on prior interest: (1 + 0.12/12)^12 - 1 = 12.6825%. EFFECT(nominal_rate, npery) computes this exact effective annual rate.
Notice that npery truncates to an integer if passed decimals, and higher compounding frequency (daily 365 vs quarterly 4) increases effective yield.
These are mathematical conversions between nominal and effective interest rates. A regulated APR may include fees or other assumptions; do not substitute it automatically for the nominal input used here.
| Offer (A) | StatedAPR (B) | PeriodsPerYear (C) | EffectiveRate |
|---|---|---|---|
| Card Monthly | 0.12 | 12 | 0.126825 (12.68%) |
| Note Quarterly | 0.12 | 4 | 0.125509 (12.55%) |
Compare monthly vs quarterly compounding
At 12% nominal APR, monthly compounding yields 0.126825 while quarterly compounding yields 0.125509—a 13.16 basis-point difference.
=ROUND(EFFECT(B2, C2), 6)Zero or negative rates and periods return #NUM!
EFFECT requires nominal_rate > 0 and npery >= 1. Passing 0% interest, negative rates, or 0 periods per year returns #NUM!.
=EFFECT(0, 12)Try the example
Use the original values or change them to test your understanding.