Calculate Average Monthly Expenses From Statements

To calculate average monthly expenses, add up the spending on twelve months of bank statements, leave out transfers between your own accounts, and divide by twelve. Twelve months catch yearly bills. In a free spreadsheet, one total per month and an AVERAGE formula give the number, and a MEDIAN beside it shows whether one odd month skewed it.

How many months of statements give a fair average of monthly expenses?

Twelve months give the fairest average, because a full year includes every bill that comes once a year and every season's costs. Three months is the least that tells you anything, and it works only if you add yearly bills by hand. Six months sits in between and still misses half the calendar.

The reason is simple. A three-month window from April to June has no heating bill, no holiday gifts, no annual car insurance if it renews in November, and no back-to-school costs. Its average will look lower than the life you actually pay for. A twelve-month window cannot miss any of those, because each one falls inside it once.

When a shorter window is the better choice

A full year is not always the right year. If your life changed recently, such as a move, a new job, a new baby or a paid-off car loan, the older months describe someone else's budget. In that case, use the months since the change, and add known yearly bills on top. The average will be less smooth, but it will describe the costs you pay now.

Getting a year of statements

Many banks let you download past statements from online banking, often going back a year or more. A year of PDFs is twelve files per account. A CSV export of transactions for a twelve-month date range, where your bank offers it, gets the same rows in one file. If you only have PDFs, converting them is the part that costs time or money. The breakdown of what a pay-per-page bank statement converter charges helps you price a year against other ways of paying. CheapBankConvert charges once per statement with no subscription, and its five-statement bundle is meant for a stack like this.

The average monthly expenses formula in a free sheet

The formula works on a small summary table: one row per month and the total spending for that month. Spending means money that left your accounts for goods, services and bills, not transfers to savings or card payments. Then =AVERAGE() over the twelve totals gives the average monthly expenses.

Building the monthly totals

Start with all your transaction rows on one sheet, named Tx here, in columns A to D: Date, Description, Amount (negative for money out) and Category. Add a fifth column, E, called Month, with =TEXT(A2,"yyyy-mm"), which turns 14 March 2026 into "2026-03". Then build the summary on a second sheet, with the months listed down column A and this in column B:

=-SUMIFS(Tx!C:C, Tx!E:E, A2, Tx!C:C, "<0", Tx!D:D, "<>Transfer")

That adds every negative amount for the month in A2, skips rows labelled Transfer, and flips the sign so spending reads as a positive number. Copy it down for twelve months.

A worked twelve-month table

Here is an example summary. The figures are illustrative and do not describe a typical household.

The formulas under the table are =AVERAGE(B2:B13) and =MEDIAN(B2:B13). In this example the average is 3,596 and the median is 3,439. The gap between them comes from December and July, the two months with holiday and travel spending.

Average by category as well

The same pattern works per category. Add a category condition to the SUMIFS, build one column per category, and average each column. That tells you groceries average 610 a month and eating out averages 190, which is more useful for planning than the single overall figure.

Spreading yearly bills across the monthly average

A yearly bill is a payment that comes once a year, such as car insurance or a membership. Over twelve months of statements it is already inside the average, spread across all twelve months. The trouble starts when you use fewer months, or when you plan cash flow, because the bill still arrives as one large payment.

When the window is shorter than a year

If you averaged three months and a 960 insurance bill was not in them, add it back by hand. Divide the yearly amount by twelve and add the result to your average: 960 divided by 12 is 80 a month. Do the same for each yearly bill you know about. A short list of these, with the month each one is due, is worth keeping next to the summary table.

Planning for the month the bill arrives

An average of 3,596 does not mean you can spend 3,596 every month. In the month the insurance renews, the bill arrives in full. Setting aside the monthly share in its own pot is what lets the average hold in practice. The sinking funds spreadsheet for yearly and seasonal bills shows how to track those pots so the money is there on the due date.

Some costs are not yearly but simply irregular: a car repair, a vet visit, a new phone. They do not arrive on a schedule, yet they show up somewhere in most years. A budget built for irregular expenses covers how to estimate them from past statements and fold them into the monthly figure.

Average or median: which monthly expense figure to plan with?

Plan with the average if you want a figure that covers the whole year, including the expensive months. Use the median if you want to know what a normal month costs, with the unusual ones set aside. Most people need both: the median for day-to-day spending and the average for the yearly total.

The median is the middle value when the twelve months are sorted. One very expensive month moves the average up but barely moves the median. In the worked table above, December's 4,905 raises the average by over 100 a month, while the median stays near the cluster of ordinary months.

Here is a quick way to decide:

• Building an emergency fund or estimating how long savings would last: use the average, because a crisis does not skip December.
• Setting a monthly spending limit for groceries or eating out: use the median per category, because it reflects a normal month.
• Checking whether spending is rising: compare this year's median with last year's, because a single big month will not distort it.

If the average and median are close, your spending is steady and the choice hardly matters.

Monthly expense averages that mislead: transfers, refunds and one-off purchases

An average misleads when the rows behind it include money that was not spending, or spending that will not repeat. Transfers between your own accounts inflate it. Refunds counted as income hide part of a purchase. A one-off purchase, such as a sofa, lifts the average for a cost you will not have next year.

Transfers and card payments

Moving 500 to savings is not an expense. Neither is paying your credit card from checking, if you also count the card's purchases. Both appear as money out on the checking statement. Label them Transfer before you total anything, and the SUMIFS above leaves them out.

Refunds

A refund is positive money on the statement. The formula above only adds negative amounts, so a refund is ignored, and the original purchase stays in full. If refunds are a large part of your year, net them against the purchase category instead: sum all amounts in the category, positive and negative together.

One-off purchases

Look at the largest five rows in the year. If one of them will not recur, give it a category such as One-off and leave it out of the planning average. Keep it in a separate line, so the yearly total still matches the statements. That way you plan with a figure that reflects your normal life, and you still know what the year cost.

Using the average monthly expenses figure for a budget or a fund

The figure has two main uses. In a budget, the per-category averages become your starting limits, adjusted for anything you plan to change. For savings goals, the overall average tells you how much an emergency fund needs to cover each month. Either way, the number is a starting point, not a target.

For a budget, take each category's median, round it, and write it as next month's plan. Then decide where you want to spend less. A starting plan based on your real history is far easier to keep than a plan based on a guess.

For an emergency fund, multiply the overall average by the number of months you want covered. With the example average of 3,596, three months is 10,788 and six months is 21,576. Some people use only essential categories for this, such as housing, food, utilities and insurance, since a crisis is also when you would cut back.

Recalculate once a year, using the most recent twelve months. Prices change, and so do households, and an average from two years ago will drift away from what you pay today.