How to Calculate Accrued Interest on a Loan in Excel (3 ways)

Get FREE Advanced Excel Exercises with Solutions!

The amount of interest you earn on a loan is known as accrued interest. It is, however, the Sum that has yet to be collected or paid. It accrues on loans such as a mortgage, savings accounts, student loans, and other investments. We can calculate accrued interest on a loan in Excel using several methods. For your better understanding, we will use a sample data set containing the Loan amount, Yearly Interest Rate, Daily Interest Rate, and Accrued Interest Period to Calculate Accrued Interest on a Loan for method 1. For method 2, we will use the data set containing the Loan Issue Date, First Interest Date, Settlement Date, Annual Interest Rate, Par Value, Frequency or Payment Mode, Basis Days, and Calculation Method.
Sample data set for method 1.

How to Calculate Accrued Interest on a Loan in Excel
Sample dataset for methods 2 and 3.

How to Calculate Accrued Interest on a Loan in Excel


3 Simple Methods to Calculate Accrued Interest on a Loan in Excel

In this part, we will see how to calculate accrued interest on a loan in Excel manually, using the ACCRINT function, and the ACCRINT function along with the DATE function.


Method 1: How to Calculate Accrued Interest on a Loan in Excel Manually

Let’s assume, we have a loan amount and a yearly interest rate is given. Now, we will see how to calculate accrued interest on this loan.
First, click on cell C6 and type the following formula.

=C5/365

Calculate Accrued Interest on a Loan in Excel manually
Here, we are calculating the daily interest rate by simply dividing the yearly interest rate by the 365 number of days.
Now, press the ENTER key. We will get our daily interest rate as follows.


Now, we have to multiply the Loan amount, Daily Interest Rate, and Accrued Interest Period. So that, we can get Monthly accrued Interest.
At this point, click on cell C9 and type the following formula.

=C4*C6*C7

Now, press the ENTER key.

How to Calculate Accrued Interest on a Loan in Excel with equation

So, our monthly accrued interest rate for the given accrued period of 30 days and loan amount for  $100,000 is $821.92.

Read More: How to Calculate Accrued Interest on Fixed Deposit in Excel


Method 2: How to Calculate Accrued Interest on a Loan in Excel Using ACCRINT Function

If we look at sample dataset 2, we will see that this accrual interest method is different. In Excel, the function ACCRINT looks like the following.

=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
Let me explain these terms to you.
Issue: This is the date when a loan or security is issued
First_interest: This argument means the date when the interest payment will first occur.
Settlement: The date when the loan will finish
Rate: Annual or Yearly Interest rate
Par: The loan amount
Frequency: This is the annual number of loan payments. Annual payments will have a frequency of 1; semiannual payments will have a frequency of 2, and quarterly payments will have a frequency of 4.
Basis: This argument is optional. This is the type of day count used to calculate the interest on a certain loan or security. The base is set to 0 if the argument is omitted. Any of the following values can be used as the basis:
0 Or Omiited- US (NASD 30/360)
1- Actual/Actual
2- Actual/360
3- Actual/365
4-European 30/360
Calculation_method: It’s either 0 or 1 (calculates accrued interest from the first interest date to the settlement date). This argument is also optional.
Now, jump into the method.
First, click on cell C13 and type the following.
=ACCRINT(C4,C5,C6,C7,C8,C9,C10,C11)

How to Calculate Accrued Interest on a Loan in Excel with ACCRINT functionNow, press the ENTER key.

So, here we go. The amount that will be accrued is $6416.67 for 11 months from January to December.
Here, If we simply, Excel first calculates interest by Multiplying C7 and C8. As a result, we are getting $7000, which is divided by 12 as the basis is 0 and we get $583.33. Finally, we are multiplying this $583.33  with 11 months from January to December.

Read More: How to Calculate Accrued Interest on a Bond in Excel


Method 3: Calculate Accrued Interest on a Loan in Excel Using ACCRINT along with Date Function

So, what if, our Issue Date, First Interest Date, and Settlement Date, are not formatted in Date? Then we will simply use the ACCRINT function along with the DATE function to solve the issue.
First, click on cell C13 and type the following formula.

=ACCRINT(DATE(2022,1,1),DATE(2022,4,1),DATE(2022,12,1),C7,C8,C9,C10,C11)

How to Calculate Accrued Interest on a Loan in Excel along with DATE functionNow, Press the ENTER key.


That’s all. Simple. The amount that will be accrued is $6416.67 for 11 months from January to December.

For method formula explanation go to method 2.

Read More: How to Calculate Interest on a Loan in Excel


Things to Remember

We have to keep in mind certain things while doing these methods.

  • The arguments for the first interest date and settlement date should be valid dates
  • You have to be aware of different date systems or date interpretation settings.
  • For Basis
Basis Day Count Basis Defined Year Year Count
0 Or Omiited- US (NASD 30/360) 360/30 12
1 Actual/Actual 366/30 12.20
2 Actual/360 360/30 12
3 Actual/365 365/30 12.1667
4 European 30/360 360/30 12

Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.


Download Practice Workbook


Conclusion

These are three different ways to calculate accrued interest on a loan in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles


<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo