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.

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)

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

Overdraft Interest Calculator.xlsx