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

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.

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

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.

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

• Next, press ENTER.

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

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 Calculate Accrued Interest on a Bond in Excel (5 Methods)

2. Inserting ACCRINT Function

In Excel, the ACCRINT function looks like the following.

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.

• 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 solve the issue. Let’s explore the method step by step.

📌 Steps:

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

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.

Follow these simple steps.

📌 Steps:

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

• Then, hit ENTER.

So, it’s the accrued interest for 1 month from Dec 22 to Jan 23.

Read More: How to Create a Monthly Accrued 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.

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.

• Secondly, press ENTER.

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

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

• Then, press the ENTER key.

Also, follow this article to explore more about calculating accrued interest on a loan.

Read More: How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)

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.

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. Please visit our website Exceldemy to explore more.