Polluted Runoff: Nonpoint Source (NPS) Pollution
Contact Us

Spreadsheet Tool for Estimating Pollutant Loads (STEPL) and Region 5 Model

Questions or Comments?
Related Information
Note:   In April 2022, EPA transitioned from the STEPL spreadsheet Model to the online Pollutant Load Estimation Tool (PLET)   All supporting documentation and training materials are here but we encourage all users to  explore PLET as a more efficient approach to your modeling needs.  
 
On this page: 

STEPL

Region 5 Model 

STEPL and Region 5: 

STEPL - Overview

STEPL provides a user-friendly Visual Basic (VB) interface to create a customized spreadsheet-based model in Microsoft (MS) Excel. It computes:
  • watershed surface runoff;
  • nutrient loads, including nitrogen, phosphorus, and 5-day biological oxygen demandAn indirect measure of the concentration of biologically degradable material present in organic wastes. It usually reflects the amount of oxygen consumed in five days by biological processes breaking down organic waste. (BOD5); and
  • sediment delivery based on various land uses and management practices.

STEPL can be used to evaluate loading and load reductions at a variety of scales. Although the terminology used throughout STEPL to define land use areas is “watershed,” these same input fields can also be treated as smaller areas, such as a project area or individual agricultural fields. For instance, if a user needs to estimate the load reductions from BMPs on individual farm fields, the “watersheds” can be further defined as “Field 1”, “Field 2”, “Field 3”, etc. There is no functional difference in the way STEPL calculates loads when watersheds or fields are used. The user defines the size and characteristics of each area being evaluated, based on the total acreage of each land-use entered into STEPL. This flexibility is not limited to agricultural fields, the “watersheds” could similarly be defined by the user as city blocks in an urbanized area.

For each watershed, the annual nutrient loading is calculated based on the runoff volume and the pollutant concentrations in the runoff water, as influenced by factors such as the land use distribution and management practices.

The annual sediment load (sheet and rill erosion only) is calculated based on the Universal Soil Loss Equation (USLE)An equation used to predict the average rate of erosion of an area on the basis of the rainfall, soil type, topography, and management measures of the area. and the sediment delivery ratio. The sediment and pollutant load reductions that result from the implementation of BMPs are computed using the known BMP efficiencies.

Model Download and Documentation

STEPL 4.4b

(Last updated: 10/27/2020)

  • STEPL 4.4b Spreadsheet Model for 10 Watersheds (Microsoft Excel 2016-ZIP) (zip)   This download includes STEPL version 4.4b which is fully functional for modeling up to 10 watersheds at a time. It does not require users to have administrator rights for installing STEPL on their computers.  Changes from version 4.4 were made to address Excel functionality issues and inconsistencies between the full installation version and the 10 watersheds version. Note: This  update replaces a version posted 10/05/2020. The only substantive change fixed a cell reference error in the ag irrigation calculations. 

STEPL 4.4

(Last updated: 03/15/2018)

Note: This update includes new BMP types (cropland and pastureland) and this version of STEPL has been upgraded to be compatible with Windows 10 and Excel 2016. It is also compatible with Windows 7 and Excel 2013.

STEPL 4.3b

(Last updated: 01/16/2017)

  • STEPL 4.3b Installation Package (zip) Note: This .zip file download contains a 'setup.exe' file that installs STEPL version 4.3 for full functionality on a customizable number of watersheds; however, it requires Administrator Rights for model installation.

Note: This version of STEPL has been upgraded to be compatible with Windows 10 and Excel 2013.

Input Data Server for STEPL

The Input Data Server for STEPL uses a waterbody selection interface based on the 12-digit Hydrologic Unit Code (HUC 12) of the waterbody in question and allows the user to download data for one or multiple HUCs.

Key features of this data server include:
  • 2011 Landuse area distribution at the Subwatershed (HUC12) level, and
  • 2012 Agricultural animal count at the Subwatershed (HUC12) level.

STEPL Training Materials

Region 5 Model

The Region 5 Model is an Excel workbook that provides a gross estimate of sediment and nutrient load reductions from the implementation of agricultural and urban BMPs. It does not estimate pollutant load reductions for dissolved constituents.

Model Download

Model Documentation 

  • Updates to Region 5 Model (PDF) (Last updated 09/28/2017)

  • Region 5 Manual -  The algorithms for non-urban BMPs are based on the Pollutants Controlled: Calculation and Documentation for Section 319 Watersheds Training Manual (Michigan Department of Environmental Quality, June 1999) (PDF). The algorithms for urban BMPs are based on data and calculations developed by Illinois EPA.

Training Materials

STEPL and Region 5 Model Questions and Answers

Question 1:  Is the Region 5 Model only for EPA Region 5 states?

Question 2:  What are the differences between STEPL and Region 5 Model?

Question 3:  My ‘Total Load’ worksheet has ‘#VALUE!’ in result cells. What went wrong?

Question 4:  The septic system failure rate seems low, are there other alternative data?

Question 5:  How can I add my own BMPs to STEPL’s BMP list?

Question 6:  Where can I find the definitions of BMPs used in the STEPL and Region 5 Model?

Question 7:  Can you help me use the BMP Calculator?

Question 8:  What are other models that can be used for load reduction calculations?

Question 9:  If I don't know exactly where all my BMPs for a particular subwatershed are located (not knowing if they're in the same fields or even affecting the same tributary) am I better off calculating them in serial or in parallel configuration for that subwatershed?

Question 10:  Why is the STEPL-calculated sediment loading rate (t/ac/yr) from my watershed much lower than the erosion rate (> 1 t/ac/yr) reported for my study area? Can you explain?

Question 11:  Some of the data I'm gathering to input into the STEPL model is not consistent. For filter strips, I have some data coming to me as actual acres of filter strip, and some coming to me as acres affected by the filter strip. What is the best way to deal with these discrepancies in the model?

Question 12:  My general question is whether it is appropriate to use STEPL to compare the loading effects of changing a particular site from one land use to another, e.g., agricultural to commercial.  I also wonder how it applies the USLE to "soil loss" from a parking lot.

Question 13:  I used the executable to create the spreadsheet file, made some changes within the spreadsheet. Now, when I reopen the file, I receive an error message from EXCEL saying 'not enough system resources to display completely.' Do you know why?

Question 14:  Can STEPL be used to model bacteria?

Question 15:  How to enable macros in MS Excel spreadsheet?

Question 16:  How to set Excel default file location to C:\STEPL or D:\STEPL?

Question 17:  I am getting an error “Please refrain from using the mouse until the spreadsheet creation has finished” when the hang-ups occur even though the mouse hasn’t moved. I even tried unplugging my mouse all together and still get this error.

Question 18:  Can STEPL be used with Macintosh Operating System (Mac OS)?

Question 19:  I am trying to Run the STEPL 4.3 and keep getting the error "Component 'MSCOMCTL.OCX' or one of its dependencies not correctly registered: a file is missing or invalid" when I press the “Run” button.

Question 1: Is the Region 5 Model only for EPA Region 5 states?

No, the Region 5 Model can be used for other states. The model got its name because it was originally developed by Indiana Department of Environmental Management based on Michigan Department of Environmental Quality’s 319 watershed training manual. Both Indiana and Michigan are EPA Region 5 states.

Question 2: What are the differences between STEPL and the Region 5 Model?

Below are the major differences between the two models:

The Region 5 Model

  • Calculates load at the source level
  • Sources are independent (no relationship between worksheets)
  • Users can not specify and update BMPs used in the model

STEPL

  • Calculates load for different sources at source and watershed level
  •  Sources are related in watershed
  •  Users can specify and update BMP list
  •  Users can use BMP calculator to estimate combined BMP efficiencies for complex BMP arrangements
  •  On-line input data server for initial model setup (Caution: Do not substitute the on-line data for real local data!)

Question 3: My ‘Total Load’ worksheet has ‘#VALUE!’ in result cells. What went wrong?

First, make sure that you have selected the appropriate State, County, and Weather Station from the lists in the Input Sheet.

Another possibility is that you probably entered some non-numerical characters in the input tables on the ‘Input’ sheet. For example, when you copy and paste ‘Agricultural animals’ data from the tables generated by the ‘STEPL Input Data Server’, you may paste non-numerical characters to the STEPL input sheet if the animal number is indicated with ‘D’ for data withheld to avoid disclosing by US Department of Agriculture.

Question 4: The septic system failure rate seems low, are there other alternative data?

Yes. The failure rate data have very high uncertainty. Users are encouraged to obtain the septic failure rate from their local health departments.

Besides the data from the ‘STEPL Input Data Server’ (National Environmental Service Center formally National Small Flow Clearinghouse, 1992 and 1998), Electric Power Research Institute conducted a state-by-state survey on septic failure rate. Below is the summary:

State

Estimated system
failure rate (in %)

Failure definition

Alabama

20

Not given

Arizona

0.5

Surfacing, backup, surface or ground water contamination

California

1-4

Surfacing, backup, surface or ground water contamination

Florida

1-2

Surfacing, backup, surface or ground water contamination

Georgia

1.7

Public hazard

Hawaii

15-35

 Improper construction, overflow

Idaho

20

Backup, surface or ground water contamination

Kansas

10-15

Surfacing, nuisance conditions (for installations after 1980)

Louisiana

50

Not given

Maryland

1

Surfacing, backup, surface or ground water contamination

Massachusetts

25

Public health

Minnesota

50-70

Cesspool, surfacing, inadequate soil layer, leaking

Missouri

30-50

Backup, surface or ground water contamination

Nebraska

40

Nonconforming system, water quality

New Hampshire

<5

Surfacing, backup

New Mexico

20

Surfacing

New York

4

Backup, surface or ground water contamination

North Carolina

15-20

Not given

North Dakota

28

Backup, surfacing

Ohio

25-30

Backup, surfacing

Oklahoma

5-10

Backup, surfacing, discharge off property

Rhode Island

25

Not given

South Carolina

6-7

Not given

Texas

10-15

Surfacing, surface or ground water contamination

Utah

0.5

Surfacing, backup, exceeding discharge standards

Washington

33

Public health hazard

West Virginia

60

Backup, surface or ground water contamination

Wyoming

0.4

Backup, surfacing, ground water contamination

Question 5: How can I add my own BMPs to STEPL’s BMP list?

You can add, delete, or edit BMPs in the “BMPList” worksheet, which can be shown (unhidden) by clicking the STEPL > View/Edit BMP List menu. On the ‘BMPlist’ worksheet, follow the steps outlined on the top-right corner. See more information in section 4.6 of the STEPL user’s guide.

Question 6: Where can I find the definitions of BMPs used in the STEPL and Region 5 Model?

You can find BMP descriptions in a Microsoft Word document, BMPDefinition.doc, under your [Local Drive C or D]:\STEPL folder after you install STEPL on your PC.

Question 7: Can you help me use the BMP Calculator?

Yes. The STEPL user’s guide has a detailed section (Appendix A) that describes the procedures and examples of using the BMP Calculator.

Question 8: What are other models that can be used for load reduction calculations?

STEPL and the Region 5 Model are two of the simplest models for calculating pollutant load reductions. There are many other models that can be used to calculate the load reductions, and these models are described in a document in the STEPL website. You can find out more about these models in the document "Nutrient and Sediment Estimation Tools for Watershed Protection (PDF)".

Question 9: If I don't know exactly where all my BMPs for a particular subwatershed are located (not knowing if they're in the same fields or even affecting the same tributary) am I better off calculating them in serial or in parallel configuration for that subwatershed?

Most non-structural BMPs should be used in parallel configuration (with the BMP Calculator) if you do not know their locations and spatial relationship. Some downstream structural BMPs or on stream BMPs may be in serial connection with the upstream BMPs; for example, a sediment settling pond may be in serial to upstream BMPs such as no-till practices. If you do not know the BMP locations in a watershed, assuming parallel BMP configurations will be better than assuming serial configurations if most of the BMPs are non-structural (except filter strips) or not in-stream.

Question 10: Why is the STEPL-calculated sediment loading rate (t/ac/yr) from my watershed much lower than the erosion rate (> 1 t/ac/yr) reported for my study area? Can you explain?

The sediment output from a watershed is different from on-site erosion. For cropland in your calculation, erosion rate may be larger than 1 t/ac/yr; however, the sediment output from your watershed is much lower. The STEPL model calculates sediment output using "erosion x sediment delivery ratio". Your watershed area is pretty large, so it results a low sediment delivery ratio. To see the sediment delivery ratio in STEPL model, click on STEPL menu > unhide/hide other STEPL sheets, and navigate to the ‘Sediment’ worksheet, and then check cell "W9". Your can modify the sediment delivery ratio based on your local data.

Question 11: Some of the data I'm gathering to input into the STEPL model is not consistent.  For filter strips, I have some data coming to me as actual acres of filter strip, and some coming to me as acres affected by the filter strip.  What is the best way to deal with these discrepancies in the model?

The important data that STEPL needs is the acreage affected by a filter strip. For example, if you have 20 acres of cropland affected by a filter strip, you can choose "Cropland-filter strip" as the BMP in STEPL and apply it to the affected area for load reduction calculation. The actual area of a filter strip is less important than the area it controls because:

  • the area of the filter strip is usually significant smaller than the affected area, and
  • the filter strip is not a source of nonpoint source pollution (in STEPL, you may exclude it from the load calculation or treat it as a type of forest).

Question 12: My general question is whether it is appropriate to use STEPL to compare the loading effects of changing a particular site from one land use to another, e.g., agricultural to commercial.  I also wonder how it applies the USLE to "soil loss" from a parking lot.

For assessment and planning purposes, you can use STEPL to compare effects of land use changes on pollutant loads (N, P, and sediment). However, STEPL was created to only give rough estimates on load and load reduction, and it is not intend for making very precise predictions. For urban land uses, STEPL does not use USLE. STEPL uses annual runoff volume x pollutant concentration to estimate pollutant loads, including sediment load.

Question 13: I used the executable to create the spreadsheet file, made some changes within the spreadsheet.   Now, when I reopen the file, I receive an error message from EXCEL saying 'not enough system resources to display completely.'  Do you know why?

This is due to a peculiar Excel behavior. Change the Input worksheet zoom level to 100% percent and try. If this is not working, make the hidden sheet visible (using the Hide/Unhide menu under STEPL) and then change the zoom level.

Question 14: Can STEPL be used to model bacteria?

STEPL can not be used to model bacteria. There are other models available to model bacteria. These include models like HSPF, SWAT, and PLOAD, which is now part of BASINS.

Question 15: How to enable macros in MS Excel spreadsheet?

Excel 2003 version: Click on Tools menu > Macro > Security > Security Level > Medium.

Excel 2007 version: Click on Office icon > Excel Options > Trust Center > Trust Center Settings > Macro Settings.

Excel 2010 version: Click on File menu > Excel Options > Trust Center > Trust Center Settings > Macro Settings.

Question 16: How to set Excel default file location to C:\STEPL or D:\STEPL?

Excel 2003 version: Click on Tools menu > Options > General tab.

Excel 2007 version: Click on Office icon > Excel Options > Save.

Excel 2010 version: Click on File menu > Excel Options > Save.

Question 17: I am getting an error “Please refrain from using the mouse until the spreadsheet creation has finished” when the hang-ups occur even though the mouse hasn’t moved. I even tried unplugging my mouse all together and still get this error.

This error occurs only on machines with a combination of Windows 7 and Microsoft Office Excel 2007 installed. STEPL program is not compatible for this combination. You need to upgrade your Excel program (e.g., Excel 2010).

Question 18: Can STEPL be used with the Macintosh Operating System (Mac OS)?

No, STEPL is only compatible with the Windows Operating System.

Question 19: I am trying to run the STEPL 4.3 and keep getting the error "Component 'MSCOMCTL.OCX' or one of its dependencies not correctly registered: a file is missing or invalid" when I press the “Run” button.

After you install the STEPL 4.3, you can find the MSCOMCTL.OCX file under the STEPL installation folder (e.g. C:\STEPL\Support\MSCOMCTL.OCX). Ask your IT personal to copy the file under C:\Windows\SysWOW64\ and follow these steps to register it:

  1. Go to Start\All Programs\Accessories.
  2. Right-click Command Prompt and then click on Run As Administrator.
  3. Type the following and then press Enter: cd c:\windows\systemWOW64.
  4. Type the following and then press Enter: regsvr32 MSCOMCTL.OCX.
  5. You should get a message saying this succeeded.
Contact Us to ask a question, provide feedback, or report a problem.
Last updated on May 9, 2022