Free Church Accounting Excel Spreadsheet Setup

Quick Answer: A free church accounting spreadsheet in Excel needs fund accounting: every gift and every expense is tagged to a fund (General, Building, Missions, a memorial gift), so restricted money is never spent on the light bill. Add a giving log by donor, a monthly treasurer's report that shows each fund's opening balance, receipts, spending and closing balance, a bank reconciliation, and year-end giving statements that meet IRS Publication 1771.

A church's books answer a different question from a business's. Instead of "did we make a profit?", the congregation and the board want to know "how much is in each fund, and was every dollar used the way the giver asked?" That is fund accounting, and a well-organized Excel workbook handles it for a small congregation without paid software.

Why can't a church use an ordinary bookkeeping template?

A general bookkeeping spreadsheet template tracks income and expense categories and one bank balance. A church needs an extra dimension on every row:

• Unrestricted funds — the general fund, spent as the board decides.
• Temporarily restricted funds — designated by the giver for a purpose (youth trip, roof repair) and released when spent on that purpose.
• Permanently restricted funds — an endowment where only the earnings may be spent, if your church has one.

One bank account usually holds all of them. The bank only sees a single balance; the spreadsheet is what says $14,200 of that balance belongs to the building fund. Mixing them up is the most common way small church treasurers get into trouble, even when nobody did anything dishonest.

The workbook, tab by tab

Anonymous loose-plate cash goes into Giving with a donor ID like ANON and fund General, so it still counts toward receipts but never appears on anyone's statement.

Counting and recording the offering

Most church fraud guidance comes down to one practice: two unrelated people count every offering together, sign the count sheet, and the deposit is made intact. In the workbook:

1. The counters fill a count sheet: cash by denomination, each check with donor and amount, and any designation written on the envelope or check memo.
2. The bookkeeper enters each check and envelope as a Giving row, with the batch number from the count sheet.
3. The Deposits tab gets one row per batch with both counters' names and the total.
4. A check cell compares them: =SUMIFS(Giving[Amount],Giving[Batch],[@Batch])-[@Total] should be zero for every batch.

Online and card giving arrives differently: the giving platform sends one payout covering many gifts, minus its fees. Enter each gift from the platform's report as a Giving row with the gross amount, and enter the fee as a Ledger expense, so donors' statements show what they actually gave.

The monthly treasurer's report

Boards want one page. Build it by fund:

Each cell is a SUMIFS on the fund and the month. For gifts: =SUMIFS(Giving[Amount],Giving[Fund],$A4,Giving[Month],Report!$B$1). Spending comes from Ledger the same way. Closing is opening plus the row. Next month's opening is this month's closing.

Below the fund table, add a budget-versus-actual section for the general fund's expense categories (salaries, utilities, building maintenance, ministry supplies). Boards notice a utility bill trending up long before they read a full ledger.

How do you reconcile the church bank account?

The Total closing balance on the report must agree with the bank. Once the statement arrives:

• Start from the statement's ending balance.
• Subtract checks written but not yet cleared; add deposits made but not yet on the statement.
• The adjusted bank balance should equal the workbook's total closing balance.
• Investigate any difference before the board meeting, not after.

Get the statement into rows so you can tick off each check and deposit against the Deposits and Ledger tabs. A CSV download from the bank is easiest; if your bank only provides PDF statements, QuickBankConvert turns them into a sheet of dated amounts you can sort by check number. The layout for the reconciliation itself, with outstanding items, is in bank reconciliation in Excel. Ideally someone other than the person who writes checks does this step.

Year-end giving statements and the $250 rule

IRS Publication 1771 (Rev. 11-2023) sets the rules your statements must follow:

• A donor cannot deduct any single contribution of $250 or more without a contemporaneous written acknowledgment from the church. The donor is responsible for getting it, but the publication says the organization must help by providing a statement.
• The acknowledgment must include the church's name, the amount of any cash gift, a description (not a value) of any property given, and a statement that no goods or services were provided in return, if that is the case. If the church provided only intangible religious benefits, the statement says so.
• One annual summary can cover several single gifts of $250 or more. Letters, postcards, computer-generated forms and email are all acceptable; there is no IRS form for it.
• Separate gifts under $250 are not added together for this rule. The publication's own example is weekly offerings under $250 from a donor whose total for the year is more.
• To count as contemporaneous, the donor must receive it by the earlier of the date they file their return or the return's due date including extensions. Sending statements in January is the practical way to meet that.
• When a payment over $75 is partly a gift and partly for goods or services (a dinner ticket, for example), the church must give a written disclosure statement.

In the workbook, a PivotTable of Giving with Donor in rows and Fund in columns, filtered to the year and excluding ANON, gives every donor's total. A mail merge from that table into a letter template produces the statements. Donors who give by card or transfer can check them against their own statements; tracking charitable donations from bank statements covers that side. The IRS overview of charitable contribution deductions is a good page to point donors to with questions about their own taxes.

When to outgrow the spreadsheet

A workbook like this suits a congregation with one or two bank accounts, a handful of funds and a volunteer treasurer. Consider church management or nonprofit accounting software when you run payroll for several staff, have more than one person entering gifts each week, need online donor portals, or an auditor asks for a general ledger with a full audit trail. Keep the spreadsheet's fund list and categories; they map straight into most systems.