EFFECT function

The EFFECT function in Excel is used to calculate the effective annual interest rate (also known as the effective annual yield) based on a nominal interest rate and the number of compounding periods per year. The effective interest rate accounts for the effects of compounding and provides a better understanding of the actual annual interest rate paid or earned.

Syntax

EFFECT(nominal_rate, npery)

Parameters

• nominal_rate: The nominal interest rate (as a decimal) that is quoted or stated, such as 6% would be entered as 0.06.
• npery: The number of compounding periods per year. For example, for monthly compounding, this would be 12; for quarterly compounding, it would be 4; for semi-annual compounding, it would be 2.

How It Works

The EFFECT function calculates the effective annual interest rate by considering the impact of compounding. The effective annual rate (EAR) reflects the true interest rate earned or paid over a year, assuming that interest is compounded multiple times during the year.

The formula for calculating the effective annual rate is:

$$\text{Effective Rate} = \left(1 + \frac{\text{Nominal Rate}}{\text{Periods per Year}}\right)^{\text{Periods per Year}} – 1$$

This formula accounts for how compounding increases the interest earned or paid.

Example

Let’s say you have the following information:

• Nominal interest rate: 6% (0.06)
• Compounding periods per year: 12 (monthly compounding)

You can use the EFFECT function to calculate the effective annual rate:

=EFFECT(0.06, 12)

Explanation of the Example:

• 0.06: The nominal interest rate (6%).
• 12: The compounding periods per year (monthly compounding).

The EFFECT function will calculate the effective annual rate, which takes into account the compounding that occurs 12 times a year.

The result will be approximately 0.0617, or 6.17%, which is the effective annual interest rate.

Important Notes

• The nominal rate is the interest rate that is quoted by lenders or investors and typically does not take compounding into account. The effective rate reflects the actual rate earned or paid due to compounding.
• The number of compounding periods per year (npery) must be a positive integer, representing how often interest is compounded annually (e.g., 12 for monthly, 4 for quarterly).
• The EFFECT function is especially useful when comparing different interest-bearing financial products that compound at different intervals.

Summary

The EFFECT function in Excel is useful for calculating the effective annual interest rate based on a given nominal interest rate and compounding periods per year. It helps provide a clearer picture of the actual interest rate, considering the effects of compounding over the course of a year.