Calculates the annual interest rate and the number of compounding periods per year
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The EFFECT Function is categorized under Excel Financial functions. It will calculate the annual interest rate with the number of compounding periods per year. The effective annual interest rate is often used to compare financial loans with different compounding terms.
As a financial analyst, we often need to make decisions on which financial loan will be best for a company. The EFFECT function will be helpful in such scenario and will facilitate comparisons, ultimately helping in making a decision.
The EFFECT function uses the following arguments:
Nominal_rate (required argument) – This is the nominal or stated interest rate.
Npery (required argument) – This is the number of compounding periods in one year.
How to use the EFFECT Function in Excel?
To understand the uses of the EFFECT function, let’s consider a few examples:
Suppose we are given the data below:
The formula to use is:
The result we get is the effective interest rate:
Chances are that the result of the EFFECT function is displayed as a decimal or shows 0%. The issue is likely to be due to the formatting of the cell containing the function. It can be fixed by formatting the cell as a percentage with decimal places.
The different ways to format the cell are:
Use keyboard shortcut CTRL+1 (i.e., Select the CTRL key and while holding this down, select the “1” (one) key).
A format box would open up as shown below. Now, we can select Percentage from the category list on the left side of the dialog box.
It will open to more options on the righthand side of the control box, which allows us to select the number of decimal places that we want to be displayed. After selecting the desired number of decimal places, click OK.
The other method is to click the Home tab and select the range of cells on which the formatting will be done. Click on the Number tab and from the drop-down menu, select Percentage.
EFFECT is calculated using the formula below:
Again, the nominal_rate is the nominal interest rate and npery is the number of compounding periods per year.
Things to remember about the EFFECT Function
#VALUE! error – Occurs when:
The nominal rate argument is not a numeric value.
The npery argument is not a proper numeric value.
Any of the arguments provided is non-numeric.
#NUM! error – Occurs when:
The given nominal rate argument is less than or equal to 0.
The given npery is less than or equal to 0.
If npery is in decimal format, it is truncated to integers.
The EFFECT function is related to the NOMINAL function through Effective rate = (1+(nominal_rate/npery))*npery – 1.
Thanks for reading CFI’s guide to the Excel EFFECT function. By taking the time to learn and master Excel functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.