The Excel EFFECT Function returns the effective annual interest rate, from the nominal annual interest rate (APR), and the number of compounding periods per year.
Excel EFFECT Function Syntax
| Syntax: | =EFFECT(nominal_rate, npery) |
Arguments
| nominal | Nominal Interest Rate (APR) | Annual interest rate, also known as APR (Annual Percentage Rate) | |
| npery | Number of Periods | Number of periods that interest is calculated |
Example
Let’s say the APR quoted by your bank is 7.5% and the repayments are monthly:
The APY (Annual Percentage Yield), or AER (Annual Equivalent Rate) is 7.76%. The compounding effect results in a slightly higher rate than you’re quoted by the lender.
Excel EFFECT Function Calculator
Enter your own values into the EFFECT Function calculator (grey cells below) to try it out.
Tip: Remember, to enter interest rates with a percentage symbol, or their decimal equivalent. E.g. 7.5% or 0.075.
Related Functions
| PMT | Returns the regular repayments for a loan based on a constant interest rate. |
| ACCRINT | Returns the accrued interest that gets paid periodically. |
| FV | Calculate the future value of an investment. |
| NPER | Calculates the number of periods to reach an investment goal. |
Learn more about interest rates here.