Overview
The National Flood Insurance Program (NFIP), managed by FEMA, provides insurance to help reduce the socio-economic impact of floods in the United States. Homes and businesses in high-risk flood areas with mortgages from government-backed lenders are required to have flood insurance. The cost of this insurance is determined by the FEMA Risk Rating 2.0 methodology, introduced on 1 October 2021, using over 35 property attributes, more than 50 rating tables and over 1,500 calculations.
This article shows how North52's advanced business rules engine for Microsoft Dynamics can be used to model the complex rules for the NFIP premiums*.
Interview video
North52's Bruce Buxton and Patrick McInerney discuss how North52 helps support complex insurance premium calculations:
Summary video
An overview of how calculating complex flood insurance can be achieved using North52's rules engine:
Rating Engine Rules for Calculating National Flood Insurance Program Premiums
There are a significant number of rules used in calculating premiums. These are defined in several technical documents on the fema.gov website:
- Risk Rating 2.0 Methodology and Data Sources
- Risk Rating 2.0 Methodology and Data Sources - Premium Calculation Worksheet Examples
- Risk Rating 2.0 Methodology and Data Sources - Appendix D Rating Factors
- Levees in Risk Rating 2.0
These rules have been analyzed and translated into Decision Tables and xCache Data Tables to allow for automated calculation of the appropriate premium.
North52 Decision Suite Solution
The North52 Decision Suite solution works like this:
- xCache Tables are set up for each of the 50+ rating tables specified in the FEMA documentation
- A complex multi-sheet Decision Table Formula is set up on the Flood Insurance Application table which calculates the premium:
- Depending on many conditional factors, various rates are looked up from the xCache Tables
- Many of the rates need to be calculated as an interpolation if values fall between rows in the rate tables
- The rates are used within multiple calculations along with application attributes to determine the final premium
Rate Tables - Set up xCache Tables
There are over 50 rating tables that are used in calculating flood insurance premiums - some of these are over 100,000 rows of rating data. These are added to the North52 solutions using xCache Tables. The xCache Tables can be easily referenced in Decision Tables for determining values that should be used for calculating specific parts of the premium calculation, and because they are separate from the rule definitions, they can be maintained separately.
The first category of rating tables defined in the Rating Factors Excel file is the Base Rates for Non-Leveed areas - a sample of this table is shown below:
We use this information to build the corresponding xCache Table in the North52 Decision Suite:
The Base Rates for Leveed properties are defined in a separate sheet in the same xCache record, allowing the logical grouping of related rate tables:
Premium Calculation Formula
Decision Table Sheets
The 1,500+ calculations for determining the premium are broken out across a multi-sheet Decision Table Formula to reduce the complexity of designing and maintaining such an intricate calculation. FEMA provides examples of these calculations in the Risk Rating 2.0 Methodology and Data Sources - Premium Calculation Worksheet Examples as shown below:
Determine Segment
The first sheet in the Decision Table Formula sets inline variables for various Segment classifications that are used to look up rates from the xCache Tables
Base Rates
The base rates are determined by looking up the xCache Tables for the relevant values. Below cell C4 is highlighted - this will return a value for a Non-Leveed, Non-Barrier Island property using the Global Action Fema_BaseRates with a parameter of InlandFloodBuilding - this is shown in cell C4 as {Fema_BaseRates[[ InlandFloodBuilding ]]}
The Global Action Fema_BaseRates displayed below shows how the xCache data table is filtered to return the relevant value. The parameter InlandFloodBuilding replaces the placeholder {0} and determines the Sheet in the xCache table that will be queried. There are 3 filter parameters that are dynamically injected at execution; Region, Segment (determined by the logic in the first sheet shown above), and the SingleFamilyHome indicator.
The xCache Table that will be looked up at runtime is the Fema_BaseRates:
If the Region = SC
and Segment = 1
and SingleFamilyHome = Yes
then the value returned to the Decision Table would be 2.255
The matching rate can be seen below:
This can be cross-checked with the expected value as shown in Example 1 reference sheet:
Complex Rating Table Lookups
Many of the rating table values do not correspond exactly to the attributes of a property. For example, when determining the rating based on a property's Distance to River value, there are many potential values that are not defined in the rating table. Let's take the inputs for Example 1 where the Distance to River value is 111.2 meters.
When looking up this value in the rating table (see below) that:
111.2 falls between 100 and 125 on rows 14 and 15
The guidance from FEMA states that in this situation factors are linearly interpolated between breakpoints. North52 provides an Interpolation() function for this situation.
When determining the rating value for Non-Leveed Distance to River Inland Flood Building and Contents the following calculation is used:
The Calculation rounds the result of the interpolation calculation to 3 decimal places.
The Interpolation function has the following signature:
Interpolation(x, InputX0, InputX1, InputY0, InputY1)
When the Formula executes the following values are used (the Input values are found in the xCache table shown above):
x = 111.2 (Distance to River property parameter value)
InputX0 = 125 (Distance to River xCache table upper value)
InputX1 = 100 (Distance to River xCache table lower value)
InputY0 = 1.05 (Inland Flood xCache table upper value)
InputY1 = 1.082 (Inland Flood xCache table lower value)
This returns a value of 1.068 that can be cross-checked with the expected value as shown in Example 1 reference sheet:
Further Detail
We have only touched on a small part of the overall scenario for calculating the NFIP premiums in this article. If you would like to understand more about this complex calculation please email info@north52.com and we will be very happy to arrange a demo.
* Note: This article is not endorsed by FEMA and all calculations and assumptions have been made using public information available on the fema.gov website.