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 of interest as well as the total Interest Earned.
STEPS:
- Select cell C8 and insert the following formula:
=C4*(1+C5*C6)
- Press Enter. This returns the amount of the final balance after one day of interest in cell C8.
- Select cell C9 and insert the following formula:
=C8-C4
- Press Enter.
- This returns the amount of simple Interest Earned in one day.
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 Calculate Interest In Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!