Lutful Hamid
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 2 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.
Download Practice Workbook
2 Easy Ways to Calculated Interest between Two Dates in Excel
Now we will look at two different ways to calculate interest. We will use Excel 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.
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.
Similar Readings
- How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)
- Car Loan Calculator in Excel Sheet – Download Free Template
- How to Calculate Credit Card Interest in Excel (3 Easy Steps)
- Bank Interest Calculator in Excel Sheet – Download Free Template
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.
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 for.
Practice Section
We have included a practice section so that you can practice the methods be yourself.
Conclusion
In this article, we have shown 2 easy ways to calculate the interest between two dates. If you have further queries on how to calculate interest between two dates Excel feel free to reach us. Our dedicated team is ready to help you with any of your Excel-related queries.
