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.

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

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

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

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

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

## 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 Interest Calculator | Finance Template | Excel Templates**

Hi Mrinmoy, Thanks well done.Henry