Building and validating actuarial mortality tables in Excel is a fundamental skill for candidates preparing for the SOA Exam LTAM (Long-Term Actuarial Mathematics). It might seem daunting at first, but with a step-by-step approach, you can create robust mortality tables that not only help you grasp key actuarial concepts but also sharpen your Excel skills in a practical way. Let’s walk through the process as if I were guiding a fellow actuarial student.
First things first: what exactly is a mortality table? In essence, it’s a table showing the probability that a person of a certain age will die before reaching the next age. This is crucial for valuing life insurance contracts, pensions, and annuities. The starting point is often a raw dataset of mortality rates (qx), which represents the probability of death within a year for each age.
To build your mortality table in Excel, begin by setting up your spreadsheet with columns for age (x), probability of death (qx), number of survivors (lx), number of deaths (dx), and probability of survival (px). The age column is your anchor, usually starting at 0 or the minimum age in your dataset and going up to the maximum age you want to model.
The qx values might come from standard tables like the SOA’s Basic Select and Ultimate tables or any custom mortality data you have. Input these into the qx column. The next step is to calculate lx, the number of people alive at age x. Conventionally, you start with a radix, say 100,000 at age 0, and compute lx+1 as lx multiplied by (1 - qx). In Excel, if your first lx is in cell B2 and qx is in C2, your formula for lx in the next row (B3) will be =B2*(1 - C2). Drag this formula down to the last age.
From lx, you can calculate dx, the number of deaths between ages x and x+1, as lx times qx. This helps you visualize how the population decreases with age. Probability of survival px is simply 1 - qx, which you can include for clarity and further calculations.
Now, a practical tip: to avoid rounding errors and keep your table precise, use a large radix (like 100,000 or 1,000,000) and keep your qx values as decimals, not percentages. This precision is important because small errors can compound and affect your later calculations like annuity factors or expected present values.
Once your basic mortality table is built, validation is key. One simple check is to ensure that the lx column is monotonically decreasing — it should never increase as age progresses. If you see an increase, double-check your formulas or data input. Also, the last lx value should approach zero (or exactly zero if the maximum age is set to an ultimate age).
Another validation step is to calculate the probabilities of survival from birth to various ages and compare them with known benchmarks or published mortality tables. For example, you might check that the survival probability to age 65 roughly aligns with actuarial standards or known life expectancies. This helps ensure your qx values and calculations are consistent.
To deepen your understanding and add complexity, you can calculate commutation functions such as Dx and Nx, which are used extensively in actuarial present value calculations. Dx is lx multiplied by the discount factor (v^x), where (v = \frac{1}{1+i}) and (i) is the interest rate. Nx is the sum of Dx values from age x to the maximum age. Implementing these in Excel involves setting up your discount factor column and using the SUM function cleverly.
One trick I’ve found helpful when working with mortality tables in Excel is to name your ranges for qx, lx, and other columns. This makes your formulas cleaner and easier to audit, especially when you start writing more complex formulas or VBA code for automation.
Speaking of automation, if you’re comfortable with VBA or want to go the extra mile, you can create Excel add-ins that calculate expected present values (EPV) of life insurance contracts using your mortality tables. This involves looping through ages, extracting probabilities using functions like VLOOKUP or INDEX, and applying actuarial formulas programmatically. While this is more advanced, it’s a powerful way to handle large datasets and repeated calculations efficiently.
Remember, the Society of Actuaries provides various mortality tables and projection scales (such as RP2014, MP2019, and others) that you can incorporate into your Excel workbook for realistic modeling. You can also apply mortality improvement scales to project future mortality rates, which is increasingly important in long-term actuarial work.
A few statistics to keep in mind: according to the SOA, the life expectancy at birth in the U.S. has been increasing steadily, with improvements in mortality rates reflected in updated tables. Incorporating these projections helps your models stay relevant and accurate, especially for LTAM exam scenarios where future mortality changes matter.
In summary, building and validating mortality tables in Excel involves:
- Inputting age and qx data accurately
- Calculating survivors (lx) and deaths (dx) iteratively
- Validating the monotonic decrease in survivors and consistency with known benchmarks
- Extending your table with survival probabilities (px), discount factors, and commutation functions
- Optionally automating calculations with VBA or Excel add-ins for EPV and annuities
- Incorporating mortality improvements and projection scales for realistic modeling
Taking it step by step and continuously checking your work ensures your mortality tables are reliable and serve as a strong foundation for exam questions and real-world actuarial tasks. Plus, the Excel skills you develop along the way will pay off in countless ways.
If you want a practical starting exercise, try building a simple mortality table from scratch using SOA’s published qx values, calculate lx and dx, and then compute the expected present value of a whole life annuity for a given interest rate. This hands-on approach cements the theory and gives you confidence for the LTAM exam.