Daily Loan Interest Calculator in Excel (Download for Free)

The daily loan is the amount of money that you need to pay based on the interest rate and annual loan amount. You can create one daily loan interest calculator in Excel. After that, all you need to do is input the interest rate and the annual loan amount. The calculator will instantly calculate the daily loan interest amount based on the input data. In this article, you will learn to create a daily loan interest calculator in Excel with ease.

What Is Daily Loan Interest?

Daily loan interest is the amount of interest that needs to be paid daily against a loan or a credit based on the annual interest rate as well as the loan amount. We can easily get the daily loan interest from the annual loan interest simply by dividing the annual loan interest by 365.

Daily Loan Interest Formula

The formula used to calculate the daily interest against a loan or a mortgage is:

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

The above formula will return the total daily loan interest amount based on the input data.

💡 Here is one thing to remember. That is the annual loan balance may not be equal to the total loan balance. Be aware of that. In the Daily Interest Calculator for loans, you are allowed to insert only the annual loan balance but not the total loan balance.

Create a Daily Loan Interest Calculator in Excel

As calculating the daily loan interest requires both annual loan balance and annual interest rate, allocate two cells for them.

After that,

❶ Fix a cell where you want to return the daily loan interest. I’ve chosen cell D7 for this instance.

❷ Then insert the following formula to calculate daily loan interest in cell D7.

=(D4*D5)/365

❸ To execute the above formula, press the ENTER button.

So you can use the above calculator to find the daily loan interest.

The next time, all you need to do is to input the annual loan balance and annual interest rate in cells D4 & D5. And then you are ready to go.

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

Suppose, you’ve taken a loan of $5,000,000 from X bank for 1 year. You need to pay a 12% interest rate on the loan amount annually. Now, what is the amount of the daily loan interest that you need to pay again the amount of money that you have taken as a loan?

In the above problem,

The annual loan balance is $5,000,000.

The annual interest rate is 12%.

Now if we input these two data into the daily loan interest calculator that we’ve created, we can easily calculate the daily loan interest amount that you need to pay.

To do that,

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

❷ Then again insert the annual interest rate i.e. 12% in cell D5.

After that, you will see the daily loan interest has been already calculated for you. Which is $1,644.

Daily Compound Loan Interest Calculator in Excel

To calculate the daily compound loan interest you need to know,

Total Loan Amount
Annual Interest Rate
Period of Loan
Payment Frequency

The formula to calculate the compound loan interest is,

Where,

A = The final amount that you need to pay back

P = Total Loan Amount

r = Annual Interest Rate

n= Payment Frequency

t= Period of loan

In the calculator below, you need to insert,

❶ Total Loan Amount in cell C4.

❷ Annual Interest Rate in cell C5.

❸ Period of the loan in cell C6.

❹ Payment frequency in cell C9.

After inserting all of these, you will get the amount of monthly payment in cell C14 and in cell C15 you will get the daily compound loan interest calculated.

To Create the daily compound loan interest calculator,

❶ Allocate cells to input Total Loan Amount, Annual Interest Rate, Period of Loan, and Payments Per Year. For this instance, I have used cells C4, C5, C6, C11 respectively.

❷ After that insert the following formula in cell C14 to calculate the Monthly Payment amount on a 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. So 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 value as original.

❸ Then insert the following formula in cell C15 to get the Daily Compound Loan Interest.

=C14/30

❹ Finally press the ENTER button.

Create a Monthly Loan Interest Calculator in Excel

To calculate the monthly loan interest in Excel, you can use the following formula:

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

Now to create a Monthly Loan Interest Calculator,

❶ Select two cells to store the annual loan balance and annual interest rate.

❷ Then choose another cell where you want to return the monthly loan interest amount. I’m choosing cell D7 for this instance.

❸ After that, insert the following formula in cell D7.

=(D4*D5)/12

❹ Now press the ENTER button to execute the formula.

So this is your monthly loan interest calculator. All you need to do is to insert the annual loan balance as well as the annual interest rate. Then you are ready to go.

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%. Now calculate the amount of money that you need to pay back monthly as loan interest.

In the above problem,

The annual loan amount is $50,000.

The annual interest rate is 15%.

To calculate the monthly loan interest,

❶ Enter the annual loan balance in cell D4.

❷ Enter the annual interest rate in cell D5.

After doing this, you will see your monthly loan interest is calculated already in cell D7 which is $625.

Things to Remember

In the Daily Loan Interest formula, insert the annual loan balance but not the total loan balance.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Daily Loan Interest Calculator.xlsx

Conclusion

To sum up, we have discussed how to create and use a daily loan interest calculator in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.