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

We will show how to calculate this accrued interest on a bond using different formulas in Excel. We will calculate the accrued interest based of the information in our sample dataset. Here, interest will be accrued bi-annually. The initial price of the bond is \$10,000 and our settlement date is 3/2/2022, so the accrued interest will be calculated from the issue date to this date.

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

The ACCRINT function gives the profit from a bond periodically.

Steps:

• Add a new cell to calculate the accrued interest.

• Go to the new cell.
• Insert the following formula.
`=ACCRINT(C5,C7,C9,C10,C11,C12,0,1)`

• Press Enter.

We can also calculate the accrued interest on a bond from the last interest date.

• Change the last argument of the formula from 1 to 0.
`=ACCRINT(C5,C7,C9,C10,C11,C12,0,0)`

• Click the Enter button.

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

Method 2 – Combine DATE and ACCRINT Functions to Determine Accrued Interest

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

Steps:

• Go to cell C14.
• Insert the following formula. We input dates directly through the DATE function here.
`=ACCRINT(DATE(2021,1,1),DATE(2022,1,1),DATE(2022,3,2),C10,C11,C12,0,1)`

• Press the Enter button.

We get the accrued interest from the issue date.

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

• Modify the last argument of the formula from 1 to 0.
`=ACCRINT(DATE(2021,1,1),DATE(2022,1,1),DATE(2022,3,2),C10,C11,C12,0,0)`

• Here’s the result.

Method 3 – Measure the Accrued Interest on a Bond Using the DAYS360 Function

The DAYS360 function considers every month to be 30 days and the whole year is 360 days.

Steps:

• Go to cell C14.
• Insert the following formula.
`=DAYS360(C5,C9,0)/360*C10*C11`

• Press Enter.

Method 4 – Estimate the Accrued Interest Using the YEARFRAC Function

The YEARFRAC function represents the fraction value of the year calculating the difference between two dates.

Steps:

• Go to cell C14 and insert the following formula.
`=YEARFRAC(C5,C9,0)*C10*C11`

• Press Enter.

We calculated the value based on U.S. standards. We can get other standards by changing the 3rd argument of the YEARFRAC function.

4 alternative options are available here.

• To get the interest based on the actual day count, choose 1 as the last argument.
`=YEARFRAC(C5,C9,1)*C10*C11`

• Here’s the result.

Method 5 – Make a Formula to Calculate the Accrued Interest

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

We will create a new formula based on the equation below.

`Accrued interest = Face Value* Proper Interest Rate* Proper Day Fraction`

• We added 3 rows in the dataset for calculation.

• Go to cell C11 now.
• Determine the Proper Interest Rate by applying the following formula.
`=C7/C9`

• Press the Enter button.

• Use the formula below to calculate the Proper Day Fraction.
`=C6/C5`

• Click the Enter button.

• Multiply the Face Value, Proper Interest Rate, and Proper Day Fraction in cell C13.
`=C8*C11*C12`

• Press Enter.

• We calculated the accrued interest on a bond annual basis. We can modify this formula for a bi-annual interest by changing the Total Number of Days and Payment Period of the dataset.

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.

Download the Practice Workbook

=

Related Articles

<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF