Excel Formulas for Calculating Actuarial Life Expectancy from Mortality Tables

Actuaries and financial planners often struggle with the tedious manual mapping of complex mortality tables to dynamic participant cohorts in Excel, which frequently leads to calculation errors. While traditional valuation models rely on rigid, static pension funding schedules, modern demands require real-time adaptability. Mastering dynamic lookup formulas grants analysts the immediate agility to automate survival probabilities, saving hours of auditing. Crucially, this method stipulates that your source data strictly aligns with standardized age intervals. For instance, integrating the 2017 Commissioners Standard Ordinary (CSO) table via INDEX/MATCH ensures seamless compliance. Below, we examine the step-by-step formulas to streamline your actuarial modeling.

Actuaries, financial planners, and risk managers frequently evaluate life expectancy to price insurance policies, value pension liabilities, and construct retirement portfolio withdrawal strategies. At the core of these calculations lies the mortality table (or life table)-a statistical database showing the probability of death at any given age. While dedicated actuarial software exists, Microsoft Excel remains the industry-standard sandbox for prototyping and performing these calculations.

This article provides a comprehensive, step-by-step guide to building an actuarial life expectancy calculator in Excel. We will cover the underlying demographic mathematics, how to structure your mortality data, and how to write both traditional and modern dynamic array Excel formulas to dynamically compute curtate and complete life expectancy for any given age.

Understanding the Anatomy of a Mortality Table

Before writing formulas, it is essential to understand the standard columns found in a demographic or actuarial life table (such as the U.S. Social Security Administration Period Life Table or the IASB's mortality scales). A typical table contains the following variables for an age $x$ (usually ranging from 0 to an ultimate age, or "omega" $\omega$, typically 100 or 120):

• Age ($x$): The current age of the cohort.
• Probability of dying ($q_x$): The probability that a person of exact age $x$ will die before reaching age $x+1$.
• Number of survivors ($l_x$): The number of people surviving to exact age $x$ from an initial arbitrary cohort (usually starting at $l_0 = 100,000$).
• Number of deaths ($d_x$): The number of deaths in the cohort between ages $x$ and $x+1$.
• Person-years lived ($L_x$): The total time lived by the cohort between ages $x$ and $x+1$.
• Total person-years lived beyond age $x$ ($T_x$): The cumulative person-years lived by the cohort from age $x$ until all members have died.
• Life Expectancy ($e_x$ or $\mathring{e}_x$): The average number of years remaining for a person who has survived to age $x$.

Setting Up the Data Structure in Excel

To implement this in Excel, start by organizing your mortality table in a dedicated worksheet named MortalityTable. Set up the columns as follows:

Note: At the maximum/ultimate age ($\omega$, e.g., age 120), the probability of dying ($q_{120}$) must be set to 1.00000, signaling that no individuals survive past this point. Consequently, $l_{121}$ will equal 0.

Evaluating Life Expectancy via Simple Lookup

If you have already calculated the complete life expectancy ($e_x$) column in your reference table, looking up the value for a specific client or policyholder is straightforward. Suppose the user inputs the target age in cell I2.

Using XLOOKUP (Modern Excel)

The XLOOKUP function is the most robust way to extract the life expectancy value without worrying about column order or sorting:

=XLOOKUP(I2, MortalityTable!A:A, MortalityTable!G:G, "Age Out of Range", 0)

Using VLOOKUP (Legacy Excel)

For backwards compatibility with Excel 2019 and older versions, use the standard VLOOKUP formula, ensuring you demand an exact match by setting the fourth argument to FALSE:

=VLOOKUP(I2, MortalityTable!A:G, 7, FALSE)

Dynamic On-the-Fly Life Expectancy Calculations

In many advanced valuation models, you may not want to pre-calculate the $T_x$ and $e_x$ columns across your entire database, especially when dealing with dynamic projection scales (where mortality rates change over time due to generational improvements). In this case, you must write a formula that calculates life expectancy directly from the raw $l_x$ (survivors) or $p_x$ (survival probability) vectors on the fly.

1. Calculating Curtate Life Expectancy ($e_x$)

Curtate life expectancy represents the average number of completed future years lived by a person aged $x$. Mathematically, it is defined as the sum of the probabilities of surviving $k$ years into the future:

e_x = ∑_k=1^ω-x _kp_x  =  ∑_k=1^ω-x (l_x+k / l_x)

We can implement this summation in Excel using a dynamic array formula. Let's assume you have ages in column A and the $l_x$ values in column C. To find the curtate life expectancy for an age entered in cell I2:

=LET(
    current_age, I2,
    age_col, MortalityTable!$A$2:$A$122,
    lx_col, MortalityTable!$C$2:$C$122,
    lx_current, XLOOKUP(current_age, age_col, lx_col),
    lx_future, FILTER(lx_col, age_col > current_age),
    SUM(lx_future) / lx_current
)

How this works:

• The LET function defines local variables to keep the formula highly readable and computationally efficient.
• lx_current locates the $l_x$ value for the input age using XLOOKUP.
• lx_future filters the survivor vector to include only the rows representing ages strictly greater than the input age.
• Finally, SUM(lx_future) / lx_current calculates the sum of all future survivors divided by the initial population at the target age. This executes the summation of $_kp_x$ in a single step.

2. Calculating Complete Life Expectancy ($\mathring{e}_x$)

Complete life expectancy accounts for the fraction of a year lived in the year of death. Under the standard actuarial assumption of a uniform distribution of deaths (UDD) throughout each year of age, complete life expectancy is approximately equal to curtate life expectancy plus half a year:

&mathring{e}_x ≈ e_x + 0.5

To evaluate this directly in Excel, simply modify our LET formula to add 0.5 to the final result:

=LET(
    current_age, I2,
    age_col, MortalityTable!$A$2:$A$122,
    lx_col, MortalityTable!$C$2:$C$122,
    lx_current, XLOOKUP(current_age, age_col, lx_col),
    lx_future, FILTER(lx_col, age_col > current_age),
    (SUM(lx_future) / lx_current) + 0.5
)

Handling Fractional Ages (Linear Interpolation)

In real-world applications-such as valuing a pension plan on an exact calculation date-the policyholder's age is rarely a whole integer. If a client is exactly 65.25 years old, a simple table lookup will return an error or an inaccurate integer value.

To evaluate the complete life expectancy for a fractional age $x + f$ (where $x$ is the integer age and $0 < f < 1$), we use linear interpolation between the life expectancies at age $x$ and age $x+1$:

&mathring{e}_{x+f} = (1 - f) \times &mathring{e}_x + f \times &mathring{e}_{x+1}

We can write a comprehensive Excel formula to handle this seamlessly, assuming the target fractional age is in cell I2:

=LET(
    target_age, I2,
    floor_age, INT(target_age),
    fraction, target_age - floor_age,
    ages, MortalityTable!$A$2:$A$122,
    e_complete, MortalityTable!$G$2:$G$122,
    ex_floor, XLOOKUP(floor_age, ages, e_complete),
    ex_ceil, XLOOKUP(floor_age + 1, ages, e_complete, ex_floor),
    ((1 - fraction) * ex_floor) + (fraction * ex_ceil)
)

This dynamic formula first separates the target age into its integer and fractional components using INT. It then looks up the life expectancies for the surrounding integer ages and performs a weighted average calculation. If the user inputs an integer age (e.g., 65.0), the fraction term becomes 0, and the formula cleanly returns the exact lookup value for age 65.

Conclusion

By shifting from rigid static tables to dynamic, vector-based formulas, you can significantly enhance the analytical power of your actuarial models in Excel. Implementing structures like LET, XLOOKUP, and FILTER not only makes your spreadsheets less prone to circular reference errors, but also allows you to scale up models to analyze complex cohorts, stochastic mortality projections, and custom cash flow structures with minimal computational overhead.