How to Calculate Daily Simple Interest in Excel (3 Easy Ways)

In the banking and financial sectors, calculating daily simple interest is important. Excel can be extremely useful for calculating daily simple interest. The purpose of this article is to show you how to calculate daily simple interest in Excel. For your convenience, I will explain multiple methods for calculating daily simple interest.

What Is Daily Simple Interest?

Simple interest is calculated on the principal amount of a loan or the initial deposit into a savings account. For daily simple interest, interest is accrued every day. The daily Interest rate is calculated by dividing the annual interest rate by 365.

The simple interest is calculated by multiplying the principal amount by the daily interest rate by the number of days which is 1 for daily simple interest.

The formula for calculating simple interest is,

Simple Interest = P*r*t

Where,

P = Principal Amount
r = Interest Rate (Daily)
t = Time (Days)

For daily simple interest, the formula will be:

Here, t = 1 day

So, Daily Simple Interest = P*r*1

The formula for calculating the final amount is,

Final Amount = P*(1+r*t)

How to Calculate Daily Simple Interest in Excel: 3 Easy Ways

For this tutorial, I have taken a dataset that contains the Principal Amount, Interest Rate (Daily), and Time (Days). I will be using this same table for calculating all the methods. I will explain 3 different methods on how to calculate daily simple interest in Excel.

1. Using Generic Formula to Calculate Daily Simple Interest in Excel

In this method, I will be using the generic formula for calculating daily simple interest.

For example, you have $10,000 as your Principal Amount. Now, with a daily Interest Rate of 0.05%, I will show you how you can calculate the Final Amount after 1 day and the Daily Interest.

Let’s see step by step how to calculate daily simple interest in Excel by using the generic formula.

Steps:

• Firstly, select the cell in which you want to calculate the Final Amount. Here, I selected cell C9.
• Secondly, in cell C9 write the following formula,

Here, I multiplied the Interest Rate (Daily) by Time (Days). And then, summed it with 1. Finally, multiply this whole value by the Principal Amount to get the Final Amount.

• Now, press ENTER and you will get the Final Amount.

• After that, select the cell where you want to calculate the Daily Interest. Here, I selected cell C10.
• Next, in cell C10 write the following formula,

Here, I subtracted the Principal Amount from the Final Amount to get the Daily Interest.

• Finally, press ENTER and you will get the Daily Interest.

Read More: Convert Compound Interest to Simple Interest in Excel

2. Use of IPMT Function to Calculate Daily Simple Interest in Excel

For this 2nd method, I will use the IPMT function. Here, I am going to use the same example that I used previously for the 1st method. But, unlike the 1st method here at first, I will calculate the Daily Interest. After that, with the Daily Interest, I will calculate the Final Amount.

Let’s see step by step how to calculate daily simple interest in Excel with the use of the IPMT function.

Steps:

• To begin with, select the cell where you want to calculate the Daily Interest. Here, I selected cell C9.
• Next, in cellC9 write the following formula,

Here, in the IPMT function, I used C6 as rate, 1 as per because I am calculating interest per day, 1 as nper because I want to see the return after 1 day, and I selected -C5 as pv which is our present value. Now, this formula will return you the value for Daily Interest.

• Finally, press ENTER and you will get your Daily Interest.

After that, you will have to calculate the Final Amount.

• Firstly, select the cell in which you want to calculate the Final Amount. Here, I selected cell C10.
• Secondly in cell C10 write the following formula,

Here, I summed the Daily Interest with the Principal Amount to get the Final Amount.

• Finally, press ENTER and you will get your Final Amount.

3. Using FV Function to Calculate Daily Simple Interest in Excel

For the 3rd method, I will be using the same example that I used for the previous two methods. Here, I will use the FV function to calculate the daily simple interest.

Let’s see step by step how to calculate daily simple interest in Excel by using the FV function.

Steps:

• To begin with, select the cell where you want to calculate your Final Amount. Here, I selected cell C9.
• After that, in cell C9 write the following formula,

Here, in the FV function, I selected C6 as rate, and 1 as nper because I am calculating on a daily basis. For PMT I left it blank(,,) because we are not making any regular payments, and for pv I selected -C5 which is our present value. Now, this formula will return you the value for the Final Amount.

• Finally, press ENTER and you will get the Final Amount.

• After that, select the cell where you want to calculate the Daily Interest. Here, I selected cell C10.
• Next, in cell C10 write the following formula,

Here, I subtracted the Principal Amount from the Final Amount to get the Daily Interest.

• Finally, press ENTER and you will get your Daily Interest.

Read More: How to Calculate Simple Interest Loan Payments in Excel

How to Calculate Daily Compound Interest in Excel

Compound interest is calculated on the initial principal amount and the earned interest from the previous period.

The formula for calculating the Final Amount for compound interest is,

Final Amount = P*(1+r/n)^nt

Where,

P = Principal Amount
r = Interest Rate (Annually)
n = Compounding Periods (Per Year)
t = Time

Here, I am going to use this generic formula to calculate daily compound interest in Excel.

For example, you have $10,000 as your Principal Amount. Your yearly Interest Rate is 5.00% and the Compounding Period per year is 1. I will show you how you can calculate the Final Amount after 1 year and the Interest you will earn. And from that interest, I will calculate the Daily Interest for the first year.

Let’s see step by step how to calculate daily compound interest in Excel by using the generic formula.

Steps:

• To begin with, select the cell in which you want to calculate the Final Amount. Here, I selected cell C10.
• After that, in cell C10 write the following formula,

Here, I divided the Interest Rate by the Compounding Periods and then summed this part with 1. And then raised this whole part to the power of Compounding Periods multiplied by Time. Finally, I multiplied this whole part by the Principal Amount to get the Final Amount.

• Now, press ENTER to get the Final Amount.

After that, I will calculate the Interest (Earned in 1st Year).

• Firstly, select the cell in which you want to calculate the Interest (Earned in 1st Year). Here, I selected cell C11.
• Secondly, in cell C11 write the following formula,

Here, I subtracted the Principal Amount from the Final Amount to get the Interest (Earned in 1st Year).

• Now, press ENTER and you will get the  Interest (Earned in 1st Year).

And finally, we can calculate the Daily Interest (For the 1st Year) from the  Interest (Earned in 1st Year).

• To begin with, select the cell in which you want to calculate the Daily Interest (For the 1st Year). Here, I selected cell C12.
• After that, in cell C12 write the following formula,

Here, I divided the Interest (Earned in 1st Year) by 365 to get the Daily Interest (For the 1st Year). 365 is the number of days in a year.

• Finally, press ENTER to get the  Daily Interest (For the 1st Year).

Things to Remember

• It should be noted that in simple interest, the interest earned is calculated on the principal amount.
•  Whereas, in compound interest, the interest earned is calculated on the principal amount and the interest earned from the previous period.

Practice Section

Here, I have provided a practice sheet. So that, you can practice by yourself different methods on how to calculate daily simple interest in Excel.

Download Practice Workbook

Conclusion

In conclusion, I tried to show you different methods on how to calculate daily simple interest in Excel. Hope it was clear to you. Apart from this, I tried to give you an idea about compound interest.

More importantly, I suggest you practice by yourself to get perfection. And lastly, if you have any questions or any suggestions don’t forget to let me know in the comment section below.

Related Articles

• How to Calculate Simple Interest and Compound Interest in Excel
• How to Calculate Simple Interest on Reducing Balance in Excel

<< Go Back to Simple Interest Formula in Excel | Excel for Finance | Learn Excel