Intercompany ECL template

An Excel template for the IFRS 9 loss allowance on amounts owed by group companies and other related parties – term loans, loans repayable on demand, cash-pool current accounts and trade balances – and on financial guarantees given for their debts. It is built for the lender's or guarantor's own separate or individual financial statements, with a fully worked example for a fictitious group.

What it does

• Stages every balance: low credit risk, a significant fall in credit grade, the 30-days-past-due backstop, the watchlist and evidence of credit impairment – with overrides that need a reason.

• Finds the probability of default from a credit grade – a shadow-rating scorecard (five ratios and three judgements) or an entered rating – and a cumulative default table you replace with your own source.

• Measures loss given default by benchmark, a recovery waterfall of the borrower's assets and creditors, collateral or your own estimate; exposure follows the repayment profile and includes undrawn commitments.

• Measures loans repayable on demand as if called at the reporting date, with probability-weighted recovery strategies: repayment over time, a sale or refinancing, or insolvency.

• Weights three forward-looking scenarios and shows year-by-year workings for every balance, so each figure can be traced and evidenced.

• Produces the IFRS 7.35H reconciliation by stage for balances and guarantees, the IFRS 9.4.2.1(c) guarantee liability, the impairment charge and balanced journals.

• Gives the IFRS 7.35M credit-grade table, IAS 24 related party figures and draft accounting-policy wording, with 20 automatic checks.

What you get

• One Excel workbook (.xlsx) with an Instructions tab, the blank template (up to 20 balances and 10 borrowers) and a worked example for a fictitious holding company with ten balances: two term loans, a loan to an associate with an undrawn facility, two demand loans, a cash-pool account, three trade balances and a guarantee of a subsidiary's bank loan.

• Clearly marked input cells, protected formulas and built-in checks, with a status line on every tab.

• Works in Microsoft Excel 2010 or later (Windows or Mac). No macros.

Scope

For the lender's or guarantor's separate or individual financial statements under IFRS Accounting Standards – intra-group balances are eliminated on consolidation. Not designed for the IFRS for SMEs Standard or FRS 102 Section 11. Purchased or originated credit-impaired assets, modifications, foreign currency, revolving cash-pool facilities and deferred tax are outside its scope. The default table supplied is illustrative and must be replaced with a source you can evidence.

Working out the amortised cost of an interest-free or below-market loan first? Use the free Intercompany loans template.

Designed for preparers, finance teams and advisers applying IFRS Accounting Standards. It is a working aid: it does not replace the Standards or professional judgement, and its scope and limitations are set out in the Instructions tab.