How to Calculate the Effective Interest Rate On Bonds Using Excel – 4 Methods

Annual Percentage Rate/Nominal Interest Rate: 12%

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

If you did not pay the interest ($100) for the first month, in the 2^nd month your principal will be $10,100.

At the end of Month 2: your Principal + Interest will be = $10100 + $10100 x 1% = $10201

To create a formula from the above statement:

= $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

At the end of the 3^rd month, your Principal + Interest will be: $10,000 x (1 + 0.01) ^ 3

… … …

… … …

… … …

After 12 months, your Principal and Interest will be: $10,000 x (1 + 0.01) ^12 = $11268.25

You are going to pay a total interest: ($11268.25 – $10,000) / $10,000 = 12.68%.

The bank indicated an Annual Percentage Rate of 12%.

Formula Breakdown 

• In year 0, you invest $95,000 (the issue price of the bond) to buy the bond. You pay $95,000 because it’s a discount bond.
• At the end of the 1^st year, you get $5,000 as the interest payment from the bond issuer. The face value of the bond is $100,000 and the nominal yearly interest rate is 5%. You get an interest payment amount of: $100,000 x 5% = $100,000 x 0.05 = $5,000.
• After the 2^nd, 3^rd, and 4^th years, you get interest payments of $5,000.
• At the end of the 5^th year, you get $105,000. Because your bond has matured. You get back the face value of the bond, of $100,000 + the yearly interest of $5,000 = $105,000.
• The IRR (Internal Rate of Return) or the Effective Interest Rate was calculated in C17: =IRR (C11: C16)

Formula Breakdown 

• The issue price is $105,000. In year 0, the investment is -$105,000.
• After years 1, 2, 3, and 4, the bondholder will get $5,000 in interest payments. The bond’s face value is $100,000 and its yearly nominal interest rate is 5%. The yearly interest payment is $100,000 x 5% = $5,000.
• At the end of year 5, the bond will mature. The bondholder will get the face value amount and the yearly interest payment: $100,000 + $5,000 = $105,000.
• The IRR function was used to calculate the internal rate of return or effective interest rate for these cash flows in C17: =IRR (C11: C16).

Formula Breakdown 

• As the interest payments are done semi-annually (twice a year), the stated rate/nominal interest rate of 5% is divided by 2: 2.5%. The semi-annual interest payment will be $100,000 x 2.5% = $2,500.
• 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, use Excel’s XIRR function.
• C11: C21 are the cash flows received, and B11: B21 is the date of receiving the cash flows. 6.274% is the effective interest rate for these cash flows.