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.
Download Overdraft Interest Calculator Excel Template
You can download the Overdraft Interest Calculator Excel Template for free from the download button below.
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 = 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.
- Then, use the SUM function to apply the following formula in cell G22 to get the total overdraft interest as shown below.
Read More: Daily Loan Interest Calculator in Excel (Download for Free)
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.
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.
- How to Calculate Accrued Interest on a Bond in Excel (5 Methods)
- Perform Service Tax Late Payment Interest Calculation in Excel
- How to Use Cumulative Interest Formula in Excel (3 Easy Ways)
- How to Create CD Interest Calculator in Excel (2 Easy Methods)
- Perform Carried Interest Calculation in Excel (with Easy Steps)
- How to Make HELOC Payment Calculator Using Principal and Interest in Excel