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,
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.
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.
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>> 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.
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.
We have included a practice section so that you can practice the methods be yourself.
Download Practice Workbook
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.