Discover the transformative power of Excel for Actuaries – today’s tool is vastly more capable than just a year ago! Back then, you might have found yourself grappling with every step. Surprisingly, many in our community haven’t yet embraced the newest Excel techniques and formulas. These advancements can elevate your Excel game to new heights, enabling you to automate tasks, enhance efficiency, and slash the time you spend on repetitive work.
I was recently inspired by a post from Andrew Chan, who brilliantly utilizes the LAMBDA function among others, to extract the latest mortality rates directly from the Society of Actuaries (SOA) website. Imagine this: simply refresh your Excel sheet, and voilà, the updated mortality rates appear right before your eyes. Who knew such magic was possible with Excel?
In this blog, we’ll explore how you can leverage the LAMBDA function to revolutionize data handling in both general and life insurance sectors. Plus, I’ll share some practical LAMBDA functions that you can start using right away to transform your data workflow. Stay tuned as we delve into the exciting world of advanced Excel techniques!
We have covered a few things in Excelbut this article is a lot different!
Automation in Life Insurance
Advanced Actuarial Excel Formulas
That’s a fantastic use of Excel’s advanced functions to calculate the remaining lives! Here’s a step-by-step explanation of how the formulas work and how to use them together:
1. 𝐑𝐞𝐭𝐫𝐢𝐞𝐯𝐞 𝐌𝐨𝐫𝐭𝐚𝐥𝐢𝐭𝐲 𝐓𝐚𝐛𝐥𝐞 𝐃𝐚𝐭𝐚
– Formula: `=𝘍𝘐𝘓𝘛𝘌𝘙𝘟𝘔𝘓(𝘞𝘌𝘉𝘚𝘌𝘙𝘝𝘐𝘊𝘌(“𝘩𝘵𝘵𝘱𝘴://𝘮𝘰𝘳𝘵.𝘴𝘰𝘢.𝘰𝘳𝘨/𝘥𝘢𝘵𝘢/𝘵1.𝘹𝘮𝘭”), “//𝘠”)`
– This formula uses the `𝐖𝐄𝐁𝐒𝐄𝐑𝐕𝐈𝐂𝐄` function to fetch the XML data from the SOA website and the `𝐅𝐈𝐋𝐓𝐄𝐑𝐗𝐌𝐋` function to extract the mortality rates (`//Y`).
2. 𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞 𝐍𝐮𝐦𝐛𝐞𝐫 𝐨𝐟 𝐋𝐢𝐯𝐞𝐬 𝐔𝐬𝐢𝐧𝐠 𝐒𝐂𝐀𝐍
– Formula: `=𝘚𝘊𝘈𝘕(1000, 𝘍𝘐𝘓𝘛𝘌𝘙𝘟𝘔𝘓(𝘞𝘌𝘉𝘚𝘌𝘙𝘝𝘐𝘊𝘌(“𝘩𝘵𝘵𝘱𝘴://𝘮𝘰𝘳𝘵.𝘴𝘰𝘢.𝘰𝘳𝘨/𝘥𝘢𝘵𝘢/𝘵1.𝘹𝘮𝘭”), “//𝘠”), 𝘓𝘈𝘔𝘉𝘋𝘈(𝘓𝘪𝘷𝘦𝘴, 𝘮𝘰𝘳𝘵_𝘳𝘢𝘵𝘦, 𝘓𝘪𝘷𝘦𝘴 * (1 – 𝘮𝘰𝘳𝘵_𝘳𝘢𝘵𝘦)))`
– This formula uses the `𝐒𝐂𝐀𝐍` function to iterate over the mortality rates and apply the `𝐋𝐀𝐌𝐁𝐃𝐀` function to calculate the remaining number of lives. Starting with an initial number of 1,000 lives, it multiplies the current number of lives by `(1 – mort_rate)` for each mortality rate in the table.
Here’s a breakdown of the `SCAN` function:
– 𝐈𝐧𝐢𝐭𝐢𝐚𝐥 𝐕𝐚𝐥𝐮𝐞: `1000` (the starting number of lives).
– 𝐀𝐫𝐫𝐚𝐲: `FILTERXML(WEBSERVICE(“𝘩𝘵𝘵𝘱𝘴://𝘮𝘰𝘳𝘵.𝘴𝘰𝘢.𝘰𝘳𝘨/𝘥𝘢𝘵𝘢/𝘵1.𝘹𝘮𝘭”), “//Y”)` (the array of mortality rates).
– 𝐋𝐀𝐌𝐁𝐃𝐀 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧: `LAMBDA(Lives, mort_rate, Lives * (1 – mort_rate))` (a custom function that calculates the remaining lives by applying the mortality rate).
This approach dynamically calculates the number of lives remaining each year based on the mortality rates from the SOA table. It’s a powerful way to leverage Excel’s capabilities for actuarial analysis! If you have any questions or need further assistance, feel free to ask.
Use of LAMBDA and LET
We often use mortality tables to perform various calculations, including life expectancy and insurance premiums. Today, let’s explore how we can elevate our analysis using Excel’s powerful 𝐋𝐀𝐌𝐁𝐃𝐀 function to calculate deaths at each age directly within our spreadsheets.
First, we can efficiently retrieve mortality table data from reputable sources like the Society of Actuaries (SOA) website using Excel’s FILTERXML function combined with WEBSERVICE. Subsequently, the SCAN function allows us to determine the number of individuals remaining alive at each age in an array.
Formula: `=𝘚𝘊𝘈𝘕(1000, 𝘍𝘐𝘓𝘛𝘌𝘙𝘟𝘔𝘓(𝘞𝘌𝘉𝘚𝘌𝘙𝘝𝘐𝘊𝘌(“𝘩𝘵𝘵𝘱𝘴://𝘮𝘰𝘳𝘵.𝘴𝘰𝘢.𝘰𝘳𝘨/𝘥𝘢𝘵𝘢/𝘵1.𝘹𝘮𝘭”), “//𝘠”), 𝘓𝘈𝘔𝘉𝘋𝘈(𝘓𝘪𝘷𝘦𝘴, 𝘮𝘰𝘳𝘵_𝘳𝘢𝘵𝘦, 𝘓𝘪𝘷𝘦𝘴 * (1 – 𝘮𝘰𝘳𝘵_𝘳𝘢𝘵𝘦)))`
We can then pass the array into the following 𝐋𝐀𝐌𝐁𝐃𝐀 function.
Now, let’s delve into the custom LAMBDA function:
𝘕𝘶𝘮𝘣𝘦𝘳𝘖𝘧𝘋𝘦𝘢𝘵𝘩 = 𝘓𝘈𝘔𝘉𝘋𝘈(𝘈𝘳𝘳𝘢𝘺,𝘙𝘦𝘥𝘶𝘤𝘦(0,𝘈𝘳𝘳𝘢𝘺,𝘓𝘈𝘔𝘉𝘋𝘈(𝘈𝘤𝘤,𝘝𝘢𝘭,
𝘓𝘌𝘛(𝘗𝘳𝘪𝘰𝘳𝘓𝘪𝘧𝘦, 𝘐𝘍( 𝘝𝘢𝘭 = 1000, 1000, 𝘛𝘢𝘬𝘦(𝘈𝘤𝘤, -1, -1) ),
𝘊𝘶𝘳𝘳𝘋𝘦𝘢𝘵𝘩, 𝘐𝘍( 𝘝𝘢𝘭 = 1000, 0, 𝘗𝘳𝘪𝘰𝘳𝘓𝘪𝘧𝘦 – 𝘝𝘢𝘭 ),
𝘊𝘶𝘳𝘳𝘦𝘯𝘵𝘙𝘰𝘸, 𝘏𝘚𝘛𝘈𝘊𝘒( 𝘊𝘶𝘳𝘳𝘋𝘦𝘢𝘵𝘩, 𝘝𝘢𝘭 ),
𝘓𝘈𝘔𝘉𝘋𝘈𝘙𝘦𝘴𝘶𝘭𝘵, 𝘐𝘍(𝘝𝘢𝘭 = 1000, 𝘊𝘶𝘳𝘳𝘦𝘯𝘵𝘙𝘰𝘸, 𝘝𝘚𝘛𝘈𝘊𝘒( 𝘈𝘤𝘤, 𝘊𝘶𝘳𝘳𝘦𝘯𝘵𝘙𝘰𝘸 ) ),
𝘓𝘈𝘔𝘉𝘋𝘈𝘙𝘦𝘴𝘶𝘭𝘵))));
This compact yet elegant formula encapsulates the following logic:
1. Initialization: 𝐑𝐄𝐃𝐔𝐂𝐄 sets an accumulator (Acc) to zero to store our results.
2. Iteration: For each value (𝐕𝐚𝐥) representing the number of lives remaining at a given age in the Array:
2.1 𝐏𝐫𝐢𝐨𝐫𝐋𝐢𝐟𝐞: If it’s the first row (starting population), PriorLife is 1000. Otherwise, it’s the last value from the accumulator (𝐀𝐜𝐜).
2.2 𝐂𝐮𝐫𝐫𝐃𝐞𝐚𝐭𝐡: If it’s the first row, there are no deaths (0). Otherwise, the current death count is PriorLife – Val.
2.3 𝐂𝐮𝐫𝐫𝐞𝐧𝐭𝐑𝐨𝐰: Combines CurrDeath and Val into a row of results.
2.4 𝐋𝐀𝐌𝐁𝐃𝐀𝐑𝐞𝐬𝐮𝐥𝐭: This conditional logic either initializes the accumulator (𝐀𝐜𝐜) with the first row’s data or vertically stacks subsequent rows of results.
3. Output: The final accumulator (𝐀𝐜𝐜) is a structured array containing the calculated number of deaths and the remaining lives at each age.
This 𝐋𝐀𝐌𝐁𝐃𝐀 function is a powerful tool that eliminates the need for manual calculations or auxiliary columns, significantly streamlining our workflows. Moreover, the 𝐋𝐄𝐓 function enhances readability by providing clear labels for intermediate calculations. The formula’s dynamic nature enables it to adapt seamlessly to various mortality tables, offering flexibility and efficiency in our actuarial analyses.
Power Query for Actuaries in Excel
Excel LABS – Advanced Formulae Environment for Actuaries
Thank you to Bryon Robidoux for creating the first actuarial LAMBDA library. This library includes a 𝐋𝐀𝐌𝐁𝐃𝐀 function called “𝐆𝐞𝐭𝐌𝐨𝐫𝐭𝐚𝐥𝐢𝐭𝐲𝐓𝐚𝐛𝐥𝐞,” which allows the direct import of mortality tables from the SOA website. 👏
You can download the library from the following GitHub gist and integrate it into Excel using the 𝐀𝐝𝐯𝐚𝐧𝐜𝐞𝐝 𝐅𝐨𝐫𝐦𝐮𝐥𝐚 𝐄𝐧𝐯𝐢𝐫𝐨𝐧𝐦𝐞𝐧𝐭: https://lnkd.in/gmzMqY_y. 🖥️
Once the library is imported, you can use the “GetMortalityTable” function in any Excel cell. For example, you can use GetMortalityTable(44, 20, 80). The first parameter is the Table ID found on the SOA website, and the second and third parameters are the minimum and maximum ages for the mortality rate to be downloaded. ⚠️
The LAMBDA function and Advanced Formula Environment enable actuaries to break down complex models into manageable components, promoting the development of Excel’s reusable actuarial functions.
For Life Insurance, this is a collation of posts by Andrew Chan, who has been giving brilliant ideas over on his LinkedIn for Actuarial Excel exercises and tips and tricks.
Automation in General Insurance for Actuaries using Excel
While there are countless tools available for enhancing data analytics, transformation, extraction, and loading, we’re going to zero in on Excel in this post. Let me share some standout features and example formulas that really highlight what Excel can do.
For General Insurance, Mayank Goyal has created these formulas to be used in creating a better Reserving Excel for Actuaries. 🙂
Advanced Actuarial Excel Formulas: LAMBDA and Excel LABS
Exploring advanced Excel formulas was a bit intimidating at first, but I soon realized it’s a journey worth taking, especially if your organization is just beginning to tap into the power of data-driven decisions. Excel is not only user-friendly and quick but also incredibly versatile, allowing you to perform repeated tasks with ease.
Taking a leaf out of Bryon and Andrew’s books, I experimented by creating an age-to-age factor triangle using straightforward triangle data. This new method means you won’t have to manually extend the triangle every time—it’s a smarter and more streamlined approach.
Code
Result
What other areas do you think that could be useful? Do you have any projects that you want to showcase or know about? Let us know in the comments below.
