Guidance and examples for using the MPCA Estimator

Caution: The MPCA Simple Estimator is being updated. The guidance on the following page is being modified to align with the updated version of the Estimator. We anticipate having the updated version of the Estimator and guidance updated by mid-October, 2019

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.

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 Simple Estimator (Estimator) spreadsheet contains 12 worksheets.

• 1 worksheet providing information about the spreadsheet
• 10 worksheets where data are entered and calculations made.
• 1 summary worksheet

Calculation worksheets

Each of the 10 calculation worksheets represents a sub-watershed. The user is not obligated to use 10 worksheets for their study area. We encourage the user to use separate worksheets for the following conditions.

• The study area has multiple well-defined sub-watersheds.
• Contributing areas to BMPs in downstream portions of treatment trains should be treated as sud-watersheds and should not include the area contributing to upstream BMPs in the treatment train, particularly if significant amounts of runoff are being treated by the upstream BMPs
• To more accurately reflect differences in land use. Although the Estimator provides flexibility in selecting land uses, certain situations may be better dealt with by treating land uses as different sub-watersheds. For example, a single residential area highly impacted by emerald ash borers may be treated as a separate sub-watershed from an adjacent residential area where tree canopy will remain intact (i.e. due to significant differences in runoff event mean concentrations).

Information: Each worksheet in the Estimator is called a sub-watershed

The 10 calculation worksheets are identical and each contains 5 sections, described below.

• Calculation of unadjusted total loads for total phosphorus (TP) and total suspended solids (TSS)
• Calculation of adjusted total loads for TP and TSS
• Calculation of TP loading reductions associated with implementation of best management practices (BMPs)
• Calculation of TSS loading reductions associated with implementation of best management practices (BMPs)
• BMP and land use input values

Each of these sections is described below.

Section 1: Calculation of unadjusted total 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.00001 acres, which creates a small amount of pollutant load. Values in yellow are calculated and cannot be changed by the User.

Information: The Estimator spreadsheet uses a mix of SI and English units. Correction factors are included in all calculations. The user mus use the correct units for input values.

Caution: To avoid calculation errors, each land use should have a minimum default area when there is acreage within that land use. The Estimator uses a value of 0.00001 and we recommend retaining that value or using a smaller value

In this section, the total unadjusted load, in pounds, is calculated for the area considered. Unadjusted means there is no consideration of reductions associated with practices such as street sweeping, pollution prevention, changes in land use, etc.

This section comprises Cells A5 through L24 of each of the 10 calculation worksheets. In this section, the user inputs land area, in acres, associated with different land uses within the area being considered. The user inputs annual precipitation, in inches. A link provides access to precipitation information if the user does not know the precipitation for the sub-watershed. The section contains default values for TP and TSS event mean concentrations (mg/L) and default values for runoff coefficients. EMCs and runoff coefficients can be changed by the user in the section containing BMP and land use inputs.

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, transportation, water, park, and agriculture. The User may input up to three other land use classifications.
• Source of Information: MPCA input or User input. For descriptions of different land uses, see [1], [2], [3].

Columns B and C, Rows 6 through 23 - 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 D, Rows 6 through 23 - Area of specified land use

• Description: Area of specific land use within the sub-watershed, 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 E, Rows 6 through 23 - Annual precipitation

• Description: Total annual precipitation in sub-watershed.

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 F, Rows 6 through 23 - 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 G and H, Rows 6 through 24 - Pollutant loads

• Description: The calculated load of the various pollutants of concern. Note that the values contained in Row 24 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

Section 2: Calculation of adjusted total loads

This section of the 10 calculation worksheets contains information and calculations for adjustments to the total loads calculated in the previous section (Unadjusted total loads). It comprises Cells A26 through L45.

Adjusted total loads account for changes in event mean concentration or runoff coefficient values used in the section "Unadjusted total loads". Examples of practices or actions that result in changes in EMC or runoff coefficients include but are not limited to the following.

• Street sweeping. Enhancing an existing street sweeping program, or initiating street sweeping will lower the emc in runoff. Alternatively, reducing street sweeping will increase the emc.
• Implementation of pollution prevention practices. Practices that reduce TP and/or TSS loads will lower the emc.
• Land use change. A change in land use may result in a change in emc and/or a change in runoff coefficient
• Altered land use practices. This potentially includes a wide range of practices that may affect the emc or runoff coefficient. Examples include disconnection of impervious surfaces, reduction in impervious surfaces, and changes in lawn practices (e.g. increased yard waste composting).

The user will adjust appropriate emcs in Cells B29 through C44, and/or adjust the appropriate runoff coefficients in cells F29 through F44. The user should provide a description or rationale in the appropriate cell in Columns K-L.

{alert|The user should provide a description or rationale for changed values in the appropriate cell in Columns K-L|alert-warning}}

Section 3: Calculations for phosphorus load reductions associated with BMP implementation

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.

In this section of the worksheet the user enters acreages for BMPs implemented within each land use in the sub-watershed. The section comprises Cells A47 through L70. The area treated by a BMP cannot exceed the total acreage within a specific land use (i.e. an error message is returned if the user attempts to enter an acreage greater than the total acres in a land use). If the cumulative acreage for multiple BMPs exceeds the total acreage within a land use, Column L returns a message stating the treated acres exceed the total acres for the land use. Note: it is possible for treated acres to exceed total acres when multiple BMPs are employed. The warning alerts the user to this situation, in which case the user should ensure the entered values are correct. If treated acres do exceed total acres, the user should consider adjusting removal efficiencies for downstream BMPs that are receiving water treated by upstream BMPs.

Caution: The area treated by a BMP cannot exceed the total acreage within a specific land use

Caution: If the cumulative acreage for multiple BMPs exceeds the total acreage within a land use, Column L returns a message stating the treated acres exceed the total acres for the land use.

Rows 48 through 65

• 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
• Source of Information: User

Row 66

• 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

Row 67

• 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

Row 68

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

Row 69

• 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

Row 70

• 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

Section 4: Calculations for TSS load reductions associated with BMP implementation

In this section of the worksheet the user enters acreages for BMPs implemented within each land use in the sub-watershed. The section comprises Cells A72 through L95. The area treated by a BMP cannot exceed the total acreage within a specific land use (i.e. an error message is returned if the user attempts to enter an acreage greater than the total acres in a land use). If the cumulative acreage for multiple BMPs exceeds the total acreage within a land use, Column L returns a message stating the treated acres exceed the total acres for the land use. Note: it is possible for treated acres to exceed total acres when multiple BMPs are employed. The warning alerts the user to this situation, in which case the user should ensure the entered values are correct. If treated acres do exceed total acres, the user should consider adjusting removal efficiencies for downstream BMPs that are receiving water treated by upstream BMPs.

Caution: The area treated by a BMP cannot exceed the total acreage within a specific land use

Caution: If the cumulative acreage for multiple BMPs exceeds the total acreage within a land use, Column L returns a message stating the treated acres exceed the total acres for the land use.

Rows 72 through 90

• 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
• Source of Information: User

Row 91

• 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: 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

Row 92

• 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

Row 93

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

Row 94

• Description: The total pollutant reduced (pounds) 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

Row 95

• 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

Section 5: Default values for BMP and land use inputs

File:Screen shot of defaults for estimator.png

Screen shot showing default values for the estimator.

This section of each of the 10 calculation worksheets 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 TP and TSS.

EMCs are given for several different land uses and for TP and TSS. 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.

Caution: 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.

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.

Caution: It is Highly Recommended you keep all information you use to calculate reductions

Addressing treatment trains

The estimator does not explicitly address stormwater treatment trains. It will therefore underestimate pollutant removal for BMPs that are in series. For example, in a treatment train consisting solely of ponds, the first pond removes the greatest fraction of pollutant concentration. Each pond in succession will treat less polluted water but will further the reduction in pollutant concentration flowing downstream. The estimator doesn’t recognize the total number of BMPs on the ground so it cannot recognize BMPs as being connected. Therefore each pond is assumed to treat the same degree of polluted water regardless of how many are connected in a treatment train.

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 in 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 Estimator uses the values from this tab 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 in the estimator that infiltrates water as the default 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 footnote¹ 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

¹Values 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.

Adjusting the pollutant removal fraction

The pollutant removal fraction is the most important factor affecting the calculations in the estimator. The default values correspond with recommended values in the manual and represent well-defended data from the literature. The user can change the default, but any change should be supported with data. For further information on pollutant removal by BMPs, see the appropriate page in this manual on BMP pollutant credits. Each of the credit articles contains information to help determine the most appropriate value for pollutant removal for a specific BMP.

Addressing BMPs not included in the estimator

The estimator allows the user to enter an additional BMP beyond the default BMPs (called Other in the estimator). Most urban BMPs fit into one of the default BMPs in the estimator. There may be exceptions however. If you can determine values for pollutant removal fraction, fraction of water that is treated, and fraction of water that is infiltrated for the BMP, you can include an additional BMP. If these values cannot be generated for the BMP, calculate pollutant removal independently and add that value to the value generated by the estimator when reporting cumulative reductions on the Annual Report form. For example, assume you had an in-line treatment system that treated stormwater runoff in a part of your conveyance system. This BMP is not easily incorporated into the estimator, but if you monitor the BMP and have pollutant removal information, you can simply add the removal amount to the amount calculated by the estimator for the remainder of your system.

Adjusting for impervious and pervious surface

The estimator uses runoff coefficients to estimate the fraction of rainfall that runs off for different land uses. Default values are typical values from the literature. Runoff coefficients can be changed in the Input values for MPCA estimator tab. Increase the runoff coefficient if your land use has greater impervious surface, or decrease the coefficient if it has less impervious surface. Ranges of values for runoff coefficients can be found here.