Spreadsheets to Estimate Compensation Ratios Based on HGM Assessments

The two spreadsheets provided here can be used to help evaluate the impacts of wetland projects and estimate mitigation requirements. The spreadsheets were developed by Frank Hanrahan based on concepts presented by the U. S. Fish and Wildlife Service (1980) and King and Adler (1992). The first was written in 1999 using Corel Quattro Pro software and the second in 2001 using Microsoft Excel.

The spreadsheets use the results of an assessment of wetland functions based on the Hydrogeomorphic (HGM) Approach at an impacted site to calculate mitigation requirements at a restored or constructed wetland site. For each function, the spreadsheets calculate a Compensation Ratio or Functional Equivalency Ratio (i.e., the number of acres of mitigation site required per acre of original wetland impacted to achieve equivalency between loss and gain of function). These ratios are based on the functional capacity index (FCI) determined at the original wetland and the predicted FCI that will be achieved over time at the restored/created wetland.

In addition, the spreadsheets calculate a weighted average compensation ratio across all functions (the ratio with trade-offs) weighted by an index of the relative importance of each function in the study area or region. Thus, a function whose relative importance is 2 has twice the weight in a trade-off decision as a function whose relative importance is 1. At the present time, judgments about the relative importance of wetland functions are necessarily subjective. Options for establishing relative importance indices include (1) negotiation and consensus of concerned public agencies and stakeholders in a project, (2) soliciting opinions from an impartial panel by a procedure such as the Delphi technique, and (3) using a procedure that relates wetland functions to the flow of human services and benefits that accrue from those functions over time (e.g., King et al. 2000). If desired, all relative importance indices can be set to one (i.e., functions are of equal value). However, averaging across functions, even if they are equally valued, implies that unlimited trade-offs among functions are acceptable in developing a compensation ratio. Users may choose to ignore the ratio with trade-offs altogether and focus instead on the individual compensation ratios for each function.

A number of assumptions have been made to simplify the applications and to allow presentation of the spreadsheet template on a single page. If these assumptions do not match the circumstances of a particular study, the investigator may wish to use the more complex but flexible Expert HGM (EXHGM) software being developed as part of the Integrated Bio-Economic Planning System (IBEPS) for use by Corps of Engineers District offices.

Some of the important features of both spreadsheets include:

• They accept a maximum of ten functions. Additional functions can be analyzed in a separate spreadsheet run; however, they will not be included in the ratio with trade-offs.
• The balance between functions lost at the impacted site and functions gained at the mitigation site is determined over a period of years called the time horizon, which is specified by the user. A time horizon of 10 to 25 years may be appropriate for most applications, except for forested wetlands and others that may require long periods to develop high levels of function on a mitigation site. For these situations, a time horizon of 50 to 100 years may be needed. See the "User Notes" incorporated into each spreadsheet for additional details.
• The spreadsheets assume that all FCIs at the original wetland, in the absence of any project, would have remained constant over the time horizon. Thus, there is no provision for incorporating increasing levels of function due to vegetation growth or succession at the original wetland site. Similarly, post-impact FCIs at the original wetland remain constant at the specified post-impact level over the time horizon.
• Changes in FCI values at the mitigation site are linear between the "Year Started" and the "Year Matured." There is no provision for entering a different trajectory (e.g., a sigmoid growth curve).
• The spreadsheet allows the user to specify a discount rate, which is commonly used in economic analyses to express the idea that "a benefit to be received in the future is less valuable than the same benefit received today." Specifying a discount rate increases the weight given to levels of wetland function achieved sooner (i.e., increasing the discount rate decreases the "present value" of functional levels accrued in the distant future). If used, a risk-free, real interest rate is suggested, currently estimated to be between 3 and 4 percent. Before using, check with Headquarters USACE for their current policy on discounting in mitigation calculations. The discount rate can be set to zero. Additional suggestions are given in the "User Notes".
• If desired, the user may enter an estimate of failure risk (i.e., the probability that mitigation will not result in any increase in FCI beyond the date of mitigation work) for each function at the mitigation site. Increasing the failure risk can affect the compensation ratio by increasing the required number of acres of mitigation. The value of the failure risk should be based upon documented experience with wetland restoration projects in the local area or region. The failure risk may be set to zero.

Notes on the Quattro Pro Version
The Quattro Pro version of the spreadsheet provides a data entry error display that alerts the user to certain kinds of actual or potential errors in data inputs. For example, an "Error" will be displayed if the date of mitigation maturity exceeds the time horizon for the analysis. In addition, an "Error??" warning message will be shown if the discount rate or failure risks are less than one. This is intended to catch erroneous inputs of decimal proportions rather than percentages. However, the warning can be ignored if the discount rate or failure risks are intentionally set to zero.

As a check on the input data and to help understand the results of the analysis more clearly, the Quattro Pro version provides optional graphical output. An example graph with explanatory notes appears below the Outputs table of the spreadsheet, starting in row 100. Graphs requested by the user appear below the example graph. Graphical output is triggered by keying Ctrl-Shift-A for Function A, Ctrl-Shift-B for Function B, etc., and must be updated the same way if you make changes in the inputs table. The graphs display the following information over the time horizon: (1) yearly FCI at the impacted site, (2) yearly FCI at the mitigation site, (3) yearly acre-for-acre surplus or deficit in FCI (i.e., the difference between (1) and (2) above, standardized to an initial level of zero), (4) yearly surplus or deficit at the equivalency acreage ratio for that function, (5) the "present value" (PV₀) of that surplus or deficit at the specified discount rate (this is the same as (4) if the discount rate = 0), and (6) the cumulative present value of the surplus or deficit over the time horizon. The last value should approach zero by the end of the time horizon, indicating that the expected benefits of the mitigation will balance the impacts by the end of the period of analysis.

Caution:
The Quattro Pro version of the spreadsheet can give erroneous answers and graphics may not be possible when used with early releases of the Corel Quattro Pro 8 software. To check the release number of your software, click on "Help|About Corel Quattro Pro." Your program release number should be 8.0.0.611 or higher. If not, check the Corel web site for information on updating your software. Be sure to examine your output for obvious errors.

Notes on the Excel Version
The Excel version also provides limited error checking of input values but does not provide any graphical display of results. Detailed user notes are available by clicking on the "Notes" tab of the Excel workbook.

Reporting Problems
Please report any problems with the spreadsheets by e-mail to Jim Wakeley at James.S.Wakeley@erdc.usace.army.mil or by phone at 601-634-3702.

References
King, D. M. and Adler, K. J. (1992). "Scientifically Defensible Compensation Ratios for Wetland Mitigation". Effective Mitigation: Mitigation Banks and Joint Projects in the Context of Wetland Management Plans. Association of State Wetland Managers, 64-73.

King, D. M., Wainger, L. A., Bartoldus, C. C., and Wakeley, J. S. (2000). "Expanding Wetland Assessment Procedures: Linking Indices of Wetland Function with Services and Values," Technical Report ERDC/EL TR-00-17, U.S. Army Engineer Research and Development Center, Vicksburg, MS.

U. S. Fish and Wildlife Service. (1980). "Habitat Evaluation Procedures", Ecological Services Manual 102, Washington, DC.

Back to HGM Approach

Web Date: October 1997
Updated: April 2008