How to Calculate the Real Interest Rate on a Loan in Excel

There are two types of interest rates: nominal (official) – the one specified in the loan agreement, and effective (real) – the one derived from compound interest calculations. Determining the cost of a loan, considering the time factors of capitalization and payments, is known as discounting.



What is the Real Interest Rate of Compound Interest Annually?

There are two approaches to discounting (converting amounts with interest rates): nominal and effective.

  1. Nominal discounting – the interest rate is stated along with the capitalization period. For example, an annual interest rate of 3.75% with monthly capitalization. In this example, 3.75% is the nominal discounting used throughout the year, and the capitalization period is one month. However, with monthly capitalization, the amount for the year will grow more than 3.75% due to compound interest, resulting in an effective interest rate of 3.82%.
  2. Effective discounting – determines the actual (real) amount of interest paid. If the nominal discounting period is the same as the capitalization period (for example, annual interest with annual capitalization), then the nominal discounting equals the effective discounting. However, if the capitalization period is shorter than the nominal discounting period, as is often the case in practice, the effective discounting will be higher than the nominal.

To calculate the effective interest rate in Excel, use the EFFECT function, and for the nominal rate – use the NOMINAL function. Here is a simple example of these facts:

EFFECT and NOMINAL functions.

Below is a chart showing 12 selected capitalization periods for a thirty-year loan. The initial loan amount is $165,000. The nominal discounting with monthly capitalization is 3.75% annually, and the annual payment is $9,169.68.

Loan payment chart.

The amount of the first payment in cell B14 is calculated using the Excel formula:

=PMT(E16/E21,E20*E21,E22,)*E21*-1

As seen in the illustration, using the EFFECT function, there is no need to create a full loan payment schedule to immediately calculate the real effective interest rate.

The chart shows two methods for calculating the effective interest rates on the loan. In each period, capitalization starts, but the unpaid loan payment increases the debt by the amount of interest. Each loan payment consists of two parts:

  1. Interest amount – payment for using the loan.
  2. Principal – the portion that reduces the loan balance.

Cell E17 contains the interest amount accrued over the year. This value is divided by the initial loan amount, and the result is returned in cell E18. To recalculate the discount rate in Excel, there is no need to build a complete chronology of all loan payments over the 30-year period. Excel offers the special EFFECT and NOMINAL functions that allow you to calculate the nominal and effective interest rates without a payment schedule:

Both the EFFECT and NOMINAL functions have two arguments:

  1. Type of discounting – the type of discounting to be used: for the EFFECT function, the nominal rate should be specified, and for the NOMINAL function, the effective rate should be specified.
  2. Number of periods – the capitalization period.

For the NOMINAL function, you should specify the effective discounting method, and for the EFFECT function, you should specify the nominal rate. The second argument, the number of periods, defines the number of capitalization periods over the current payment term. In this example, the period is 1 year, as the annual payment term was used. The year consists of 12 months, so the nominal discounting is divided into 12 capitalization periods. If the loan terms included daily capitalization, the second argument would be 365.

Formula for Calculating the Real Interest Rate in Excel Using the FV Function

The real interest rate for a loan or deposit with compound interest, that is, the effective discounting method, can also be calculated using the FV (Future Value) function. If your version of Excel provides the EFFECT function, there is no need to use the older FV function, but understanding the differences between these two functions may be useful when working with files created in older versions of Excel. The formula is as follows:

FV Function.

Download example of calculating real interest rate on loan in Excel download file

This formula calculates the future value of a loan starting with $1 at an annual interest rate of 3.75%, with monthly capitalization over the entire year. Then the initial loan amount is subtracted from the result of the formula. Under these conditions, the total debt to the bank will be $1.03815. This means that the bank will earn $0.03815 on this loan. The real effective interest rate for this loan is 3.82% annually.