Method 1 – Calculate Daily Interest in Excel to Find Simple Interest
Suppose you have invested $1,000,000 at an 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 (1/365) of interest as well as the total Interest Earned.
STEPS:
- Select cell C8 and insert the following formula.
- Press Enter. This returns the amount of the final balance after one day of interest in cell C8.
=C4*(1+C5*C6)
- Select cell C9 and insert the following formula.
- Press Enter.
- This returns the amount of simple Interest Earned in one day.
=C8-C4
Method 2 – Daily Interest Calculation for Compound Interest in Excel
Case 2.1 Use Daily Compound Interest Formula
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%. Let’s determine the Final Balance and Interest Earned if the interest is compounded daily.
STEPS:
- Select cell C9 and insert the following formula:
=C4*(1+C5/C7)^(C6*C7)
- Press Enter. This returns the amount of Final Balance in cell C9 after daily compounding.
- Select cell C10 and insert the following formula:
=C9-C4
- Press Enter. This returns the amount of Interest Earned after daily compounding.
Read More: How to Use Cumulative Interest Formula in Excel
Case 2.2 Use of FV Function to Calculate Daily Compound Interest
To illustrate this method we will use the previous dataset.
STEPS:
- Select cell C9.
- Insert the following formula in that cell:
=FV(C5/C7, C6*C7, ,-C4)
- Press Enter.
- Select cell C10 and insert the following formula:
=C9-C4
- Press Enter.
- The results should be the same as before.
Case 2.3 Calculate Daily Compound Interest Using IPMT Function
Suppose we have the principal of $5000 and the bank is offering 0.5% interest. As the amount will be daily compounded so we will consider the number of compounding periods per year 365. Let’s calculate daily interest earned for the first month.
STEPS:
- Select cell C8.
- Insert the following formula in that cell:
=IPMT(C5/C6,1,1,-C4)
- Press Enter.
- This returns the amount of “Daily Interest Earned for the First Month” in cell C8.
Download Practice Workbook
You can download the practice workbook from here.
Related Articles
- How to Calculate Interest Between Two Dates in Excel
- How to Calculate GPF Interest in Excel
- Calculation of Interest During Construction in Excel
- How to Perform Actual 360 Interest Calculation in Excel
- How to Split Principal and Interest in EMI in Excel
- Perform Carried Interest Calculation in Excel
<< Go Back to Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I don’t understand how you get $50,000 interest after only ONE day?? Yes, $1,000,000 at 5% would be $50,000, but would that not be for the entire year, not one day’s interest? I believe you may lead people astray with that calculation. Thanks!
https://www.exceldemy.com/calculate-daily-interest-in-excel/
Hello Jerry Boor,
Thank you for pointing that out! The $50,000 interest would indeed be for the entire year, not just one day’s interest. The formula and method used are correct, but the value for time was incorrectly set, which caused the confusion. I have since updated the article to reflect the correct time value (1/365) for daily interest. I appreciate your input in helping to ensure the accuracy of the content. Please feel free to check out the revised version of the article. Thanks again for your valuable feedback!
Regards
ExcelDemy