Loss Modeling Using Spreadsheet‐Based Simulation

ABSTRACT With the dramatic increase in the speed of personal computers and steep decline in the cost of computing, simulation has become one of the standard tools in the risk manager's toolbox and should now become one of the standard tools in the risk management and insurance student's toolbox. This teaching note aims to facilitate this process by showing how to create and run a simulation in a spreadsheet environment, and interpret simulation results to gain insight and understanding about a real-world problem. Specifically, this teaching note provides step-by-step instruction for simulating the present value of payments for losses occurring within a 1-year policy period. Losses are covered by an aggregate excess of loss treaty. The uncertainty lies in the frequency and severity of losses as well as in claim processing time, and also in the discount rate for calculating the present value of loss payments. INTRODUCTION For a long time, it has been virtually impossible for anyone but the "quants" who had some computer programming expertise to use simulation to model risk management and insurance problems. Thanks to the transparency, flexibility, and speed of spreadsheet-based simulation softwares, simulation modeling need no longer be viewed as the exclusive province of the "quants." Indeed, the success of simulation efforts is usually predicated upon active collaboration by "quants" and nonquants. Spreadsheet is practically the second language of business. By sparing the "quants" and nonquants the trouble of having to reinvent simulation in an Excel environment, spreadsheet-based simulation software allows both groups to focus on structuring problems that make managerial sense and on interpreting results for the purpose of supporting and improving the quality of executive decisions. Not surprisingly, simulation has become one of the standard tools in the risk manager's toolbox. It is only a matter of time when simulation modeling becomes one of the standard tools in the risk management and insurance student's toolbox. This teaching note aims to facilitate this process by showing, in the context of a simplified real-world insurance problem, how to create and run a simulation in a spreadsheet environment, and how to interpret simulation results to gain insight and understanding about the problem.1 This note provides step-by-step instruction for simulating the present value of payments for losses occurring within a 1-year policy period.2 The uncertainty lies in the frequency and severity of losses as well as in claim processing time, and also in the discount rate for calculating the present value of loss payments. In the example, interarrivai times (C^sub j^ C^sub j-1^) between losses are independent and identically distributed (i.i.d.) with an exponential distribution, the loss amounts (X^sub j^) are i.i.d. with a Pareto distribution, the time (L^sub j^) from occurrence to payment for a covered loss has a Weibull distribution that depends on the loss amount, the required log return (R^sub ΔT^) over a period of time has a normal distribution that depends on the length of the period, and finally, all losses during the 1-year policy period are covered by an aggregate excess of loss treaty.3 The notation and specific distributional assumptions are given in the next section. NOTATION AND ASSUMPTIONS j = index for a loss event, j = 1, 2, 3, . . . ; E^sub j^ - binary variable indicating if loss event j occurred within the 1-year policy period; N = number of covered loss events within the 1-year policy period; C^sub j^ = time of the jth loss, C^sub j^ - C^sub j-1^ ~ i.i.d. Exponential(0.2), Co = 0; X^sub j^ = amount of loss associated with loss event j, X^sub j^ ~ i.i.d. Pareto(1000,3); L^sub j^ = time from occurrence to payment for the jth loss, L^sub j^ ~ Weibull(1.5, LN(X^sub j^ + 1)/6); T^sub j^ = date of payment for the jth loss, T^sub j^ = C^sub j^ +L^sub j^; T*^sub n^ = date of the nth payment regardless of when the covered loss occurred, n = 1, . …