The Excel EFFECT function is a Financial formula that calculates and returns the effective annual interest rate, for a given nominal annual interest rate and number of compounding periods per year. The effective annual interest rate is used to compare financial loans with different compounding terms. Compounding interest is a very powerful thing and this formula is especially useful in calculating the "actual" interest rate you would get when for a specific number of periods, such as when comparing investment options. In this guide, we’re going to show you how to use the EFFECT function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Excel EFFECT Function Syntax
Arguments
| nominal_rate | The nominal interest rate between 0 and 1. |
| npery | The number of compounding periods per year. |
Example
The Excel EFFECT function calculates the effective annual interest rate, with a given nominal annual interest rate and number of compounding periods per year. The nominal interest rate (the rate you are entering in the formula) is the interest rate before taking inflation or compounding rates into account. The effective annual interest rate is the actual interest rate earned due to compounding. In the case of a short-term investment (shorter than the nominal interest rate duration), you would be looking at the effective rate to evaluate an investment. For example, the effective annual interest rate of a nominal rate 12% compounded semi-annually is equal to 12.36%:
Tips
- The equation used in the Excel EFFECT function is (1 + (nominal_rate / npery)) * npery -
- Npery is truncated to an integer.
- You can use NOMINAL function to calculate the nominal annual interest rate by given effective interest rate and the number of compounding periods per year.
Issues
- If nominal_rate ≤ 0 or if npery < 1, the EFFECT function returns the #NUM! error value.
