Automation for IBNR Provision Using Power BI and Excel

Abstract

In Ecuador, Life insurance companies are regulated by Companies, Securities and Insurance Superintendence and according to resolution No. SCSV-378-2017-S “Standards for the technical prudence of companies that finance comprehensive prepaid health care services” this type of companies must make a series of provisions, among others, the Incurred But Not Reported Reserve IBNR, this provision ensure that the company has sufficient resources to cover the claims made by their affiliates. IBNR is calculated by an actuary, based on the reimbursements made by the companies at the cut-off date using claims triangles with the Chain Ladder method. It is so, that in this document it is proposed to automate the calculation of this reserve using Power BI to reproduce the recalculation made by the Actuary through an independent area (Internal Audit, Risks, Comptroller, Financial Management, etc.) and in this way ensure that the estimate is fairly made and accounted for.