How to Calculate Daily Interest in Excel (2 Easy Ways)

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.

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.