The Retrospective Life Insurance Method: A Pedagogic Spreadsheet Application

Abstract

This paper describes an innovative Excel spreadsheet model, referred to as the Retrospective Method Spreadsheet, that has been successfully used in risk management and insurance, and personal finance courses to demonstrate the retrospective life insurance method. There are numerous pedagogic benefits of modeling life insurance funding in a spreadsheet application, especially when class time is limited. Our method provides an intuitive understanding of the sophisticated mathematical and statistical models common to actuarial science. The model is quite flexible and can be applied to life insurance policies (e.g., interest-sensitive whole life and universal life products) reflecting a variety of fee, expense, surrender charges, current interest rates or interest rate scenarios. Furthermore, current cost of insurance assumptions may be generated for any age, by gender, and for any proposed premium payment pattern. Given the interest rate sensitivity of the spreadsheet application, the paper also includes a discussion of how to utilize Monte Carlo simulation in investigating the impact of interest rate variability on insurance policy performance.

Keywords: Retrospective Life Insurance Method, Finance Pedagogy, Spreadsheet Applications

JEL Classification: A20, A23, G30