MS Excel: EFFECT function for EAR and AER

The EFFECT function in Excel calculates the effective annual interest rate (EAR) or annual equivalent rate (AER) based on the nominal interest rate and the number of compounding periods per year.

The EFFECT function is used to determine the true annual interest rate when interest is compounded more frequently than annually. This is particularly useful in financial analysis to compare rates with different compounding periods.

Syntax

EFFECT(nominal_rate, npery)

Argument	Description
nominal_rate	The nominal annual interest rate (as a decimal). For example, 5% should be entered as 0.05.
npery	The number of compounding periods per year. For example, if interest is compounded quarterly, npery would be 4.

Key Points

• The EFFECT function calculates the effective annual interest rate based on the nominal rate and compounding frequency.

• This is important for comparing different investments or loans with varying compounding periods.

• The EFFECT function assumes the interest is compounded regularly over the course of a year, with the result reflecting the true annual interest rate.

  
  

Practical Example

Scenario:

You are comparing two investment options. One offers a nominal annual interest rate of 6%, compounded quarterly. You want to calculate the effective annual interest rate to understand the actual return after compounding.

Formula:

=EFFECT(0.06, 4)

Explanation:

• nominal_rate: The nominal interest rate is 6% (entered as 0.06).

• npery: Interest is compounded quarterly, so npery is 4.

Result: 0.06136 or 6.136%

Explanation:The effective annual interest rate (EAR) is 6.136%, which is higher than the nominal rate of 6% because the interest is compounded quarterly. The EAR reflects the true return on the investment when accounting for compounding.

Another Example:

Consider an investment with a nominal rate of 8% that is compounded monthly. You want to calculate the effective annual interest rate.

Formula:

=EFFECT(0.08, 12)

Result: 0.08328 or 8.328%

Explanation:The effective annual interest rate is 8.328%, which is slightly higher than the nominal rate due to the monthly compounding.

Summary

Item	Value
Nominal Rate	6% (0.06)
Compounding Periods	4 (Quarterly)
Effective Annual Rate	6.136%

Important Notes

• The EFFECT function assumes that the nominal rate is expressed as a decimal. For example, for a 5% nominal rate, input 0.05.

• The npery argument determines how frequently interest is compounded per year. Common values are:

  • 1 for annual compounding

  • 4 for quarterly compounding

  • 12 for monthly compounding

  • 365 for daily compounding (rarely used for practical purposes)

• The EFFECT function gives a more accurate view of the true annual rate of return or cost on an investment, loan, or credit card when interest is compounded.

When to Use EFFECT?

• To compare the true interest rate of different financial products that have varying compounding periods.

• In loan comparisons, to determine the true cost of loans with different compounding frequencies.

• For investment analysis to calculate the real return on investments that compound interest periodically.

• In credit card interest rate calculations, where interest is compounded daily, monthly, or quarterly.

Conclusion

The EFFECT function in Excel is a valuable tool for calculating the effective annual interest rate (EAR), which gives you the true annual interest rate after considering compounding. It helps you compare investments or loans with different compounding intervals to understand their real returns or costs.