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