2 Approaches to Calculate Interest Between Two Dates in Excel

In this article, we will calculate the interest between two dates in Excel. We need to calculate interest for many purposes, such as bank loans, house loans, savings, etc. One can calculate these interests manually but if we need to calculate the interests for a big dataset that will be time-consuming. Here, Excel can help us to make the process easier by using different built-in functions and custom formulas.

Look at the overview of this article. The first table illustrates our dataset and the table below that describes the methods, outcomes, and formulas we used.

Contents hide

📁 Download Excel File

Learn to Calculate Interest Between Two Dates in Excel with These 2 Suitable Approaches

1. Calculating Interest Using Customized Excel Formula

2. Excel IPMT Function for Calculating Interest Between Dates

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Download the Excel file below.

Calculate Interest Between Two Dates.xlsx

Learn to Calculate Interest Between Two Dates in Excel with These 2 Suitable Approaches

In this article, we will learn two approaches for calculating interest between two dates in Excel. From the dataset, we can see that the loan amount is $12000 for 4 years. The annual interest rate is 8%. Now here we will calculate the interest amount for Jan 15 to Feb 15, 2023.

approach

1. Calculating Interest Using Customized Excel Formula

In the first approach, we will use a custom Excel formula to calculate the simple interest of a given amount. Here is the formula for simple interest.

Simple Interest = P × i × n

Where, P = Principle Amount

i = Rate of Interest

n = Time Period

We will calculate the simple interest based on this given formula with a custom Excel formula.

⬇️⬇️ STEPS ⬇️⬇️

First, go to the Formulas worksheet.
We will compute the interest amount here. As you can see, we’ve added a new row to compute the interest amount.

Select cell C11.
Enter the following formula.

=C5*(C10-C9)*C6/365

🔨 Formula Breakdown

C5*(C10-C9)*C6/365

👉 Here, C5 is the principle.

👉 Here, C10–C9 indicates the total number of days for which we are counting the interest amount.

👉 And C6/365 is used to convert the annual interest to the daily interest rate. 365 is the number of total days in a year.

Press the Enter key.

Here we have calculated the interest for the 33 days starting from 15 Jan to 17 Feb.

📕 Read More: 5 Examples to Calculate Monthly Interest Rate in Excel

approach

2. Excel IPMT Function for Calculating Interest Between Dates

In the second approach, we will use the IPMT function. The IPMT function takes a few inputs and returns an interest amount for a given interval of time based on the inputs. The inputs are the interest rate, the interval of time for which we want to calculate the interest amount, the compounding period per year, and the amount for which we want to calculate the interest.

⬇️⬇️ STEPS ⬇️⬇️

To begin, go to the IPMT worksheet.
We have introduced three new rows in this worksheet. The period row contains the period for which we are calculating interest. Which contains 1. Because at first we are going to calculate the interest amount per day and then we will calculate interest between two dates.

Then select cell C12.
Enter the following formula.

=IPMT(C6/365,C11,C7*365,-C5)

🔨 Formula Breakdown

IPMT(C6/365,C11,C7*365,-C5)

👉 Here we converted the annual interest rate to the daily interest rate by using C6/365.

👉 C11 is 1 which indicates that we are calculating the interest for 1 day.

👉 C7*365 is used to calculate the total number of repayments in four years.

👉 -C5 means the amount of money for which we are calculating interest. The IPMT function usually returns a negative value as it indicates the cash outflow. To make it positive we used a negative sign before the amount.

The previous formula calculated the interest for one day.
Now, we will have to calculate the interest for the given time period, starting from 15 Jan to 17 Feb. For this, we will go to cell C13 and enter the following formula.

=C12*(C10-C9)

🔨 Formula Breakdown

C12*(C10-C9)

👉 C12 represents interest per day.

👉 C10-C9 calculates the total days between the given two dates.

Now we get the interest amount for the given time period has been calculated.

📕 Read More: 4 Approaches to Calculate Home Loan Interest in Excel

📝 Takeaways from This Article

📌 Here, we calculated interest between two dates using a custom Excel formula based on the basic mathematical formula.

📌 We have learned the use of the IPMT function in Excel.

📌 We also learned what is simple interest and how to calculate that using formula.

Conclusion

Finally, we have come to the end of this article. In this article, we tried to provide every possible way to calculate interest between two dates in Excel. As many methods have been demonstrated here, you may quickly choose which one is suitable for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit ExcelDen to discover more about Excel.