Views:

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*. 

FEMA Flood Insurance Website

 

 

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:

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:

North52 Rules Engine for Dynamics 365 - FEMA Base Rates Table

We use this information to build the corresponding xCache Table in the North52 Decision Suite:

North52 Decision Suite - xCache Rating Table example

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:

North52 Decision Suite - xCache Rating Table example

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:

North52 Decision Suite Dynamics 365 Rules Engine FEMA Calculation Example

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

FEMA Segments - North52 Decision Suite Business Rules Engine for Dynamics 365

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 ]]} 

FEMA Base Rates - North52 Business Rules Engine for Dynamics 365

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.  

FEMA Base Rates Global Action - North52 Business Rules Engine for Microsoft Dynamics 365

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:

FEMA Base Rate Table - North52 Business Rules Engine for Microsoft Dynamics 365

This can be cross-checked with the expected value as shown in Example 1 reference sheet:

FEMA Example 1 - North52 Business Rules Engine for Microsoft Dynamics 365

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.

FEMA Complex Rating Table Lookups - North52 Business Rules Engine for Microsoft Dynamics 365

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.

FEMA Complex Lookups - North52 Business Rules Engine for Microsoft Dynamics 365

When determining the rating value for Non-Leveed Distance to River Inland Flood Building and Contents the following calculation is used:

Interpolation Function - North52 Business Rules Engine for Microsoft Dynamics 365

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:

FEMA Example 1 Sheet - North52 Business Rules Engine for Microsoft Dynamics 365

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.