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.

Overview of this article


๐Ÿ“ Download Excel File

Download the Excel file below.


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.

Dataset for calculating interest between two dates

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.

Opening Formulas worksheet

  • 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.

Inserting Formula

  • Press the Enter key.

Obtained result

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.

Opening IPMT worksheet

  • 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.

Entering formula for Interest amount per day

  • 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.

Inserting Formula

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

Obtained result

๐Ÿ“• 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.


Related Articles

(Visited 42 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo