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

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

## 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)
1. Enter the annual loan balance (\$50,000) in cell D4.
2. 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.

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

1 Comment
1. Hi Mrinmoy, Thanks well done.Henry

Advanced Excel Exercises with Solutions PDF