Average daily bank balance: count every day, not every transaction

Two transaction rows don’t represent thirty days

Here's a fictional account for April 2026. It starts the month with $100, receives $200 on April 11 and pays out $150 on April 21. Nothing else moves. The statement's two transaction rows end at $300 and $150, so averaging them gives $225.

That answer leaves out the first ten days entirely. It also gives each transaction row one vote, regardless of how long its balance lasted. You need to give each calendar day one vote instead.

A balance-day is just a balance multiplied by the number of days it applies. Here, 5,500 divided by 30 gives $183.333…: $183.33 when displayed to two decimals. The April 11 deposit belongs in April 11's closing balance, not April 12's. The withdrawal works the same way.

Adding the $100 opening balance as a third row and averaging all three happens to give the right answer in this example because all three balances last ten days. That's a coincidence, not a method. Change the transaction dates and the day weights change.

The opening balance is not optional

For an April 1–30 calculation, you need the balance immediately before April 1's movements, plus all movements through April 30. Use the statement's opening balance for that period, or the previous period's closing balance when the periods meet without a gap. Keep one account and one currency in the sheet.

If your export starts with a transaction on April 11, that doesn't make April 11 the start of the month. Nor does it tell you what was in the account before then. Without a reliable opening balance, stop: ask for the earlier statement or a bank-confirmed balance at the boundary. A blank cell must not quietly become zero.

There's a second, separate question: do you have the whole period? A day with no transactions has a known carried-forward balance only when your records actually cover that day. A missing statement page is not a quiet day. The statement coverage checker can help you inspect gaps between statement periods, but it can't certify that every movement inside a statement was captured.

Build a small daily sheet in Excel or Google Sheets

If you just want the daily schedule without setting up formulas, use the daily balance calculator. You still need the opening balance and complete records. To keep the calculation in your own workbook, use the layout below.

Start in a blank sheet so the cell references below line up. This layout handles one period of up to 31 calendar days, with transaction data in rows 2 through 1000. For the fictional April example, import this CSV into columns A through C, with the headings in row 1. If pasting leaves everything in one column, split it using the comma delimiter.

Date,Amount,Description
2026-04-11,200.00,Deposit
2026-04-21,-150.00,Withdrawal

Amounts are positive for money entering this bank account and negative for money leaving it. Don't add the opening balance as a transaction: it goes in its own cell. Before calculating, check that the dates and amounts imported as numeric spreadsheet values, not text. For example, =ISNUMBER(A2) and =ISNUMBER(B2) should both return TRUE. Check each populated row; changing a cell's display format alone isn't a reliable repair for text. Our date and number format guide covers that problem.

Put the following labels in column E and their values or formulas in column F. Using DATE avoids asking the spreadsheet to guess what a typed date means. The formulas here use English function names and comma separators.

Format F2 and F3 as dates. F3 should show April 30 and F5 should show 30. The extra one in the day-count formula includes both endpoints. Microsoft's EOMONTH documentation explains how a zero month offset returns the last day of the starting month. For a non-calendar statement period, enter its actual end date in F3 instead; don't let EOMONTH choose a different period for you.

When you change the period, use dates without a time component in F2 and F3, and check that F5 is a whole number from 1 to 31. The daily area below has only 31 rows; a longer period needs more daily rows and a wider range in the final average formula.

Now put Date, Net movement and Closing balance in H1, I1 and J1. Enter these formulas in the named cells:

H2: =$F$2
H3: =IF(H2="","",IF(H2<$F$3,H2+1,""))
I2: =IF(H2="","",SUMIFS($B$2:$B$1000,$A$2:$A$1000,">="&H2,$A$2:$A$1000,"<"&H2+1))
J2: =IF(H2="","",IF(ISNUMBER($F$4),$F$4+SUM($I$2:I2),NA()))

Copy H3 down through H32. Then copy I2:J2 down through I32:J32, and format column H as dates. The date formula stops after F3, so unused rows remain blank. April needs 30 daily rows, ending at row 31; row 32 stays blank.

Column I adds every movement dated within that calendar day. Both Microsoft and Google document this SUMIFS pattern: sum the amounts whose dates meet both criteria. Column J then adds the accumulated daily movements to the opening balance. On a quiet day, column I is zero, so the balance stays where it was. Keep all date and amount ranges the same size if you extend the transaction area beyond row 1000.

Rows dated before F2 or after F3 don't enter this calculation. They don't adjust F4 either: that must already be the balance immediately before the chosen period.

Finally, label E6 Average daily balance and enter this in F6:

=IF(AND(COUNT(I2:I32)=$F$5,COUNT(J2:J32)=$F$5),AVERAGE(J2:J32),NA())

Format F6 to two decimal places rather than rounding the daily calculations. You should see $183.33. The two COUNT checks catch missing numeric cells in either the movement or closing-balance column. They don't tell you whether the CSV contains every transaction, so the source check still matters. If F4 is blank or nonnumeric, the active balance rows and F6 return #N/A. Microsoft describes NA as a way to mark missing information. A verified opening balance of zero is different and is allowed.

Keep both same-day entries, and let February have its own length

Suppose you add two more fictional movements on April 16: a deposit of $80 and a withdrawal of $80. Leave both rows in the transaction area. Column I will total them to zero for that day, column J will remain at $300, and the month's average will still display $183.33.

This is why the sheet sums movements instead of trying to pick the last row for a date. You don't need to guess which same-day row came last to reconstruct its final balance from a complete set of signed movements. You also don't need to delete offsetting rows to simplify the arithmetic; keeping them preserves the record you can compare with the statement.

For a leap-day check, move the original two transactions to February 11 and February 21, 2024, and set F2 to =DATE(2024,2,1). Leave the opening balance at $100. F3 becomes February 29 and F5 becomes 29. The $100 and $300 balances each last ten days, while the $150 balance lasts nine: 5,350 balance-days divided by 29, or $184.48 displayed. February 29 counts even though no money moves that day.

You can count the days without listing every one

If the account changes only a few times, the first table is enough for a second calculation. Multiply each end-of-day balance by the number of calendar days it lasts, add those products, then divide by the total day count. You're doing the same arithmetic as the daily sheet, just grouping equal balances together.

For the April example, put the three balances, 100, 300 and 150, in B2:B4 on a separate sheet, and the three day counts, 10, 10 and 10, in C2:C4. The formula is:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

This follows the weighted-average method in Microsoft's average calculation guide. It returns the same $183.33 displayed result. Don't include a totals row in those ranges.

The day counts deserve the attention here. An interval starts on the day its closing balance first applies and ends the day before the next change. Its length is the next change date minus the current start date. For the final interval, use the period's end date plus one as the next boundary. In April, that makes the last interval May 1 minus April 21: ten days. Check that the interval lengths add to the full period, with no gaps or overlaps.

A neat average can still start from bad data

Before using the result, compare the last daily balance with the statement's closing balance. In the fictional example, $100 + $200 - $150 = $150, which must also be the April 30 balance. If yours differs, investigate missing rows, repeated rows and reversed signs before trusting the average. The statement balance checker can help with that opening-plus-movements check; it does not check day weighting.

A matching closing balance isn't the end of the review. Moving a deposit to the wrong date can leave the closing balance unchanged while changing the daily average. Compare the dates with the source statement as well, and use a consistent bank-defined date field rather than mixing purchase dates, posted dates and pending activity. Don't change a date just to make your number match the bank's number.

If the bank offers a downloadable transaction file for the whole period, start there and keep the statement for its opening and closing balances. If PDF statements are all you have, AnyStatement can convert them into spreadsheet rows. Review the extracted dates and amounts against the PDF before feeding them into this calculation. The converter doesn't choose your bank's balance definition.

The bank’s definition still matters

The arithmetic here has a real banking counterpart. In its UK account information, BPI Europe defines monthly average daily balance as the sum of daily end-of-day balances divided by the number of days in the month. That definition is undated; the page's August 10, 2026 effective date belongs to its time-deposit rates, not to a revision of the definition. This is an example from one bank, not a universal account rule.

In the United States, Regulation DD, § 1030.2(d) defines average daily balance as the sum of the full principal in the account for each day, divided by the number of days in the period. Its average daily balance method applies a periodic rate to that average. The account disclosure rules and interpretation allow different methods or periods for fee-related minimum balances and interest calculations, but require each method and period to be disclosed. These rules concern covered US consumer deposit accounts, not credit-card APR or lending eligibility.

An average of $183.33 doesn't mean the account stayed above $183.33: our example had only $100 for the first ten days. If you're checking a fee or interest figure, ask the bank which balance measure, dates and period it uses before comparing numbers. If a lender requested an average, ask it to specify the period, balance definition and supporting documents. A spreadsheet result doesn't replace that answer.

We opened the linked bank, CFPB, Microsoft and Google documentation on October 17, 2026. Using Python Decimal, we checked the examples and compared the formula logic with an independent daily loop and weighted intervals across 300 randomized statements. Checks included leap February, same-day entries, out-of-period rows and missing opening balances. We didn't execute the formulas in Excel or Google Sheets, inspect your account terms or test a bank's fee or interest calculation.