How to Calculate Accrued Interest on a Bond in Excel (5 Methods)

The accrued interest on a bond is specified as the amount of earning on a debt. Here, the bond is considered to be a debt. And when the owner of a bond sells this to others, he gets a profit from that bond. In this article, we will discuss 5 methods to calculate accrued interest on a bond in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

What is Accrued Interest on a Bond?

Accrued interest is considered as the profit for the investors. Usually, investors buy bonds and get profit on each period. Maximum bonds pay profit bi-annually. Some bonds run yearly. If an investor is willing to sell the bond before the maturity period, he gets a profit from the buyer. This is called accrued interest of a bond. This interest is calculated based on the last settlement date to the selling date.

Where,

Face Value                  = Initial Price of the Bond

Daily Interest Rate       =Annual interest/365

Days                             =Last Issue date- Settlement date

5 Methods to Calculate Accrued Interest on a Bond in Excel

We will show how to calculate this accrued interest on a bond using different formulas in Excel. A sample dataset containing the information of a bond is given below. We will calculate the accrued interest basis of this information.

In this proposal, interest will be given bi-annually. So, every six months investors will get profit. The initial price of the bond is $10000 and our settlement date is 3/2/2022. So, the accrued interest will calculate from the issue date to this date.

1. Apply the ACCRINT Function to Calculate Accrued Interest on a Bond

The ACCRINT function gives the profit from a bond periodically.

We will use this ACCRINT function to calculate accrued interest in this segment.

Step 1:

• First, add a row to calculate this accrued interest.

Step 2:

• Go to Cell C12.
• Write down the following formula.

Step 3:

• Now, press Enter.

Here, we get the accrued interest from the issue date to the settlement date.

We can calculate the accrued interest on a bond from the last interest date. Because the investor got the interest of that date. So, when he sells this to a new investor, he will give the interest from the last interest payment date to the settlement date.

Step 4:

• Go to Cell C14 Change the last argument of the formula from 1 to 0.

Step 5:

• Click the Enter button.

This accrued interest is from the last payment date to the settlement date.

Read more: How to Calculate Accrued Interest on a Loan in Excel

2. Combine DATE & ACCRINT Functions to Determine Accrued Interest

The DATE function gives a date value from some sequential numbers.

We can combine the DATE and ACCRINT functions to determine the accrued interest on a Bond.

Step 1:

• Now, go to Cell C14.
• Put the following formula. We input dates through the DATE function here.

Step 2:

• Press the Enter button.

We get the accrued interest from the issue date.

We can determine the accrued interest from the last interest payment date to the settlement date.

Step 3:

• We’ve modified the last argument of the formula from 1 to 0. So, the formula becomes:

Step 4:

• Click the Enter button.

Read more: How to Calculate Interest Between Two Dates Excel

3. Measure the Accrued Interest on Bond Using the DAYS360 Function

The DAYS360 function considers every month is 30 days and the whole year is 360 days. It does not consider the different lengths of different months.

We will use this function and find out the accrued interest easily.

Step 1:

• Go to Cell C14.
• Write down the following formula.

Step 2:

• Now, press Enter.

So, this is our desired interest. We’ve used the FALSE argument for the USA standard. For Europe, we can use the TRUE argument.

Read more: How to Calculate Interest Rate in Excel

Similar Readings

• Calculate Interest in Excel with Payments (3 Examples)
• How to Calculate Daily Interest in Excel (2 Easy Ways)
• Bank Interest Calculator in Excel Sheet – Download Free Template
• How to Find Interest Rate in Future Value Annuity (2 Examples)

4. Guess Accrued Interest Using the YEARFRAC Function

The YEARFRAC function represents the fraction value of the year calculating the difference between two dates. It offers 5 options when calculating value.

Step 1:

• Now, go to Cell C14 and put the formula below.

Step 2:

• Now, press Enter.

Find our desired value.

We calculate this based on USA standards. We can get other standards by changing the 3rd argument of the YEARFRAC function.

4 alternative options are available here.

Step 3:

• We want to get the interest basis on the actual day count. So, choose 1 as the last argument.

Step 4:

• Again, press Enter button.

Notice the values. The interest of the USA and actual day count standard are respectively $877.08 and $873.29. Values changes in different standards.

Read more: How to Calculate Accrued Interest on Fixed Deposit in Excel

5. Make a Formula to Calculate Accrued Interest

We have the following dataset that does not contain any date value.

For this kind of dataset, we create a new formula based on the equation below.

The accrued interest on a bond will be calculated on yearly basis according to the dataset.

We added 3 rows in the dataset for calculation.

Step 1:

• Go to Cell C11 now.
• Determine the Proper Interest Rate applying the following formula.

Step 2:

• Press the Enter button.

Step 3:

• Enter Cell C12 now.
• Put the formula below to calculate Proper Day Fraction.

Step 4:

• Click the Enter button.

Step 5:

• Now, multiply the Face Value, Proper Interest Rate, and Proper Day Fraction on Cell C13.

Step 6:

• Finally, press Enter.

Here, we calculate the accrued interest on a bond annual basis.

We can modify this formula on a bi-annual basis. Just change the Total Number of Days and Payment Period of the dataset.

Read more: Simple Interest Formula in Excel

Things to Remember

• In the U.S., we use 30/360 for corporate and municipal bonds, while U.S. Treasury notes and bonds use the actual/actual day count basis.
• Only the ACCRINT formula accurately returns accrued interest on any date.
• Carefully select the dates when using the ACCRINT function.

Conclusion

In this article, we described 5 methods to calculate accrued interest on a bond in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Further Readings

• How to Calculate Monthly Interest Rate in Excel (3 Simple Methods)
• Calculate Interest Rate on a Loan in Excel (2 Criteria)
• Daily Loan Interest Calculator in Excel (Download for Free)
• How to Calculate Gold Loan Interest in Excel (2 Ways)