How to Create a Daily Loan Interest Calculator in Excel (Download for Free)

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)

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:

Daily Loan Interest Calculator.xlsx