How to Calculate the Effective Interest Rate Including Discount Points

Borrowers, lenders, investors and savers must have a way to precisely communicate financial data so that it is well understood by all. The effective interest rate (EIR) is one such data item, expressing the cost of a loan or the interest income earned by an investor or saver.

Two important concepts are needed to understand the EIR and similar interest rates on loans:

• ‌Compounding‌: This occurs when you add a period’s accrued interest to the loan principal. The effect of compounding means you pay interest on interest. The interest charged increases as you decrease the compounding period (or, equivalently, increase the number of times compounding occurs within a year). The highest interest results from continuous compounding, whereas simple (i.e., non-compounded) interest generates the lowest interest amount.
• ‌Fees‌: Many loans, including mortgages and personal loans, charge closing fees. These include origination fees, title fees, lawyer’s fees, etc. You can pay fees upfront or roll them into the loan. In the latter case, you will pay interest on a larger principal, increasing a loan’s overall cost.

The EIR is also called the effective annual interest rate (EAR), the annual percentage yield (APY) or the annual equivalent rate (AER). It is the compounded percentage interest rate that you pay on a loan, expressed on a yearly basis. It differs from the nominal interest rate, a rate of return using simple interest (i.e., non-compounded interest).

An EIR will never be less than the nominal rate and is usually greater because loans frequently employ compound interest. A third standard rate of interest, the annual percentage rate (APR), includes the fees required to get a loan but excludes compounding. APR is used for advertising credit card interest.

The effective interest rate formula is ‌Effective Interest Rate = (1 + i/n)^n − 1‌, where‌:‌

‌i‌ = nominal interest rate

‌n‌ = number of periods​

You can calculate the EIR by hand or with a calculator, but Excel has a convenient function that makes it easy to compare different loan offers.

According to Microsoft Support, you use Excel’s EFFECT function to calculate the EIR:

‌EIR = EFFECT(nominal interest rate, number of compounding periods per year)‌

You can substitute the loan’s APR for the nominal interest rate since both are non-compounded, but be aware that the result will include the effect of fees.

Suppose you shop for a mortgage from three banks, all charging the same closing costs. Bank A offers you a 30-year mortgage with monthly compounding and a nominal interest rate of 6 percent. You can quickly convert the number in Excel to an EIR as follows:

‌EIR = EFFECT(6%, 12) = 6.1678%‌

You can instead use the EIR equation:

‌EIR = (1 + 6%/12)^12 – 1 = 6.1678%‌

Bank B offers you daily compounding on a 30-year mortgage at 6 percent. Excel calculates EIR as follows:

‌EIR = EFFECT(6%, 365) = 6.1831%‌

Bank C offers 6 percent continuously compounded, which requires a different formula:

‌EIR = ei − 1‌

where e is the exponential constant, approximately equal to 2.718 and available from Excel via the EXP function. Plugging EXP into the EIR function:

‌EIR = EXP(1)^6% - 1 = 6.1837%.‌

Unless Banks B or C offer a free toaster oven, you want to borrow from Bank A. It has the lowest compounding frequency, producing the lowest EIR. In real life, the banks will probably charge different closing costs, so you will also want to compare all three APRs.

As described by the Consumer Financial Protection Bureau, discount points reduce a mortgage’s interest rate. Each point is equal to ‌1 percent‌ of the loan amount. You pay the points upfront to receive the rate reduction on the loan’s EIR, APR or nominal rate.

Typically, lenders will cut the mortgage rate by 25 to 50 basis points (i.e., ‌0.25 percent to 0.50 percent‌) for each discount point you purchase. Discount points can reduce the overall mortgage cost over 30 years but may not save you money if you sell the house after a few years.