## What Is Daily Loan Interest?

Daily loan interest represents the amount of interest that must be paid each day on a loan or credit, based on the annual interest rate and the loan amount. You can easily calculate daily loan interest by dividing the annual loan interest by 365.

## Daily Loan Interest Formula

The formula to calculate daily interest for a loan or mortgage is as follows:

`Daily Loan Interest = (Annual Loan Balance X Annual Interest Rate) / 365`

This formula provides the total daily loan interest based on the input data.

**Important Note**: The annual loan balance may not be the same as the total loan balance. Keep this in mind. When using the Daily Interest Calculator for loans, you should input only the annual loan balance, not the total loan balance.

## Creating a Daily Loan Interest Calculator in Excel

- Allocate two cells in your Excel sheet for the annual loan balance and annual interest rate.
- Choose a cell where you want to display the daily loan interest. Let’s use cell
**D7**. - In cell
**D7**, insert the following formula to calculate daily loan interest:

`=(D4*D5)/365`

- Press the
**ENTER**key to execute the formula.

- Input the annual loan balance and annual interest rate in cells
**D4**and**D5**, respectively.

## An Example of the Application of the Daily Loan Interest Calculator in Excel

Let’s break down the calculation step by step:

**Annual Loan Balance**: You’ve taken a loan of $5,000,000.

**Annual Interest Rate**: The interest rate on the loan is 12% (expressed as a decimal, this is 0.12).

Let’s calculate the daily loan interest using the formula we discussed earlier:

`Daily Loan Interest = (Annual Loan Balance X Annual Interest Rate) / 365`

Substitute the given values:

`Daily Loan Interest = ($5,000,000 X 0.12) / 365`

Insert the annual loan balance amount i.e. $5,000,000 in cell **D4**.

Insert the annual interest rate i.e. 12% in cell **D5**.

Therefore, the daily loan interest amount you need to pay is approximately $1,644.

**Read more:** Bank Interest Calculator in Excel Sheet – Download Free Template

## Daily Compound Loan Interest Calculator in Excel

**1. Understanding the Components: **

To calculate the daily compound loan interest, you’ll need the following information:

- Total Loan Amount (P)
- Annual Interest Rate (r)
- Period of Loan (t)
- Payment Frequency (n)

**2. Formula for Compound Loan Interest:**

The formula to calculate the compound loan interest is as follows:

Where,

**A** represents the final amount that you need to pay back.

**P** is the Total Loan Amount.

**r** is the Annual Interest Rate.

**n** is the Payment Frequency (number of times interest is compounded per year).

**t** is the Period of the loan (in years).

**3. Setting Up the Calculator:**

In your Excel sheet, allocate cells for the following inputs:

- Total Loan Amount (cell
**C4**) - Annual Interest Rate (cell
**C5**) - Period of Loan (cell
**C6**) - Payments Per Year (cell
**C11**)

**4. Monthly Payment Calculation:**

Insert the following formula in cell **C14** to calculate the monthly payment amount on the loan:

`=IF(roundOpt,ROUND(-PMT((1+C5/$C$10)^(365/$C$11)-1,$C$6*$C$11,$C$4),2),-PMT((1+C5/$C$10)^(365/$C$11)-1,$C$6*$C$11,$C$4))`

**Formula Breakdown**

**PMT((1+C5/$C$10)^(365/$C$11)-1,$C$6*$C$11,$C$4)**calculates the monthly compound loan interest amount which is**-77995.4656307853**.**ROUND(-PMT((1+C5/$C$10)^(365/$C$11)-1,$C$6*$C$11,$C$4),2)**rounds up the monthly compound loan interest amount to two decimal places,**77995.4656307853**becomes**77995.46.****=IF(roundOpt,ROUND(-PMT((1+C5/$C$10)^(365/$C$11)-1,$C$6*$C$11,$C$4),2),-PMT((1+C5/$C$10)^(365/$C$11)-1,$C$6*$C$11,$C$4))**generates rounded version of the monthly payment if Rounding option is On. Otherwise, it leaves the original value.

**5. Daily Compound Loan Interest:**

To get the daily compound loan interest, insert the following formula in cell C15:

`=C14/30`

**6. Execute the formulas:**

Press the **ENTER** key to execute the formulas.

**Read more:** Create Quarterly Compound Interest Calculator in Excel

## Create a Monthly Loan Interest Calculator in Excel

**1. Understanding the Components:**

To calculate the monthly loan interest, we’ll enter the following formula:

`Monthly Loan Interest = (Annual Loan Balance X Annual Interest Rate) / 12`

**2. Setting Up the Calculator:**

- Select two cells in your Excel sheet to store the annual loan balance (cell
**D4**) and annual interest rate (cell**D5**). - Choose another cell where you want to display the monthly loan interest amount. For this example, let’s use cell
**D7**

**3. Insert the Formula:**

- In cell
**D7**, insert the following formula to calculate the monthly loan interest:

`=(D4*D5)/12`

**4. Execute the Formula:**

- Press the
**ENTER**key to calculate the monthly loan interest.

Now you have your monthly loan interest calculator! Simply input the annual loan balance and annual interest rate, and the monthly interest amount will be automatically calculated in cell D7.

## An Example of the Application of the Monthly Loan Interest Calculator in Excel

Suppose you took a loan of $50,000 from ABC Bank with an annual interest rate of 15%. Let’s calculate the monthly loan interest:

- Annual loan amount: $50,000
- Annual interest rate: 15% (expressed as a decimal, this is 0.15)

- Enter the annual loan balance ($50,000) in cell
**D4**. - Enter the annual interest rate (0.15) in cell
**D5**.

After doing this, you’ll see that the monthly loan interest is already calculated in cell **D7**, which amounts to **$625**.

## Things to Remember

- Remember to use the annual loan balance in the formula, not the total loan balance.

**Download the Practice Workbook**

You can download the practice workbook from here:

## Further Readings

- Make Reverse Compound Interest Calculator in Excel
- How to Develop CD Interest Calculator in Excel
- How to Create SIP Interest Calculator in Excel
- How to Generate Overdraft Interest Calculator in Excel

**<< Go Back to Finance Template | Excel Templates**

Hi Mrinmoy, Thanks well done.Henry