Overdraft Interest Calculator in Excel (with Example)

What Is Overdraft Interest?

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 then pay the overdraft back 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

Consider a dataset with the following financial activities on a personal bank account. Parentheses indicate negative values.

overdraft dataset

  • 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)

daily overdraft interest calculation

  • Use the SUM function to apply the following formula in cell G22 to get the total overdraft interest as shown below.
=SUM(G7:G21)

overdraft interest calculator

Read More: How to Develop CD Interest Calculator in Excel


Things to Remember

  • You must use an 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.

Download Overdraft Interest Calculator Excel Template

You can download the Overdraft Interest Calculator Excel Template for free from the download button below.


Related Articles


<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo