How to calculate effective interest rate on bonds using Excel In this blog post, I will discuss how to calculate the effective interest rate on bonds using Excel.

What is the effective interest rate?

If you know what an effective interest rate is, you can skip this section. Go to the next section.

The effective interest rate is the interest rate that you get or have to pay actually from your investment or for your loan.

Say, you took a loan of amount \$10,000 from a local bank with the annual percentage rate of 12%. The bank said that your interest will be compounded every month.

You don’t know what that means. Read carefully, it is important to understand this concept.

Annual Percentage Rate/Nominal Interest Rate: 12%

So, 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 for 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, so, 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.

This article will give you a more detailed idea about the nominal and effective interest rate: Nominal Interest Rate Formula Excel (calculate from Effective Rate).

Let’s now calculate the effective interest rate on bonds.

Example 1: Calculating effective interest on discount bonds

You have some extra money and you want to invest your money in a bond.

Here are the details of the bond:

• Face Value: \$100,000
• Stated Rate/Nominal Rate/Coupon Rate/APR: 5%
• Maturity Period: 5 years
• Interest Payment Frequency: Yearly
• Issue Price: \$95000 (the bond is selling on a discount)

If you want to know more about the bond, go through this link.

Calculating Effective interest rate and calculating the Internal Rate of Return actually the same thing. So, I will show you how to calculate the Internal Rate of Return of the above bond.

Take a look at the following image. The image is self-explanatory. But let me explain it step by step.

At year 0, you’re investing \$95,000 (issue price of the bond) to buy the bond. You’re paying \$95,000 because the bond is selling in discount.

At the end of the 1st year, you’re getting \$5,000 as the interest payment from the bond issuer. How this \$5,000 is calculated?

The face value of the bond is \$100,000 and nominal yearly interest rate is 5%. So, you will get interest payment of amount: \$100,000 x 5% = \$100,000 x 0.05 = \$5,000.

In the same way, after 2nd, 3rd, and 4th years, you will get the interest payments of amount \$5,000.

But after the end of 5th year, you will get \$105,000. Because your bond has matured and you will get back the face value of the bond \$100,000 + the yearly interest \$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)

IRR function returns the internal rate of return for a series of cash flows.

Syntax of IRR function: IRR (values, [guess])

As the values of the IRR function, we have input the periodical cash flows and as the guess, we did not pass any value as it is not required.

And we get 6.19% as the effective rate of these cash flows.

So, you see it is not tough to calculate the effective rate on bonds that are sold in discount.

Example 2: Calculating effective interest in premium bonds

In this example, our bond is selling with a premium.

Here are the details of the bond:

• Face Value: \$100,000
• Stated Rate/Nominal Rate/Coupon Rate/APR: 5%
• Maturity Period: 5 years
• Interest Payment Frequency: Yearly
• Issue Price: \$105,000 (the bond is selling with premium) For this bond, issue price is \$105,000. So, at year 0, our investment is showing -\$105,000.

After year 1, 2, 3, and 4, bondholder will get interest payments of amount \$5,000. Because the bond’s face value is \$100,000 and its yearly nominal interest rate is 5%. So, every year 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 = \$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 the Excel’s IRR function to calculate the internal rate of return or effective interest rate for these cash flows in the cell C35: =IRR (C29: C34).

And we get the internal rate of return or the effective interest rate of 3.88%.

So, you see the huge difference of getting a bond in discount and in premium.

But 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, your money is safer with the company.

Example 3: Calculating effective interest when payments are not yearly

In this example, I will show you a bond that pays interest payments semi-annually (twice a year), not yearly. Some issuers of bonds might even pay monthly, quarterly, or at any frequency.

Here are the details of the bond:

• Face Value: \$100,000
• Stated Rate/Nominal Rate/Coupon Rate/APR: 5%
• Maturity Period: 5 years
• Interest Payment Frequency: Semi-annually (twice a year)
• Issue Price: \$95,000 (the bond is selling on a discount)

Say, you bought the bond on 1-Jan-2018 and here will be your cash flows for the next 5 years. 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 payments will be \$100,000 x 2.5% = \$2,500.

And when the bond will mature, you will get \$102,500 (face value + the last 6 months’ interest).

To get the internal rate return or the effective rate of these cash flows, we have to use Excel’s XIRR function. Because the cash flows are not yearly. When the cash flows are received in discrete manners, you have to use the XIRR function to get the internal rate of return of the cash flows.

XIRR function returns the internal rate of return for a schedule of cash flows.

Syntax of XIRR function: XIRR (values, dates, [guess]).

In the cell G22, I have used this formula: =XIRR (G11: G21, F11: F21)

G11: G21 are cash flows received, and F11: F21 are the dates of receiving the cash flows.

And we get 6.274% effective interest rate for these cash flows.