Below, we have a dataset that contains the Principal Amount, Interest Rate (Daily), and Time (Days).
Method 1 – Using a Generic Formula to Calculate Daily Simple Interest in Excel
Steps:
- Select the cell in which you want to calculate the Final Amount. Here, I selected cell C9.
- Enter the following formula in cell C9:
=C5*(1+C6*C7)
Here, I multiplied the Interest Rate (Daily) by Time (Days) and summed it with 1. I multiplied this whole value by the Principal Amount to get the Final Amount.
- Press ENTER, and you will get the Final Amount.
- Select the cell where you want to calculate the Daily Interest. Here, I selected cell C10.
- Enter the following formula in cell C10:
=C9-C5
Here, I subtracted the Principal Amount from the Final Amount to get the Daily Interest.
- Press ENTER, and you will get the Daily Interest.
Read More: Convert Compound Interest to Simple Interest in Excel
Method 2 – Using the IPMT Function to Calculate Daily Simple Interest in Excel
Steps:
- Select the cell where you want to calculate the Daily Interest. Here, I selected cell C9.
- Enter the following formula in cell C9:
=IPMT(C6,1,1,-C5)
Here, in the IPMT function, I used C6 as rate, 1 as per because I am calculating interest per day, 1 as per 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.
- Press ENTER, and you will get your Daily Interest.
To calculate the Final Amount,
- Select the cell in which you want to calculate the Final Amount. Here, I selected cell C10.
- Enter the following formula in cell C10:
=C5+C9
Here, I summed the Daily Interest with the Principal Amount to get the Final Amount.
- Press ENTER, and you will get your Final Amount.
Method 3 – Using the FV Function to Calculate Daily Simple Interest in Excel
Steps:
- Select the cell where you want to calculate your Final Amount. Here, I selected cell C9.
- Enter the following formula in cell C9:
=FV(C6,1,,-C5)
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 the value for the Final Amount.
- Press ENTER, and you will get the Final Amount.
- Select the cell where you want to calculate the Daily Interest. Here, I selected cell C10.
- Enter the following formula in cell C10:
=C9-C5
Here, I subtracted the Principal Amount from the Final Amount to get the Daily Interest.
- 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 will 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 to calculate the Final Amount after 1 year and the Interest you will earn. From that interest, I will calculate the Daily Interest for the first year.
Steps:
- Select the cell in which you want to calculate the Final Amount. Here, I selected cell C10.
- Enter the following formula in cell C10:
=C5*(1+C6/C7)^(C7*C8)
Here, I divided the Interest Rate by the Compounding Periods and then summed this part with 1. Then, I 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.
- Press ENTER to get the Final Amount.
To calculate the Interest (Earned in 1st Year),
- Select the cell where you want to calculate the Interest (Earned in 1st Year). Here, I selected cell C11.
- Enter the following formula in cell C11:
=C10-C5
Here, I subtracted the Principal Amount from the Final Amount to get the Interest (Earned in 1st Year).
- Press ENTER to get the Interest (Earned in 1st Year).
To calculate the Daily Interest (For the 1st Year) from the Interest (Earned in 1st Year),
- Select the cell in which you want to calculate the Daily Interest (For the 1st Year). Here, I selected cell C12.
- Enter the following formula in cell C12:
=C11/365
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.
- 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.
- Meanwhile, compound interest calculates the interest based on the principal amount and the interest earned from the previous period.
Practice Section
Here is a practice sheet. Practice the different methods on how to calculate daily simple interest in Excel.
Download the Practice Workbook
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
Get FREE Advanced Excel Exercises with Solutions!