Accrued Interest refers to the payable or receivable interest on a loan or bond at a certain period. Accrued interest doesnâ€™t compound during the coupon period, so the interest occurs on a daily basis. It accrues on loans such as mortgages, savings accounts, student loans, and other investments. In this article, weâ€™re going to show you how to create an accrued interest calculator in Excel. Afterward, the accrued interest calculator in Excel can find the accrued interest upon data insertion.

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

## What Is Accrued Interest?

For investors, accrued interest is regarded as a profit. Investors often purchase bonds and as a result, they gain profit every time. Maximum bonds pay interest twice a year. Some bonds are annualized. An investor makes money from the buyer if he is willing to sell the bond before it matures. This is referred to as bond accrued interest. Based on the most recent settlement date to the selling date, this interest is computed.

**Accrued Interest = Face Value * Daily Interest Rate * Days**

Where,

**Face Value**Â Â Â Â Â Â Â Â Â = Initial Price of the Bond

**Daily Interest Rate** Â Â =Annual interest/365

**Days**Â Â Â Â Â Â Â Â Â Â Â Â Â Â =Last Issue date- Settlement date

## 5 Methods to Create Accrued Interest Calculator in Excel

Excel is an obvious tool for our day-to-day activities. We can largely bank on Excel for a myriad of purposes that involve calculations. Here, weâ€™ll create an accrued interest calculator using various methods along with some functions in Excel. So letâ€™s explore them one by one.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience. If any methods wonâ€™t work in your version, then leave us a comment.

### 1. Calculating Accrued Interest Manually

Letâ€™s assume, we have a bond amount and an annual interest rate. Also, weâ€™ve got the interest period in our hands. Now, we will see how to calculate accrued interest on this bond. So letâ€™s begin.

**📌**** Steps:**

- At the very beginning, select cell
**D8**and enter the following formula into the cell.

`=D6/365`

Here, **D6** represents the **Annual Interest Rate**. **365** indicates the actual total days in a year.

- After that, press the
**ENTER**key.

- Then, go to cell
**D12**and write down the formula below.

`=PRODUCT(D4,D8,D10)`

Actually, we are multiplying these three entities using **the PRODUCT function**.

- Next, press
**ENTER**.

- Alternatively, you can use the following formula in cell
**D12**.

`=D4*D8*D10`

So, the accrued interest for the amount of **$1,00,000** on a **5%** annual interest rate for a period of **45** days is **$616.44**.

**Read More: **How to Create Monthly Accrued Interest Calculator in Excel

### 2. Inserting ACCRINT Function

In Excel, the **ACCRINT **function looks like the following.

**=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])**

Letâ€™s explain the arguments first.

**issue:** This is the date when a loan or bond is issued.

**first_interest:** This argument means the date when the interest payment will first occur.

**settlement:** The date when the loan will be finished.

**rate:** Annual or Yearly Interest rate.

**par:** The bond/loan amount.

**frequency:** This is the annual number of loan payments. **1** for Annual payments, **2** for Semi-annual payments, and 4 for Quarterly payments.

**basis:** The basis is set to **0** if the argument is omitted. [Optional]

**0** Or Omiited- US (NASD 30/360)

**1**– Actual/Actual

**2**– Actual/360

**3**– Actual/365

**4**-European 30/360

**calc_method:** Itâ€™s either **0** or **1** (calculates accrued interest from first_interest date to settlement date). **[Optional]**

It’s simple & easy, just follow along.

**📌**** Steps:**

- First of all, select cell
**D20**and paste the following formula.

`=ACCRINT(D4,D6,D8,D10,D12,D14,D16,D18)`

- As usual, press
**ENTER**.

So, here we go. The accrued interest amount is **$5000** for **12** months from **January 22** to **January 23**.

Here, If we simply, Excel is first calculating interest by multiplying **C10** and **C12**. As a result, we are getting **$5000**, further which is divided by **12** as the basis is **0** and we get **$416.67**. Finally, we are multiplying this **$416.67 **withÂ **12** months from January to January.

Here, we got the interest amount of **$4583.33** because we are getting it for **11** months. So, **$416.33 *** **11** = **$4583.33**.

**Read More:** Create Late Payment Interest Calculator- Download for Free

### 3. Combining ACCRINT and DATE Functions

So, what if, our *issue* date, *first_interest* date, and *settlement* date, are not formatted in Date? Then we will simply use **ACCRINT** along with **the DATE function** to change the date format issue. Letâ€™s explore the method step by step.

**📌**** Steps:**

- At first, select cell
**D20**and type the following formula.

`=ACCRINT(DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(2022,2,1),DATE(2023,1,1),D10,D12,D14,D16,D18)`

We can use the cell reference while using the **DATE **function. For this, weâ€™ve to utilize **YEAR**, **MONTH**, and **DAY** functions also. Otherwise, we can get dates without using cell references as we did in the **First Interest Date** and **Settlement Date**.

- Then, press
**ENTER**.

**Similarly**, for **11** months we get the amount of **$4583.33**.

**Read More:** How to Create FD Interest Calculator in Excel

### 4. Using DAYS360 Function

The **DAYS360 **function considers every month as **30** days and the whole year as **360** days. It does not consider the different lengths of different months. This function gives the number of days between two dates. The syntax of the function is like below.

**=DAYS360(start_date,end_date,[method])**

Follow these simple steps.

**📌**** Steps:**

- Firstly, select cell
**D16**and put the following formula into the cell.

`=DAYS360(D6,D8,FALSE)*D10*D14`

So, itâ€™s the accrued interest for **1** month from Dec 22 to Jan 23.

**Read More: **How to Make TDS Interest Calculator in Excel

### 5. Utilizing 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. The syntax of the function is the following.

**=YEARFRAC(start_date, end_date, [basis])**

Users must multiply the returned value with **365**, Par Value, and Daily Interest Rate to display the accrued interest. Letâ€™s see the process in detail.

**📌**** Steps:**

- First of all, go to cell
**D14**and place the formula below.

`=D12/365`

- Secondly, press
**ENTER**.

- Then, select cell
**D16**and write down the following formula.

`=YEARFRAC(D6,D8,0)*365*D10*D14`

- Later, press
**ENTER**.

If you observe carefully, you can see that here we are getting more interest for the same period compared to the **previous method**. Because here we are considering **365** days in a year whereas, in the previous method, it was **360** days.

## How to Calculate Accrued Interest on a Loan

Like **Method 2**, we can use the **ACCRINT **function to calculate accrued interest on a loan. So, without further delay, letâ€™s see how we can do it.

**📌**** Steps:**

- Initially, go to cell
**D20**and paste the formula below.

`=ACCRINT(D4,D6,D8,D10,D12,D14,D16,D18)`

- Then, press the
**ENTER**key.

**Read More: **How to Create TDS Late Payment Interest Calculator 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. - The arguments for the
date and*first_interest*date should be valid dates*settlement*

## Practice Section

Though it is easy, it needs practice. Thatâ€™s why we have attached practice sheets for you so that you can internalize the methods that you find easy and useful. Please do it by yourself. If you find any difficulty while doing practice, please inform us.

## Conclusion

This article provides easy and brief solutions to create an accrued interest calculator in Excel. Donâ€™t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

## Related Articles

- Make a Prejudgement Interest Calculator in Excel
- Create a Post-Judgement Interest Calculator in Excel
- How to Create a Money Market Interest Calculator in Excel
- How to Generate GST Interest Calculator in Excel
- Make Service Tax Late Payment Interest Calculation in Excel
- Create a Simple Interest Loan Calculator with Excel Formula

**<< Go Back to Interest Calculator | Finance Template | Excel Templates**