Detention Pond Routing with an Excel Spreadsheet
Posted on Tuesday, November 29, 2011 at 9:22 PM

 Introduction

To obtain an Excel spreadsheet for carrying out detention pond routing calculations and plotting inflow and outflow hydrographs, click here to go to our download page.  Read on for information about the use of an Excel spreadsheet for storm water detention pond routing.

Overview

Detention pond routing is used to project an outflow hydrograph from a stormwaterdetention pond based on a given inflow hydrograph, stage-storage information for the pond, and stage-outflow information based on the outflow control device.  An output from the routing process is typically a plot of the inflow and outflow hydrographs similar to that shown at the right.  The outflow is often controlled by a rectangular weir, an orifice, and/or a pipe.  In some cases two-stage control is used with perhaps an orifice to provide outflow control for small storms and a weir to control the outflow rate from larger storms.  The routing process should be set up so that changes can be made in outflow control parameters and effects on the outflow hydrograph can then be observed.

Input Information Needed

In addition to an inflow hydrograph like that shown above, stage-storage and stage-outflow information is needed.  The stage-storage information would typically be in the form of a table, graph, or equation showing the pond volume, V, as a function of the pond depth, h.  The stage-outflow information is typically in the form of an equation for outflow, O, as a function of pond depth, h, based on the type of outflow control device, as described in the next section.

Stage-Outflow Equations

A rectangular weir is one possible outflow control device, often in a riser as shown in the diagram at the left.  The equation for pond outflow  is:     O = CdL(h – P)1.5 where the parameters in the equation are as follow:

  • O = pond outflow = discharge over the rectangular weir in cfs for U.S. units (m3/s for S.I. units)
  • Cd = the discharge coefficient for the weir.  Typical value for U.S. units is 3.3 (1.84 for S.I. units)
  • L = weir length in ft for U.S. units (m for S.I. units)
  • h = stage (depth of water in pond) in ft for U.S. units (m for S.I. units)
  • P = height of weir crest above pond bottom in ft for U.S. units (m for S.I. units)

Equations like this are also available for an orifice outlet, two stage outlet, and pipe outlet.  These equations are given and used in the Excel spreadsheets for pond routing from our download page.

The Storage Indication Routing Equation

In addition to the input information described above, a routing equation is needed.  A commonly used routing equation is the Storage Indication Equation:

0.5(I1 + I2 )Δt  +  (S1 – 0.5O1Δt)  =  (S2 + 0.5O2Δt) Where:

  • Δt is the time interval used for the inflow and outflow hydrographs in minutes
  • I1 and I2 are successive values of the inflow from the inflow hydrograph (cfs - U.S. or m3/s - S.I.)
  • S1 is the initial value of pond storage (pond volume at the beginning of the storm in cfs - U.S. or m3/s - S.I.)
  • O1 is the initial outflow rate at the beginning of the storm in ft3 - U.S. or m3 - S.I.)
  • S2 and O2 are the pond storage and outflow respectively at time Δt after the beginning of the storm in the same units shown above.

For a given inflow hydrograph, I1, I2 , and all subsequent values of inflow for the duration of the storm are known.  Thus if the initial pond volume, S1, and initial pond outflow, O1, are known, then all of the parameters on the left hand side of the equation are known so the value of the right hand side of the equation (S2 + 0.5O2Δt) can be determined.

Now comes the elegant part of the storage indication routing procedure.  As described above S vs h and O vs h must be available, in the form of tables, graphs or equations.  Thus for any value of h, the parameter, S + 0.5OΔt can be determined and values of S and Ocan be determined for a known value of S + 0.5OΔt.  Thus, by stepwise calculations, the outflow hydrograph (O vs t) can be obtained.

An Excel Spreadsheet as a Pond Routing Calculator

The Excel spreadsheet template shown below can be used to carry out the pond routing procedure described above.   Why bother to make these calculations by hand?  This Excel spreadsheet can handle rectangular weir, orifice, two-stage (orifice/weir), pipe outflow control, and two-stage (pipe/weir), and is available in either U.S. or S.I. units at a very low cost from our download page.  These spreadsheets also generate a table and graph showing the inflow and outflow hydrographs for a given set of input parameters.

References

1. McCuen, Richard H., Hydrologic Analysis and Design, 2nd Ed, Upper Saddle River, NJ, 1998.