Spreadsheets for Water Quality-Based NPDES Permit Calculations

Updated November, 2010 by Greg Pelletier

Several spreadsheets were developed by the Washington State Department of Ecology to aid NPDES permit writers. These spreadsheets are referred to in Ecology's Permit Writer's Manual (Department of Ecology Publication Number 92-109). All of the spreadsheets are available in Excel workbooks. A detailed description and user instructions are available to download (128 Kb Word document).

The following spreadsheets are available to download:

PWSPREAD.XLS: This Excel file contains the following spreadsheets:
- dosag2 calculates critical sag of dissolved oxygen downstream from a point source using the Streeter-Phelps equation.
- idod2 calculates concentrations of dissolved oxygen at a mixing zone boundary accounting for dilution of dissolved oxygen and initial dissolved oxygen demand.
- nh3fresh3 calculates freshwater un-ionized and total ammonia criteria from temperature and pH from the formulas in WAC 173-201A as amended 20-Nov-2006.
- nh3salt calculates saltwater total ammonia criteria from temperature, pH, and salinity to meet the un-ionized ammonia criteria in WAC 173-201A as amended 20-Nov-2006.
- phmix2 calculates the pH of a freshwater mixture of two sources from temperature, pH, and alkalinity.
- phmix3 calculates the pH of a saltwater mixture of two sources from temperature, pH, alkalinity, and salinity.
- rivplum5 is a simple dilution model for rivers based on the method in chapter 5 of the book "Mixing in Inland and Coastal Waters" by H.B. Fischer et al. (1979, Academic Press Inc.)
FARFIELD.XLS: (requires the Analysis ToolPak Add-In under the "Tools/Add-Ins" menu): This Excel workbook calculates far-field mixing of a plume using the method of N.H. Brooks (1959, "Diffusion of Sewage Effluent in an Ocean Current," in "Waste Disposal in the Marine Environment", edited by E.A. Pearson, pp, 246-267, Pergamon Press, New York, NY.) This workbook is useful for estimating dilution beyond the range of near-field models such as UDKHDEN or PLUMES/UM using Brook's 4/3-power law, linear diffusivity, and constant diffusivity algorithms.
TSDCALC.XLSM is an Excel workbook composed of several spreadsheets. It is used for reference and for developing water quality-based permit limits. The spreadsheets CRITERIA.XLS and NH3FRESH3 contain or calculate water quality criteria. REASPOT.XLS, LIMIT.XLS, and HUMAN-H.XLS determine reasonable potential (to violate the water quality standards) and calculate effluent limits. PERFORMLIM calculates performance-based effluent limitations and accounts for autocorrelation if known. The individual spreadsheets contained in TSDCALC are:
- criteria.xls
- nh3fresh3
- reaspot.xls
- limit.xls
- performlim
- human-h.xls