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).
Some errors were identified in the original File:MPCA Estimator.xlsx. We recommend you download and use the updated file.
Download updated MPCA Estimator here: File:MPCA Estimator-2.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.
Contents
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
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
Calculations for pollutant load reductions
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
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
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
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).
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
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
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.
Tips for using the estimator
The Estimator is a simple tool based on simple assumptions. this section contains some tips for applying the tool to different situations that you may encounter.
Addressing treatment trains
The estimator does not explicitly address stormwater treatment trains. It will therefore underestimate pollutant removal for BMPs that are in series. Adjusting the estimator to more closely simulate pollutant removal for treatment trains can be challenging since the estimator uses a lumped BMP approach in which all similar BMPs are lumped as a single BMP. For example, permeable pavement with no underdrain, bioinfiltration, and infiltration basins are all lumped together as infiltrator BMPs. Another complication is that each treatment train differs and attempting to model them as a single system creates inaccuracies.
To get an idea of how to adjust the estimator to account for treatment trains, we ran a series of treatment train scenarios through the MIDS calculator and compared them to results for the estimator. A more detailed description of this exercise, including results, is in a Word document (File:Treatment trains.docx. Recommendations are summarized below.
- The Estimator appears to provide reasonable results when infiltration practices are employed in a treatment train. Pollutant removal fractions in the Input values for MPCA estimator tab could be adjusted slightly upward (e.g. 2 to 3 percent).
- The Estimator underestimates treatment when filtration and settling practices are utilized. Pollutant removal fractions in the Input values for MPCA estimator tab should be adjusted by upward 0.10 units.
- Bacteria were not included in the analysis. If bacteria are assumed to primarily be associated with sediment, the above recommendations could be applied to bacteria.
Tables with recommended values for pollutant removal fractions are in a Word document (File:Treatment trains.docx).
Adjusting EMCs (event mean concentrations)
If you are calculating reductions in loading as a percent, there is no value if modifying the EMCs in the estimator since the only factors affecting the percent removal are the BMP removal fraction and the fraction of runoff being treated by and/or infiltrating through the BMP. If you are calculating reductions in pounds or number of bacteria, the EMC affects the initial pollutant load. The higher the load the greater the reduction when the BMPs are applied. Values in the estimator are on the high end of concentrations for the different land uses. For more information on EMCs, see [tabled values in the manual].
If you have a land use not shown in the estimator, values should be obtained from the literature. Recommended values for agriculture are shown below.
- For row crop agriculture, use medium density residential land use for phosphorus and TSS.
- For pasture, use Open space land use for phosphorus and TSS.
- For bacteria, derive values from the literature as bacteria concentrations are highly variable in agricultural systems as a result of manure management and livestock practices.
Adjusting the fraction of annual water treated and infiltrated in a BMP
The default value for the fraction of runoff treated by BMPs is 0.9, except for wet basins and wetlands, where the value is 1.0. The 0.9 corresponds with treatment of the first 1 inch of runoff on B soils, while a value of 1.0 assume all runoff is treated by the BMP. this value can be changed in the input values for MPCA estimator tab if your BMPs are sized for a different water quality volume or if you have different soils. The table below can be used to determine the appropriate number. For example, if your soils were A rather than B, you should enter a value ranging from 0.92 to 0.96, depending on the specific soil type. If you had B soils but the water quality volume was 0.75 inches, the value should be changed to 0.81.
Similarly, the fraction of runoff that is infiltrated into an infiltrator BMP is 0.9. Again, this value should be adjusted if the water quality volume or soils differ from 1 inch and B soils. The only other BMP that infiltrates water is biofiltration. The infiltration fraction for this BMP is 0.2, which is based on data generated from MIDS calculator runs. Infiltration may occur in other BMPs, in particular permeable pavement with an underdrain and swales. A value of 0.2 can be entered for permeable pavement with underdrains to make it similar to biofiltration. An infiltration value for swales is difficult to generate because of the many potential swale configurations. The MIDS calculator is one tool that can be used to generate a value for fraction of water infiltrated in swales.
Annual volume, expressed as a percent of annual runoff, treated by a BMP as a function of soil and Water Quality Volume. See footnote1 for how these were determined.
Link to this table
| Soil | Water quality volume (VWQ) (inches) | ||||
|---|---|---|---|---|---|
| 0.5 | 0.75 | 1.00 | 1.25 | 1.50 | |
| A (GW) | 84 | 92 | 96 | 98 | 99 |
| A (SP) | 75 | 86 | 92 | 95 | 97 |
| B (SM) | 68 | 81 | 89 | 93 | 95 |
| B (MH) | 65 | 78 | 86 | 91 | 94 |
| C | 63 | 76 | 85 | 90 | 93 |
1Values were determined using the MIDS calculator. BMPs were sized to exactly meet the water quality volume for a 2 acre site with 1 acre of impervious, 1 acre of forested land, and annual rainfall of 31.9 inches.