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 **Loan amount**, **Yearly ****Interest**** Rate**, **Daily Interest Rate**, **Accrued Interest Period** to Calculate Accrued Interest on a Loan for **method 1**. For **method 2**, we will use data set containing **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**.

Sample dataset for **methods 2** and **3**.

**Table of Contents**hide

**Download Practice Book**

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

In this article 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`

Here, we are calculating the **daily interest rate** by simply dividing the **yearly interest rate** by the **365 number of days**.

Now, press **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 **ENTER **key.

So, our monthly accrued interest rate for the given **accrued period of 30** days and l**oan 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**

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])`

**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 first interest date to 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)`

Now, press **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 is first calculating interest by Multiplying **C7** and **C8**. As a result, we are getting **$7000**, further 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**

**Similar Readings**

**How to Calculate Interest Rate on a Loan in Excel (2 Criteria)****Daily Loan Interest Calculator in Excel (Download for Free)****How to Calculate Interest Rate in Excel (3 Ways)****Create Late Payment Interest Calculator in Excel and Download for Free**

**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 **ACCRINT** 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)`

Now, Press **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 Between Two Dates 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.

**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. You may also browse this site’s other **Excel**-related topics.