How to Use the EFFECT Formula in Excel

The Effect formula in Excel returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

The effective annual interest rate is the interest rate that is earned or paid over the life of an investment. It differs from the nominal interest rate because it takes into consideration compounding while the nominal interest rate does not. Therefore, the effective interest rate is higher than the nominal interest rate.

Formula Explanation

• Nominal_rate (required): The nominal interest rate.
• Npery (required): The number of compounding periods per year.

Formula

Example:

On January 1, 2020, Davidson & Co. acquired equipment through a loan for $1,000,000 payable. The loan characteristics differ depending on the options selected on December 31 of each year with a market rate of interest between 8% – 12%. Determine the effective interest rate.

Solution:

In this example, the effective interest rate is calculated using the nominal rate of 0.667% for scenario 1 (since interest is paid monthly) and there are 12 periods. We can calculate the effective interest rate manually and through the Excel formula to arrive at the same result below. It’s higher than the nominal interest rate due to compounding. We can similarly calculate the effective annual interest rate under scenarios 2 and 3.

Calculating Rate per Period:

Manual Method Formula Implementation:

Excel Formula Implementation:

The blue cell represents the nominal rate, and the red cell refers to the period per year for each scenario respectively.

This shows how we are able to go back into the effective annual interest rate manually, the long way, and proving that the effective annual interest rate in Excel is working accurately.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!