Average Daily Balance from Bank Statements in Excel

TL;DR The average daily balance is the sum of the account's end-of-day balance for every day in a period, divided by the number of days. Days with no transactions still count, at the previous day's balance. In Excel, build a calendar with one row per day, look up the last balance on or before each date with XLOOKUP (match mode -1), and average the column. Or use the weighted method: multiply each balance by the number of days it lasted and divide by the total days. Both give the same answer. Start from a converted statement whose running balance you've verified against the opening and closing balances.

Average daily balance comes up more often than you'd expect. Lenders reviewing a business loan application may look at it to judge how much cushion a business keeps. Some bank accounts set fee waivers or interest tiers around average or minimum balances. Credit card interest is commonly calculated on an average daily balance. And business owners use it themselves to understand cash patterns, such as how low the account runs before payroll and how much sits idle that could earn interest elsewhere.

Some statements print an average balance for the period, and some don't. Even when they do, you may need it for a different period: a quarter, a year, the last 90 days, or the period a lender specifies. This guide shows how to calculate it yourself from statement data in Excel, with methods that handle weekends, quiet days and multi-month periods correctly.

What average daily balance means

Picture the account balance at the end of each day. On days with transactions, it changes. On days without, it stays the same. The average daily balance is the plain average of those end-of-day values across every calendar day in the period, including weekends and holidays.

The most common mistake is averaging only the balances printed next to transactions. That ignores quiet days and gives too much weight to busy ones. If the account sits at 50,000 for 25 days and dips to 2,000 for 5 busy days with lots of transactions, a transaction-weighted average looks low, while the true daily average is much higher.

Note that different institutions may define the calculation slightly differently, for example whether a day's balance is taken at the start or end of the day, or how pending items count. When a specific definition matters, for a fee waiver, a loan covenant or an interest calculation, use the definition in that agreement. The methods below use end-of-day posted balances, the most common approach.

Start with reliable data

You need the opening balance for the period and every posted transaction with its date. Download the statements and convert them to a table with Date, Description, Amount (signed) and Balance.

SheetStatement converts statement PDFs to Excel or CSV, keeps the running balance where the statement prints one, and checks that the opening balance plus all activity equals the closing balance. The free plan covers 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans cover longer periods, useful when a lender asks for six or twelve months.

Verify the data before calculating anything:

=ROUND(Opening + SUM(Tx[Amount]) - Closing, 2)

should be zero. If the statement doesn't print a running balance, build one. Our guide to the running balance formula in Excel shows how. If it does print one, compare your calculated running balance with it line by line. A single missing transaction shifts every daily balance after it, so this check matters more here than almost anywhere. The free balance checker confirms statement totals quickly.

Method 1: a daily calendar

This is the most transparent method, and the easiest to explain to someone else.

Step 1: list every day

On a new sheet, put the period's start date in A2. In Excel 365, generate every day in one formula:

=SEQUENCE(EndDate - StartDate + 1, 1, StartDate, 1)

Format the column as dates. In older versions, put the start date in A2, =A2+1 in A3, and fill down to the end date.

Step 2: end-of-day balance for each day

The end-of-day balance is the running balance after the last transaction on or before that date. With the transaction table sorted by date (and by statement order within a day):

=XLOOKUP(A2, Tx[Date], Tx[Balance], Opening, -1, -1)

The fifth argument, -1, finds an exact match or the next smaller date, so quiet days pick up the last balance before them. The sixth argument, -1, searches from the bottom, so on a day with several transactions you get the balance after the last one. The fourth argument returns the opening balance for days before the first transaction.

For a version without XLOOKUP, use LOOKUP, which naturally returns the last match:

=IFERROR(LOOKUP(2, 1/(Tx[Date]<=A2), Tx[Balance]), Opening)

Step 3: average

=AVERAGE(B2:B32)

That's the average daily balance for the period. If you'll reuse the calendar, convert it to an Excel table named Cal with Date and Balance columns (and later a Month column), so the formulas in the rest of this guide can refer to it by name. With the calendar in place, you can also see the minimum, the maximum and the days below a threshold:

=MIN(B2:B32)

=COUNTIF(B2:B32, "<"&5000)

Method 2: weighted by days

The weighted method avoids building a calendar. Each balance counts for the number of days it lasted.

Collapse the transactions to one row per date with activity, keeping the end-of-day balance (the last balance for that date). Put the result in a table named Daily, with the period start date and the opening balance as its first row, so the days before the first transaction are counted.

Add a Days column. For every row except the last, it's the next row's date minus this row's date. With the table starting in row 2:

=A3 - A2

For the last row, count through the end of the period, inclusive:

=PeriodEnd - A2 + 1

Then:

=SUMPRODUCT(Daily[Balance], Daily[Days]) / SUM(Daily[Days])

SUM(Daily[Days]) should equal the number of days in the period, which is a useful check in itself.

Both methods give the same result. The calendar is easier to audit; the weighted method is more compact.

A worked example

A 30-day period, 1 to 30 September. Opening balance 10,000.00.

Weighted calculation:

• 10,000.00 × 4 days (1–4 Sep) = 40,000
• 16,000.00 × 10 days (5–14 Sep) = 160,000
• 7,000.00 × 10 days (15–24 Sep) = 70,000
• 4,000.00 × 6 days (25–30 Sep) = 24,000

Total 294,000 ÷ 30 days = 9,800.00 average daily balance.

Now compare the naive approach of averaging the four balances that appear next to transactions or the period start (10,000, 16,000, 7,000, 4,000): 9,250.00. Wrong, because it gives a 4-day balance the same weight as a 10-day one. The difference grows with more uneven activity.

Longer periods and multiple statements

For a quarter, a year or a lender's 90-day window, combine statements into one table first. Check that each statement's opening balance equals the previous statement's closing balance; a gap means a missing statement, and any calendar built across it will be wrong. Our guide to combining multiple bank statements in Excel covers the setup.

Then build one calendar across the whole period. Monthly averages fall out of a AVERAGEIFS on the calendar:

=AVERAGEIFS(Cal[Balance], Cal[Date], ">="&DATE(2026,9,1), Cal[Date], "<="&DATE(2026,9,30))

Or add a Month column to the calendar and use a pivot table for averages, minimums and maximums by month. A chart of the daily balance column shows the cash cycle at a glance: the payroll dips, the month-end low, the slow build after customer payments land.

Several accounts

If a business has several accounts, a lender or an internal analysis may want the combined average. Two options:

• Sum the daily balances first, then average. Build a calendar per account, add a Total column that sums them, and average that.
• Average each account, then add the averages. Over the same period, this gives the same answer.

Either way, internal transfers between accounts don't affect the combined balance, so you don't need to remove them for this calculation, only for income and spending analysis.

Low-balance days and the minimum balance

The average hides the lows. Two accounts can share an average of 20,000, one holding steady around that figure, the other swinging between 60,000 and a few hundred. For risk, the second account is very different, and anyone reviewing the statements will notice. Add a few companion measures from the same calendar:

• Minimum daily balance: =MIN(Cal[Balance]). Some account fees and waivers depend on a minimum rather than an average.
• Days below a threshold: =COUNTIF(Cal[Balance], "<"&Threshold). Pick a threshold that means something to you, such as one week of outgoings.
• Negative days: =COUNTIF(Cal[Balance], "<0"). Days overdrawn. Lenders often look at these, and they may come with fees.
• Lowest day of the month, by month: a MINIFS on the calendar with a month criterion shows whether the low point is getting better or worse.

=MINIFS(Cal[Balance], Cal[Month], "2026-09")

Plot the daily balance as a line with the threshold as a second, flat line. Every dip below it is a day worth understanding: a late customer payment, a large annual bill, payroll falling just before receipts.

Presenting the numbers

If you're preparing the figures for a lender, a partner or your own board, a one-page summary works better than a workbook full of formulas:

Add a short note under the table explaining anything unusual: a large annual insurance payment that caused a low in August, or an owner contribution that lifted September. Keep the statements themselves alongside, because anyone relying on the figures will want to check them against the source.

Uses for average daily balance

Loan applications. Lenders reviewing business bank statements may look at average balances alongside deposits, the number of days with low or negative balances, and returned items. We can't say how any specific lender weighs these, but preparing the numbers yourself means no surprises. Our guide to bank statements for a business loan covers what's commonly reviewed, and cash flow statements from bank statements helps you tell the story behind the numbers.

Fee waivers and interest tiers. If an account waives a fee above an average or minimum balance, the calendar shows how close you are and on which days you dipped below. Check your account agreement for the exact definition used.

Credit card interest. Many card issuers calculate interest on an average daily balance over the billing cycle. Your cardholder agreement explains the method. For card statements, purchases increase the balance owed, so flip the sign convention or work with the balance owed directly. See our credit card statement converter for converting card statements.

Cash management. If the average balance is consistently well above what the business needs for a month of outgoings, some of it might work harder elsewhere. If the minimum regularly falls near zero before payroll, a buffer or a change in payment timing might reduce stress. These are decisions for you and your advisers; the calendar just gives you the facts.

Interest checks. On an interest-bearing account, average daily balance multiplied by the rate and the fraction of the year gives a rough expected interest figure to compare with what was paid. Rates, compounding and day-count conventions vary, so treat it as a sanity check, not an exact figure.

Common mistakes

Averaging transaction balances. Covered above. Always count every calendar day.

Using the wrong balance on busy days. On a day with several transactions, use the balance after the last one. That's why the XLOOKUP searches from the bottom.

Unsorted data. The lookup assumes the transaction table is in date order. Sort by date first, keeping the statement's order within each day.

Missing the opening balance. Days before the first transaction carry the opening balance. Without it, the lookup returns an error or zero for those days.

Text dates. If dates are text, lookups fail silently or return wrong values. Our guide to fixing bank statement dates in Excel covers the fixes.

Gaps between statements. A missing month makes the calendar carry an old balance across weeks it doesn't know about. Check that statements chain before combining.

Doing it in Google Sheets

The calendar method works in Google Sheets with the same structure. SEQUENCE generates the dates, and XLOOKUP with the same match and search modes returns the end-of-day balance. Our guide to converting a bank statement PDF to Google Sheets covers getting the data in.

FAQ

What is average daily balance?

It's the average of an account's end-of-day balances across every calendar day in a period, including days with no transactions.

How do I calculate it in Excel?

List every day in the period, look up the end-of-day balance for each with XLOOKUP using match mode -1 and a bottom-up search, and average the column. Or multiply each balance by the days it lasted and divide by the total days.

Why not just average the balances on my statement?

Those balances appear only on days with transactions, so busy days get too much weight and quiet days are ignored. Count every calendar day.

Do weekends and holidays count?

Yes, in the standard calculation. The balance on those days is the last balance before them. If an agreement defines the calculation differently, follow the agreement.

My statement shows an average balance. Why is mine different?

Your bank may use a different definition, such as a different cut-off time, or treat certain items differently. Use the bank's figure where the bank's definition matters, such as for fee waivers.

Can I calculate it across several months?

Yes. Combine the statements into one table, check that each opening balance matches the previous closing balance, and build one calendar for the full period.

How do I calculate it for several accounts?

Build a daily balance for each account, sum them by day, and average the total. Transfers between the accounts don't affect the combined figure.

What's the difference between average and minimum balance?

The average is the mean of every end-of-day balance in the period. The minimum is the single lowest end-of-day balance. Some account terms use one, some the other, so check which applies.

How do I count days my account was overdrawn?

From the daily calendar, count the days with a negative end-of-day balance using COUNTIF with a criterion of "<0".

Can I use a transaction download instead of statements?

You can, if it covers every day of the period and you know the opening balance. Statements are safer because they include official opening and closing balances to check against.

Do lenders use average daily balance?

Some lenders look at average balances when reviewing bank statements, alongside deposits and low-balance days. Practices vary by lender, so ask yours what they review.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro $19 and Business $39.