Mukesh Dipto
In this article, we will explain how to calculate daily interest in excel. Calculating daily interest in excel is very useful in the banking sector and financial areas. It’s also applicable in different types of business applications. The focus of this article is to give you a brief idea about calculating daily interest in excel. To illustrate this process to you we will go over different types of examples so that you can learn and implement the process properly in your work or education.
Download Practice Workbook
You can download the practice workbook from here.
What is Daily Interest?
A daily interest loan accrues interest every day. The interest rate determined by dividing the Annual Interest Rate by 365 is referred to as the Daily Interest Rate.
What is Daily Simple Interest?
Simple interest is a fast and simple way to figure out how much a loan will cost you in interest. The amount of simple interest is calculated by multiplying the principal amount by interest rate by the number of days between payments to calculate simple interest. To calculate the daily simple interest the value of the period will be 1 day.
Simple interest is calculated using the following formula:
Simple Interest = P*r*n
Where,
P = Principal Amount
R = Rate of interest
n = Time period
So, the formula for daily simple interest will be:
Daily Simple Interest = P*r*1
Where, n = 1 day.
The formula for the total amount gained after applying simple interest cab be calculated by the following formula:
A = P*(1+r*n)
What is Daily Compound Interest?
Compound interest accrued on a savings or loan’s initial principle as well as the accrued interest from previous periods. We can say it is an Interest of Interest. The term “Daily Compounding“ refers to when our daily interest/return is compounded.
Daily compound interest formula:
Final Investment = Initial Amount*(1+Rate of Interest/365)^n*365
Where, n = Number of years
So, Daily Compound Interest = Final Investment–Initial Amount
Daily Compound Interest = Initial Amount*(1+Rate of Interest/365)^n*365–Initial Amount
2 Easy Ways to Calculate Daily Interest in Excel
In this article, we will discuss two types of calculation of daily interest in excel. In the first method, we will calculate daily simple interest, and In the second method, we will calculate daily compound interest.
1. Calculate Daily Interest in Excel to Find Simple Interest
Suppose, you have invested $1,000,000 at the annual interest rate of 5%. Let’s see how much simple interest you will receive daily on your principal. In the following dataset, we will calculate the Final Balance after one day of interest as well as the total Interest Earned.
So, let’s see how we can perform the above actions by following some simple steps.
STEPS:
- Firstly, select cell C8 and insert the following formula:
=C4*(1+C5*C6)
- Next, press Enter. This returns the amount of the final balance after one day of interest in cell C8.
- Secondly, select cell C9 and insert the following formula:
=C8-C4
- After that, press Enter.
- Finally, the above action returns the amount of simple Interest Earned in one day.
Read More: Simple Interest Formula in Excel (With 3 Practical Examples)
Similar Readings
- How to Calculate Gold Loan Interest in Excel (2 Ways)
- Create Late Payment Interest Calculator in Excel and Download for Free
- Calculate Interest in Excel with Payments (3 Examples)
- How to Calculate Principal and Interest on a Loan in Excel
- How to Calculate Interest on a Loan in Excel (5 Methods)
2. Daily Interest Calculation for Compound Interest in Excel
We use our money in long-term investments to increase our wealth efficiently. Most of the banks or financial institutions follow the compound interest rule. In this section, we will discuss 3 methods to calculate daily interest for compound interest.
2.1 Use Daily Compound Interest Formula
First and foremost, we will use the daily compound interest formula to calculate daily interest in excel.
Suppose, you have deposited $5000 in a bank at the interest rate of 7%. We will figure out what will be the Final Balance and Interest Earned if the interest is compounded daily.
Let’s take a look at the steps to perform this action.
STEPS:
- First, select cell C9 and insert the following formula:
=C4*(1+C5/C7)^(C6*C7)
- Now, press Enter. This returns the amount of Final Balance in cell C9 after daily compounding.
- Next, select cell C10 and insert the following formula:
=C9-C4
- After that, press Enter.
- Finally, the above action returns the amount of Interest Earned after daily compounding.
2.2 Use of FV Function to Calculate Daily Compound Interest
The FV function is a financial function that determines an investment’s future value based on a fixed interest rate. We can calculate daily compound interest by using the FV function also. To illustrate this method we will solve the previous problem with the FV function. We can take a look at the following image to see the dataset again.
Let’s see the steps to use the FV function to calculate daily compound interest.
STEPS:
- At the beginning select cell C9. Insert the following formula in that cell:
=FV(C5/C7, C6*C7, ,-C4)
- Next, press Enter. This action gives the same result for the Final Balance that the compound interest formula did in the previous example.
- Then, select cell C10 and insert the following formula:
=C9-C4
- After that, press Enter.
- Finally, the above command returns the same result for total Interest Earned that the compound interest formula did in the previous example.
2.3 Calculate Daily Compound Interest Using IPMT Function
The IPMT function returns the interest amount of mortgage payment in a given period. This function assumes that the interest rate and total amount of the payment remain constant throughout.
Suppose, we have the principal of $5000 and the bank is offering 0.5% interest. We want to calculate daily compound interest using the IPMT function. As the amount will be daily compounded so we will consider the number of compounding periods per year 365.
So, let’s look at the steps of how we can use the IPMT function to calculate daily interest earned for the first month.
STEPS:
- Firstly, select cell C8.
- Secondly, insert the following formula in that cell:
=IPMT(C5/C6,1,1,-C4)
- After that, press Enter.
- Finally, the above command returns the amount of “Daily Interest Earned for the First Month” in cell C8.
Read More: How to Calculate Interest Rate in Excel (3 Ways)
Conclusion
At last, this article discusses how to calculate daily interest in Excel. For the best results, download, and practice using our practice workbook, which is attached to this post. If you have any questions, please leave a comment below. We will make every effort to respond as soon as possible.
