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
_________________________________________________________________