Guidance and examples for using the MPCA Estimator

This User Guide is intended to assist Municipal Separate Storm Sewer System (MS4) Permittees in the completion of the Commissioner-approved TMDL Annual Reporting Form (TMDL Form).

The MPCA Stormwater Program will hold a workshop on the MPCA Estimator on June 17th, 2015 from 8:30a to noon at Blaine City Hall.

To participate in this workshop you will need to:

• Download the Estimator to a laptop that you will be bringing to the workshop. We strongly recommend you review either the quick guide or full guidance for using the Estimator prior to the workshop.

• This will be a hands-on workshop where you work directly with the Estimator. If you intend on using your BMP information at the workshop, inventory your structural BMPs (swales/strips, constructed basins, infiltrators, and filters) and the discharge areas they treat and bring that information to the workshop. If you do not bring your BMP information, example information will be provided.

Please RSVP to Mary Hammes by June 1st if you plan on attending.

Download MPCA Estimator here: File:MPCA Estimator.xlsx

A quick guide for the estimator is available Quick Guide: MPCA Estimator tab.

The MPCA estimator worksheet presents an optional calculator approach to computing the pollutant load reduction for four pollutants: Total phosphorus, Total Suspended Solids (TSS), E. coli, and fecal coliform (note: the estimator may not be used for any other pollutants). Results from the estimator can be used in the Cumulative reductions tab. The estimator applies only to specific structural BMPs and is a simplistic tool that provides rough estimates of loading and load reductions. It should not be used for modeling a stormwater system or selecting BMPs. The following color coding applies to this worksheet.

• Grey cells are column and row headings
• White cells are User input (some cells contain default values but can be edited)
• Yellow cells contain formulas that calculate loads (can only be edited in "Input values for MPCA estimator" tab)
• Red cells contain formulas that contain results for reductions (cannot be edited)

The estimator utilizes the Simple Method to estimate land use based pollutant loads for total phosphorus, total suspended solids (TSS), and total bacteria (Schueler, 1987). BMPs included in the estimator are biofiltration (bioretention with an underdrain), bioinfiltration (bioretention with no underdrain), filter strip, landscaped roof (green roof), permeable pavement with an underdrain, sand filter, swale, wet basin, and constructed wetland. Users may include other BMPs if they have reliable data on pollutant removal efficiency.

The estimator will compute pollutant reduction using BMP performance data as published in the 2012 International BMP Database. The database compiles Event Mean Concentration (EMC) performance data and reports in terms of 25th Percentile, Median, and 75th Percentile. The estimator computes the load reduction according to the formula

Load Reduction for a BMP = Load in – Load out = (EMC in * Volume in) – (EMC out * Volume out).

This is then converted to a percentage reduction

% Reduction = Load Reduction ÷ Load in.

The cumulative reduction is the sum of load reduced for all BMPs. This computed reduction can be input into the Cumulative reductions tab of the TMDL form.

The Estimator can only be used for one TMDL at a time. If a Permittee has multiple TMDLs and chooses to use the Estimator, separate calculations must be made for each TMDL.

Caution: The results from the MPCA Estimator are intended to show a generalized value that represents approximate progress towards meeting TMDL WLAs. The MPCA estimator should not be used for other modeling or compliance purposes. Instead, Permittees should use field investigations combined with more detailed models.

Detailed guidance for the MPCA Estimator

The MPCA Estimator is divided into five sections.

• Calculation of loads for total phosphorus, TSS, E. coli bacteria, and fecal coliform bacteria.
• Calculation of reductions in loading for total phosphorus
• Calculation of reductions in loading for TSS
• Calculation of reductions in loading for E. coli bacteria
• Calculation of reductions in loading for fecal coliform bacteria

Download MPCA Estimator here: File:MPCA Estimator.xlsx

Calculation of total pollutant loads

File:Estimator calculating loads.PNG

Screen shot of the section where pollutant loads are calculated for the MPCA Estimator. Note that defaults values exist in many of the white cells, but the User can change these cells. To avoid a calculation error, the default area for each land use is 0.001 acres, which creates a small amount of pollutant load. Values in yellow are calculated and cannot be changed by the User.

Column A - Land use

• Description: This column contains various classifications for land use. Only classifications having adequate information for pollutant concentrations in the literature were included. The User may include additional land uses if adequate information exists for pollutant concentrations in runoff from that land use. The User may also include just a single land use to represent the entire area within the TMDL Project watershed, although this may require estimates of pollutant concentrations and runoff coefficients for areas with multiple land uses.
• Input: Land use category. Options include commercial, industrial, institutional, multi-use, municipal, open space, residential-high density, residential low-density, residential-medium density, and transportation. The User may input up to two other land use classifications.
• Source of Information: MPCA input or User input. For descriptions of different land uses, see [1], [2], [3].

Columns B through E, Rows 6 through 17 - Event mean concentrations

• Description: The influent Event Mean Concentrations (EMCs) of the pollutants of concern for each classification of land use listed in Column A.
• Input: Default value with an option for the User to enter a different value
• Source of Information: The 2011 National Stormwater Quality Database is the primary source of this information. Cells in the worksheet are populated with mean or median values from the Upper Midwest (Zone 1), but the User may enter a different value in the Input values for MPCA estimator tab if they have reliable data.

Column F, Rows 6 through 17 - Area of specified land use

• Description: Area of specific land use within the TMDL study area, in acres.
• Input: Drainage area, in acres. A value must be entered into this cell for the pollutant reduction to be calculated. Cells are therefore populated with a default value of 0.001 acres to avoid returning an error in the calculations.
• Source of Information: User. TMDL reports provide maps and acreages for TMDL study areas. Shapefiles of TMDL study areas can be found at [4].

Column G, Rows 6 through 17 - Annual precipitation

• Description: Total annual precipitation in MS4 region.

Note: The default value is 30.65 inches per year, which is the average annual precipitation at the Minneapolis-St. Paul International airport. The User should input the appropriate value for their location. The references below can be used to determine this value.

• Input: Annual rainfall, in inches
• Sources of Information:
  • TMDL study
  • Minnesota Climatology Working Group
  • MPCA Stormwater Manual
  • U.S. Climate data

Column H, Rows 6 through 17 - runoff coefficients

• Description: Standard runoff coefficient for each type of land use.
• Input: Default value with an option for the User to enter a different value
• Source of Information: MPCA input values based on literature. The default is set at the median. The User may enter a value in the Input values for MPCA estimator tab if available data supports the value. For a discussion of runoff coefficients, see [5].

Columns I through L, Rows 6 through 18 - Pollutant loads

• Description: The calculated load of the various pollutants of concern. Note that the values contained in Row 18 are the total values for all the land uses. Loading values take into consideration the concentration of pollutant in the stormwater runoff, drainage area, annual rainfall, and runoff coefficient.

This is the base loading from which reductions are made. The User should therefore not change the inputs once the initial calculations are made because it may affect the calculations for pollutant reductions.

• Input: None
• Source of Information: The values in these cells are calculated based on the information entered in the previous cells

Pollutant load reductions

Screen shot showing the section where reductions in phosphorus loading are calculated in the MPCA Estimator. Sections for TSS, E. coli and fecal coliform bacteria are similar.

This part of the worksheet is comprised of four sections corresponding with calculations for phosphorus, TSS, coliform bacteria, and E. coli bacteria. The User inputs areas being treated by a particular BMP within a specific land use. Total pounds or bacteria reduced and percent reductions from the estimated load are generated based on the pollution reduction efficiency of the BMP.

Rows 23 through 34, 44 through 55, 65 through 76, and 86 through 97

• Description: The area, in acres, treated by the BMP, for different land uses. Land uses are listed in Column A.

Note: These cells represent the area tributary to a BMP, which may not be the entire area for the type of land use. Not all cells need to be filled in. The User should only enter data in the cells relating to the specific BMPs and the land use classification of the BMPs’ drainage area.

Note that if the User attempts to enter BMP acreages greater than the land use area, an error message is generated since one BMP cannot treat an area greater than the land use area. If the User enters a total acreage for all BMPs that exceeds the area of the land use, a warning message will be generated. This warning does not prevent the User from proceeding, since the total acreage for multiple BMPs may exceed the total land use acreage. For example, assume runoff from a 1 acre commercial area drains to an underground infiltration BMP. The area may also include a 0.2 acre green roof, resulting in 1.2 acres of BMP for the 1 acre commercial area. Generally, it is unlikely the BMP acreage will exceed the land use area.

• Input: The area, in acres, tributary to the BMP. Different BMPs are contained in different columns.
  • Column B: Biofiltration (bioretention with underdrain)
  • Column C: Infiltration (BMP with no underdrain)
  • Column D: Filter Strip
  • Column E: Landscaped roof (Green roof)
  • Column F: Permeable pavement with underdrain
  • Column G: Sand filter
  • Column H: Swale
  • Column I: Wet basin
  • Column J: Wetland
  • Column K: Other

Note: Read the subheadings carefully to ensure that the information is entered under the correct pollutant of concern.

• Source of Information: User

Rows 35, 56, 77, and 98

• Description: The removal efficiencies for listed BMPs
• Input: None or User input. Removal efficiencies are based on information from the Minnesota Stormwater Manual and the Minimal Impact Design Standards (MIDS) calculator. BMP database sources shown below include compilations of data from multiple studies. The User may input other values based on field studies or published BMP performance data.

Note: With the biofiltration BMP, the User will select the removal efficiency based on the type of media present within the BMP. If the media mix used is C or D, or if another mix is used that has a phosphorus content of 30 mg/kg or less per the Mehlich 3 test, then the User shall select the phosphorus removal fraction of 0.44. If another media type is present that has a phosphorus content greater than 30 mg/kg then the User shall select a phosphorus removal fraction of 0.

Note: Typically a manufacturer will supply the pollutant removal data for their device. The International BMP Database and USEPA Verified Technologies have pollutant removal information that can be used to verify manufacturer’s data.

• Sources of Information:
  • MN Stormwater Manual
  • International BMP Database, 2012
  • USEPA Verified Technologies
  • Center for Watershed Protection, 2000
  • University of New Hampshire Stormwater Center Data Report, 2005

Rows 36, 57, 78, and 99

• Description: The fraction of the annual runoff that is treated by the BMP. This is based on the amount of total runoff that enters the BMP.
• Input: None or User input
• Source of Information: MPCA Input. The default value is set for 1.0 for wet basins and wetlands based on the assumption that all annual runoff passes through these BMPs. The default value is set at 0.9 for the remaining BMPs assuming the BMPs are designed to treat 1.0 inches of runoff. This is the most common performance goal, but this value must be changed if other performance goals are used. The User can calculate annual runoff treated by a BMP by using the MIDS calculator, or can estimate the value from the following data from the Minneapolis-St. Paul International Airport.

Daily precipitation vs annual runoff MSP airport

Rows 37, 58, 79, and 100

• Description: The fraction of the treated water that is infiltrated by the BMP. Defaults are 100 percent for all infiltration BMPs and 0 percent for the rest. All of the water captured by an infiltration BMP is assumed to infiltrate into the soil underlying the BMP. The assumption that no water infiltrates in the remaining BMPs is a conservative assumption, since some water always infiltrates through the bottom unless the BMP is lined. The MIDS calculator can be used to estimate volume loss in BMPs with an underdrain. Typical infiltration in a BMP with an underdrain and an underlying D soil will be about 20 percent (0.20).
• Input: None or User input
• Source of Information: MPCA Input, but User can change the value in the Input values for MPCA estimator tab.

Rows 38, 59, 80, and 101

• Description: The total pollutant reduced (pounds for phosphorus and TSS; number for E. coli and fecal coliform) from each BMP type. The reduction equals the fraction of total area treated by the BMP within each land use multiplied by the removal efficiency of the BMP, summed across all land uses. The total reduction for all BMPs combined is calculated and shown in Column W for each of the four pollutants.
• Input: None
• Source of Information: Auto-calculated

Rows 39, 60, 81, and 102

• Description: The percent reduction in total load for the pollutant of concern from each BMP type. The percent reduction is calculated by dividing pounds reduced by total load and multiplying by 100. The total percent reduction for all BMPs combined is calculated and shown in Column L for each of the four pollutants.
• Input: None
• Source of Information: Auto-calculated

Default values for the Estimator

Screen shot showing default values for the estimator.

In the Estimator, the tab (worksheet) called Input values for MPCA estimator contains default values for filter BMP performance, event mean concentrations (EMCs), and runoff coefficients. The values for BMP performance are only for the water that is filtered and are expressed as fractions, which represents the fraction of pollutant that is removed by the BMP. A value of 0.50, for example, means the BMP removes half of the pollutant. Note that the values for infiltration BMPs is 0 because it is assumed that all pollutant in infiltrated water is removed. The equations built in to the Estimator account for this complete removal for infiltrated water. Removal values are shown for total phosphorus, total suspended solids, E. coli, and fecal coliform.

EMCs are given for several different land uses and for total phosphorus, TSS, E. coli, and fecal coliform. Runoff coefficients are given for several different land uses.

The user can change the default values in this tab. Once changed, the default values are lost, although they can be re-entered.

Example calculations using the MPCA Estimator

Download MPCA Estimator here: File:MPCA Estimator.xlsx

File:Example map for simple estimator example.png

Map used for the MPCA Estimator example. Note there are four impaired waters (3 lakes and a stream) and 4 MS4s (3 cities and a college). MS4 boundaries, watersheds of the impaired lakes, and the location of BMPs are shown and described in the legend. Only structural BMPs that are required to be quantified are included in the map.

File:Screen shot inputs for simple estimator example.png

Screen shot showing inputs used for the MPCA Estimator example. The inputs match the information provided in the map.

Example calculations were made for reductions in pollutant loading for the map shown to the left. There are 4 MS4s (City A, City B, City C, and the MS4 College) and 4 impaired waters (Lake 1, Lake 2, Lake 3, and Stream 1). The lakes are impaired for phosphorus and the stream is impaired for TSS and E. coli bacteria. Below is a summary of MS4 contributions to each impaired water.

• City A drains to Lake 1, Lake 2 and Lake 3. It does not operate any BMPs in the Lake 3 watershed but has BMPs in the watersheds for Lake 1 and Lake 2. The estimator must therefore be used twice, one for each impairment the City contributes to and for which the City has implemented BMPs to decrease its phosphorus load.
• City B drains to Lake 1 and Lake 2 but has not implemented any structural BMPs. There are no pollutant reductions to calculate.
• City C drains to all four impairments. It has implemented BMPs to decrease the pollutant load to Lake 2, Lake 3, and Stream 1. The estimator will therefore be run three times; once for each impaired water where the City has implemented BMPs to decrease its pollutant load.
• MS4 College drains to Lake 1 and has implemented a BMP to decrease its pollutant load. The estimator can be used.

City A

File:Screen shot estimator example city A lake1.PNG

Results from using the MPCA Estimator for City A, Lake 1.

File:Screen shot estimator example city A lake2.PNG

Results from using the MPCA Estimator for City A, Lake 2.

Information: Click on an image to enlarge it. Right click on the image to open an enlarged view in another tab.

City A comprises 585 acres within the Lake 1 watershed. The breakdown by specific land use is shown in the summary table above. The City has implemented 5 BMPs within the watershed that can be included in the MPCA Estimator.

• INF3 and INF4 are infiltration BMPs (permeable pavement) in a high density residential area. The drainage area to INF3 is 0.9 acres and the drainage area to INF4 is 1.3 acres. The User therefore enters 2.2 acres in Row 28, Column C of the worksheet (Infiltration (BMP with no underdrain), high density residential land use).
• FIL3 is a media filter located within a high density residential area. The drainage area served by the BMP is 2.3 acres. The User enters 2.3 in Row 28, Column G (sand filter, high density residential land use).
• CB1 is a constructed wetland. Total drainage to the wetland is 3.2 acres, with 2 acres from a medium density residential area and 1.2 acres from a low density residential area. The User enters 1.2 in Row 29, Column J (wetland, low density residential land use) and 2 in Row 30, Column J (wetland, medium density residential).
• S1 is a swale that drains 1.6 acres, of which 1.1 is commercial land use and 0.5 acres is transportation land use. The User enters 1.1 in Row 22, Column H (swale, commercial land use) and 0.5 in Row 31, Column H (swale, transportation land use).

File:Create a new worksheet.PNG

Screen shot illustrating how to create a new worksheet tab in the TMDL spreadsheet. The contents of the estimator tab can be copied into this worksheet, allowing multiple runs of the estimator in the same spreadsheet. There is no practical limit to the number of new worksheets that can be added to the spreadsheet.

Using the defaults in the worksheet, reductions in phosphorus loading are shown in Rows 37 (total pounds reduced) and 38 (Percent load reduced). The total load reduction for all BMPs is 5.875 pounds, or about 1.00 percent of the original loading of 586.45 pounds. The User can enter either the pounds reduced (5.875) or the percent reduced (1.00) in the Cumulative reductions tab.

The estimator only allows the user to estimate loads for one impaired water at a time. The User should either save the worksheet as a separate Excel file, keep a record of the inputs used for this impairment, or insert additional estimator worksheets into the spreadsheet. To create additional estimator worksheets within the spreadsheet, on the Home tab in Excel, click on Insert and select Insert Sheet. Then Copy the contents of the MPCA estimator tab into the new worksheet. Tabs within the spreadsheet can be renamed by double clicking on the tab at the bottom of the screen and then typing in a new name. See the image to the left and Excel Help for more information.

A new version of the estimator is run for the Lake 2 watershed. City A comprises 84 acres of this watershed and has implemented just one BMP that can be used in the estimator. This is a bioinfiltration BMP that drains 2 acres of medium density residential area. this is an infiltration BMP, so the User enters 2 in row 30, Column C (Infiltration (BMP with no underdrain), Residential - medium density). this BMP results in a reduction of 2.004 pounds of phosphorus, or 2.46 percent of the original load of 81.33 pounds.

City A may conduct additional BMPs that decrease pollutant load. If these BMPs can be quantified, they can be entered as Other BMPs in the worksheet.

City C

City C comprises 297 acres of the Lake 2 watershed. The City has implemented one BMP that can be used in the estimator. This is a dry pond that drains 8 acres of medium density residential land use. The User enters 8 in Row 30, Column I (Wet basin, Residential - medium density). The resulting decrease in phosphorus loading is 4.453 pounds or 1.39 percent of the initial load of 319.67 pounds. This example again utilizes the default values in the worksheet.

As with City A, the estimator can only be used for one impaired water at a time. The User must therefore run the estimator separately for Lake 3 and Stream 1.

City C comprises 366 acres of the Lake 3 watershed. The City has implemented two BMPs, an underground sand filter that serves 5 acres of medium density residential land use, and a 12 acre dry swale that serves 10 acres of commercial development and 2 acres of transportation. The User enters 5 in Row 30, Column G (Sand filter, residential - medium density), 10 in Row 22 Column H (Swale, Commercial), and 2 in Row 31, Column H (Swale, Transportation). The resulting total decrease in phosphorus load is 9.087 pounds or 2.24 percent of the total load of 405.62 pounds.

City C comprises 427 acres of the Stream 1 watershed (127 acres in the Oasis Lake watershed and 300 acres in the Lake 3 watershed). The City has implemented a dry swale that drains 10 acres of commercial land and 2 acres of transportation land use, and a tree trench that drains 8 acres of commercial land and 7 acres of industrial land. For the tree trench, the user enters 8 in Row 22, Column C (Commercial; Infiltration) and 7 in Row 23, Column C (industrial; infiltration). For the dry swale the User enters 10 in Row 22, Column H (Commercial; Swale) and 2 in Row 31, Column H (transportation; Swale). The resulting decrease in TSS load is 21,885.4 pounds or 9.78 percent of the initial load of 223,855 pounds. The decrease in E. coli load is 3.12 percent of the initial load. Note the swale does not reduce any of the E. coli load.

• Screen shots of estimator results for City C. Click on an image for a larger view.
• Estimator example city C lake 2.png

  Lake 2.

• Estimator example city C lake 3.png

  Lake 3.

• Estimator example city C stream.png

  Stream 1; TSS

• Estimator example city C stream e coli.png

  Stream 1; E coli

MS4 College

File:Estimator example MS4 College.png

Screen shot showing results and inputs for the estimator for the MS4 College.

The MS4 College is a non-traditional regulated MS4. The area of the college is 84 acres and the college owns and operates the stormwater conveyance within the campus boundaries. The entire acreage of the college lies within the Lake 1 watershed. Land use within the campus consists of 15 acres of park and 69 acres of institutional land use.

The college has constructed and operates one BMP (FIL1). The BMP is a perimeter sand filter that accepts runoff from a 3 acre area. The land use contributing runoff to the BMP is institutional. The User therefore enters 3 in row 24, Column G of the estimator (Sand filter, Institutional). The BMP decreases phosphorus loading by 1.391 pounds, or 1.77 percent from the initial load of 78.75 pounds.

Entering results into the Cumulative reductions tab

File:Screenshot estimator into cumulative reductions tab.PNG

Screen shot illustrating entry of the results from City A into the Cumulative reductions tab.

Results of the MPCA estimator can be entered into the Cumulative reductions tab. This is illustrated in the image to the right for City A. In this example, for Lake 1 a value of 5.875 is entered into Row 4, Column F. In Column D the units are selected from the dropdown box, In this case the units are pounds reduced. For Lake 2 a value of 2.46 is entered in row 5, column F and the units are % load reduction. The calculation method, which is an optional field, is entered as MPCA estimator.