top of page

IFRS 17 GMM Insurance Financial Projection Model

User-friendly IFRS 17 GMM insurance 5-year quarterly insurance business financial projection excel model for 10 insurance portfolios / groupings.

DOWNLOAD DETAILS:

Excel Models 

Populated & Unpopulated

€‎169.00

User-friendly Excel model for the preparation a of 5-year 3 statement (Income Statement, Balance Sheet and Cash flow Statement) financial projection with quarterly timeline for a general insurance company on an IFRS 17 General Measurement Model (GMM) approach generating insurance revenue, insurance service expenses, reinsurance expenses, finance expenses, investment return and other non-technical expenses.

The model allows the user to model up to 10 different insurance portfolios / grouping including separate premium amounts; reinsurance arrangements; commission and other directly attributable expenses; payment and recognition patterns; loss ratios and risk adjustment percentages. The model calculates balances for contractual service margin, risk adjustments and present values for future cash flows for each insurance portfolio / grouping.

Apart from insurance revenues , the model allows the user to model other operating expenses, investment return, fixed assets, corporate tax, borrowings, equity additions and dividend distributions.

KEY OUTPUTS

  • Projected full financial statements (Income Statement, Balance Sheet and Cash flow Statement) presented on a quarterly basis across 5 years and summarised on an annual basis.

  • Dashboard with:

    • Summarised projected Income Statement and Balance Sheet;

    • Compounded Annual Growth rate (CAGR) for each summarised income statement and Balance Sheet line item;

    • List of key ratios including, amongst other, gross service resultmargin; cession ratio, investment return, profit margins, average return on equity and average debt to equity ratio;

    • Bar charts summarising income statement and Balance Sheet projections;

    • Chart and table presenting insurance service result by portfolio / grouping.

KEY INPUTS

Setup Inputs:

  • Name of business;

  • Currency;

  • First projection year;

  • Naming for insurance portfolios, payment patterns, recognition patters, operating expenses, and fixed asset categories;

 

Pattern Inputs:

  • Premium payment patterns;

  • Claims payment patterns;

  • Recognition patterns.

 

Projection Inputs:

  • Insurance Service Result:

  • Gross premium written;

  • Gross loss ratios;

  • Gross commission ratios;

  • Risk adjustment percentage;

  • Payment and recognition patterns;

  • Proportional and non-proportional reinsurance

  • Discount rate.

  • Other operating expense inputs;

  • Financial investment inputs including return percentage and target cash balance;

  • Dividend inputs including amount or percentage of retained earnings and frequency;

  • Fixed Assets including addition amounts and useful life;

  • Borrowings including addition/redemption amounts and interest rate;

  • Share Capital additions.

MODEL STRUCTURE

The model comprises of 8 tabs split into input ('i_'), calculation ('c_'), output ('o_’) and system tabs. The tabs to be populated by the user are the input tabs ('i_Setup', ‘i_Patterns’ and 'i_Assumptions'). The calculation tab uses the user-defined inputs to calculate and produce the projection outputs which are presented in 'o_Fin Stats' and ‘o_Dashboard’.

​

System tabs include:

  • A 'Front Sheet' containing a disclaimer, instructions and contents;

  • A Checks dashboard containing a summary of checks by tab.

KEY FEATURES

  • The model follows best practice financial modelling guidelines and includes instructions, checks and input validations;

  • The model is not password protected and can be modified as required following download;

  • The model contains a dynamic timeline that allows a different starting projection period;

  • Timeline is split on a quarterly basis and summarised on an annual basis;

  • Insurance Contract assets and liabilities are split into CSM, risk adjustment and present value for future cash flows;

  • The model allows for the following number of underlying categories for each line item (these can be easily expanded if required):

  • Insurance Portfolios / groupings – 10 portfolios / groupings;

  • Operating expenses – 10 categories;

  • Payment Patterns – 10 patterns;

  • Recognition patterns – 10 patterns;

  • Fixed Assets – 3 categories;

  • Apart from projecting revenue and costs the model includes the possibility to model, investment return, fixed assets, borrowings, dividends and corporate tax;

  • Business name, currency, starting projection period are fully customisable;

  • Revenue, cost, fixed asset and borrowing category descriptions are fully customisable;

  • The model includes a checks dashboard which summarises all the checks included in the various tabs making it easier to identify any errors.

bottom of page