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.