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
|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|
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.
|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.