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.

ACCRINT Function to Calculate Accrued Interest on a Bond

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)

ACCRINT Function to Calculate Accrued Interest on a Bond

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)

ACCRINT Function to Calculate Accrued Interest on a Bond

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)

Excel DATE & ACCRINT Functions to Determine Accrued Interest

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)

Excel DATE & ACCRINT Functions to Determine Accrued Interest

Here’s the result.

Excel DATE & ACCRINT Functions to Determine Accrued Interest

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

Measure the Accrued Interest on Bond Using the DAYS360 Function

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

Accrued Interest based on the Excel YEARFRAC Function

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.

Accrued Interest based on the Excel YEARFRAC Function

To get the interest based on the actual day count, choose 1 as the last argument.

=YEARFRAC(C5,C9,1)*C10*C11

Accrued Interest based on the Excel YEARFRAC Function

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.