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.

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

• 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

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.

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

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

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

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

• Here’s the result.

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

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.

• We added 3 rows in the dataset for calculation.

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

• Press the Enter button.

• Use the formula below to calculate the Proper Day Fraction.

• Click the Enter button.

• Multiply the Face Value, Proper Interest Rate, and Proper Day Fraction in cell C13.

• 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

• How to Calculate Gold Loan Interest in Excel
• How to Calculate Credit Card Interest in Excel
  
• How to Calculate Home Loan Interest in Excel
• How to Calculate Interest on a Loan in Excel
• How to Calculate Principal and Interest on a Loan in Excel

<< Go Back to Excel for Finance | Learn Excel