How to Calculate Interest Between Two Dates in Excel (3 Ways)

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.

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.

Pressing the 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

interest-between-2-dates.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Misled Reader Sep 9, 2023 at 3:53 AM

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.
- Reply
  Yousuf Khan Sep 11, 2023 at 12:46 PM
  Hello MISLED READER,
  
  Thank you for reading our article and your feedback. You have specified an incorrect use of the IPMT function 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. The IPMT function 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 CalculateInterest function I have created:
```
Function CalculateInterest(Principal As Double, AnnualInterestRate As Double, _
    LoanTermInYears As Integer, CompoundingPeriodsPerYear As Integer, _
    StartDate As Date, EndDate As Date) As Double
```
  Here are the steps to implement the function:
  
  1. Save the below VBA code to a Module.
```
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
```
  2. Now, enter the VBA function in cell C11 >> press Enter key 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