The Effective Interest Rate reflects the true annual yield or cost of an investment or loan gainable or payable due to the compounding over the period. Users can calculate the effective interest rate on bonds using Excel. Thus, Excel’s IRR, XIRR, and EFFECT functions and the conventional effective interest rate formula can calculate the effective interest rate.
In this article, we discuss the effective interest rate with examples and methods to calculate it on bonds in Excel.
Effective Interest Rate with Example
= (1 + i/n)
The offered Interest Rate = i
And Frequency of the Compounding in a Year = n
Say, you took a loan of $10,000 from a local bank with an annual percentage rate of 12%. The bank said that your interest would be compounded every month. You don’t know what that means. Read the following scenario carefully; it is important to understand this concept.
Annual Percentage Rate/Nominal Interest Rate: 12%
So, the monthly interest rate: 12%/12 = 1%
At the end of Month 1: your Principal + Interest will be: $10,000 + $10,000 x 1% = $10,000 (1 + 0.01) = $10100
Here, I am assuming that you did not pay the interest ($100 in this case) for the first month. So, for the 2nd month, your principal will be $10,100.
At the end of Month 2: your Principal + Interest will be = $10100 + $10100 x 1% = $10201
As I want to make a formula from the above statement, I am going to restate the above line in the following way:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); As $10100 = $10,000 x (1 + 1%)
= $10,000 x (1 + 1%) ^2; As (1 + 1%) x (1 + 1%) = (1 + 1%) ^2
= $10,000 x (1 + 0.01) ^ 2
In this way, at the end of the 3rd month, your Principal + Interest will be: $10,000 x (1 + 0.01) ^ 3
… … …
… … …
… … …
In this way, after 12 months, your Principal and Interest will be: $10,000 x (1 + 0.01) ^12 = $11268.25
So, you are going to pay total interest: ($11268.25 – $10,000) / $10,000 = 12.68%. But the bank said that your Annual Percentage Rate was 12%.
What the bank said was your Nominal Interest Rate, but due to compound interest monthly, you’re paying more than the nominal interest rate. This interest rate is called the Effective Interest Rate.
Go through the below section to learn more about Effective Interest Rate calculation for different bonds.
How to Calculate Effective Interest Rate On Bonds Using Excel: 4 Easy Methods
Excel has multiple in-built functions to calculate various interest rates for bonds or investments. Follow any of the below methods to calculate the effective interest rate on bonds using Excel. To accomplish the task, we will apply several functions including the IRR, XIRR, and EFFECT functions.
Method 1: Using IRR Function to Calculate Effective Interest Rate On Discount Bonds
The Effective Interest Rate and the Internal Rate of Return are actually the same thing. So, Excel’s IRR function that calculates the Internal Rate of Return can be used to calculate the Effective Interest Rate as well.
Users may want to invest money in a Discount Bond. In that case, users can use the IRR function to find the Effective Interest Rate. The IRR function returns the internal rate of return for a series of given cash flows. The syntax of the IRR function is
IRR (values, [guess])
The details of the discount bond are depicted in the below image.
To calculate the Effective Interest Rate, users don’t need to pass any other values other than the IRR function arguments.
Steps: Type the following formula in the C17 cell.
Explanation of the Scenario
- At year 0, you’re investing $95,000 (the issue price of the bond) to buy the bond. You’re paying $95,000 because the bond is selling at a discount.
- At the end of the 1st year, you’re getting $5,000 as the interest payment from the bond issuer. How is this $5,000 calculated? The face value of the bond is $100,000 and the nominal yearly interest rate is 5%. So, you will get an interest payment amount: $100,000 x 5% = $100,000 x 0.05 = $5,000.
- In the same way, after the 2nd, 3rd, and 4th years, you will get the interest payments of $5,000.
- But after the end of the 5th year, you will get $105,000. Because your bond has matured, you will get back the face value of the bond, of $100,000 + the yearly interest of $5,000 = $105,000. So, these are the cash flows you get over the period of holding the above bond.
- Now, we have calculated the IRR (Internal Rate of Return) or the Effective Interest Rate using excel’s IRR function in the cell C17: =IRR (C11: C16)
Method 2: Calculating Effective Interest Rate for Premium Bonds in Excel
In the case of users investing in premium bonds, the same IRR function can calculate the Effective Interest Rate. The process is the same as what they did for discount bonds. The basic components of the premium bond are shown in the latter image.
Steps: Apply the below formula to any blank cell (i.e., C17).
Explanation of the Scenario
- For this bond, the issue price is $105,000. So, at year 0, our investment is showing -$105,000.
- After years 1, 2, 3, and 4, the bondholder will get $5,000 in interest payments because the bond’s face value is $100,000 and its yearly nominal interest rate is 5%. So, every year, the interest payment will be $100,000 x 5% = $5,000.
- At the end of year 5, the bond will mature. So, the bondholder will get the face value amount and the yearly interest payment of = $100,000 + $5,000 = $105,000. So, for this bond, these are the cash flows that the bondholder will receive for the next 5 years.
- We have used Excel’s IRR function to calculate the internal rate of return or effective interest rate for these cash flows in the cell C17: =IRR (C11: C16).
If you compare the Effective Interest Rate for both cases, you’ll see there’s a huge difference between discount and premium bonds’ rates.
⧭Tips: Before buying a bond, you will not just calculate the effective interest rate; you will also check out the bond’s rating (by an Independent Rating Company). The higher the rating of a bond, the safer your money is with the company.
Method 3: Finding Effective Interest Rate for Semi-annually Payments
What if a bond pays interest not yearly but semi-annually (twice a year)? To some extent, some issuers of bonds might even pay monthly, quarterly, or at any frequency. The XIRR function returns the internal rate of return for a schedule of cash flows. As we mentioned earlier, the Internal Rate of Return and the Effective Interest Rate are the same. The syntax of the XIRR function is
XIRR (values, dates, [guess])
The fundamental components of the discount bond are shown below.
Steps: Apply the following formula to the C22 cell.
Explanation of the Scenario
- As the interest payments will be done semi-annually (twice a year), we have to divide the stated rate/nominal interest rate of 5% by 2. That will return 2.5%. So, our semi-annual interest payment will be $100,000 x 2.5% = $2,500.
- And when the bond matures, you will get $102,500 (face value + the last 6 months’ interest). To get the internal rate of return or the effective rate of these cash flows, we have to use Excel’s XIRR function because the cash flows are not yearly. Since the cash flows are received in a discrete manner, you have to use the XIRR function to get the internal rate of return of the cash flows.
- C11: C21 are the cash flows received, and B11: B21 is the date of receiving the cash flows. And we get a 6.274% effective interest rate for these cash flows.
Method 4: Applying EFFECT Function to Calculate Different Effective Interest Rates
In the previous methods, we calculated the Effective Interest Rate from scheduled cash flows from an investment or bonds. What if users want to calculate the Effective Interest Rates only? Excel’s EFFECT function calculates the Effective Interest Rate of different types of payments within a year. The syntax of the EFFECT function is
Steps: Use the following formula to calculate the effective interest rates for a given interest rate and frequencies.
The typical Effective Interest Rate formula ( (1 + i/n)n – 1) in the adjacent cell to cross-check the EFFECT function’s outcomes.
Download Excel Workbook
This article discusses the effective interest rate and methods to calculate it for bonds or investments using functions in Excel. Follow any of the described methods to find the desired effective interest rate. Comment if you have further inquiries or have anything to add.