How to Calculate Effective Interest Rate On Bonds Using Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Calculate Effective Interest Rate On Bonds Using Excel

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

The effective interest rate is the interest rate that you get or have to actually pay on your investment or for your loan. For a given interest rate and frequency of compounding within a year,

Effective Interest Rate, r = (1 + i/n)n – 1

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.

Discount Bond Components

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.

=IRR(C11:C16)

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).

=IRR(C11:C16)

Calculate Effective Interest Rate of Premium Bonds Using Excel

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.

=XIRR(C11:C21,B11:B21)

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.

Read More: How to Perform Interest Rate Swap Calculation in Excel


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

EFFECT(rate,periods)

Steps: Use the following formula to calculate the effective interest rates for a given interest rate and frequencies.

=EFFECT($E$3,C6)

How to Calculate Different Effective Interest Rates in Excel

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


Conclusion

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.


Related Articles

<< Go Back to Interest Rate Calculator | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

2 Comments
  1. I want printed materials for courses

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo