Subject: Spreadsheets for Water Quality based NDPES Permit Calculations http://olympus.dis.wa.gov/www/access/ecology/pwspread.html SPREADSHEETS FOR WATER QUALITY-BASED NPDES PERMIT CALCULATIONS Updated August 4, 1995 by Greg Pelletier The spreadsheets identified below were developed by the Washington State Department of Ecology's Environmental Investigations and Laboratory Services Program to aid NPDES permit writers. These spreadsheets are referenced in Ecology's Permit Writer's Manual (Department of Ecology Publication Number 92-109). All of the spreadsheets are in separate Lotus 1-2-3 WK1 files and also have been combined and reformatted into an Excel 5 workbook. * DOSAG2.WK1: This spreadsheet calculates critical sag of dissolved oxygen downstream from a point source using the Streeter-Phelps equation. A detailed description and user instructions are available. See below for FTP access to the description and user instructions. DOSAG2.WK1 also is available to download via FTP. * IDOD2.WK1: This spreadsheet calculates concentrations of dissolved oxygen at a mixing zone boundary accounting for dilution of dissolved oxygen and initial dissolved oxygen demand. A detailed description and user instructions are available. See below for FTP access to the description and user instructions. IDOD2.WK1 also is available to download via FTP. * NH3FRESH.WK1: This spreadsheet calculates freshwater un-ionized and total ammonia criteria from temperature and pH from the formulas in the EPA Gold Book (EPA 440/5-86-001) and state water quality standards (Chapter 173-201A Washington Administrative Code). A detailed description and user instructions are available. See below for FTP access to the description and user instructions. NH3FRESH.WK1 also is available to download via FTP. * NH3FRES2.WK1: This spreadsheet calculates freshwater un-ionized and total ammonia criteria from temperature and pH from the formulas modified by EPA which are proposed to be adopted in the 1995 revision to the state water quality standards. The modified formulas in this spreadsheet are recommended in the Permit Writer's Manual. A detailed description and user instructions are available. See below for FTP access to the description and user instructions. NH3FRES2.WK1 also is available to download via FTP. * NH3SALT.WK1: This spreadsheet calculates saltwater total ammonia criteria from temperature, pH, and salinity to meet the un-ionized ammonia criteria. A detailed description and user instructions are available. See below for FTP access to the description and user instructions. NH3SALT.WK1 also is available to download via FTP. * PHMIX2.WK1: This spreadsheet calculates the pH of a mixture of two sources from temperature, pH, and alkalinity. A detailed description and user instructions are available. See below for FTP access to the description and user instructions. PHMIX2.WK1 also is available to download via FTP. * RIVPLUM4.WK1: This is a simple dilution model for rivers. A detailed description and user instructions are available. See below for FTP access to the description and user instructions. RIVPLUM4.WK1 also is available to download via FTP. * WQBP2.WK1: This spreadsheet calculates water quality based permit limits to meet acute and chronic aquatic life criteria for specific chemicals. A detailed description and user instructions are available. See below for FTP access to the description and user instructions. WQBP2.WK1 also is available to download via FTP. * PWSPREAD.XLS: This Excel 5 file contains all of the above spreadsheets. PWSPREAD.XLS is available to download via FTP. _________________________________________________________________ Detailed Descriptions and User Instructions The complete set of detailed descriptions and user instructions for all of the above files also is available to download via FTP. _________________________________________________________________ Spreadsheet DOSAG2.WK1 Revised October 19, 1993 This spreadsheet replaces a previous version called DOSAG.WK1. This spreadsheet calculates the critical dissolved oxygen sag and concentration downstream from a point source load of BOD in a river using the Streeter-Phelps equations. The method used is documented in EPA/600/6-85/002a (Water Quality Assessment: A Screening Procedure for Toxic and Conventional Pollutants in Surface and Ground Water). This spreadsheet is recommended for use as a screening tool to determine the potential for dissolved oxygen standards to be violated. DOSAG.WK1 may be overly simplistic for deriving limits for effluent BOD. If DOSAG.WK1 suggests the dissolved oxygen sag is close to or below the water quality standard, then a more sophisticated model such as QUAL2E or WASP5 should be used to derive appropriate effluent limits. Those water quality models are designed to more accurately simulate water movements, mass transport, and water column processes. USER INSTRUCTIONS FOR THE INPUT SECTION Step 1: Enter the permittees effluent characteristics, including permitted discharge and maximum (e.g, weekly) 5-day BOD (referred to as CBOD5 for "carbonaceous" 5-day BOD). Carbonaceous 5-day BOD is less than the total 5-day BOD if nitrification occurs during the test. The minimum national standards for carbonaceous 5-day BOD in effluent after secondary treatment are a monthly average of 25 mg/L and weekly average of 40 mg/L (40 CFR Part 133). Guidance for determining if carbonaceous 5-day BOD should be substituted for total 5-day BOD is contained in Ecology's Permit Writer's Manual (section V-3.6). Nitrogenous BOD (NBOD) should also be estimated if it is significant (e.g. if nitrification is not significant during secondary treatment). NBOD can be estimated as: NBOD = 4.57 * (Ammonia N + Organic N) where concentrations of NBOD, ammonia N and organic N are expressed in mg/L. Effluent temperature and dissolved oxygen for the analysis are also entered at this step. The spreadsheet may be used to estimate the maximum permissible effluent CBOD5 and NBOD that will meet the water quality standards for dissolved oxygen. A trial and error solution is necessary for this purpose. Trial values of effluent CBOD5 and NBOD may be entered until the dissolved oxygen at the critical sag meets the water quality standard. Step 2: Enter receiving water characteristics. These will generally be conditions at the 7Q10 discharge. Upstream CBOD5, NBOD, dissolved oxygen and temperature at the design river flow (e.g., 7Q10) should be entered. The local channel elevation and channel slope (e.g., from USGS topographic maps) downstream from the discharge should also be entered. Downstream average channel depth and velocity at the design flow should be entered also. If no receiving water data are available, it would be desirable to collect data. Channel cross-sections of depth and velocity can be measured during the critical season. If measurements are not taken near critical conditions, then Manning's equation may be used to estimate velocity and depths from the measurements. Several cross-sections proceeding downstream from the discharge may be needed to characterize the river to the point of critical sag if velocities and depths are not uniform. Dye studies to measure travel time may be useful if velocities are variable. If significant tributaries, groundwater inflows, or other pollutant loads occur before the predicted critical sag point, then a more sophisticated model should be used (e.g. QUAL2E). Measurements of water quality (e.g. dissolved oxygen, ammonia, BOD) in the receiving water from upstream and at intervals downstream to the critical sag point are also desirable for model calibration. If the model is applied without sufficient data to demonstrate calibration, then the model should mainly be used to screen for potential violation of standards. If effluent BOD is required to be more restrictive than current technology-based limits, then calibration data are probably needed. Separate calibration and verification data sets taken on different dates may be needed in many cases where the accuracy of the model is in question. Step 3: Enter the reaeration rate (base e) at 20 degrees C in cell D27. Suggested values using empirical equations referenced in EPA/600/6-85/002a are given below cell D27 for guidance in selecting an appropriate value. If the calculated values are used, select the most appropriate equation based on applicable depth and velocity (e.g., if depth is Step 4: Enter the BOD decay rate (base e) at 20 degrees C in cell D36. A calculated value based on the Wright and McDonnell equation referenced in EPA/600/6-85/002a is provided and may be entered in cell D36 at Step 4 if desired. USER INSTRUCTIONS FOR THE OUTPUT SECTION The user does not need to change or enter any values or formulas in the Output Section. The travel time and distance to critical sag, deficit at critical sag, and dissolved oxygen concentration at critical sag are displayed in the Output Section. Return to top _________________________________________________________________ Spreadsheet IDOD2.WK1 Revised October 19, 1993 This spreadsheet replaces a previous version called IDOD.WK1. This spreadsheet calculates the dissolved oxygen concentration at a mixing zone boundary from dilution of dissolved oxygen in the effluent and ambient background and immediate dissolved oxygen demand of the effluent. The method used is presented in EPA/600/6-85-002b (Water Quality Assessment: A Screening Procedure for Toxic and Conventional Pollutants in Surface and Ground Water - Part II Revised 1985) and EPA/430/9-82-011 (Revised Section 301(h) Technical Support Document). USER INSTRUCTIONS FOR THE INPUT SECTION Step 1: Specify the dilution factor for effluent at the chronic mixing zone boundary. This value should represent dilution at critical conditions if the spreadsheet is being used for developing NPDES permit limits. The dilution factor used should represent the reciprocal of the volume fraction of effluent present at the mixing zone boundary (see Permit Writer's Manual section VI-2.1). Step 2: Enter the background dissolved oxygen concentration in the receiving water. The 10th percentile during the critical season is recommended as a reasonable worst case. If no data are available it would be desirable to collect data describing background dissolved oxygen concentrations during the critical season (e.g. upstream from the discharge to a river). Step 3: Enter the effluent dissolved oxygen concentration. The 10th percentile during the critical season is recommended as a reasonable worst case. Step 4: Enter the immediate dissolved oxygen demand (IDOD) of the effluent if known. The IDOD represents the oxygen demand of reduced substances which are rapidly oxidized (e.g. sulfides to sulfates). If the effluent contains measurable dissolved oxygen, then the IDOD may be negligible. If IDOD is to be determined experimentally, the procedures in Standard Methods 1979 edition could be followed. However, the method was omitted from Standard Methods in the 1985 edition because of concerns about the accuracy of the test. USER INSTRUCTIONS FOR THE OUTPUT SECTION The user should not enter or change the value or formula in the output section. The dissolved oxygen at the mixing zone boundary is presented in the output section. Return to top _________________________________________________________________ Spreadsheet NH3FRESH.WK1 and NH3FRES2.WK1 Revised October 19, 1993 (NH3FRESH.WK1) and December 12, 1994 (NH3FRES2.WK1) These spreadsheets replace a previous version called AMMONIA.WK1. NH3FRESH.WK1 calculates the freshwater acute and chronic criteria for un-ionized and total ammonia for specified temperature and pH using the procedure described in the EPA Gold Book (EPA 440/5-86-001) and listed in WAC 173-201A. NH3FRES2.WK1 contains the formulas modified by EPA that are proposed to be adopted in the 1995 revision of the state water quality standards. The spreadsheets also calculate the amount of un-ionized ammonia present in a sample if total ammonia, temperature, and pH are known. USER INSTRUCTIONS FOR THE INPUT SECTION Step 1: Specify the temperature (design condition at the mixing zone boundary) for which un-ionized ammonia criteria or concentrations are to be estimated. If the spreadsheet is being used to calculate criteria for a NPDES permit limit, the 90th percentile temperature during the critical season is recommended for a reasonable worst-case condition. If no data are available it may be desirable to collect data during the critical season to describe temperature at the mixing zone boundary. Step 2: Specify the pH (design condition at the mixing zone boundary) for which un-ionized ammonia criteria or concentrations are to be estimated. If the spreadsheet is being used to calculate criteria for a NPDES permit limit, the 90th percentile pH during the critical season is recommended for a reasonable worst-case condition. If no data are available it may be desirable to collect data during the critical season to describe pH at the mixing zone boundary. Step 3: Specify the sample total ammonia concentration if known. Entering a value here only affects Output Step 2 (calculation of un-ionized ammonia present in a sample). No input is required at this step if the spreadsheet is being used only to calculate criteria from temperature and pH (i.e. values entered at this step do not affect criteria calculations). Step 4: Specify "Acute TCAP" according to the Gold Book (enter 20 if salmonids are present; 25 if salmonids are absent). Step 5: Specify "Chronic TCAP" according to the Gold Book (enter 15 if salmonids are present; 20 if salmonids are absent). USER INSTRUCTIONS FOR THE OUTPUT SECTION The user should not enter or change any values or formulas in the Output Section. The spreadsheet calculates the amount of un-ionized ammonia present in a sample at Output Step 2 if the sample total ammonia was specified at Input Step 3. Output Step 3 provides the acute and chronic criteria for un-ionized ammonia expressed in ug/L as NH3-N. Output Step 4 provides the acute and chronic criteria for total ammonia expressed in ug/L as NH3-N. Return to top _________________________________________________________________ Spreadsheet NH3SALT.WK1 Revised October 19, 1993 This spreadsheet replaces a previous version called HAMPSON.WK1. This spreadsheet calculates water quality criteria for ammonia in saltwater using the method specified in EPA 440/5-88-004 (Ambient Water Quality Criteria for Ammonia (Saltwater)-1989). USER INSTRUCTIONS FOR THE INPUT SECTION Step 1: Specify the temperature (design condition at the mixing zone boundary) for which un-ionized ammonia criteria are to be estimated. If the spreadsheet is being used to calculate criteria for a NPDES permit limit, the 90th percentile temperature during the critical season is recommended for a reasonable worst-case condition. If no data are available it may be desirable to collect data during the critical season to describe temperature at the mixing zone boundary. Step 2: Specify the pH (design condition at the mixing zone boundary) for which un-ionized ammonia criteria to be estimated. If the spreadsheet is being used to calculate criteria for a NPDES permit limit, the 90th percentile pH during the critical season is recommended for a reasonable worst-case condition. If no data are available it may be desirable to collect data during the critical season to describe pH at the mixing zone boundary. Step 3: Specify the salinity (design condition at the mixing zone boundary) for which un-ionized ammonia criteria are to be estimated. If the spreadsheet is being used to calculate criteria for a NPDES permit limit, the 10th percentile salinity during the critical season is recommended for a reasonable worst-case condition. If no data are available it may be desirable to collect data during the critical season to describe salinity at the mixing zone boundary. USER INSTRUCTIONS FOR THE OUTPUT SECTION The user should not enter or change any values or formulas in the output section. The acute and chronic criteria are expressed three ways: 1) as unionized ammonia in mg/L as NH3 at Output Step 5; 2) as total ammonia in mg/L as NH3 at Output Step 6; and 3) as total ammonia in mg/L as NH3-N at Output Step 7. For derivation of total ammonia waste load allocations and comparisons with effluent total ammonia data, it is recommended that the criteria be expressed as total ammonia in mg/L as NH3-N for simplicity. [Note: the criteria in EPA 440/5-88-004 Tables 2 and 3 are for total ammonia as mg/L as NH3, which should be multiplied by 0.822 to convert to mg/L as NH3-N.] Return to top _________________________________________________________________ Spreadsheet PHMIX2.WK1 Revised October 19, 1993 This spreadsheet replaces a previous version called PH-MIX.WK1. This spreadsheet calculates the pH of a mixture of two flows using the procedure in EPA's DESCON program (EPA, 1988. Technical Guidance on Supplementary Stream Design Conditions for Steady State Modeling. EPA Office of Water, Washington DC). The major form of alkalinity is assumed to be carbonate alkalinity. Also, alkalinity and total inorganic carbon are assumed to be conservative. USER INSTRUCTIONS FOR THE INPUT SECTION Step 1: Specify the dilution factor for effluent at the mixing zone boundary. This value should represent dilution at critical conditions if the spreadsheet is being used for developing NPDES permit limits. The dilution factor used should represent the reciprocal of the volume fraction of effluent present at the mixing zone boundary (see Permit Writer's Manual section VI-2.1). Step 2: Specify the upstream characteristics, including temperature, pH, and alkalinity. For development of NPDES permit limits for ammonia, the 90th percentiles during the critical season are recommended. If no data are available, it is desirable to collect data describing upstream temperature, pH, and alkalinity during the critical season. Step 3: Specify the effluent characteristics, including temperature, pH, and alkalinity. For NPDES permit limits, a reasonable worst case estimate of each may be estimated from DMR data (e.g. for ammonia limits use 90th percentile values from the DMR data during the critical season). If effluent data are not available then data should be collected during the critical season. In many cases, pH in ambient receiving water (at Step 2 above) may be assumed to represent the pH in the mixing zone. USER INSTRUCTIONS FOR THE OUTPUT SECTION The user does not need to enter or change any values or formulas in the Output Section. The spreadsheet calculates and displays the pH at the mixing zone boundary at Output Step 4. Some important factors that can influence pH are not included in this calculation. For example, photosynthesis in the receiving water may increase pH downstream from the mixing zone. In many cases where dilution is relatively large (e.g. greater than a dilution factor of 20) the pH in the mixing zone will be dominated by ambient conditions. This spreadsheet should be used mainly where effluent dilution is relatively low and effluent pH and alkalinity are much different than in the receiving water. Return to top _________________________________________________________________ Spreadsheet RIVPLUM4.WK1 Revised May 19, 1995 This spreadsheet calculates dilution at a specified point of interest downstream from a point discharge to a river. The procedure used is described in Fischer et al., 1979 (Mixing in Inland and Coastal Waters, Academic Press) and referenced in EPA/505/2-90-001 (TSD for WQ-based Toxics Control). The calculation for dilution factors incorporates the boundary effect of shorelines (Fischer et al., equation 5.9) using the method of superposition. This spreadsheet is based on the assumption that the discharge: 1) is a single point source, which is most appropriate for single port or short diffusers, or side-bank discharges; and 2) is completely and rapidly mixed vertically, which usually only occurs in shallow rivers. If the diffuser length occupies a substantial portion of the stream width, or the discharge is not vertically mixed over the entire water column within the acute mixing zone, an alternative model should be used such PLUMES or CORMIX. RIVPLUM4.WK1 is useful for estimating dilution in shallow rivers for side-bank discharges or single-port outfalls. This spreadsheet replaces a previous version called RIVPLUM3.WK1. RIVPLUM4.WK1 was modified to include calculation of the flux-average dilution factor. USER INSTRUCTIONS FOR THE INPUT SECTION Step 1: Enter the effluent design flow (see Permit Writer's Manual section VI-3.3.2). Step 2: Specify the receiving water characteristics, including average channel depth, velocity and width downstream from the discharge at the design flow (e.g, at 7Q10. NOTE: The product of depth*width*velocity should equal the receiving water discharge rate downstream from the discharge). Also enter either the channel slope downstream from the discharge (e.g., as measured from a USGS topographic map) or Manning's "n" coefficient for roughness. Finally, enter either 0 (if slope is entered above) or 1 (if Manning's "n" is entered above). The slope or Manning's "n" are used to estimate shear velocity and transverse mixing coefficients. Either method may be used, depending on which data are more readily available. It is not necessary to specify both slope and Manning's "n". If comparisons are made between the two methods then care should be taken to be sure that slope and Manning's "n" values are consistent with velocity, depth, and width data since all are related by Manning's equation. In general, it is not desirable to overestimate Manning's "n" because a lower value will generally be more protective since it will predict a lower transverse mixing coefficient. If the Manning option is used, the following values may be appropriate estimates for Manning's "n" (EPA/600/3-87-007 after Henderson, F.M., Open Channel Flow, Macmillan Co., New York, NY, 1966): * Artificial channel, earth, smooth, no weeds: 0.020 * Artificial channel, earth, some stones and weeds: 0.025 * Natural channel, clean and straight: 0.025 - 0.030 * Natural channel, winding with pools an shoals: 0.033 - 0.040 * Natural channel, very weedy, winding and overgrown: 0.075 - 0.150 If no receiving water data are available, then data collection would be desirable. Measurements of channel cross-sections of width, depth, and velocity should be collected within the mixing zone at conditions near critical low flow (e.g. near 7Q10). If conditions are significantly different than 7Q10 during measurements, then data may need to be adjusted (e.g. using Manning's equation). Step 3: Enter the distance between the diffuser midpoint and the nearest shoreline of the river (e.g., for a side-bank discharge enter 0). Step 4: Enter the location of the downstream point at which dilution factors will be estimated, including the distance downstream from the diffuser and the distance from the nearest shoreline. The "point of interest" is the location at which dilution factors will be estimated in the Output Section. The highest concentration of effluent downstream from the outfall will be the same distance from shore as the point of discharge. Therefore, the distance from shore for the point of interest should be the same as for the diffuser midpoint in Step 3 for a worst case. However, the dilution at any point downstream may be estimated using any combination of distances downstream and from shore for the "point of interest." Step 5: Enter the transverse mixing coefficient constant. A value of 0.6 is recommended for most natural channels. Fischer reports that the transverse mixing coefficient can range from 0.1 to 0.2 for straight artificial channels. Curves and sidewall irregularities increase the coefficient such that in natural streams it is rarely less than 0.4. If the stream is slowly meandering and the sidewall irregularities are moderate, then the coefficient is usually in the range of 0.4 to 0.8. Therefore, a value of 0.6 is usually recommended in natural channels. Uncertainty in this constant is usually at least +/- 50 percent. USER INSTRUCTIONS FOR THE OUTPUT SECTION The user does not need to enter or change any values or formulas in the Output Section. The plume characteristics incorporating the shoreline effect are displayed at Step 5 of the Output Section, including the approximate distance downstream to complete mix, theoretical maximum available dilution at complete mix of effluent with the receiving water, flux-average dilution at the specified downstream distance, and the calculated dilution factor at the specified point of interest downstream from the discharge. The distance downstream to complete mixing is often overestimated because most natural channels contain sharp bends or changes that increase mixing beyond the processes included in the model. The model is most useful for predicting mixing where the channel is represented over a relatively short distance (e.g. to the mixing zone boundary). Return to top _________________________________________________________________ Spreadsheet WQBP2.WK1 Revised October 19, 1993 This spreadsheet replaces a previous version called WQBP-CON.WK1. This spreadsheet calculates water quality-based permit limits, including calculations of waste load allocations (WLAs) and permit limits incorporating effluent variability for specific chemical concentrations. The method used is documented in Box 5-2 of EPA/505/2-90-001 (Technical Support Document for Water Quality-based Toxics Control). USER INSTRUCTIONS FOR THE INPUT SECTION Step 1: Specify water quality standards/criteria that apply to the receiving water. These include the acute and chronic concentration values (e.g., mg/L or ug/L) appropriate for the specific chemical of interest. The acute and chronic water quality criteria that apply to the conditions (e.g., hardness, pH, temperature) at the point of compliance (e.g., end-of-pipe or within the mixing zone for acute and 300 feet downstream from the discharge for chronic river) should be specified. Step 2: Specify the upstream or background concentration of the parameter of interest in the receiving water for the acute and chronic evaluations (e.g. at river flow of 7Q10). The 90th percentile concentrations during the critical season are recommended for a reasonable worst-case. If no data are available it would be desirable to collect data during the critical season to characterize upstream concentrations unless background can be considered negligible (e.g. background can usually be considered negligible for residual chlorine). Step 3: Enter the dilution factors that apply at the point of compliance with acute and chronic criteria (see Permit Writer's Manual section VI-2.1). Dilution factors should be defined as the reciprocal of the volumetric fraction of effluent present at the mixing zone boundary. If actual dilution factors have been reliably estimated from tracer studies or plume modeling, then those values should be entered in the spreadsheet. In rivers, the dilution factors for permit limit calculations should not exceed the dilution obtained from mixing the effluent design flow with 25% or 2.5% of the critical upstream river flow for chronic and acute criteria, respectively. If water quality criteria are required to be met at the end-of-pipe, then a dilution factor of 1 should be entered. Step 4: Enter the coefficient of variation for the effluent concentration of the parameter of interest (e.g., use 0.6 if less than 10 effluent samples are available). Step 5: Specify the number of days for the chronic average (EPA recommends using 4 days). Step 6: Specify the number of samples per month that the permittee will be required to report to monitor compliance with the permit. USER INSTRUCTIONS FOR THE OUTPUT SECTION The user does not need to enter or change any values or formulas in the Output Section. The spreadsheet calculates permit limits incorporating effluent variability using the method described in the EPA TSD. Estimated daily maximum and monthly average permit limits are calculated and displayed in the Output Section at Step 4 in the same concentration units used for water quality criteria. Return to top _________________________________________________________________