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

Get FREE Advanced Excel Exercises with Solutions!

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 InvestmentInitial Amount

Daily Compound Interest = Initial Amount*(1+Rate of Interest/365)^n*365Initial 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.

Calculate Daily Interest in Excel to Find Simple Interest

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)

Calculate Daily Interest in Excel to Find Simple Interest

  • 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.

Calculate Daily Interest in Excel to Find Simple Interest

Read More: Simple Interest Formula in Excel (With 3 Practical Examples) 


Similar Readings


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.

Use Daily Compound Interest Formula

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)

Use Daily Compound Interest Formula

  • 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.

Use Daily Compound Interest Formula


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.

Use of FV Function to Calculate Daily Compound Interest

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)

Use of FV Function to Calculate Daily Compound Interest

  • 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.

Use of FV Function to Calculate Daily Compound Interest


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.

Calculate Daily Compound Interest Using IPMT Function

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.

Calculate Daily Compound Interest Using IPMT Function

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.


Related Articles

Mukesh Dipto
Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo