# Overdraft Interest Calculator in Excel (with Example)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to make an Overdraft Interest Calculator in Excel. Overdraft interest is paid by customers to the bank on the amount withdrawn from the account after the balance becomes zero. Overdraft interest rates are usually lower than proper loans. So this is a good source to get short-term loans in cases of urgency. But you must know how much you will need to pay in interest, as often other charges are also applicable. In this article, we will explain how you can easily calculate the overdraft interest in Excel.

## What Is Overdraft Interest?

Overdraft is a facility provided by banks to their customers to withdraw money beyond the required minimum balance or zero. It is a short-term loan but better than an actual loan as the interest rate for an overdraft loan is usually lower. Overdraft interest is the amount of interest the customer needs to pay on the excessive amount withdrawn until payback. Banks usually compound the overdraft interest daily until the borrower repays the excessive withdrawal.

You can calculate the overdraft interest using the following formula.

I = OD × P × r ÷ D

Where,
I = Overdraft Interest
OD = Amount Overdrawn
P= Periods or Days Until Repayment
r = Annual Interest Rate
D = 365 (days in year, 366 in case of leap year)

Assume you have \$2000 in your bank account. You need to withdraw \$2500 to meet daily expenses. You will deposit the excess \$500 and more 15 days later. Now if the interest rate for an overdraft loan is 18%, then the overdraft interest you must pay is \$500 × 15 × 0.18 ÷ 365 = \$3.7. The bank will deduct this amount and other charges, if applicable, from your account when you deposit again.

## Overdraft Interest Calculator in Excel

Now we will explain how to calculate the overdraft interest in Excel. Assume you made the following financial activities with your account. The amounts in brackets indicate negative values. • Now enter the following formula in cell G7 and drag the fill handle icon below. Here the IF function allows us to calculate the overdraft interest only when the ending balance (your account balance) is negative i.e. you have overdrawn. 1 in the formula indicates the daily interest.
`=IF(F7<0,F7*1*\$D\$4/\$G\$4,0)` • Then, use the SUM function to apply the following formula in cell G22 to get the total overdraft interest as shown below.
`=SUM(G7:G21)` ## Things to Remember

• You must use absolute reference for annual interest rate and days in a year in the formula. Otherwise, copying the formula will yield errors.
• You can replace the 1 in the formula with the required number of days in case you need to calculate the overdraft interest on a single amount for that particular number of days.

## Conclusion

Now you know how to make an Overdraft Interest Calculator in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.

## Related Articles #### Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  