EFFECT Function
Returns the effective annual interest rate by calculating the nominal interest rate with compounding periods
What is the EFFECT Function?
EFFECT is a financial function in Excel that calculates the nominal interest rate with the number of compounding periods that occur per year. By calculating the nominal interest rate with compounding periods, this function returns the effective annual interest rate as the answer.
It is useful in calculating and finding interest rates from a specific period. It’s also resourceful for businesses to compare investment options between companies. They can be helpful for businesses to look at the growth of their company between specific periods.
Financial analysts can use EFFECT to determine which decisions and types of financial loans are best for their businesses in the long run. They can compare their data with other years and see if their company is growing.
Where
- nominal_rate = the stated interest rate
- npery = the number of compounding periods per year
Effective annual interest rates are the interest rates earned through compounding.
Key Takeaways
- The EFFECT helps retrieve the annual interest rate of a year by using the nominal rate and the compounding periods that happen per year.
- EFFECT and NOMINAL functions are similar, with compounding periods being the only argument that remains stable in the functions.
- Another similarity between the two functions is their reliance on the effective rate.
- If any argument has a non-numeric value, a negative integer, or a value equal to 0, the function will return #VALUE! indicating that the function doesn’t allow those values.
- If the nominal rate or the number of compounds is negative or equal to 0, then the function will return #NUM, meaning that the function has an error in its equation.
How to use the EFFECT Function in Excel?
When calculating EFFECT in Excel, you input the stated interest rate and the number of compounding periods. For instance, as shown below, you input cell C2 (10%) for the nominal_rate and cell C3 (4) in the npery section.
Once you’ve imputed the required arguments in the formula, you get an answer of 10.38%. This answer is our effective annual interest rate.
We’ll go through this function by providing more examples. Let’s say a company has compiled the data over a 5-year span of its nominal interest rates with the number of compounding periods per year. They want to find out what their effective interest rate for each year is.
In 2012, they had a 12% nominal interest rate with 4 compounding periods that occurred in that year. So by using the EFFECT function, we have an effective interest rate of 12.55% for 2012.
In 2013, there was a 12.3% nominal rate with 3 compounding periods. So when we imputed these numbers in the function, we got 12.81% as the annual interest rate for 2013.
Next, for 2014, there was a 13.50% nominal interest for that year with 3 compounding periods. So we got an answer of 14.12% for our effective interest.
Then, in 2015, there was a decrease in the nominal from last year. We went down to 13.10% for 2015 and had 6 compounding periods. By inputting these two numbers in the EFFECT function, we got 13.84% as our annual interest rate for 2015.
Finally, for 2016, we saw a sharp increase in the NIR at 15.70%, with 8 compounding periods for that year. Using EFFECT, we got 16.82% as our answer for the effective interest rate.
EFFECT vs. NOMINAL Function
Now that we have a good understanding of how to calculate the effect, you’re probably wondering if there is a way you can calculate the nominal interest rate, and the answer can be calculated by using the nominal function.
NOMINAL is a function in Excel that returns the nominal interest rate through a given effective rate and compounding periods. The calculation method for this function is similar to the EFFECT; the biggest difference is that the nominal and effective interest rates are swapped.
where
- effect_rate = effective interest rate
- npery = the number of compounding periods per year.
It’s a simple function to calculate. All you have to do is insert the effect rate and the yearly compound period into the syntax. Let’s say we have a 10% EIR with 4 compounded periods.
Once you’ve inputted those numbers into the formula, you get back 9.65% as your nominal interest rate, as shown below.
We’ll go through this in greater detail by providing more examples, which should give you a better understanding of how NOMINAL works and how it differs from EFFECT. Below is a screenshot of the previous and new examples using the nominal function.
A company has compiled its data over 5 years and gathered each year's effective interest rates and compounding periods. Next, they want to find the nominal interest rates for those 5 years.
In 2017, we had 14% EIR with 10 compounding periods. Therefore, using the nominal formula, we have a nominal interest rate of 13.19% for that year.
For 2018, we are shown that our EIR is 14%, and we have 10 compounded periods. So our nominal rate for 2018 is 14.44%.
Then, in 2019, our EIR is 16.20% with 12 compounding periods. Considering those two numbers, we found that our NIR is 15.11%.
Next, in 2020, we saw a decrease in the EIR compared to last year. We went from 16.20% in 2019 to 10.50% in 2020. We also have fewer compounding periods that year. Our NIR for 2020 is 10.08%.
Finally, we see a slight increase in the EIR in 2021 at 11.30%, with only 4 compounding periods that year. As a result, our NIR for 2021 is 10.85%.
Note
EFFECT and NOMINAL functions are connected through the use of the effective rate, which is [(1 + nominal/compound)^(compound) - 1]
or Want to Sign up with your social account?