# How to Create Accrued Interest Calculator in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

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

• If we change the Settlement Date to 1 Dec 2022 then we can see what will happen. Here, we got the interest amount of \$4583.33 because we are getting it for 11 months. So, \$416.33 * 11 = \$4583.33.

### 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. ### 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])

📌 Steps:

• Firstly, select cell D16 and put the following formula into the cell.
`=DAYS360(D6,D8,FALSE)*D10*D14`
• Then, hit ENTER. So, it’s the accrued interest for 1 month from Dec 22 to Jan 23.

### 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. ## 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 first_interest date and settlement date should be valid dates

## 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. ## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  