---
source: https://carbone.io/examples/bank-reconciliation-report/
title: "Bank Reconciliation Automation: Excel Reports | Carbone"
description: "Generate the month-end bank reconciliation in Excel from your bank reconciliation software or ERP: adjusted balances, aged items and entries as formulas."
generated_at: "2026-11-17"
---

# Bank reconciliation report

Month-end cash reconciliation with aging, journal entries and sign-off

[![Bank reconciliation report: XLSX generated from an XLSX template and JSON data](/examples/bank-reconciliation-report/thumb-result.webp)](/examples/bank-reconciliation-report/result.xlsx "Download the generated document")

[Download template](/examples/bank-reconciliation-report/template.xlsx)

[Sample data](/examples/bank-reconciliation-report/data.json)

[Download the generated XLSX](/examples/bank-reconciliation-report/result.xlsx)

- **Template:** XLSX
- **Export as:** XLSX, PDF, ODS, CSV, HTML [+19 more](/documentation/developer/http-api/generate-reports.md#output-file-type)
- **Carbone:** 5.18+

Features

-   [Loop](/documentation/design/repetitions/with-arrays.md)
-   [Formula](/documentation/design/template-formats/xlsx.md)
-   [Conditional formatting](/documentation/design/template-formats/xlsx.md)
-   [Chart](/documentation/design/advanced-features/charts.md)
-   [Number format](/documentation/design/formatters/number.md)
-   [Date](/documentation/design/formatters/date.md)
-   [Inline condition](/documentation/design/conditions/inline-conditions.md)

## Generate bank reconciliations from your reconciliation software

At each month-end close, the general ledger team reconciles every bank account to its GL cash account, and a controller reviews and signs the reconciliation before the books close. The matching engine of your bank reconciliation software or ERP compares the bank feed (BAI2 or camt.053) with the general ledger, and the items it cannot match become the reconciling items. Your close job sends them to the Carbone API as JSON and gets back one Excel workbook per account, ready for review. Every balance, total and check is an Excel formula, so the reviewer can recompute the reconciliation.

Reconciliations are a control activity of the COSO internal control framework, and the [GAO evaluation tool](https://www.gao.gov/assets/a76618.html) asks for signatures as evidence that they were performed. Reconciling promptly also protects the company: under [UCC 4-406](https://www.law.cornell.edu/ucc/4/4-406), a customer who does not report an altered check or an unauthorized signature within one year of the statement loses that claim against the bank.

In this example, Dunmere Industrial Supply reconciles its operating account at Pellbrook National Bank for October 2026. The company, the bank and the people are fictional, and the account number is masked.

In this example, you will:

-   Print the header and the preparer and reviewer sign-off, with real Excel dates
-   List each type of reconciling item in its own loop
-   Age the items with formulas and flag those over 30, 60 and 180 days
-   Build the two-sided summary, from the balance per bank and the balance per books to a difference of 0.00
-   Propose the journal entries of the book-side items and tie them out
-   Chart the open items of the last six month-ends

## Bank reconciliation structure

| Block | Content |
| --- | --- |
| Header | Company, bank, masked account, GL cash account, entity, period end, currency |
| Sign-off | Preparer and reviewer with their titles and signature dates, status, business days to review |
| Summary | Balance per bank statement, deposits in transit, outstanding checks and payments, bank errors, adjusted bank balance. Balance per general ledger, interest, direct receipts, bank fees, returned items, book errors, adjusted book balance. Unreconciled difference |
| Matching | Auto-matched, manually matched and unmatched items, with their share |
| Tables A to D | One table per type of reconciling item: date, reference, description, type, days outstanding, aging, amount, then the clearing date on the next statement or the journal entry |
| Table E | Proposed journal entries of the book-side items, with debits, credits and a tie-out to table D |

The JSON holds the raw data of the reconciliation system. An excerpt:

```json
{
  "periodEnd": "2026-10-31",
  "glAccount": { "number": "1010", "name": "Cash, operating account" },
  "bankStatement": { "endingBalance": 14051945.53 },
  "ledger": { "endingBalance": 12847903.18 },
  "reviewedBy": { "name": "A. Whitfield", "title": "Assistant Controller", "signedOn": "2026-11-06" },
  "matching": { "autoMatched": 1186, "manuallyMatched": 54 },
  "outstandingPayments": [
    { "date": "2026-04-22", "reference": "CHK 046870", "payee": "Pruett Calibration Services", "method": "Check", "amount": 640, "clearedOn": null },
    { "date": "2026-10-09", "reference": "CHK 049204", "payee": "Brasswick Packaging", "method": "Check", "amount": 42118.9, "clearedOn": "2026-11-02" }
  ],
  "bookItems": [
    { "date": "2026-10-15", "reference": "ANL-2609", "description": "Account analysis charge, September",
      "type": "Bank fee", "amount": -3912.55, "journalEntry": "JE-2610-093" }
  ]
}
```

Bank-side amounts are positive, and the summary subtracts the outstanding payments. Book-side amounts carry their effect on the book balance: a bank fee is negative.

## How to build a bank reconciliation in Excel

### 1\. Write the header and the sign-off

```text
A1: {d.company.name}{o.preReleaseFeatureIn=5018000}
A3: {d.periodEnd:diffD('2000-01-01','days'):mul(-1):add(36526):formatN}
A7: {d.preparedBy.name}, {d.preparedBy.title}
D8: {d.reviewedBy.signedOn:diffD('2000-01-01','days'):mul(-1):add(36526):formatN}
```

-   `{o.preReleaseFeatureIn=5018000}` prints nothing. It makes the [formulas](/documentation/design/template-formats/xlsx.md#formulas), the [conditional formatting](/documentation/design/template-formats/xlsx.md#conditional-formatting) and the print area follow the generated rows.
-   Excel stores a date as a number of days. [:diffD](/documentation/design/formatters/date.md#diffd-todate-unit-patternfromdate-patterntodate) counts the days from January 1, 2000, and [:add(36526)](/documentation/design/formatters/number.md#add-value) adds the Excel number of that date. With [:formatN](/documentation/design/template-formats/xlsx.md#numbers-and-dates) last, the cell is a real date, displayed by its cell format: `"Month ended "mmmm d, yyyy` in A3, `"Signed "mmmm d, yyyy` under each signature.
-   J7 prints the status with `=IF(ROUND($G$25,2)=0,"Reconciled, no unexplained difference","Unreconciled difference")`, and J8 counts the business days between the period end and the review with `=NETWORKDAYS($A$3+1,$D$8)`.

### 2\. List the reconciling items in loops

Each type of item has its own table and its own [loop](/documentation/design/template-formats/xlsx.md#loops): `d.depositsInTransit` in row 29, `d.outstandingPayments` in row 35, `d.bankErrors` in row 41 and `d.bookItems` in row 47. In table B:

```text
A35: {d.outstandingPayments[i].date:diffD('2000-01-01','days'):mul(-1):add(36526):formatN}
B35: {d.outstandingPayments[i].reference}
G35: {d.outstandingPayments[i].amount:formatN}
H35: {d.outstandingPayments[i].clearedOn:ifEM:show('1900-01-01'):diffD('2000-01-01','days'):mul(-1):add(36526):formatN}
A36: {d.outstandingPayments[i+1].reference}
```

Row 36 ends the loop and is removed. H35 writes the date of the payment on the next bank statement. When the payment has not cleared, [:ifEM:show](/documentation/design/conditions/inline-conditions.md#show-text-elseshow-text) passes an early date to the next formatters, and the cell format `[<100]"Open";mm/dd/yyyy` prints Open.

The total row below each loop counts and sums the generated rows: `=COUNT(G35:G36)` with the format `[=1]0" item";0" items"`, `=SUM(G35:G36)`, and `=COUNTIF(H35:H36,">100")` for the payments already cleared. Carbone extends these ranges to all the rows of the loop.

### 3\. Age the items and flag the exceptions

-   E35, days outstanding: `=$A$3-A35`, the period end minus the transaction date.
-   F35, aging: `=IF(E35>180,"Stale-dated",IF(E35>60,"Over 60 days",IF(E35>30,"31 to 60 days","")))`. A bank does not have to pay an uncertified check presented more than six months after its date ([UCC 4-404](https://www.law.cornell.edu/ucc/4/4-404)), so a check over 180 days is voided and reissued, or reported as unclaimed property.
-   Two conditional formatting rules on E35:F35 color the exceptions only: `=$E35>60` in red and `=AND($E35>30,$E35<=60)` in amber. Carbone extends them to every generated row.

### 4\. Build the two-sided summary

The summary reads the total rows of the tables. Carbone moves these references when the loops add rows.

-   Bank side: G11 is `{d.bankStatement.endingBalance:formatN}`, then `=G31` adds the deposits in transit, `=-G37` subtracts the outstanding payments, `=G43` adds the bank errors, and `=SUM(G11:G14)` gives the adjusted bank balance.
-   Book side: G17 is `{d.ledger.endingBalance:formatN}`. Each line adds the book-side items of one type, like `=SUMIFS($G$47:$G$48,$D$47:$D$48,"Interest earned")`, then `=SUM(G17:G22)` gives the adjusted book balance. An item with an unknown type stays out of the summary and shows as a difference.
-   G25 is `=ROUND(G15-G23,2)`. A conditional formatting rule fills it in red when it is not 0.00, and the status turns red.
-   The matching block counts the unmatched items from the tables, `=F31+F37+F43+F49`, and divides each count by the total: `=IF($K$14=0,0,K11/$K$14)`.

### 5\. Propose the journal entries

Row 53 repeats `d.journalEntryLines`: `{d.journalEntryLines[i].entry}`, `{d.journalEntryLines[i].account}`, `{d.journalEntryLines[i].debit:formatN}` and `{d.journalEntryLines[i].credit:formatN}`. The cell format `#,##0.00_);(#,##0.00);_)` leaves the zero side empty.

-   Row 55 totals the debits and the credits. A rule turns them red if they differ.
-   G56 computes the net change to the cash account: `=SUMIFS(G53:G54,$B$53:$B$54,$U$1)-SUMIFS(H53:H54,$B$53:$B$54,$U$1)`. Hidden cell U1 holds `{d.glAccount.number}`.
-   H56 compares it with the total of table D, `=ROUND(G56-G49,2)`, and prints "Agrees with D" through its number format.

### 6\. Chart the open items and set up the page

Hidden columns P to S hold the [chart](/documentation/design/template-formats/xlsx.md#charts) data. Rows 11 to 15 read the five prior month-ends with [array indexes](/documentation/design/substitutions/the-basics.md#accessing-arrays), like `{d.history[0].monthEnd:formatD('MMM')}` and `{d.history[0].within30:formatN}`. Row 16 counts the items of the current month from the tables, like `=COUNTIFS($E$29:$E$30,"<=30")+COUNTIFS($E$35:$E$36,"<=30")+...`. The stacked chart sits in J17, above the loops, so the generated rows do not move it.

Freeze the panes under the header, and print in landscape, fitted to the page width. The [header and footer](/documentation/design/template-formats/xlsx.md#page-layout) print `{d.company.name}`, the masked account, the period and `Page &P of &N`.

## Related Examples

- [Checking account statement](/examples/bank-statement-expert/index.md): DOCX template to PDF.
- [Excel transaction report](/examples/transaction-report/index.md): XLSX template to XLSX.
- [Quarterly financial dashboard](/examples/s-financial-dashboard/index.md): XLSX template to XLSX.

## Related topics

- [Accounts receivable aging report](/examples/accounts-receivable-aging-report/index.md)
- [Budget vs actual report](/examples/budget-vs-actual-report/index.md)
- [13-week cash flow forecast](/examples/cash-flow-forecast-13-week/index.md)
- [Sales commission statement](/examples/sales-commission-statement/index.md)
- [Excel transaction report](/examples/transaction-report/index.md)
- [Monthly treasury FX report](/examples/financial-report/index.md)
