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 don't need any of them to pass, since every ALTAM, ASTAM and FSA-track question is written to be solvable with the 2016 toolkit. But if you use modern Excel at work or school, you no longer have to downgrade on exam day, and a few of these functions remove real sources of silent error. The details of the update are in the companion news post.
The exam-day rules
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, never more cryptic.
ASTAM expects you to compute in Excel. The guide says tables aren't provided for ASTAM, and candidates are expected to use Excel to calculate probabilities and quantiles from common distributions.
The old restrictions stand: no Solver, no Analysis ToolPak, no Help. Goal Seek and pivot tables are available. F1 is disabled, so you need to know any function you plan to use cold. Without Solver, finding the premium that makes an equation of value hold still means Goal Seek (one variable, works fine) or rearranging the algebra. The exams have never needed the Analysis ToolPak.
If you practice on Microsoft 365 at home, the newest features (GROUPBY, PIVOTBY, the REGEX functions, Python in Excel, Copilot) aren't part of the perpetual Excel 2024 release, so they won't be on the exam machine, which also has no internet access.
The functions worth learning
XLOOKUP
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 fine 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's no index to count, exact match is the default (VLOOKUP defaults to approximate match, another common silent error), it can search leftward, and the optional fourth argument controls what happens when a value is missing:
=XLOOKUP(B2, Ages, Qx, "not found")
On ALTAM, where the mortality tables arrive as an Excel workbook, this is the most direct upgrade: every table pull becomes a two-reference formula you can check at a glance.
LET
If you've written a profit-test or reserve formula where the same discount factor appears three times, LET fixes it. You name intermediate calculations inside one formula, compute each once and refer to 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's a two-year term insurance EPV with every assumption named. In the 2016 version, 1/(1.05) is retyped in every term, and a typo in one of them is nearly impossible to spot under time pressure. When a number looks wrong, you read the LET line by line like a derivation, and when the interest rate should have been 4.5%, you change one value, not four. It also reads like the worked solution you'd write in the answer booklet, which helps the grader.
SORT, FILTER and TAKE
ASTAM's risk measure questions are where the dynamic array functions pay off. Say 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 and AVERAGE finishes it. In Excel 2016 this meant a helper column of LARGE(A:A, k) calls dragged down 50 rows, or sorting the data in place and hoping you didn't disturb anything else.
The threshold version, averaging everything at or above the 95th percentile, is one FILTER:
=AVERAGE(FILTER(A2:A1001, A2:A1001 >= PERCENTILE.INC(A2:A1001, 0.95)))
The two formulas implement slightly different definitions (a fixed count of order statistics versus a percentile threshold), and your written answer should say which one the question wants. SORT and FILTER also handle everyday jobs like ranking development factors, pulling the policies above a retention or isolating one origin year from a listed triangle.
SEQUENCE and spill ranges
=SEQUENCE(10) spills the integers 1 through 10 down a column: projection years, payment times, policy durations. With spill arithmetic, standard structures become one formula each:
- 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
The grid saves the most time: a projection that used to be one formula copied into thirty cells, each a chance to break a relative reference, becomes one formula. A spilling formula needs empty cells to spill into, and Excel returns #SPILL! if anything blocks it. Leave room around spill ranges and label them, because the grader sees the grid without seeing which cell generated it.
IFS and SWITCH
Layered logic like reinsurance layers, benefit bands and 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 the states in a multi-state model:
=SWITCH(B2, "H", qHealthy, "S", qSick, "D", 1)
Both arrived in Excel 2019, so they're also new to Prometric. They save less time than the four above, but they cut the parenthesis-matching errors nested IFs invite.
What to skip: LAMBDA, MAP and SCAN
LAMBDA lets you define your own functions, and MAP and SCAN apply a function across arrays. SCAN can roll a recursive reserve or fund value forward in one formula, but 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, go ahead. If not, the months before a sitting are the wrong time to start.
How to fold this into your prep
Don't rebuild your Excel habits in the last two weeks before a sitting. If your exam is a few months out, here's what I'd do:
- Pick the two functions that match your weak points. For most candidates that's XLOOKUP (table pulls) and either LET (long formulas) or SEQUENCE (projection grids).
- Use them exclusively in two or three full practice sessions on real written-answer problems, so the syntax is automatic.
- Leave everything else alone. INDEX/MATCH, SUMPRODUCT and Goal Seek still work.
For practice 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 the way you would at Prometric.