Intercompany Reconciliation Template for Multi-Entity Businesses

If your business runs through more than one legal entity (a holding company, an operating LLC, a property LLC that owns the building, maybe a shared services company that runs payroll), money moves between them all the time. Management fees, rent, loans, cost recharges and cash sweeps all create intercompany balances. One entity records a receivable, the other records a payable, and in theory the two match to the penny.

In practice they rarely do. An intercompany reconciliation finds out why, pair by pair, so the balances can be eliminated cleanly when you consolidate. This page gives you a free intercompany reconciliation template for Excel and Google Sheets, a free online version you can use right here, and a step-by-step guide with a worked example.

Free, no sign-up. Works in Excel, Google Sheets and LibreOffice. Handles up to 8 entities (28 entity pairs).

Free online intercompany reconciliation tool

List your entities, enter each entity’s intercompany balances, then explain the differences, or click Load example. The tool matches every pair of entities, shows what’s reconciled and what isn’t, lists the entries each entity still needs to book, and runs an elimination check for the whole group. You can print it, save it as a PDF, download it as Excel, Word or CSV, or send a link to your controller or outside accountant.

1. Group and entities

CodeLegal entity name

Use a short, unique code for each entity (HQ, OPS, RE). The codes fill the dropdowns below.

2. Intercompany balances per each entity’s books

Entity (books of)CounterpartyAccountDue from ($)Due to ($)Net

One row per intercompany account at period end. Due from is a receivable, Due to is a payable; both are positive numbers.

3. Reconciling items

StatusDateRecorded byCounterpartyTypeDescriptionAmount ($)

“Recorded by” is the entity that has already booked the item. Enter amounts as positive numbers; the type decides the sign. Cleared items are kept for your records but don’t count. Your data stays in your browser. Nothing is sent to our server unless you choose to save it to your account.

4. Your intercompany reconciliation

Entries to book

Elimination check

Prepared by: ____________________   Reviewed by: ____________________   Date: __________

Made with the free intercompany reconciliation tool at bookkeepingsheets.com

What is an intercompany reconciliation?

An intercompany reconciliation compares what two related entities say they owe each other on the same date. If Holdings shows a $48,500 receivable from Operating (“Due from Operating”), Operating should show exactly $48,500 payable to Holdings (“Due to Holdings”). When it doesn’t, something has been recorded by one side and not the other, recorded at a different amount, or recorded in the wrong period.

The check matters because of consolidation. When you combine the entities into one set of group financial statements, every intercompany receivable is eliminated against the matching payable, and intercompany revenue against the matching expense. US GAAP (ASC 810) requires those eliminations, and lenders and investors reading consolidated statements expect them. If the two sides don’t agree, the elimination leaves a leftover balance that doesn’t belong to anyone, and your consolidated balance sheet is wrong.

Most differences fall into a few predictable groups:

  • Charges one side hasn’t booked: Holdings invoices a management fee or loan interest on the 31st, and the subsidiary books it next week.
  • Accruals one side hasn’t billed: Operating accrues the payroll services it owes Shared Services, but Shared Services hasn’t issued the recharge yet.
  • Cash in transit: one entity wired a payment on the 30th, and the other recorded the receipt on the 1st.
  • Currency and rounding: entities in different currencies translate the same balance at different rates.
  • Errors: duplicate postings, wrong counterparty, wrong account, a charge posted to a third-party vendor instead of the related entity.

What’s inside the template

The workbook has five sheets:

  • Start Here: group name, period end date, currency, a tolerance (differences up to this amount count as reconciled; the default is $1.00) and a list of up to 8 entities with a short code for each. A result box shows how many entity pairs are reconciled and the total unexplained difference.
  • Balances: one row per intercompany account from each entity’s trial balance: the entity, the counterparty, the account, and the balance as Due from (receivable) or Due to (payable). Net is calculated for you. A Check column flags unknown entity codes or rows where an entity is matched with itself.
  • Items: the reconciling items that explain each difference. You choose the entity that has recorded the item, the counterparty, one of 8 item types, a description and a positive amount. The type sets the sign and fills a Who records column that tells you which entity books the fix.
  • Pair Report: the printable report. Every possible pair of entities, each one’s net balance with the other, the difference, the amount explained by open items, what’s still unexplained and a green Reconciled or red Investigate status. Below it: group totals, an elimination check and prepared-by and reviewed-by lines.
  • Lists: the 8 item types, their sign and a one-line explanation of each.

Inputs are blue text on a yellow background; everything else is a formula. The file comes filled with a four-entity example so you can see how it works. Replace the entities and delete the example rows on Balances and Items before you start.

How to reconcile intercompany accounts in 6 steps

Step 1: Close each entity’s books to the same date

Every entity should have posted its intercompany activity through the same cut-off date before you start. A common rule is that intercompany invoices for the month are issued by a set working day and both sides post them in the same period. Without a shared cut-off date you’ll reconcile timing differences forever.

Step 2: List every entity and give it a code

Short codes (HQ, OPS, RE, SVC) keep the dropdowns readable. Use the same codes your team already uses in account names or the general ledger, if you have them.

Step 3: Pull the intercompany balances from each trial balance

For each entity, enter every Due from and Due to account, and say which related entity it’s with. An intercompany loan and its accrued interest can be separate rows. The template adds them together for the pair. If an entity has one combined “Intercompany” account for all related parties, ask for a breakdown by counterparty; you can’t reconcile a lump sum.

Step 4: Read the pair report

For every pair, A’s net balance with B (Due from minus Due to) plus B’s net balance with A should be zero. A positive difference means A shows more owed to it than B admits to owing; a negative difference means the opposite.

Step 5: Explain each difference with reconciling items

Start with the biggest difference. Compare the two intercompany ledgers line by line for the month, find the transactions that appear on one side only or at different amounts, and enter each one as an item. “Recorded by” is always the entity that has booked it. When a pair’s unexplained amount is within tolerance, it turns green.

Step 6: Book the fixes and get sign-off

The Who records column (and the “Entries to book” table in the online tool) tells you which entity owes an entry. Timing items are booked by the counterparty; errors are corrected by the entity that made them. Have each entity’s accountant sign off on its pairs, and keep the report with your month-end close file.

Intercompany reconciliation example (sample)

Harbor Group has four entities: Harbor Group Holdings (HQ), Harbor Operating (OPS), Harbor Real Estate (RE) and Harbor Shared Services (SVC). Here’s the October 31 pair report from the template’s example data:

PairA’s net with BB’s net with ADifferenceExplained byUnexplained
HQ ↔ OPS$48,500-$45,500$3,000October management fee billed by HQ, not yet booked by OPS ($3,000)$0
HQ ↔ RE$150,625-$150,000$625Loan interest accrued and billed by HQ, not booked by RE ($625)$0
HQ ↔ SVC-$8,025$9,100$1,075IT support recharge billed by SVC, not booked by HQ ($1,250)-$175
OPS ↔ RE-$6,000$12,000$6,000Rent wired by OPS on 10/30, not yet recorded by RE ($6,000)$0
OPS ↔ SVC-$5,400$5,000-$400Payroll services accrued by OPS, not yet billed by SVC (-$400)$0
RE ↔ SVC-$2,300$2,300$0Nothing to explain$0

Five of the six pairs are reconciled. HQ ↔ SVC still has $175 unexplained: after the $1,250 recharge, HQ’s payable to SVC is $175 higher than SVC’s receivable. Comparing the two ledgers turns up a $175 software recharge from SVC that HQ posted twice. Add it as an “Error: receivable too low / payable too high” item recorded by HQ, and the pair, and the whole group, reconciles. HQ reverses the duplicate.

The elimination check tells the same story for the group: total Due from of $227,525 against total Due to of $217,225 leaves $10,300 of differences, $10,475 is explained by open items, and -$175 is left until the duplicate is found.

How the calculations work

  • Net balance: for each row, Net = Due from – Due to. A pair’s net for entity A is SUMIFS(Net, Entity, A, Counterparty, B).
  • Difference: A’s net with B + B’s net with A. If both sides agree, one is the exact negative of the other and the sum is zero.
  • Explained: the signed total of open items for the pair, in either direction. A charge billed, a payment sent or an error that makes the recording entity’s balance too high counts as plus; a charge accrued, a payment received or an error that makes it too low counts as minus.
  • Unexplained: Difference – Explained. The pair is reconciled when the absolute value is within your tolerance.

The template uses only SUMIFS, COUNTIFS and INDEX/MATCH, so it works the same in Google Sheets.

Common intercompany reconciliation mistakes

  • Using one “intercompany” account for everyone. You need balances by counterparty. Set up separate Due to and Due from accounts (or a counterparty field) for each related entity.
  • No cut-off rule. If one entity books October charges in November, every month starts with differences that aren’t really errors.
  • Netting differences across pairs. A $500 difference with one entity and -$500 with another don’t cancel. Reconcile each pair on its own.
  • Plugging the difference. Booking an unexplained amount to “intercompany suspense” makes the report green and the books wrong. Find the transaction.
  • Ignoring the P&L side. Intercompany management fee income in HQ should match the management fee expense in OPS. If the balance sheet reconciles but the income and expense don’t, something was posted to the wrong account.
  • Undocumented loans. Intercompany loans should have a written note and a stated interest rate. For tax purposes the IRS expects related-party loans to charge an arm’s-length rate; the Applicable Federal Rates it publishes each month are the usual reference point. Ask your tax advisor what applies to you.

FAQ

What’s the difference between “due to” and “due from”?

“Due from” is an intercompany receivable: a related entity owes you. “Due to” is an intercompany payable: you owe a related entity. Every Due from in one entity should have a matching Due to in the other.

How often should intercompany accounts be reconciled?

Monthly, as part of the close, before you consolidate. Groups with a lot of intercompany activity often agree balances with each other a few days before month-end so the close isn’t held up.

Who should book the correcting entry?

For timing items, the entity that hasn’t recorded the transaction yet books it, usually in the same period if the books are still open. Errors are corrected by the entity that made them. Both entities should agree before anyone posts.

Can I use this template for entities in different currencies?

Yes, if you enter every balance in one reporting currency, translated at the same rate on both sides. Rate differences go on the Items sheet as FX differences. For large foreign subsidiaries, agree on the rate source and date in advance.

Does the template work in Google Sheets?

Yes. Upload the .xlsx file to Google Drive and open it with Google Sheets. The dropdowns, formulas and color highlights all carry over.

What should a template for reconciling multiple entities include?

A good intercompany account reconciliation template for several entities needs four things: one row per entity with a short code, the balance each entity shows for every other entity (due to and due from), a pair report that nets each pair to zero and flags the difference, and a place to list reconciling items such as goods in transit, timing gaps or FX differences until they are booked. This template does all four for up to 8 entities and works in Excel and Google Sheets.

Is there an intercompany reconciliation sample I can follow?

Yes. The intercompany reconciliation example above walks through a filled-in sample with four entities, the pair report, the differences it finds and how each one is explained. Download the template and replace the sample numbers with your own.

Related templates

An intercompany reconciliation sits alongside your monthly bank reconciliation: get each entity’s cash right first. Intercompany charges that are invoiced like any other bill can also be tracked on the accounts receivable aging report and the accounts payable tracker, and a 13-week cash flow forecast shows when intercompany loans and sweeps will hit each entity’s bank account. For the journal entries behind these balances, see due to / due from accounting explained. New to spreadsheets? Start with our guide to bookkeeping in Excel.

This guide and template are for general record-keeping and information. They aren’t tax, legal or accounting advice. For questions about your own situation, talk to a qualified accountant or tax professional.