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.

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

`=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.

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

`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

- 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**