Very often we need to calculate interest between two dates. Manual calculation of interest can be lengthy and tiresome. Using Excel we can do these calculations quickly and easily. In this article, we will show you 3 easy ways to calculate interest between two dates using Excel functions.

Here, we will see how to calculate interest between two dates. The following image shows the given data set. We have to calculate the interest on a $100,000 Loan Amount, with monthly compounding and an interest rate of 5%. We are to find the interest between 22nd of February and 19th of March. The loan is to be repaid in 5 years.

**Table of Contents**hide

## 3 Suitable Ways to Calculate Interest Between Two Dates in Excel

Now we will look at three different ways to calculate interest. We will use Excel function, **VBA** function and manual formula to calculate the interest between those two dates.

### 1. Calculate the Interest between Two Dates Using IPMT Function

**IPMT function **of Excel lets us calculate interest payment. To calculate interest using the IPMT function we need to know the interest rate per period, the number of periods of which we want to calculate the interest, the total number of repayments, and the loan amount. In our case, we will type the following formula in **C12 **cell,

`=IPMT(C5/12,1,C6*C7,C4)`

**Formula Breakdown**

** Rate**>>**C5/12**>> Converts annual interest rate to monthly interest rate**Per**>> **1**>> We want to calculate interest rate for 1 month in this case**Nper**>>**C6*C7**>> Gives us the total number of repayments. In this case 5 years * 12 months/year= 60 repayments**Pv**>> **C4**>> Gives us the principal amount of the loan.**IPMT(C5/12,1,C6*C7,C4)**>> Gives us the interest payment for the given period.

**ENTER**key we will find the interest between two dates in

**C12**cell.

Here, the minus sign before the amount $18.75 denotes the cash outflow i.e., we will have to pay cash to repay the interest.

**Note**

The **IPMT** function does not consider the start and end dates as it returns interest rates based on periods and not specific calendar dates. To consider the start and end dates, see the below methods

### 2. Calculate the Interest between Two Dates Using Simple Interest

In the case of simple interest, compounding does not take place. So, the calculation of simple interest is fairly easy. To calculate the simple interest between two dates, first, we will need to convert the annual interest rate into daily interest rate. Then we will multiply the daily interest rate with the principal amount and the number of days. To do so, in the **C10 **cell we will type,

`=C4*(C8-C7)*(C5/365)`

**Formula Breakdown**

** C4**>> Gives us the principal amount or total loaned amount**C8-C7**>> Gives us the number of days between March 19, 2022 and February 22, 2022**Output **is>> 25 days**C5/365**>> Gives us the daily interest rate**C4*(C8-C7)*(C5/365)** >> Gives us the product of the three i.e., the interest.

Pressing the **ENTER **key we will find the interest in **C10 **cell.

### 3. Use VBA Function to Calculate Interest Between Two Dates in Excel

In this method, we will create a custom VBA function that will calculate the interest based on given loan details between two specific dates.

- First, go to
**Developer**tab >> click**Visual Basic**.

- In the
**Visual Basic**window, click on**Insert**>>**Module**>> write the below**VBA**code in the module >> close the window.

```
Function CalculateInterest(Principal As Double, AnnualInterestRate As Double, _
LoanTermInYears As Integer, CompoundingPeriodsPerYear As Integer, _
StartDate As Date, EndDate As Date) As Double
Dim MonthlyInterestRate As Double
Dim TotalPeriods As Integer
Dim Interest As Double
MonthlyInterestRate = AnnualInterestRate / CompoundingPeriodsPerYear
TotalPeriods = LoanTermInYears * CompoundingPeriodsPerYear
Interest = -Principal * WorksheetFunction.IPmt(MonthlyInterestRate, _
DateDiff("m", StartDate, EndDate) + 1, TotalPeriods, 1, 0)
CalculateInterest = Interest
End Function
```

This VBA code builds a custom function that finds the interest between two given dates.

- Enter the below custom
**CalculateInterest**function in cell**C11**>> press**Enter**.

`=CalculateInterest(C4,C5,C6,C7,C8,C9)`

As a result, we obtain 18.47 as the interest between the dates in **C8** and **C9**.

## Things to Remember

- When using IPMT function keep in mind that you have to convert the period for which you are calculating interest into the same unit of time (month, day, year etc.) as the repayments periods’.
- When using the simple interest, be sure to turn the interest rate into the same unit in which the period you are calculating the interest.

## Practice Section

We have included a practice section so that you can practice the methods be yourself.

**Download Practice Workbook**

## Conclusion

In this article, we have shown 3 easy ways to calculate the interest between two dates in Excel. If you have further queries on how to calculate interest between two dates in Excel feel free to reach us. Our dedicated team is ready to help you with any of your Excel-related queries.

Unfortunately I’m pretty sure your first example is incorrect. No where in the formula does it use either the beginning or ending dates (cells C8 & C9). You can see this by there lack of inclusion in the formula. Or, you can recreate this simple example and change the ending date do 12/31/2060 for example and the result is the same. All you’ve calculated is the interest payable in the 1st period of a 5 year note represented by the hardcoded “1” used in the “PER” section of the formula.

Hello MISLED READER,

Thank you for reading our article and your feedback. You have specified an incorrect use of the

IPMTfunction that does not consider the start and end dates. And, you are correct about the fact that the method calculates the interest payable in the 1st period of a 5 year note represented by the hardcoded “1” used in the “PER” section of the formula. TheIPMTfunction returns interest rates based on periods and not specific calendar dates. Thank you for pointing out the article gap to us. We will fix the issue with correct information. For now, we will show you another method here.You can use the other Excel basic formula to calculate the interest between two dates. Or, enter a custom VBA function as described below.

This function will take the loan details, including start and end dates, as input arguments and return the interest amount.

Here is the syntax and arguments of the

CalculateInterestfunction I have created:Here are the steps to implement the function:

1. Save the below VBA code to a Module.

2. Now, enter the VBA function in cell

C11>> pressEnterkey to get the interest.This formula calculates the interest between February 22, 2022, and March 24, 2022, based on the provided loan details.

Feel free to let us know your future queries and suggestions as we always appreciate them. Thank you.

Regards,

Yousuf Shovon