Effective annual interest rate

Effective annual interest rate - Excel formula

To calculate effective annual interest rate use the EFFECT functiom based on the nominal interest rate and the compounding frequency.

How to calculate effective annual interest rate?

  1. Select cell G2.
  2. Type =EFFECT(rate, C3)
  3. Here, rate is the annual nominal interest rate, and C3 represents the number of compounding periods.
  4. Press Enter.
  5. The formula calculates and returns the Effective Annual Rate (EAR) for the specified compounding frequency.

Example

In this example, we have a dataset in columns B through D. It calculates the effective annual rate (EAR) based on different compounding frequencies. The dataset includes compounding frequencies in column B. These frequencies are yearly, semiannual, quarterly, and others. Column C contains the corresponding number of periods per year.

The goal is to create a formula to calculate the effective interest rate for each frequency. We use the annual nominal interest rate provided in cell G2.

Formula:

=EFFECT($G$2,C3)

Effective annual interest rate - Excel formula

The formula in cell D3 uses the EFFECT function to calculate the EAR dynamically based on the nominal rate and the compounding periods.

Explanation

The EFFECT function calculates the effective annual interest rate (EAR) from a given nominal annual rate and the number of compounding periods per year.

The syntax is: EFFECT(nominal_rate, npery)

  • nominal_rate: The nominal annual interest rate (in this case, $G$2, which is 6% or 0.06).
  • npery: The number of compounding periods per year. In this case, referenced from C3, which varies depending on the row.

$G$2: $G$2 is an absolute reference to the cell containing the nominal annual rate, which is 6%. The dollar signs ensure that this reference doesn’t change when the formula is copied to other cells.

C3 contains the number of compounding periods per year. For example, in row 3 (Yearly): C3 = 1 (compounded once per year). In row 4 (Semiannual): C4 = 2 (compounded twice per year). And so on for other frequencies.

Since there is only one compounding period, the EAR remains the same as the nominal rate: 6.000%.

Download the Workbook.