Build a VAT Return Summary from Transactions

Every VAT quarter, someone turns the period's transactions into the handful of figures a return asks for: VAT charged on sales, VAT reclaimable on purchases, the net amount due, and the value of sales and purchases. Accounting systems produce the return themselves, but finance teams still build their own summary — to review the figures before filing, to combine several entities or systems, or because part of the data lives outside the ledger. The calculation is simple arithmetic; the difficulty is getting every tax code into the right box, spotting lines where the VAT does not match the rate, and being able to show a reviewer exactly which transactions make up each figure. This page builds that summary with pandas, using the UK's nine-box return as the worked example, with the code-to-box rules held in a table so they can be adapted to other countries' returns. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is the quarter's transactions — one row per invoice line with type (sale or purchase), tax code, net amount and the VAT amount as recorded in the ledger. The tax codes are standard rate (S, 20%), reduced rate (R, 5%), zero-rated (Z), exempt (E), outside the scope of VAT (OS, such as wages or some fees) and reverse charge (RC, services bought from abroad, where the buyer accounts for the VAT). The box rules below follow the UK return; they are an illustration of the technique, not tax advice, so confirm the treatment of your codes with current guidance or your adviser.

Step 1 — Load and check every line's VAT

Before summarising, check that each line's recorded VAT matches its net amount times its rate. Differences of a penny are rounding; anything more is a coding or keying error that should be fixed before the return, not discovered after:

import pandas as pd

tx = pd.read_excel("vat_q3_transactions.xlsx")
RATES = {"S": 0.20, "R": 0.05, "Z": 0.0, "E": 0.0, "OS": 0.0, "RC": 0.0}
tx["Expected VAT"] = (tx["Net"] * tx["Tax code"].map(RATES)).round(2)
tx["VAT diff"] = (tx["VAT"] - tx["Expected VAT"]).round(2)
exceptions = tx[tx["VAT diff"].abs() > 0.01]
print(len(tx), "lines;", len(exceptions), "with VAT not matching the rate")
print(exceptions[["Ref", "Type", "Tax code", "Net", "VAT", "Expected VAT", "VAT diff"]].to_string(index=False))

Two lines out of 172 fail. One sale carries 12.40 more VAT than its rate gives — perhaps a keying error. One standard-rated purchase has VAT at 5% — either the code or the amount is wrong, and only the invoice will tell which. Reverse-charge lines record no VAT on the invoice, because the supplier does not charge it; the VAT is calculated by the buyer, in the next step. The exceptions go on their own sheet so they are resolved, or consciously accepted, before filing.

Step 2 — Map tax codes to boxes with a table

Which box a line feeds depends on its type and tax code. Writing that as if statements buries the tax rules in code; a mapping table keeps them visible and reviewable, and lets another country's return reuse the same program with a different table:

MAP = pd.DataFrame([
    # type,       code, net box, output VAT box, input VAT box
    ("sale",      "S",  6,   1,    None),
    ("sale",      "R",  6,   1,    None),
    ("sale",      "Z",  6,   None, None),
    ("sale",      "E",  6,   None, None),
    ("sale",      "OS", None, None, None),
    ("purchase",  "S",  7,   None, 4),
    ("purchase",  "R",  7,   None, 4),
    ("purchase",  "Z",  7,   None, None),
    ("purchase",  "E",  7,   None, None),
    ("purchase",  "OS", None, None, None),
    ("purchase",  "RC", "6+7", 1,  4),
], columns=["Type", "Tax code", "Net box", "Output box", "Input box"])

lines = tx.merge(MAP, on=["Type", "Tax code"], how="left", validate="many_to_one", indicator=True)
unmapped = lines[lines["_merge"] == "left_only"]
assert unmapped.empty, f"unmapped tax codes: {unmapped[['Type', 'Tax code']].drop_duplicates().values.tolist()}"
lines["RC VAT"] = (lines["Net"] * 0.20).where(lines["Tax code"] == "RC", 0.0).round(2)
print(MAP.to_string(index=False))

Zero-rated and exempt sales carry no VAT but still count in the value of sales (box 6); out-of-scope items appear nowhere. Reverse charge is the interesting row: the buyer accounts for VAT as if it had made the sale and reclaims it as input VAT, so the same amount goes into box 1 and box 4 — it usually nets to zero — and the net value appears in both box 6 and box 7. The validate="many_to_one" guard makes the merge fail if the table accidentally has two rows for one code, and the assertion fails the run if a transaction uses a code the table does not know — the safe behaviour, since a new code silently ignored means a wrong return.

Step 3 — Sum the boxes

def box_sum(mask, column):
    return round(float(lines.loc[mask, column].sum()), 2)

net6 = lines["Net box"].astype(str).str.contains("6")
net7 = lines["Net box"].astype(str).str.contains("7")
boxes = {
    1: box_sum(lines["Output box"] == 1, "VAT") + box_sum(lines["Tax code"] == "RC", "RC VAT"),
    2: 0.0,
    4: box_sum(lines["Input box"] == 4, "VAT") + box_sum(lines["Tax code"] == "RC", "RC VAT"),
    6: round(float(lines.loc[net6, "Net"].sum())),
    7: round(float(lines.loc[net7, "Net"].sum())),
    8: 0, 9: 0,
}
boxes[3] = round(boxes[1] + boxes[2], 2)
boxes[5] = round(boxes[3] - boxes[4], 2)
LABELS = {1: "VAT due on sales and other outputs", 2: "VAT due on acquisitions (NI only)", 3: "Total VAT due",
          4: "VAT reclaimed on purchases and other inputs", 5: "Net VAT to pay (or reclaim)",
          6: "Total value of sales, excluding VAT", 7: "Total value of purchases, excluding VAT",
          8: "Supplies of goods to the EU (NI only)", 9: "Acquisitions of goods from the EU (NI only)"}
summary = pd.DataFrame({"Box": list(LABELS), "Description": list(LABELS.values()), "Amount": [boxes[b] for b in LABELS]})
print(summary.to_string(index=False))

Boxes 6 and 7 are reported in whole pounds, the VAT boxes in pounds and pence, which is why they are rounded differently. Boxes 2, 8 and 9 apply only to goods moving between Northern Ireland and the EU and are zero here. Box 5 is box 3 minus box 4: positive means VAT to pay, negative a repayment. This quarter the VAT reclaimed on purchases, 43,187.34, slightly exceeds the 43,124.09 due on sales, so the return shows a repayment of 63.25 — a result worth a second look, because a single miscoded purchase can turn a payment into a repayment. The 274.71 purchase from Step 1 is a case in point: if its correct VAT is the 1,098.85 the rate implies, box 4 rises by 824.14.

Step 4 — Reconcile and write a traceable workbook

The VAT boxes should agree with the movement on the ledger's VAT accounts for the quarter; any difference is investigated before filing. Here the ledger totals are the recorded VAT columns by type:

ledger_output = round(float(tx.loc[tx["Type"] == "sale", "VAT"].sum()), 2)
ledger_input = round(float(tx.loc[tx["Type"] == "purchase", "VAT"].sum()), 2)
rc_total = box_sum(lines["Tax code"] == "RC", "RC VAT")
recon = pd.DataFrame({
    "Check": ["Box 1 less reverse charge = ledger output VAT", "Box 4 less reverse charge = ledger input VAT"],
    "Return": [boxes[1] - rc_total, boxes[4] - rc_total], "Ledger": [ledger_output, ledger_input]})
recon["Difference"] = (recon["Return"] - recon["Ledger"]).round(2)
print(recon.to_string(index=False))

with pd.ExcelWriter("vat_return_2026_q3.xlsx", engine="openpyxl") as xw:
    summary.to_excel(xw, sheet_name="Return", index=False)
    recon.to_excel(xw, sheet_name="Reconciliation", index=False)
    exceptions.to_excel(xw, sheet_name="Exceptions", index=False)
    (lines.pivot_table(index=["Type", "Tax code"], values=["Net", "VAT", "RC VAT"], aggfunc="sum").round(2)
          .to_excel(xw, sheet_name="By tax code"))
    lines.drop(columns="_merge").to_excel(xw, sheet_name="Transactions", index=False)
    MAP.to_excel(xw, sheet_name="Box mapping", index=False)

Both reconciliation lines agree to the penny, because the boxes were built from the same recorded VAT amounts — the exceptions from Step 1 are included at their recorded values until corrected, which is right: the return must match the books, and the books are what gets fixed. The workbook gives a reviewer the return, the reconciliation, the exceptions, totals by type and tax code, every transaction with the boxes it feeds, and the mapping table itself. Any figure on the return can be traced to its lines with a filter, which is what a reviewer — or a tax inspector — will want to do. Combining entities that report in different currencies first needs the conversion in Convert Multi-Currency Transactions with an FX Rate Table.

Common pitfalls

• Tax rules hard-coded in if statements. Keep the code-to-box mapping in a table.
• Unknown tax codes ignored. Fail the run when a code is not mapped.
• Forgetting reverse charge. It adds to both output and input VAT and to boxes 6 and 7.
• Leaving out zero-rated and exempt values. They belong in boxes 6 and 7.
• No reconciliation. The boxes must agree with the ledger's VAT accounts.

Conclusion

To build a VAT return summary from transactions, first check every line's VAT against its rate and list the exceptions, then map each type and tax code to return boxes through an explicit table that fails on unknown codes, apply reverse charge to both sides, sum the boxes with the right rounding, and reconcile the VAT boxes to the ledger. Write the return together with its reconciliation, exceptions, breakdown and transactions so every figure can be traced.

Frequently asked questions

How do I calculate VAT return boxes from a transaction list in pandas? Map each transaction's type and tax code to the boxes it feeds with a mapping table, merge it onto the transactions, and sum the net and VAT columns per box.

How is reverse charge handled on a VAT return? The buyer calculates the VAT on the net value and includes it as output VAT and as input VAT, and includes the net value in both the sales and purchases value boxes.

Do zero-rated and exempt sales go on the VAT return? Yes. They carry no VAT but their net value is included in the total value of sales, while out-of-scope items are left out.

How do I check that VAT amounts are correct? Recompute each line's VAT as net times the rate for its tax code and list lines where the recorded VAT differs by more than rounding.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Trial Balance from a General Ledger — the ledger the return reconciles to.
• Build a Supplier Payment Run Proposal — another accounts payable task.
• Validate a Merge to Avoid Duplicate Rows — the merge guard used here.