Since the SOA's August 17, 2026 update to its Prometric guide, written-answer candidates get the 2024 versions of Word and Excel at the testing center instead of the 2016 versions, and four additions do most of the useful work: XLOOKUP, LET, SORT, and SEQUENCE. You do not need any of them to pass. Every ALTAM, ASTAM, and FSA-track question is written to be solvable with the 2016 toolkit. But if you already use modern Excel at work or school, you no longer have to downgrade your habits on exam day, and a few of these functions remove real sources of time pressure and silent error. Details of the update itself are in the companion news post.
This post goes function by function with worked actuarial examples, then covers what to skip and how to fold any of it into your prep without disrupting what already works.
The ground rules before the functions
Three environment facts shape everything below.
First, on the Excel-graded portions of ALTAM and ASTAM, graders grade the workbook. Partial credit depends on a grader being able to follow your method, so a compact clever formula that hides your reasoning is worth less than a labeled sequence of steps that stops one step short. Use the new functions to be faster and clearer, not more cryptic.
Second, ASTAM expects you to compute in Excel. The guide says tables are not provided for ASTAM; candidates are expected to use Excel to calculate probabilities and quantiles from common distributions. Fluency with the statistical functions and with basic array handling is not optional there.
Third, the old restrictions stand: no Solver, no Analysis ToolPak, no Help. Goal Seek and pivot tables are available. F1 is disabled, so any function you plan to use, you need to know cold.
XLOOKUP: retire the column count
The classic VLOOKUP failure on a timed exam is silent: you write =VLOOKUP(B2, A2:F102, 4, FALSE), miscount the column index by one, and pull the wrong rate into a calculation that looks perfectly healthy for the next forty minutes.
XLOOKUP names the return column directly:
=XLOOKUP(B2, Ages, Qx)
where Ages and Qx are the two columns themselves (references or named ranges). There is no index to count, exact match is the default (VLOOKUP defaults to approximate match, another classic silent error), it can search leftward, and the optional fourth argument gives you explicit control when a value is missing:
=XLOOKUP(B2, Ages, Qx, "not found")
For ALTAM, where the mortality tables arrive as an Excel workbook, this is the single most direct upgrade: every table pull becomes a two-reference formula you can read back and verify at a glance.
LET: name your intermediate values
If you have ever written a profit-test or reserve formula where the same discount factor appears three times, LET is the fix. It lets you name intermediate calculations inside one formula, compute each once, and reference it by name:
=LET(i, 0.05, v, 1/(1+i), q, 0.012, p, 1-q, 100000*(v*q + v^2*p*q))
That is a two-year term-insurance EPV with every assumption visible and named. Compare the 2016 version, where 1/(1.05) is retyped in every term and a typo in one instance is nearly impossible to spot under time pressure.
Two exam-day benefits. Debugging: when a number looks wrong, you can read the LET line by line like a derivation instead of unwinding nested parentheses. Editing: when you realize the interest rate should have been 4.5%, you change one value, not four.
There is also a presentation benefit specific to written-answer grading: a LET formula reads like the worked solution you would write in the answer booklet, with assumptions declared before use. Graders can follow it.
SORT, FILTER, and TAKE: tail values in one formula
ASTAM's risk-measure questions are where the dynamic array functions earn their place. Suppose 1,000 simulated aggregate losses sit in A2:A1001 and you need the CTE at the 95% level, the average of the worst 50 outcomes:
=AVERAGE(TAKE(SORT(A2:A1001,,-1), 50))
SORT with -1 sorts descending, TAKE keeps the top 50, AVERAGE finishes it. In Excel 2016 the same calculation meant a helper column of LARGE(A:A, k) calls dragged down 50 rows, or sorting the data in place and hoping you did not disturb anything else.
The threshold-based alternative, averaging everything at or above the 95th percentile, is one FILTER:
=AVERAGE(FILTER(A2:A1001, A2:A1001 >= PERCENTILE.INC(A2:A1001, 0.95)))
Note that these two formulas implement two slightly different definitions (a fixed count of order statistics versus a percentile threshold), and that distinction is exactly the kind of thing a written answer should state explicitly. Being able to compute both in seconds leaves you time to say which one the question wants.
SORT and FILTER also handle the mundane versions of this: ranking development factors, extracting the policies above a retention, isolating one origin year from a listed triangle.
SEQUENCE and spill: projection grids without dragging
SEQUENCE generates a series in one formula. =SEQUENCE(10) spills the integers 1 through 10 down a column: projection years, payment times, policy durations. Combined with spill arithmetic, standard structures become one-liners:
- A 10-year discount factor column at 4%:
=1.04^-SEQUENCE(10) - A 10-year annuity-immediate PV factor, from first principles:
=SUM(1.04^-SEQUENCE(10)) - A 10-year by 3-scenario discount grid, with the three rates in
B1:D1:=(1+B1:D1)^-SEQUENCE(10)spills the full 10-by-3 block from a single formula.
That last pattern is the quiet workhorse. A projection that used to be one formula copied into thirty cells, each a chance to break a relative reference, becomes one formula you can audit in one read.
One mechanical caution: a spilling formula needs empty cells to spill into, and returns #SPILL! if anything blocks it. Lay out your workbook with room around spill ranges, and label them, because the grader sees the grid without seeing which cell generated it.
IFS and SWITCH: flatten the nesting
Layered logic, reinsurance layers, benefit bands, state-dependent rates, reads much better without nested IFs. An excess-of-loss recovery with a 250k retention and a 250k limit:
=IFS(A2 <= 250000, 0, A2 <= 500000, A2 - 250000, TRUE, 250000)
SWITCH does the same for discrete codes, such as multi-state model states:
=SWITCH(B2, "H", qHealthy, "S", qSick, "D", 1)
These arrived in Excel 2019, so they are also new relative to what Prometric had before. They save less time than the four functions above, but they cut the parenthesis-matching errors that nested IFs invite.
What to skip: LAMBDA, MAP, SCAN
LAMBDA lets you define your own functions; MAP and SCAN apply a function across arrays. They are genuinely powerful (SCAN can roll a recursive reserve or fund value forward in one formula), and genuinely not worth learning for an exam. The syntax is unforgiving, the payoff over a plain cell-by-cell recursion is small at exam scale, and no question will assume them. If you already use them daily at work, use them. If not, this is the wrong month to start.
What still is not there
Solver's absence is the one that shapes technique: solving for the premium that makes an equation of value hold still means Goal Seek (available, one variable, works fine) or rearranging the algebra. The Analysis ToolPak's absence costs nothing; the exams have never needed it.
And to repeat the practice-environment caution: the newest Microsoft 365 features (GROUPBY, PIVOTBY, REGEX functions, Python in Excel, Copilot) are not part of the perpetual Excel 2024 release and will not be on the exam machine. Neither will the internet.
How to fold this into your prep
Do not rebuild your Excel habits in the final two weeks before a sitting; fluency under pressure beats modernity. But if your exam is a few months out, the efficient path is narrow and concrete:
- Pick the two functions that map to your actual weak points. For most candidates that is XLOOKUP (table pulls) and either LET (long formulas) or SEQUENCE (projection grids).
- Use them exclusively in two or three full practice sessions, working real written-answer problems end to end, so the syntax is automatic rather than recalled.
- Keep everything else as it is. INDEX/MATCH, SUMPRODUCT, and Goal Seek did not stop working, and there is no prize for novelty.
For practice material in the graded format, FreeFellow's ASTAM question bank and ALTAM question bank are free, including written-answer drills with model solutions you can work in Excel exactly as you would at Prometric.