Handbook for Drinking Water State Revolving Fund Programs: Cash Flow Modeling to Maximize Efficiency and Use December 2020 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Table of Contents 1 Introduction 1 2 Advantages of CFM 3 2.1 Potential Benefits of CFM 3 2.2 Measures of DWSRF Program Efficiency 4 Fund Utilization 5 Disbursement Ratio 6 3 Principles of CFM 9 3.1 Basic Principles 9 3.2 Program Management to Optimize CFM 10 Principles of Prudent Fiscal Management 11 Strategies for Optimizing Loan Allocation Using CFM 12 4 Steps to Implementing CFM 14 Step 1: Establish the Time Frame of Analysis 15 Step 2: Identify Funds Available for Lending (Inflows) 16 Step 3: Identify All Funds Required During Each Period (Outflows) 17 Step 4: Develop a Conceptual Model 18 Step 5: Identify Data Sources and Collect Data 20 Step 6: Build the Summary Tab 22 Step 7: Construct the Shell and Build the Formulas 22 Step 8: Confirm the Data 23 Step 9: Enter Data 24 Step 10: Test the Model 24 Step 11: Interpret Results 25 Step 12: Update the Model Regularly 26 Step 13: Create Access and Backups 27 Appendix A: Example Model 28 How to Read this Appendix 28 Example Model Overview 29 Example Model with Sample Data 36 i ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook 1 Introduction The Drinking Water State Revolving Fund (DWSRF) was established by the 1996 Safe Drinking Water Act (SDWA) Amendments with the goal of providing a dedicated source of funding for states to finance water system infrastructure and to implement programs to help achieve the public health protection objectives of the Act. Priority for DWSRF infrastructure financing is given to projects to address immediate public health concerns; to achieve SDWA compliance; and to address household affordability issues according to state affordability criteria. The DWSRF program is a federal-state partnership that provides communities with low-interest loans and other assistance for drinking water infrastructure projects. Congress appropriates annual funding for the program, which EPA then uses to award capitalization grants to each state. Each state receives capitalization grants based on its needs, as measured by the results of the quadrennial Drinking Water Infrastructure Needs Survey and Assessment, with no state receiving less than 1% of the national allotment Each state provides a 20% match, which goes into a revolving fund along with the capitalization grants. The state DWSRF Program enters into assistance agreements with eligible systems for eligible projects. As the project is constructed, the state reimburses the borrower for expenses and, once the project is complete, the borrower repays principal and interest to the state DWSRF generally over 20 to 40 years. The loan repayments and interest flow back into the fund and are used to make more loans. A well-managed SRF can offer project financing in perpetuity. Cash Flow Modeling (CFM) is a forecasting tool that incorporates all inflows and all outflows of the funds to provide an estimate of a state's capacity to make loans, ensuring the fullest possible utilization of all available DWSRF infrastructure financing resources. CFM is a tool that DWSRF programs can use to organize information critical to making financial decisions and anticipate the availability of funds in future years. A CFM approach to fund Advantages of DWSRF Cash Flow Modeling Maximize annual lending capacity. Avoid accumulating excess idle cash which represents unrealized public health protection. Increase the speed with which funds revolve through the program. Informs efficient leveraging decisions. Better achieve Safe Drinking Water Act [SDWA] public health protection objectives. 1 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook management can help states develop a comprehensive picture of the status of direct loan and leveraged loan1 DWSRF programs, have meaningful discussions about future lending decisions, and optimize DWSRF annual planning. State DWSRF programs can use CFM to maximize the efficiency of their operations. A unique feature of the DWSRF is the optional authority that each state has to direct some of their annual capitalization grant into "set-asides." Set-asides are not part of states' infrastructure financing resources and are used by states to help water systems address compliance challenges that do not require capital investment For purposes of CFM, the key point is that set-asides do not revolvethey are (with minor exceptions) not paid back and represent one-time expenditures. A maximum of approximately 31% of a state's capitalization grant can be directed to set-asides, but few states maximize set-aside usage. The cumulative weighted national average set-aside was 16.4% as of June 30, 2019 (this means thatsince the inception ofthe DWSRF program in 1997, the cumulative weighted average annual set-aside use has been 16.4%). However, in the past 4 years, the annual weighted average use of set-asides has been over 20%, nearly 4 percentage points higher than the long-term annual weighted average. This Handbook is an introductory document to help states understand the basics of CFM, the data required for CFM, and the steps required to develop a model. Chapters 2 and 3 summarize why states should implement CFM and describe basic management principles that can maximize the effectiveness of CFM. Chapter 4 lays out the step-by-step process of CFM. Appendix A includes a series of figures from EPA's example cash flow model that demonstrate concepts addressed in Chapter 4. While reading this Handbook, states should keep in mind that each DWSRF program has unique characteristics that must be considered and incorporated into a cash flow model. For example, state policies regarding loan fees, penalties, and specific loan repayment requirements can affect the design of the model. This Handbook aims to assist the user in identifying the basic characteristics each DWSRF program should consider for CFM. However, CFM must be tailored to the specific characteristics of the program being modeled, and this Handbook does not provide a comprehensive evaluation of all potential data considerations a DWSRF program may face. After reading this Handbook, DWSRF program staff should have the information they need to begin the process of implementing CFM. This Handbook is geared toward programs that do not leverage. This Handbook refers to leveraged programs in a few instances, but these programs require more complex modeling than what is described in this Handbook. 1 Direct loan programs fund loans using federal and state matching grants and other payments to the SRF (e.g., loan repayments and interest earnings). A leveraged loan program uses federal and/or state matching grants to secure revenue bonds. Proceeds from these bonds are used to fund additional loans through the DWSRF program. States can also leverage their DWSRF programs by pledging DWSRF loan repayments to secure the bonds. Leveraged loan programs can substantially increase the amounts available to fund DWSRF- eligible projects when loan demand is high enough. 2 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook 2 Advantages of CFM CFM helps states optimize lending capacity, which is essential for maximizing the potential of the DWSRF program to protect public health. The statute and the regulations that established the DWSRF program direct states to funds as efficiently as possible and in an expeditious and timely manner."3 States that manage DWSRF funds efficiently are more likely to meet the DWSRF target of 100% draw-down of a capitalization grant within two years of award. Efficient fund management also helps to avoid accumulation of idle cash. Excess idle funds that remain in the state's accounts or the federal treasury slow the revolving fund process as repayment and interest from the loans these funds could be supporting are not flowing back into the fund. These idle funds also represent unrealized public health protection as they are not at work financing infrastructure projects. 2.1 Potential Benefits of CFM Implementing CFM enables DWSRF programs to efficiently use funds, optimize lending capacity, make informed decisions, reduce cash balances, increase funds revolving through the program, and ultimately, better achieve SDWA's public health protection goals. Optimize lending capacity. By using CFM, states can predict their cash balance in any given period and use that prediction to maximize the amount of funds they can lend. With a comprehensive picture of a DWSRF program's available funds, states can lend out as much money as prudently "promote the efficient use of fund resources"2 and "expend all Cash is for Invoices, Not Loans Loans are integral to the program because they are the legal structures through which projects are financed. When water systems submit invoices to the DWSRF for reimbursement, that signals that actual construction activity has occurred on the ground. Therefore, disbursements are generally a more accurate indicator of DWSRF program results. Using CFM, a manager can get a better idea of lending capacity. Rather than locking down large amounts of cash now for projects that may take many years to construct, a program can ensure it has the cash available for projects as it is needed for invoices and lend beyond the cash available at present. If managers think about cash in terms of invoice needs, instead of loan needs, DWSRF programs can stretch lending capacity considerably. 2 SDWA ง1452 (1) (A) 3 2000 Interim Rule - 40 CFR 35.3550(1) 3 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook possible, knowing enough funds will be available to reimburse systems when they submit invoices and pay costs associated with managing the DWSRF. Informed decisions. CFM provides a complete understanding of the status of the fund for informed decision making both in the short- and long-term. The model consolidates all the lending-related financial information about the DWSRF program into one location, structured such that information is easy to access. Therefore, states can anticipate how different lending scenarios will affect the lending capacity of the fund over the course of several years. The model can also help determine if leveraging is appropriate for an individual program by giving the state the ability to run lending scenarios with and without leveraging to understand its effect on the loan program. Reduce cash balances. A DWSRF program needs cash on hand to meet disbursement obligations to projects under construction. Excess cash, beyond that needed to meet projected disbursement obligations, with a margin of safety, may result in a state achieving less public health protection than possible. A few states may face state statutory, regulatory or policy provisions requiring more cash to be held on hand. The best practice being achieved by 13 states is a disbursement ratio of under two years. As will be discussed shortly, this means the state has enough cash on hand to meet disbursement obligations for two years. Increase funds revolving through the program. When DWSRF programs increase lending amounts and reduce the amount of idle funding in state and federal accounts, funds revolve more quickly through the DWSRF. When funds revolve more quickly, more loan repayments and interest are paid back into the DWSRF. With an increased volume of lending, a state DWSRF earns more interest and thus grows its fund to allow even more lending. Maximize public health protection. The benefits of CFM identified above enable DWSRF programs to make more and/or larger loans, maximizing their ability to protect public health. CFM thereby increases the amount of funds invested directly in infrastructure projects. As more and/or larger projects are financed, public health protection increases. 2.2 Measures of DWSRF Program Efficiency Early in DWSRF Program history, EPA and the states developed a suite of seven indicators for measuring the program's progress.4 The indicators were: Assistance Provided as a Percentage of Funds Available for Projects (also called Fund Utilization or Pace) Return on Federal Investments Disbursements as a Percentage of Assistance Provided Net Return after Forgiving Principal Net Return on Contributed Capital 4 US EPA Memorandum dated February 25,2003 entitled Implementation of DWSRF Financial Indicators. 4 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Set-Aside Spending Rate Estimated Additional DWSRF Assistance Provided Due to Leveraging In 2018, EPA and the states developed three additional indicators to those already existing.5 The three new indicators are: Ratio of Undisbursed Project Funds to Disbursements (also known as Disbursement Ratio) Total Net Net Interest Margin Of this total set of 10 indicators, fund utilization and disbursement ratio are most relevant to this Handbook. Fund Utilization Fund utilization, or pace, is an indicator that estimates how quickly funds are committed to finance DWSRF projects. Fund utilization is typically reported as a percentage, and it is calculated as follows: Fund Utilization (Pace) = (Assistance Provided / Funds Available) Simply put, fund utilization considers all funds signed into assistance agreements divided by all funds available for assistance agreements. Fund utilization may be calculated on an annual or cumulative basis. Generally, focus has been on cumulative pace as the best indicator of long-term program performance. If calculating cumulative fund utilization, the above formula must use cumulative assistance provided and cumulative funds available. High fund utilization indicates high efficiency at entering into assistance agreements. However, taken alone, pace can be apparently high (for example, greater than 95%) yet disbursements can be quite low. This occurs when projects being financed are not proceeding to construction or are proceeding slowly through construction. The importance of pace as a program management metric makes clear the importance of understanding the constituent metrics used to calculate it. Assistance provided. This value represents the dollar amount of DWSRF assistance provided via executed assistance agreements. If calculating cumulative fund utilization, cumulative assistance provided is calculated as follows: Cumulative Loan Commitments + Cumulative Refinance of Short-Term Debt + Cumulative Refinance of Long-Term Debt + Cumulative Guarantee or Purchase of Insurance } 5 Memorandum dated April 26,2018 entitled Implementation of Additional SRF Financial Indicators. 5 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Funds available. The calculation for funds available includes only funds made available for projects, including net transfers and excluding amounts reserved for set-asides. If calculating cumulative fund utilization, cumulative funds available is calculated as follows: Cumulative Federal Capitalization Grants Awarded + Cumulative State Match Deposited +Cumulative Net Transfers with CWSRF - Cumulative Net Amount Reserved for Set-Asides + Net Funds Provided by Leveraged Bonds + Net Loan Principal Repayments + Net Interest Earnings after Repaying Match Bonds Public health protection is delivered when projects are constructed and placed into operation. In order to complement the pace metric, which measures efficiency at entering into assistance agreements, the disbursement ratio metric was established to measure the efficiency with which funds are disbursed to assistance recipients for project construction. Disbursement Ratio A key component of strategic DWSRF fund management is the balancing of cash inflows and outflows. The disbursement ratio evaluates how efficiently SRF funds are revolving (and thus, balancing inflows and outflows) by examining a program's disbursement rate over a set time period and comparing it to the amount of cash on hand. The results of this measure reflect the number of years it would take to spend the cash on hand assuming that future disbursements are consistent with average annual disbursements over the past 3 years. The disbursement ratio is expressed in terms of years and defined as follows: Disbursement Ratio = Undisbursed Project Funds / 3-year Average Annual Disbursements The disbursement ratio is a snapshot in time. Undisbursed project funds is a value as of a specific date. The value can change from day to day over the annual cycle of DWSRF operations. The most significant changes will occur with major events such as draws on a capitalization grant, a leveraging action, or any other action bringing in or disbursing a relatively large amount of cash. The disbursement ratio at a single point in time is not, in itself, especially insightful. However, for reasons more fully developed below, the disbursement ratio at a single point in time and its trend over time are quite informative when considered together. Because disbursements fluctuate from year to year, the ratio is calculated based upon an average of annual disbursements over the past three years. This approach smooths out year-to-year variations in disbursement amounts. Some events can make the undisbursed project funds "N > Cumulative Funds Available J 6 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook uncharacteristically high if they occur just before the snapshot of undisbursed funds is taken. For example, an infusion of cash from a capitalization grant award or leveraging event could substantially increase the program's undisbursed project funds. Such a timing coincidence tends to be a one-off event. Even if it happened regularly the disbursement ratio should be low if the average 3-year disbursement value represents the regular disbursement of that amount from year to year. The only way the disbursement ratio can increase over time is through accumulation of undisbursed project fund cash and/or a decrease in the 3-year average annual disbursement The lower the disbursement ratio, the more efficiently the state is moving dollars into actual infrastructure. A low disbursement ratio indicates great efficiency in revolving project funds. As repayments and interest flow into the corpus they are quickly disbursed to other projects. The DWSRF achieves enhanced public health protection when it gets projects built and into operation. A high disbursement ratio indicates that a program is not fully or efficiently utilizing its project funds. Such programs are not lending up to their full potential. A high disbursement ratio may be caused by excessively conservative lending practices and/or low demand for assistance. A state with a high disbursement ratio could benefit from carefully reviewing its program and determining how it can more efficiently revolve its project funds. It is equally important to track the disbursement ratio over time and observe the direction in which it moves. A state with a low but upward trending disbursement ratio may want to examine why the ratio is trending upward and identify how to keep it low. Figure 1 depicts DWSRF disbursement ratios using the average disbursement levels for each state6 in 2016, 2017, and 2018. Disbursement ratios in this figure have been calculated by dividing a DWSRF program's undisbursed DWSRF cash in 2018 by the program's average disbursement amount in 2016, 2017, and 2018. 6 The data set includes the 50 states and Puerto Rico. 7 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook 25 20 What is a sensible target? States known to use CFM 5 0 DWSRF Programs Figure 1. State disbursement ratios. The median disbursement ratio in Figure 1 is about 4 years. Fourteen states have disbursement ratios of 5 to 22 years. Twenty-four states have disbursement ratios between 2 and 5 years; while 13 states (23% of states) have achieved disbursement ratios of under 2 years. Because of the unique factors influencing practice and performance in each state, the individual state disbursement ratio must be evaluated thoughtfully. Some states may face significant challenges in achieving a disbursement under two tears while other states may easily be able to achieve a disbursement ratio well under one year. States should focus on achieving as low a disbursement ratio as possible consistent with state requirements and prudent fund management. With careful fund management and a focus on achieving a low disbursement ratio, it is possible to achieve fund utilization rates that exceed 100%. A utilization rate above 100% indicates that the total value of the state's loan commitments exceeds the DWSRF funds available today. It is possible for a DWSRF program to safely maintain a utilization rate greater than 100% for several consecutive years when it is able to predict cash flows and balances several years into the future. For example, the state knows that it can make a loan commitment this year and that it will be paid out over the next three years. When the state monitors its cash flows closely, it can make the commitment even if it does not have enough cash on hand today. It knows that future repayments will be available to make disbursements on the loan and other commitments when needed. CFM is a necessary tool for making these cash flow predictions and enabling states to safely maintain a fund utilization rate greater than 100% for several years (but not indefinitely). 8 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook 3 Principles of CFM 3.1 Basic Principles The basic principle of CFM is to evaluate the flow of all funds into and out of the program to estimate the amount of funds that will be available for DWSRF loans. CFM incorporates program- specific information to account for all income and expenditures of the fund over a period of time. This information is then used to generate an estimate of the DWSRF program's lending capacity. Understanding the timing of and tracking all incomes and expenditures is essential to CFM because the reliability of the results of the model will depend in part on the accuracy and completeness of the inputs (i.e., inflows and outflows). Maximum lending capacity is determined by the balance of these inflows and outflows on a periodic basis. Figure 2 illustrates the basic principle of CFM. Figure 2 portrays a simplified version of the cash flow element associated with cash balances in an SRF. Money enters the state revolving fund in the form of: capitalization grants; state match; interest on corpus (i.e., interest earned on the cash balances in the fund); principal and interest payments on loans; and bond disbursements (for leveraged programs). Money leaves the SRF in the form of: loan disbursements and bond repayments (for leveraged programs). The arrows circling back to the revolving fund demonstrate the revolving nature of the loan funds. Bond Disbursements Bond Repayments Loan Loan Loan Loan Loan Interest on Corpus State Match Annual Capitalization Grant Each loan results in disbursements (outflows), principal repayments and interest on loans (inflows) DWSRF Revolving Fund Figure 2. Tracking and anticipating inflows and outflows of an SRF is complex and requires CFM. Cash balances also depend on the timing of these inflows and outflows, which can cause account balances to fluctuate on a daily basis and quickly become difficult to track. For example, disbursements to loan recipients, principal repayments, interest on loans, and bond repayments 9 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook occur at different times, and with differing regularity, throughout the year. In contrast, capitalization grants and state matching funds are typically deposited once per year. All inflows and outflows have differing timelines that should be taken into consideration. When programs are running smoothly, leveraging - or issuing bonds to increase available cash to the fund - can be incorporated on an as-needed basis. This will create an inflow to the fund. All of these inflows can be combined to provide the outflows necessary to meet loan commitments. CFM accounts for all these inflows and outflows to provide a more accurate understanding of the available funds. Eventually, a program using CFM may find that it is fully maximizing its inflows and may consider leveraging to better meet demand from prospective loan recipients. Leveraged funds must consider additional inflows and outflows, such as bond disbursements and bond repayments, compared to non-leveraged funds. CFM allows states to improve their lending capacity compared with the historical approach to DWSRF management. Under a historical project-to-grant model as depicted in Figure 3, states obligate annual capitalization grants to specific projects based on the size of the capitalization grants and state match. This approach limits states' lending potential and resembles management of construction grants rather than loans. The project-to-grant model in Figure 3 only accounts for basic inflows and outflows and does not consider loan repayments revolving into the DWSRF, as shown in Figure 2. The project-to-grant approach establishes a direct tie between capitalization grant funds and specific projects, assuming that lending cannot exceed capitalization grant and state match amounts. It does not provide the necessary information to clearly understand the status of funds. Consequently, the project-to-grant approach can adversely impact the aim of the DWSRF program by hindering pace and lowering the potential for public health protection. Inflows Outflows Leveraqi 3.2 Figure 3. Elements of SRF managed using a project-to-grant model Program Management to Optimize CFM CFM is one component of an overall sound DWSRF management strategy and is most effective when implemented with other basic prudent management principles. Principles of fiscal management 10 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook and strategies that can help a DWSRF program optimize loan allocations when using CFM are discussed below. Principles of Prudent Fiscal Management To maximize the effectiveness of CFM, it should be accompanied by implementation of basic fiscal management principles. "Cutting the tie," maximizing the strategic use of fungibility, and using the "first in, first out" approach will likely improve the effectiveness of CFM. Principle 1. Cutting the Tie. "Cutting-the-tie" refers to removing the link between specific DWSRF loans and specific capitalization grants. Managing cash flows and optimizing the DWSRF fund is made possible by cutting the tie. As previously discussed, DWSRF programs have historically obligated annual capitalization grants to specific projects based on the size of the capitalization grants. Under this historical model, programs forgo many of the benefits that a revolving fund offers, and they frequently exhibit high amounts of unliquidated obligations (ULOs). Attaching loans to specific funds results in substantial excess cash that remains undrawn until projects are complete, which can extend up to five years. Additionally, the traditional model prevents the state from viewing the overall status of the fund and understanding lending predictability. Cutting the tie between capitalization grants and specific projects increases lending flexibility and helps states get money out and invested in communities. It also supports the CFM principle of pooling funding to determine lending capacity, as depicted in Figure 2. If done well, managing a program on a cash flow basis can allow states to commit funds to more projects, creating greater benefits in public health and the environment. Principle 2. Fungibility. Fungibility refers to flexibility of funds and the extent to which state programs can substitute one funding source for another. All DWSRF sources are fungible, meaning states can reimburse a borrower from any DWSRF source whether that be capitalization grants, state match, interest, or repayments. This allows the program greater control and facilitates CFM by allowing funds to be allocated where they are needed. DWSRF has significant flexibility in this regard. For example, states can disburse money from their Using Fungibility to Reduce ULOs A strategic approach to deciding which source to draw from can help states reduce ULOs and meet all obligations. For example, states can reduce ULOs more effectively by: corpus and later reimburse state funds by drawing on a federal capitalization grant. Despite the large amount of Drawing only from capitalization grant funds after a grant is received, and flexibility allowed by SRF programs, there are some Using state funds for loan disbursements and repaying those funds with federal funds limitations in the use of DWSRF funds, such as with reprogramming set-aside V. when a capitalization grant is received. 11 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook amounts. It is not common to reintegrate unused set-aside amounts into the main DWSRF fund, and a formal request to EPA must be made to gain approval to reprogram set-asides. Principle 3. First In, First Out. First In, First Out (FIFO) is an accounting concept in which the funds acquired first are used first. There are several sources within SRFs that can be drawn upon to reimburse project expenditures. It is good practice to draw federal capitalization grant funds and state match funds before other funds. Furthermore, disbursements should be made with the oldest available capitalization grant funds to close them out in a more-timely manner. By drawing down the oldest grants first, FIFO is key in helping states to meet the ULO policy requirement that states can have no more than two open capitalization grants at any one time. Strategies for Optimizing Loan Allocation Using CFM In addition to the above fiscal management principles, there are three strategies that can help a DWSRF program optimize loan allocations once CFM is in use. These include promoting project timeliness, maintaining a robust list of projects, and building program demand. CFM will help the program identify a lending goal based on the modeled lending capacity. These strategies will help the program meet that lending goal. Strategy 1. Promoting project timeliness. A timely disbursal of DWSRF funds relies on projects proceeding on time and construction remaining on schedule. DWSRF loan funds or set-aside funds can be used to support water systems from project conception to planning and design to ensure systems are fully prepared to proceed to construction. When states provide financial support to systems for planning purposes, awarding these funds separately from construction funding avoids money sitting in the DWSRF account before construction begins. Awarding construction funding only when projects are completely prepared to begin construction will help ensure that funds are used when they are needed. Strategy 2. Maintaining a robust list of projects. Even if states are effective in promoting project timeliness, it is unlikely that every project proceeds according to schedule. If planned projects are not ready to receive their loan at the scheduled time, and if the state does not have other projects to which loans can be disbursed, the benefits of CFM will be limited. To prevent this situation, states can develop a robust, realistic list of high priority, ready-to-proceed projects. This pipeline ensures that the state has enough loan demand ready to enter into signed agreements, and it ensures expeditious use of funds as it identifies the order and readiness of future projects. When planned projects experience unanticipated issues that delay their readiness for funding, states can quickly advance other ready-to-proceed projects from their list. The cumulative value of the projects on the list should exceed the anticipated amount of assistance the state expects to provide in the given timeframe to ensure states are able to meet their funding targets. A pragmatic approach to projecting loan amounts that avoids over inflating will help states develop a realistic list. If water systems ultimately sign for lower amounts than initially planned for, the state will have a difficult time meeting their funding target 12 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Strategy 3. Building program demand. States can ensure they have a well-developed fundable list of projects by conducting outreach so that water systems are well aware of the financing opportunities the DWSRF provides. When water systems are well informed of the DWSRF program, the state will have a higher demand for loans. As a result, the state will have more eligible projects to include on their fundable list Consulting engineering firms that work on water infrastructure programs can be valuable partners in generating demand for the DWSRF program, as both the engineering firms and the DWSRF benefit from encouraging more water infrastructure projects in the state. To further develop project lists, states may also encourage systems with high need to seek support from the DWSRF program. For example, sanitary surveys collect information about public water systems and are a useful source for identifying potential borrowers. Those who conduct the surveys in each state, along with PWSS managers and capacity development coordinators, may know of eligible systems that could apply for DWSRF funds. DWSRF and PWSS programs have the same goal of protecting the public's health, and so fostering a culture of interaction between the programs will better their ability to address drinking water concerns. In addition, the state's Drinking Water Needs Survey Coordinator can help provide information about survey results, which already identify DWSRF-eligible projects needed at water systems. 13 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook 4 Steps to Implementing CFM The steps to implementing CFM generally fall under three primary phases: planning, building, and testing and maintaining (Figure 4). The initial steps in the planning phase typically can be accomplished relatively quickly. While straightforward, these steps are critical to developing a model that suits the program's needs and addresses the program's unique qualities. The building phase requires diligence and careful thought to construct an effective model. Structuring the model in a prudent way and conducting quality assurance throughout the process is crucial to generate accurate results. The test and maintain phase involves ongoing time and effort In this phase, states develop a deeper understanding of the model and ensure the long-term success of CFM for their SRF program. Plan Build Test & Maintain Step 1: Establish timeframe Step 6: Build results tab Step 10: Test model Step 2: Identify inflows Step 7: Construct shell Step 11: Interpret results Step 3: Identify outflows Step 8: Confirm data Step 12: Regular updates Step 4: Conceptualize model Step 5: Identify data sources Step 9: Enter data Step 13: Manage access and create backups Figure 4. Overall process that should be followed to implement CFM. 14 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Plan Step 1: Establish timeframe Step 2: Identify inflows Step 3: Identify outflows Step 4: Conceptualize model 9 Step 5: Identify data sources Step 1: Establish the Time Frame of Analysis Timeframe. The first step in implementing CFM into an SRF program is establishing the time frame of the analysis, which is how far out in time the model projects lending capacity. Considering loan disbursement schedules and loan repayment periods will help in determining a proper timeframe because the timing of these key inflows and outflows will determine the structure and duration of the analysis. The model should extend at least as far out as the longest loan term. Most programs' loan repayment terms are generally 20 years but can be as long as 40 years,7 so the model may need to extend several decades into the future to account for the long-term effect of a loan issued in the current period. Starting Date. The starting date is important for determining which data the state will enter into the model. A start date that coincides with the most recently completed fiscal year may be most logical. Using the start of the fiscal year will provide the state with final data on loans and account balances, which can be used as a reliable starting point for the model. The state will then need to fill in data for any periods since the selected starting date. Period. The periods are the time intervals in which the model will evaluate inflows and outflows. States should consider the timing of key inflows and outflows to determine the most appropriate period. The period of analysis may be annually, quarterly, monthly, or another length of time based on the state's reporting periods and what is best for the program. All inflows and outflows occur on their own time scale, so when deciding on a period, states should consider: When and how often disbursements are processed and paid When capitalization grants are awarded to the states When state match funds are added to the SRF accounts When and how often bond disbursements are paid (for leveraged programs only) 7 The Safe Drinking Water Act [SDWA] Amendments in America's Water Infrastructure Act [AWIA] of 2 018 extended the maximum-authorized DWSRF loan amortization period from 20 years to up to 30 years for any DWSRF-eligible recipient or from 30 years to up to 40 years for state-defined disadvantaged communities, or design life, whichever is shorter. ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Step 2: Identify inflows Step 1: Establish Timeframe Plan Step 3: Identify outflows Step 4: Conceptualize model * Step 5: Identify data sources Step 2: Identify Funds Available for Lending (Inflows) After determining the timeframe of the analysis, programs should identify all potential sources of funds for lending. These will comprise the model's inflows. Reviewing the various funds available for lending is relatively straightforward but requires careful thought to ensure that every possible source is identified. Sources may include: Federal capitalization grants - Funds are appropriated by Congress and awarded to states based on the results of the most recent Drinking Water Infrastructure Needs Survey. The model should include unencumbered funds from all current and previous grants. Set-aside funds - States may set aside up to approximately 31% of funds from federal capitalization grants to be used to provide technical assistance and capacity development to water systems. If the state submits a formal request to EPA to change the terms of the capitalization grant, unused set-aside funds may be moved to the infrastructure loan fund and should be included when identifying inflows. State match - Upon receiving the capitalization grant, all states provide a 20% match to the amount of the grant. Principal and interest repayments from projects - States can predict how much borrowers will repay to the SRF based on the loan amount, repayment term, and interest rate set for the loan. If applicable, the state can consider its average default rate on loans when predicting loan repayments. Interest earnings on corpus - States account for the interest they will accrue on their repayment and state funds based on their interest rate. Fees and penalties - If there are any fees to borrowers during the period, such as origination fees, these should be itemized. States can review their historic fee and penalty records to estimate annual averages to include in the model. Bond disbursements - If a state has a leveraged program, these are also a source for potential lending. When considering inflows, understanding the timing of each is important for the model's accuracy. Each state has different timing considerations, and the model should be tailored accordingly. For example, loan repayment schedules vaiy from state to state. Some states may require that repayments begin before the loan has been fully disbursed, while in others repayment may begin some predetermined time after disbursements are complete. Even within the same state, timing may vary from loan to loan. 16 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook As part of this step in the planning process, states may find it useful to identify the timing of each inflow to understand how each inflow may be best integrated in the model. Accounting for Fluctuations in Capitalization Grants When states begin their cash flow analysis, they may not know the amount of future federal capitalization grants, which introduces uncertainty. If a state's timeframe of analysis begins at the start of a state fiscal year, they will not know the size of that year's capitalization grant The prior year's capitalization grant can be used as a starting point for the analysis. Estimates of future capitalization grants can be adjusted to understand how changes in federal funding could affect lending capacity. If planning periods are calendar quarters, the capitalization grant can be included in the period that it arrives in the state. It is also best practice to incorporate the state match when it occurs. To understand a program's lending capacity strictly based on loan repayments, states should begin by estimating loan capacity without the future year's capitalization grant. The largest source of funds for new loans is the projected repayment of existing loans. Incorporating existing loan repayments only, excluding capitalization grants, will provide a conservative estimate of baseline lending capacity. Plan Step 1: Establish timeframe Step 3: Identify All Funds Required During Each Period (Outflows) Similar to identifying inflows, states must identify all funds required during each period that will factor into the model as outflows. Outflows will include: Step 2: Identify inflows Step 3: Identify outflows Step 4: Conceptualize model ~ Step 5: Identify data sources Disbursements to water systems - These will make up a large portion of outflows, and in some states, may be the only outflows. Set-asides - States should note that set-asides will not revolve back into the SRF. Additional subsidization - Within the DWSRF program, there is a discretionary subsidy program for "disadvantaged communities" as defined by the state based on publicly-reviewed criteria approved by EPA. Under this provision, states can provide a subsidy to those 17 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook communities of up to 35% of a capitalization grant in the form of negative interest rates or principal forgiveness.8 Elements to consider under a leveraged program might include: Bond repayments o Principal o Interest Fees associated with leveraging o Closing costs o Bond counsel fees o Underwriting fees o Rating agency fees States with non-leveraged programs will not need to consider these outflows. States may find it useful to organize all outflows and their time period in an identical structure to the table used for inflows. After identifying all available funds and existing obligations within the predetermined period, states will have a basic understanding of the funds available for new loans. Step 4: Develop a Conceptual Model Next, states will need to organize the information they gathered in the previous steps to determine how inflows and outflows will be connected in the model. Excel and other spreadsheet-based software are well suited for CFM because they are readily available, easy to update, and able to support the formulas needed for CFM. This Handbook uses Excel to demonstrate CFM basics, but states can choose to use other software that better fit their needs. Prior to creating the actual model, it may be helpful to develop a data map or other visual representation of all items that will be incorporated into each tab of the Excel spreadsheet. The data map graphic in Figure 5 demonstrates a conceptual sketch of the model. This sketch shows where various pieces of information will fit into the eventual Excel file and where data from one tab will inform data in others. The foundation of the model is an accurate summary of loans, as demonstrated in the Loan Summary tab at the far right of the figure. In this tab, the state will need to document all loans that are actively disbursing or in repayment. The tab should specify basic loan characteristics (e.g., borrower, interest rate, repayment period), and include the current status of each loan (i.e., where each loan is in the disbursement or repayment process). The status should Plan Step 1: Establish timeframe Step 2: Identify inflows ~ Step 3: Identify outflows Step 4: Conceptualize model Step 5: Identify data sources 8 The SDWA Amendments provided in the AWIA of 2 018 require states to use at least 6 percent but no more than 35 percent of the capitalization grant amount for additional subsidy for state-defined Disadvantaged Communities. Prior to AWIA, the SDWA set the floor at zero percent and the ceiling at 30 percent. 18 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook also include an estimate of when loans will move from actively disbursing to the repayment process. This information will inform detailed projections of loan amortization in the Loan Amortization tab and disbursements in the Projected Loan Disbursement tab. These data feed into the Inflows and Outflows tabs of the model. Finally, all the inflows and outflows feed into the Results tab, which can be used to determine the state's annual lending capacity and projected lending amounts. Projected lending then flows back into the Loan Disbursement and Loan Amortization tabs to inform long-term disbursements and loan repayments. Initial Funds Available Inflows Outflows Annual Lending Capacity Inflows Tab Beginning Annual Funds Available Capitalization Grant and State Match Interest Earnings on Corpus Estimated Loan Repayments Interest on Actively Disbursing Loans Loan Fees and Penalties Total Estimated Inflows Outflows Tab Loan Disbursements Set-Asides Additional Subsidization Total Estimated Outflows Loan Amortization Amortization on Loans in Repayment Est. Interest on Loans Actively Disbursing Est. Amortization on Future Loans Total Estimated Loan Repayments Basic Loan Information Status of Current Loans Projected Loan Disbursements Disbursements on Loans Actively Disbursing Disbursements on Future Loans Total Estimated Disbursements Figure 5. Example of the conceptual model prior to spreadsheet development. 19 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Plan Step 1: Establish timeframe Step 2: Identify inflows Step 3: Identify outflows Step 4: Conceptualize model Step 5: Identify data sources Step 5: Identify Data Sources and Collect Data After identifying the types of data needed, states should identify potential sources of inflow and outflow data to include in the model. Data collection is one of the most critical and potentially difficult steps in the model development process. Some data may be decentralized and maintained by different departments or people in the SRF program. Other data may be easily attainable, such as the status of loans. These data may be in a centralized data management system, such as the Loan to Grants Tracking System (LGTS), the state's loan tracking system, or already in spreadsheet form. Other data may be less readily available or altogether unavailable, such as future disbursements. Future disbursements may be provided by the loan recipient in a disbursement schedule, estimated by the DWSRF program, or may not exist and will need to be created. Data gaps and uncertainties will require some approximation based on the best information available to the state at the time the model is developed. States can use CFM as a tool to better understand those data and, over time, improve disbursement projections. As the state collects data, it should think through some important elements of the data and consider how they could affect the model. Developing the model to facilitate the type of data entry necessary will help with long-term use and maintenance. For example, if data are exported from a system, states will want to design the model to match this export structure to facilitate data entiy, quality assurance, and updates. States should consider all their data sources to plan how each will be integrated into the model. Some sources may need to be entered manually, while some may be transferable from existing databases. 20 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Regularity and irregularity of data sources refer to the predictability of model inputs. Irregular data, such as disbursements, may be less predictable. In contrast, loan repayments may occur on a regular schedule, such as monthly or quarterly. Any data gaps and uncertainties will require some approximation based on the best information available to the state at the time the model is developed. States can use CFM as a tool to better understand those data and, over time, can improve their estimates. Developing Disbursement Projections Projections of loan disbursements may be the most challenging to identify or predict. For actively disbursing loans, project delays can make the disbursement process slower than anticipated. For future loans, disbursements are even more uncertain because the loan must be finalized, and the project must begin before disbursements are made. Nevertheless, there are methods for approximating future disbursements for the purpose of CFM. Ideally, states would have planned disbursements for all assistance agreements, which could be entered into a spreadsheet to show planned disbursements by period for the duration of each assistance agreement. In practice, this information may not be readily available. Information may be attainable from the loan recipient as an estimated disbursement schedule. Alternatively, the state's SRF office could access historic disbursement data and develop reasonable projections for current and future loans. Although time consuming, mining past disbursement data may provide the best prediction. The table below is an example of an SRF's historic disbursement schedule. A similar schedule can be used to project that program's future disbursements. Example Average Percentage of Loan Disbursed by Year and Loan Size Loan Size Year >$25M $10M - $25M $1M - $10M <$1M 1 20% 29% 34% 59% 2 40% 38% 50% 41% 3 22% 20% 16% 0% 4 10% 13% 0% 0% 5 8% 0% 0% 0% Total 100% 100% 100% 100% 21 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Step 6: Build the Summary Tab The model's summary tab will display the CFM summary information and identify the key pieces of information resulting from the model. When planning the structure of the summary results tab, states should determine how to best present results based on the type of data used throughout the model. Before conducting the analysis, thinking carefully about the format and presentation of the results will help maximize the benefit of the CFM process. States may need to track data quarterly but only wish to evaluate results on an annual basis, or they may wish to view quarterly lending capacity. How often they want to evaluate results will affect the structure of the tab. In determining the results tab's structure, states should consider their desired level of detail. For example, the results tab may show only the initial funds available, inflows, and outflows. For states that desire greater detail, the results tab may also include additional detail about inflows by breaking them down into the amount in capitalization grants, state match, and loan repayments that are received during a quarter. Similarly, the outflows can be further specified by identifying the amount estimated for future disbursements. Figure A-l in Appendix A shows an example of a results tab that gives an overview of inflows, outflows, and annual lending capacity. Step 7: Construct the Shell and Build the Formulas Setting up a robust model structure will allow for ease of development, use, and modification later on. Once the results tab has been developed, states can build the structure of the rest of the model. This process can occur prior to data entry, and includes determining necessaiy rows, columns, and tabs to include in the model. Using the conceptual model described in Step 4 as a guide, states can develop each of the other tabs in the Excel spreadsheet. Figure A-2 through Figure A-8 in Appendix A provide an example of what a shell may look like in Excel at this stage in the model development process. Determining the necessaiy rows, columns, and tabs of the spreadsheet before entering data will be useful, although the format of the spreadsheet should be constructed based on the data the state will input into the model. The timeframe and periods decided on in Step 1 will determine how far into the future the model will extend. The inflow and outflow data collected in Steps 2 and 3 will determine which and how many data fields are necessary. As states construct the model shell, they may find it helpful to identify the calculations and corresponding formulas they will need later on in model development as a way to anticipate a logical structure of the model. For example, Build Step 6: Build results tab Step 7: Construct shell ~ Step 8: Confirm data Step 9: Enter data Build Step 6: Build results tab Step 7: Construct shell Step 8: Confirm data * Step 9: Enter data 22 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook periods should be organized in a similar fashion on each tab to make it easy to develop formulas that draw from other tabs. Once the shell has been constructed, the state is ready to begin creating the formulas. When developing the formulas, states should consider what results they want to generate based on the results tab created in Step 6. Creating formulas in a way that they can be easily copied across columns and down rows will help with the development process and allow states to easily check the formulas. Step 8: Confirm the Data Prior to entering data into the model, it is critical that states carefully review and confirm their accuracy, regardless of the data entry method. The accuracy of the model depends heavily on the accuracy of the data entered. The results of the model may guide funding decisions for the period; therefore, a careful review of data for accuracy is an important step of CFM. A generally reliable approach to quality assurance is having two different staff members thoroughly review the data before they are entered in the model. The following quality assurance techniques may help the state in this process. Do the data look reasonable? A basic check to ensure the data look reasonable can catch several types of data entry issues or irregularities. Outliers may be data entry errors. For example, if there is a loan term of 200 years, it is likely it was meant to be 20. Additionally, states should ensure totals appear to add up to reasonable amounts given the program's lending practices. This step may be especially important for modeled data, which may be more susceptible to errors. Are the data complete? Examining the data for completeness can identify errors in the export or data collection process. States may need to return to source data to identify what is missing. This step can also identify potential gaps in source data that should be filled. All signed loans and agreements should be accounted for. Are all data fields defined and understood? Ensure that all fields in the file are understood and those developing and modifying the file clearly comprehend how the data fit into the model. As part of this process, the state should review units to confirm they are accurate. Through this process, the state should be able to identify and address any inconsistencies in the data. Once two staff members have reviewed the data and certified that they are correct, the data are ready to be entered in the model. Build Step 6: Build results tab Step 7: Construct shell Step 8: Confirm data Step 9: Enter data 23 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Build Step 6: Build results tab Step 7: Construct shell Step 8: Confirm data Step 9: Enter data Step 9: Enter Data Once data have been collected and confirmed, it can be carefully entered into the model. Data may need to be hard-keyed into the model, pulled from other databases in batch uploads, or transferred from separate, newly-created data models used to generate necessary data points. States should ensure that formulas are preserved as data are entered. After the file has been populated, states should confirm that data were entered correctly. Using two or more staff to confirm the data entry can save significantly more time and effort later should the data - and subsequent calculations - be incorrect. Figure A-9 through Figure A-16 provide examples of sample data entered in the tabs of the model. These data were generated for illustrative purposes and include summary rows to simplify the process. For example, in Figure A-9, loans awarded in previous years are consolidated in a single row for each year. However, a state developing its model will likely need to itemize each loan on a separate row to account for the unique characteristics of each loan and improve the accuracy of the model. States can organize Steps 7 through 9 in the way that makes most sense for them. For example, the person developing the model might choose to first enter data and then create formulas and manipulate the structure of the model around those data. As long as these steps are implemented methodically and with care, the order in which they are completed does not matter. Test & Maintain Step 10: Test model Step 11: Interpret results Step 12: Regular updates Step 13: Create access and backups Step 10: Test the Model Once the model has been created, states should test the model to confirm data and learn how to use it. To test the model, states should complete the following checks: Ensure correct data entiy. o Confirm that all data migrated or were entered properly. o Do a visual check to see if there are any glaring issues. o Compare sums and totals in the model to the original data source to confirm consistency. Spot check formulas o Check that formulas work as intended and populate through the model. o Confirm that links within formulas draw from the correct locations. o Check ranges to ensure that the entire range is included in the formula and no important data are omitted from calculations. Identify and address any value errors o Determine why negative numbers may be present and fix them. 24 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Test & Maintain Step 10: Test model Step 11: Interpret results ts Step 12: Regular updates ~ Step 13: Create access and backups o Check for error cells and adjust formulas to remedy these cells. Adjust certain data or assumptions to become familiar with the model o Assume no new federal capitalization grants will flow into the model to see how much in loans can be made using only current assets and loan repayments, o Estimate with lower/higher yield rate of projects, o Estimate with slower/faster rate of project completion, o Estimate with lower/higher interest rates (for leveraged programs). o Try changing other data and assumptions to gain more confidence with the model and reveal more information about lending capacity. Step 11: Interpret Results After inputting data and testing the model, states can analyze and interpret its results. The results of the model will include an estimate of total outflows, total inflows, and unused funds in each period. Figure A-17 and Figure A-18 in Appendix A display sample data in the Results tab of the example model. From these numbers, states can estimate how much funding is available for lending in a given period and determine its lending goal for the year. States also can develop other informative summaiy statistics. For example, a model will allow a state to compare its potential annual lending capacity under CFM against its average annual capitalization grant to understand the effect of CFM on the state's lending capacity in the long term. Using CFM Results to Evaluate Lending Capacity From Dedicated Funds When reviewing the CFM results, a quick way for a state to gauge its capacity for loans is to assume $0 of new federal funding by inputting $0 into the federal capitalization grants inflow. While there is no plan to substantially reduce or remove capitalization grants, this assumption allows the state to understand its lending capacity in perpetuity from only the current assets and revolving dollars. When using the model to determine lending capacity, it is prudent to retain some cash balance as a buffer from year to year. For example, a state should establish a lending goal that is lower than its full lending capacity reflected in the model. States may also review the potential fluctuations in projected lending from year to year. As these projections depend on loan timeframes, some variation is 25 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook normal, especially when a program is significantly increasing lending amounts to draw down cash balances. The results represent an approximation of lending capacity. Given the nature of the data inputs, the model will be more accurate in the near-term and decrease in accuracy as the years go out. Additionally, as identified in the conceptual model, some data vary, and data gaps can exist. Therefore, states should not assume the output of the CFM is exact, and states should explore the uncertainty inherent in the estimates. Test & Maintain Step 10: Test model Step 11: Interpret Results Step 12: Regular updates Step 12: Update the Model Regularly Maintaining the model is critical to the success of CFM. States need to remain informed about their cash flow to continue making decisions about financing projects and ensure they are maximizing their lending capacity. It is intended to be a living tool with regular updates. Updating the model as frequently as possible is best, though given staffing constraints monthly or quarterly may be more realistic. Changes in data should also drive model maintenance, as would new management decisions. If new management decisions require modeling having a current modeling cycle complete for that process may be useful. Step 13: Create access and backups How Accurate Should the Model be? When updating their models, DWSRF programs may notice that the model did not predict each year's data accurately. A model should not be expected to accurately predict all cash flows. There are uncertainties in the modeling process that will result in slight differences between the model's predictions and actual cash flows from period to period. Slight variations are not concerning. However, significant or systematic deviations from model predictions should be carefully examined and may require updating data or adjusting future assumptions. For example, a large loan that disburses more quickly than anticipated could have a significant effect on cash balances in a given year. The data in the model should be adjusted to account for this type of anomaly. 26 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Test & Maintain Step 10: Test model Step 11: Interpret Results Step 12: Regular updates Step 13: Create access and backups Step 13: Create Access and Backups Once the model is fully developed and in use, the state must determine who has access to the model. Limiting staff access may minimize uncertainty and mistakes. On the other hand, limiting access to only one person poses a risk. Allowing approximately two people access may adequately allow for quality control while preventing errors that result from too many people working in the model. Since staff will turn over, documentation of the model, including definitions of its components and location of its data sources, is essential. States may consider developing a user guide with this information. Mentoring staff about the model will help in relaying institutional knowledge and assist in the long-term implementation of CFM. In addition to staff access, states should also consider model storage. Saving backup models is critical should something go awry with the current model. States may want to consider storing the backup file in a different location than the current model and should remember to resave a backup file each time updates are made to the model. 27 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Appendix A: Example Model EPA has developed an example model to demonstrate the basic principles of CFM. This example model is intended to be a starting point for states as they develop their own models. It includes tables for basic information that is needed to track and predict cash balances over time, and it demonstrates the fundamental relationships that are integral to CFM. As states progress in the development of their own models, they will likely need to modify elements of this example model to account for details unique to their SRF programs. To access associated files and spreadsheets, visit the SRF SharePoint site's Training area. How to Read this Appendix This appendix is designed to guide a new user through EPA's example model. A series of figures, accompanied by written description, demonstrate the various tabs and information in the example model. To fully understand the organization of the model and the information states will need in order to use it, it is recommended that users open the example model and click through the tabs as they read through this appendix. It may be necessary to carefully review this appendix more than once to internalize the functions of the example model. To begin: 1. Open EPA's example model "CFM Example Model Frame wo rkxlsx," located on EPA's SRF SharePoint site. This model includes descriptions of the elements in each tab and formulas, but it does not include any data. 2. Step through the information for Figure A-l through Figure A-8. These figures walk through the basic organization of the model without example data. The purpose of walking through these empty tables is to think through the framework of the model before entering data. States should use this version of the model when inputting their own data, but only after completing steps 3 and 4 (below) enough times to feel comfortable using the model. 3. After reviewing Figure A-l through Figure A-8, open EPA's example model "CFM Example Model_wExampleData.xlsx," located on EPA's SRF SharePoint site. This model has been pre- populated with example data to better demonstrate the functions of the model. 4. Step through the information for Figure A-9 through Figure A-18. These figures explain how states should enter data into the model. After careful review of this appendix, states should be equipped to begin designing their own model. 28 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Example Model Overview The following figures provide an overview of the structure of the example cash flow model. The example model is organized into seven tabs: Results, Inflows, Outflows, Amortization, Projected Disbursements, Loan Summary, and Disbursement Schedule. DWSRF programs may find that they need additional tabs to support their data analysis needs, but these seven tabs form the most basic structure of a cash flow model. Each tab is described in detail below. This "Results" tab (Figure A-l) demonstrates how a program could organize the outputs of a cash flow model. The critical information in a results tab will be the total initial funds available, total outflows, total inflows, and the resulting annual lending capacity or end-of-year (EOY) cash balance. These elements must be organized by the period chosen for the model. This example uses an annual period for simplicity, but states may choose to track cash flows by month or quarter. Navigate to the first tab of the workbook. A B C I D E F G H 1 ~2~ Results Basic State and Modeling Information 4 J Analysis Start Date 2019 5J Period Annual 61 Avg. Annual Cap Grant 7j State Match 81 Avg. Set-Asides (as % of Cap Grant) 9J Corpus Annual Interest Rate ioj Avg. Annual Fees & Penalties (as % of lending) 11J Funds at Start of Modeling Exercise 12J 13J Summary Information 2019 2020 2021 2022 2023 14 Initial Annual Funds Available 15] Inflows 16J Outflows 17J Annual Lending Capacity (EOY Cash Balance) $0 $0 $0 $0 $0 181 19] | Projected Lending 20 21 22 23 24 25 \ Program Summary Results 26 Average Annual Lending Capacity 27J Average Annual Cap Grant 28 Cumulative Cap Grant 2019 - 2029 29] Average Annual Projected Lending Vo Total Project Lending 2019 - 2029 s Amortization ProjectedDisbursements Figure A-l. Results tab 29 LoanSummary DisbursementSchi ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook The critical information in a results tab will be the total initial funds available, total outflows, total inflows, and the resulting annual lending capacity or end-of-year (EOY) cash balance. These elements must be organized by the period chosen for the model. This example uses an annual period for simplicity, but states may choose to track cash flows by month or quarter. The "Results" tab can also be used to document basic inputs critical to the model. This example lists several data elements in the "Basic State and Modeling Information" box. For simplicity, this model assumes that these elements are constant each year, but states may choose to specify these values for each year or period of the model. Finally, the "Results" tab can include key program statistics to demonstrate long-term averages. Sample data for the "Results" tab are demonstrated and described later in Figure A-18. A cash flow model should include a tab that documents all the program's loans. The example model includes a "Loan Summary" tab, which is located near the end of the workbook. Details about DWSRF loans will form the foundation of the model, and all other tabs build from the data that are entered here. Figure A-2 is an example of how to organize loan information. Loan Information Water System Name Year Executed Loan Amount Interest Rate Term (yrs) Status Disbursed as of FY18 Balance as of FY18 First Year of Repayment i \^m Figure A-2. Loan Summary tab 30 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Key fields to include in the "Loan Summary" tab will be: basic information to identify each loan (i.e., water system/recipient name, loan number, and loan name); details about each loan (i.e., year the loan is executed, loan amount, interest rate, and term); and the status of the loan (i.e., how much funding has been disbursed and how much funding is left on the loan waiting for disbursement). A DWSRF program would need to collect and enter these data into the model. Many of these basic data fields will be necessary to accurately predict account balances through the model. Sample data for the "Loan Summary" tab are demonstrated and described later in Figure A-9. Disbursement data are a critical input to a model. Figure A-3 shows the example model's "Projected Disbursements" tab, which provides one example of how a program can organize projected disbursements. This tab should contain disbursement data for all current loans (executed loans actively disbursing and awaiting first disbursement) and future loans that have not yet been awarded (the table for future loans is depicted in Figure A-4 below). Key information in this tab includes information to identify each loan (these fields should be consistent with the identifying information in the "Loan Summary" tab in Figure A-2) and information to help predict future disbursements on the loan. Most likely, this will include the current balance on the loan (i.e., undisbursed funds), and the projected disbursements in each of the future periods of the model. Disbursements Current Loans and Projected Disbursements Estimated Disbursements in FY Water System Name Loan Number Loan Name Year Executed Loan Amount Interest Rate Term Status Balance as of FY18 1 2019 1 2020 2021 2022 Subtotal Disbursements on Current Loans =SUM(K5:K15)| o $0 $0 ... | Inflows | Outflows | Amortization | Projected Disbursements | LoanSummary | DisbursementSchedule | ฎ [TE Figure A-3. Current loans and projected disbursements in the Projected Disbursements tab. 31 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook In this example, future disbursements are estimated on an annual basis, but states may choose to predict disbursements on a quarterly or even monthly basis. States can develop a formula to predict future disbursements (beginning in column K), or they can manually enter estimated projections for each future period. As discussed in Step 5 of this Handbook, states should develop projected disbursement schedules based on historic loan disbursement data, and these schedules should be revisited from year to year or revised to account for large deviations from disbursement projections. The example model includes an example loan disbursement schedule in the "Disbursement Schedule" tab. Sample data for the "Projected Disbursements" tab are demonstrated and described later in Figure A-10. Note that a DWSRF program will likely need more rows than pictured in Figure A-3 to accommodate all loan actively disbursing. Additional rows can easily be inserted. If rows are added, it is good practice to ensure that the total row still captures the full range of the table. As seen in Figure A-3, pressing the "F2" key shows the range of the formula in row 16. Just as disbursements are projected for current loans in Figure A-3, a DWSRF program should project disbursements on loans that will be made in the future. An example of this table is pictured in Figure A-4 and located in the same "Projected Disbursements" tab immediately below the table pictured in Figure A-3. Because the model will project several decades into the future, disbursements on future loans are critical inputs to estimate outflows each year. Once again, subtotal formulas should be checked if rows are added to this table. Sample data for this table in the "Projected Disbursements" tab are demonstrated and described later in Figure A-ll. B Projected Future Loans and Disbursements Loan Number Loan Name Year Executed Loan Amount Interest Rate Balance as of FY18 Water System Name Subtota Disbursements on Future Loans =SUM(K21:K31 Inflows Outflows Amortization Projected Disbursements LoanSummary DisbursementSchedule ฉ s: e Figure A-4. Future loans and disbursements in the Projected Disbursements tab 32 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Loan amortization, or loan repayments, are critical inflows to a DWSRF program that must be captured in a cash flow model. The model should include basic information on loans and their repayment schedules, as demonstrated in the "Amortization" tab of the example model, pictured in Figure A-5. Once again, the tab should include information to identify each executed loan (these fields should be consistent with the identifying information in other tabs). The "Amortization" tab should also capture future loan repayment schedules to anticipate when funds will flow back into the DWSRF account. Loan Amortization Future Principal and Interest Payments on Current Loans Water System Name Loan Number Status Loan Amount Interest Rate Term (yrs) First Year Repayment 2019 2020 2021 2022 Subtotal Principal and Interest SO So So So Inflows Outflows Amortization ProjectedDisbursements LoanSummary DisbursementSchedule ฉ 0: n e Figure A-5. Future principal and interest payments on current loans in the Amortization tab. As pictured in Figure A-5, the example model documents when a borrower began paying back a loan (column H "First Year Repayment") and the amount that will be repaid in each future period of the model (beginning in column I). This table should include projections for all executed loans. For loans that are actively disbursing, the DWSRF program should project what period these loans will begin repayments. Sample data for this table are demonstrated and described later in Figure A-12. 33 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook In addition to future repayment schedules, the model should include any interest payments due on current loans that are actively disbursing (if applicable to the state's DWSRF program). As pictured in Figure A-6, the example model includes a table to capture these payments in the "Amortization" tab, immediately below the table pictured in Figure A-5. Sample data for this table are demonstrated and described later in Figure A-14. 1 22 23 24 25 26 27 28 29 30 31 Interest on Loans Actively Disbursing Water System Name Loan Number Status Loan Amount Interest Rate Term (yrs) First Year Repayment 2019 2020 2021 2022 Projected Interest Payments on Future Loons $0 So So So Subtotal Interest on Loans Actively Disbursing $0 So $0 So Inflows Outflows Amortization ProjectedDisbursements LoanSummary DisbursementSchedule ฉ he Figure A-6. Interest on loans actively disbursing in the Loan Amortization tab. 0 The example model collects and summarizes outflows from the fund in the "Outflows" tab, depicted in Figure A-7. This tab provides an example of how a state can develop a summary of all program outflows. In this example, the tab draws from the subtotals of other tabs to calculate total estimated outflows in each period. Row 4 draws from the subtotal (row 16) in the "Projected Disbursements" tab (Figure A- 3) and row 5 draws from the subtotal (row 32) in the "Projected Disbursements" tab (Figure A-4). For simplicity, row 7 calculates estimated set-aside funds based on the modeling information defined in the "Results" tab (Figure A-l). The formula applies the same percentage of the capitalization grant each year. 34 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook A e C D E F G ป 1 1 2 Outflows 3 Outflow 2019 2020 2021 2022 2023 2024 4 Disbursements on Current Loans 5 Disbursements on Future Loans 6 Subtotal Disbursements $0 $0 $0 $0 SO so 7 Reserved for Set Asides 8 Other 9 Subtotal Expenses $0 $0 $0 $0 SO So 10 11 | Subtotal Estimated Outflows =SUM(C6,C9)| So $0 So So $o| 12 13 Adjustments: 14 Actual Disbursements on Loans 15 Difference 16 ~ | Results | Inflows Outflows Amortization | ProjectedDisbursements | LoanSummary | DisbursementSchedule .. ฉ i M 1 Figure A-7. Outflows tab. The "Outflows" tab should include all outflows that the program needs to consider when predicting cash balances. Rows can be easily inserted into this table to accommodate additional outflows not included in the example model. The example model includes an "Other" field (row 8) to accommodate unanticipated outflows. If additional rows are added, all formulas should be checked using the "F2" key, as pictured above in Figure A-7. The subtotal of estimated outflows in row 11 feeds into the "Outflows" row (row 16) of the "Results" tab (Figure A-l). The "Outflows" tab also includes a feature in rows 14 and 15 that a DWSRF program can use to check the accuracy of its loan disbursement predictions. The example model collects and summarizes inflows from the fund in the "Inflows" tab, depicted in Figure A-8. This tab provides an example of how a state can develop a summary of all program inflows. Similar to the "Outflows" tab, this tab draws from the subtotals of other tabs to calculate total estimated inflows in each period. Row 9 draws from the subtotal in row 19 of the "Amortization" tab (Figure A-5) and row 11 draws from the subtotal in row 30 of the "Amortization" tab (Figure A-6). In addition, rows 6, 7,10, and 12 calculate other inflows based on the modeling information defined in the "Results" tab (Figure A-l). 35 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook A B C D E F G H 1 1 2 3 Inflows 4 Inflow 2019 2020 2021 2022 2023 2024 5 Beginning of Annual Funds Available $0 So So So So So 6 Federal Capitalization Grants 7 State Match 8 Subtotal Grant Payments and State Match So So SO So So SO 9 Principal and Interest Repayments 10 Interest Earnings on Corpus 11 Interest on Loans Actively Disbursing 12 Fees and Penalties 13 Subtotal Estimated Loan and Interest So So So So So SO 14 ซ] | \Subtotal Estimated Inflows \-SUM(CS,C13^\ $0 $0 $0 $o| 16 ~ Results Inflows Outflows Amortization ProjectedDisbursements LoanSummary DisbursementSchedule Figure A-8. Inflows tab. Once again, these inputs are generalized for each period for simplicity, but a DWSRF program may choose to develop inputs particular to each period. Row 5, "Beginning of Annual Funds Available", captures the DWSRF program's cash balances at the beginning of the period and draws from row 14 in the "Results" tab (Figure A-l). This is included for reference in this tab and does not factor into any of the formulas in this tab. The "Inflows" tab should include all inflows that the program needs to consider when predicting cash balances. Rows can be easily inserted into this table to accommodate additional inflows not included in the example model. A program should take care not to include additional subsidization when calculating loan repayments because these subsidies do not revolve back into the fund. Row 15 calculates a subtotal of inflows for each period of the model, and this value feeds into the "Inflows" row (row 15) of the "Results" tab (Figure A-l). Example Model with Sample Data To help illustrate some of the concepts described above, the example model has been populated with sample data. The figures that follow walk the user through the data populated in the model "CFM Example Model_wExampleData.xlsx." Figure A-9 includes example data for the "Loan Summary" tab. The "Loan Summary" tab should document all loans that are currently disbursing (referred to as "open" loans in 36 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook this example model) and loans that are in repayment (referred to as "closed" loans in this example model). As previously described, the data include basic information on each loan and the status of each loan. A D Loan Information Water System Name Loan Number Loan Name Year Executed Loan Amount Interest Rate Term (yrs) Status Disbursed as of FY18 Balance as of FY18 First Year of Repayment Estimate of all 2000 Loans SRF 111 Consolidated 2000 $15,000,000.00 0.02 20 Closed $15,000,000.00 $0.00 2005 Estimate of all 2001 Loans SRF112 Consolidated 2001 $20,000,000.00 0.018 20 Closed $20,000,000.00 $0.00 2006 Estimate of all 2002 Loans SRF 113 Consolidated 2002 $21,000,000.00 0.02 20 Closed $21,000,000.00 $0.00 2007 Estimate of all2003 Loans SRF 114 Consolidated 2003 $25,000,000.00 0.02 20 Closed $25,000,000.00 $0.00 2008 Estimate of all 2004 Loans SRF 115 Consolidated 2004 $12,000,000.00 0.02 20 Closed $12,000,000.00 $0.00 2009 Estimate of all 2005 Loans SRF 116 Consolidated 2005 $13,000,000.00 0.02 20 Closed $13,000,000.00 $0.00 2010 Estimate of all 2006 Loans SRF 117 Consolidated 2006 $20,000,000.00 0.02 20 Closed $20,000,000.00 $0.00 2011 Estimate of all 2007 Loans SRF118 Consolidated 2007 $20,000,000.00 0.02 20 Closed $20,000,000.00 $0.00 2012 Estimate of all 2008 Loans SRF 119 Consolidated 2008 $18,000,000.00 0.02 20 Closed $18,000,000.00 $0.00 2013 Estimate of all 2009 Loans SRF120 Consolidated 2009 $19,000,000.00 0.02 20 Closed $19,000,000.00 $0.00 2014 Estimate of all 2010 Loans SRF121 Consolidated 2010 $15,000,000.00 0.02 20 Closed $15,000,000.00 $0.00 2015 Estimate of all 2011 Loans SRF 122 Consolidated 2011 $12,000,000.00 0.02 20 Closed $12,000,000.00 $0.00 2016 System 1 SRF 123 Leaky Faucet Water Co 2012 $6,000,000.00 0.02 20 Closed $6,000,000.00 $0.00 2017 System 2 SRF 124 Leaky Faucet Water Co 2012 $8,000,000.00 0.02 20 Closed $8,000,000.00 $0.00 2017 System 3 SRF 125 Leaky Faucet Water Co 2012 $5,000,000.00 0.02 20 Closed $5,000,000.00 $0.00 2017 System 4 SRF126 Leaky Faucet Water Co 2013 $8,000,000.00 0 30 Closed $8,000,000.00 $0.00 2018 System 5 SRF 127 Leaky Faucet Water Co 2013 $5,000,000.00 0.02 20 Closed $5,000,000.00 $0.00 2018 System 6 SRF 128 Leaky Faucet Water Co 2014 $8,000,000.00 0.02 20 Open $7,840,000.00 $160,000.00 2020 System 7 SRF 129 Leaky Faucet Water Co 2015 $8,000,000.00 0.02 20 Open $7,366,000.00 $634,000.00 2021 System 8 SRF 130 Leaky Faucet Water Co 2015 $5,000,000.00 0.02 20 Closed $5,000,000.00 $0.00 2019 System 9 SRF 131 Leaky Faucet Water Co 2015 $6,000,000.00 0 20 Open $5,524,500.00 $475,500.00 2021 Results Inflows Outflows ProjectedDisbursements LoanSummary DisbursementSchedule ฉ 0= Figure A-9. Loan Summary tab with sample data. It is important to capture all loans that are contributing to inflows or outflows from the DWSRF program. For simplicity, this model consolidates all loans prior to 2012 into an annual estimate. For example, in row 4, "Estimate of all 2000 Loans" is a single summary line intended to represent all loans that were executed in 2000. In a real cash flow model, each loan should be identified separately. The data entered into this tab should reflect the status of loans as of the most recently completed period. In this example, the workbook models years 2019 and beyond. Thus, the most recently completed period is 2018. As the data demonstrate, all loans that are "closed" (i.e., fully disbursed and in repayment) as of the end of 2018 have a "Balance as of FY18" equal to zero. Entering data that reflect a single starting point is consistent with Step 1 of this Handbook, in which a program must select the "starting point" of the analysis. Figure A-10 shows the "Projected Disbursements" tab with sample data. This tab calculates disbursements on loans currently disbursing (referred to as "open" in this example) using the disbursement schedule estimated by the program and the loan information from the 37 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook "Loan Information" tab (Figure A-9). States should remember that this tab will reflect estimates only, and actual disbursements should be examined on a regular basis (quarterly or annually]. A B C D E F G H 1 K L M N ,| 7 Current Loans and Projected Disbursements Estimated Disbursements in FY 8 Water System Name Loan Number Loan Amount Interest Rate Term Status Balance as of FY18 2019 2020 2021 2022 9 Estimate of all 2000 Loans SRF111 2000 $15,000,000 Closed $0 10 Estimate of all 2001 Loans SRF112 2001 $20,000,000 Closed $0 11 Estimate of all 2002 Loans SRF113 2002 $21,000,000 Closed $0 12 Estimate of all 2003 Loans SRF114 2003 $25,000,000 Closed $0 13 Estimate of all 2004 Loans SRF115 2004 $12,000,000 Closed $0 14 Estimate of all 2005 Loans SRF116 2005 $13,000,000 Closed $0 15 Estimate of all 2006 Loans SRF117 2006 $20,000,000 Closed $0 16 Estimate of all 2007 Loans SRF118 2007 $20,000,000 Closed $0 17 Estimate of all 2008 Loans SRF119 2008 $18,000,000 Closed $0 18 Estimate of all 2009 Loans SRF 120 2009 $19,000,000 Closed $0 19 Estimate of all 2010 Loans SRF 121 2010 $15,000,000 Closed $0 20 Estimate of all 2011 Loans SRF 122 2011 $12,000,000 Closed $0 21 System 1 SRF 123 2012 $6,000,000 Closed $0 22 System 2 SRF 124 2012 $8,000,000 Closed $0 23 System 3 SRF 125 2012 $5,000,000 Closed $0 24 System 4 SRF 126 2013 $8,000,000 Closed $0 25 System 5 SRF 127 2013 $5,000,000 Closed $0 26 System 6 SRF 128 2014 $8,000,000 Open $160,000 $160,000 $0 $0 $0 27 System 7 SRF 129 2015 $8,000,000 Open $634,000 $474,000 $160,000 $0 $0 28 System 8 SRF 130 2015 $5,000,000 Closed $0 29 System 9 SRF 131 2015| $6,000,000 Open $475,500 $355,500 $120,000 $0 $0 30 System 10 SRF 132 2016 $5,000,000 Closed $0 31 System 11 SRF 133 2016 56,000,000 Closed $0 32 System 12 SRF 134 2016 $3,500,000 Open $780,500 $503,125 $207,375 $70,000 $0 33 System 13 SRF 135 2016 $800,000 Open $178,400 $115,000 $47,400 $16,000 $0 34 System 14 SRF 136 2016 $3,000,000 Open $669,000 $431,250 $177,750 $60,000 $0 35 System 15 SRF 137 2017 $5,000,000 Open $1,115,000 $2,116,250 $718,750 $296,250 $100,000 Results Inflows Outflows Amortization Projected Disbursements LoanSummary DisbursementSchedule ฉ HE 0 Figure A-10. Current loans and projected disbursements with sample data. Loans in repayment (referred to as "closed" loans in this example) are included in this table for informational purposes only and do not feed into the model from this tab. These loans have been greyed out. As loans move from open to closed, this tab can be updated to reflect that. For example, "System 6" in row 26 in Figure A-10 is planned to complete its disbursements in 2019. At that time, the DWSRF program can change the "Status" in column I from "Open" to "Closed." The DWSRF program should update this table with new loans that are executed in each period, ensuring that this table is maintained as a running list of all loans and their past and future disbursements. 38 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook It is essential to estimate the projected disbursements for future loans to accurately estimate the outflows in any given year. Figure A-ll provides an example of how a program can organize this information in a cash flow model. The table pictured in Figure A-ll is located below the table for current and past loans (Figure A-10). For simplicity, this example model uses a lump sum estimate for each year and uses a generalized disbursement schedule to project disbursements for all loans executed for thatyear. For example, row 52 "FY2019 Loans" includes a single lump sum in column F to represent the value of all loans that the program anticipates executing in FY 2019. Using a basic 5-year disbursement schedule (defined in the "Disbursement Schedule" tab), the model predicts that approximately 35% of those funds will be disbursed in 2020 (column L), 42% will be disbursed in 2021 (column M), 14% will be disbursed in 2022 (column N), and so forth until the funds for those loans are fully disbursed by the end of FY 2024. A DWSRF program may find that this level of detail is sufficient to project future loan disbursements, but some programs may need to develop these estimates with more detail depending on their historic disbursement patters. In this example model, the estimated disbursements each year draw from the "Projected Lending" field in the "Summary" tab (Figure A-9) and will automatically update as a DWSRF program adjusts its anticipated lending amounts. This feature enables the user to modify projected lending assumptions in the "Summary" tab to see its effect on future lending capacity. A B C D E F G 1 > L M N 50 Projected Future Loans am d Disbursements 51 Water System Name Loan Number Loan Name Year Executed Loan Amount Interest Rate Term Status Balance as of FY18 2019 2020 2021 2022 52 FY2019 Loans n/a n/a 2019 $90,000,000 n/a n/a n/a n/a $0 $31,837,500 $38,092,500 $12,937,500 53 FY2020 Loans n/a n/a 2020 $89,000,000 n/a n/a n/a n/a $0 $0 $31,483,750 $37,669,250 54 FY2021 Loans n/a n/a 2021 $56,000,000 n/a n/a n/a n/a $0 $0 $0 $19,810,000 55 FY2022 Loans n/a n/a 2022 $25,000,000 n/a n/a n/a n/a $0 $0 $0 $0 56 FY2023 Loans n/a n/a 2023 $18,000,000 n/a n/a n/a n/a $0 $0 $0 $0 57 FY2024 Loans n/a n/a 2024 $25,000,000 n/a n/a n/a n/a $0 $0 $0 $0 58 FY2025 Loans n/a n/a 2025 $38,000,000 n/a n/a n/a n/a $0 $0 $0 $0 59 FY2026 Loans n/a n/a 2026 $55,000,000 n/a n/a n/a n/a $0 $0 $0 $0 60 FY2027 Loans n/a n/a 2027 $65,000,000 n/a n/a n/a n/a $0 $0 $0 $0 61 FY2028 Loans n/a n/a 2028 $64,000,000 n/a n/a n/a n/a $0 $0 $0 $0 62 FY2029 Loans n/a n/a 2029 $58,000,000 n/a n/a n/a n/a $0 $0 $0 $0 63 Subtotal Disbursements on Future Loans $0 $31,837,500 $69,576,250 $70,416,750 64 | Results Inflows | Outflows | Amortization Projected Disbursements LoanSummary | DisbursementSchedule (+) < ~ 0 Figure A-l 1. Projected future loans and disbursements with sample data. Figure A-12 shows the "Amortization" tab with sample loan data. This tab calculates principal and interest payments on executed loans. The table includes data on loans that are in repayment (referred to as "closed" in this example) and loans that are currently disbursing (referred to as "open" in this example). The "Amortization," "Project Disbursements," and "Loan Summary" tabs should all contain the same basic loan information. For example, loan number 128 is included in the "Amortization" tab (row 28), the "Project Disbursements" tab (row 26 in Figure A-10), and the "Loan Summary" tab (row 21 in Figure A-9), and the loan details are the same in each tab (i.e., 39 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook whether the loan is "open" or "closed", the loan amount, and the anticipated first year of repayment). For simplicity, the model combines principal and interest payments into one repayment value per period, but some programs may choose to track principal and interest separately. This does not affect the results of the model, but it may be important to the program for record keeping purposes. A c D E F G H ' J < L 9 Future Principal and Interest Payments on Current Loans 10 Water System Name Loan Number Status Loan Amount Interest Rate Term (yrs) First Year Repayment 2019 2020 2021 2022 11 Estimate of all2000 Loans SRF111 Closed $15,000,000 0.02 20 2005 $917,351 $917,351 $917,351 $917,351 12 Estimate of all 2001 Loans SRF 112 Closed $20,000,000 0.018 20 2006 $1,199,655 $1,199,655 $1,199,655 $1,199,655 13 Estimate of all 2002 Loans SRF 113 Closed $21,000,000 0.02 20 2007 $1,284,291 $1,284,291 $1,284,291 $1,284,291 14 Estimate of all 2003 Loans SRF 114 Closed $25,000,000 0.02 20 2008 $1,528,918 $1,528,918 $1,528,918 $1,528,918 15 Estimate of all 2004 Loans SRF 115 Closed $12,000,000 0.02 20 2009 $733,881 $733,881 $733,881 $733,881 16 Estimate of all 2005 Loans SRF 116 Closed $13,000,000 0.02 20 2010 $795,037 $795,037 $795,037 $795,037 17 Estimate of all 2006 Loans SRF 117 Closed $20,000,000 0.02 20 2011 $1,223,134 $1,223,134 $1,223,134 $1,223,134 18 Estim ate of all 2007 Loans SRF 118 Closed $20,000,000 0.02 20 2012 $1,223,134 $1,223,134 $1,223,134 $1,223,134 19 Estimate of all 2008 Loans SRF 119 Closed $18,000,000 0.02 20 2013 $1,100,821 $1,100,821 $1,100,821 $1,100,821 20 Estimate of all2009 Loans SRF 120 Closed $19,000,000 0.02 20 2014 $1,161,978 $1,161,978 $1,161,978 $1,161,978 21 Estimate of all2010 Loans SRF 121 Closed $15,000,000 0.02 20 2015 $917,351 $917,351 $917,351 $917,351 22 Estimate of all 2011 Loans SRF 122 Closed $12,000,000 0.02 20 2016 $733,881 $733,881 $733,881 $733,881 23 System 1 SRF 123 Closed $6,000,000 0.02 20 2017 $366,940 $366,940 $366,940 $366,940 24 System 2 SRF 124 Closed $8,000,000 0.02 20 2017 $489,254 $489,254 $489,254 $489,254 25 System 3 SRF 125 Closed $5,000,000 0.02 20 2017 $305,784 $305,784 $305,784 $305,784 26 System 4 SRF 126 Closed $8,000,000 0 30 2018 $266,667 $266,667 $266,667 $266,667 27 System 5 SRF 127 Closed $5,000,000 0.02 20 2018 $305,784 $305,784 $305,784 $305,784 28 System 6 SRF 128 Open $8,000,000 0.02 20 2020 $0 $489,254 $489,254 $489,254 29 System 7 SRF 129 Open $8,000,000 0.02 20 2021 $0 $0 $489,254 $489,254 30 System 8 SRF 130 Closed $5,000,000 0.02 20 2019 $305,784 $305,784 $305,784 $305,784 31 System 9 SRF 131 Open $6,000,000 0 20 2021 $0 So $300,000 $300,000 32 System 10 SRF 132 Closed $5,000,000 0.02 20 2019 $305,784 $305,784 $305,784 $305,784 33 System 11 SRF 133 Closed $6,000,000 0 20 2019 $300,000 $300,000 $300,000 $300,000 34 System 12 SRF 134 Open $3,500,000 0.02 20 2022 $0 $0 $0 $214,049 35 System 13 SRF 135 Open $800,000 0.02 20 2022 $0 $0 $0 $48,925 Results | Inflows | Outflows Amortization ProjectedDisbursements | LoanSummary | DisbursementSchedule | (+) * \ | [Tj Figure A-12. Future loan amortization on current loans with sample data. Once a loan has been fully disbursed, principal and interest payments are often relatively easy to project. However, estimating future repayments for loans currently being disbursed will depend on the accuracy of the loan disbursement projections. These repayment estimates should be revisited by the DWSRF program as loans are fully disbursed. This tab demonstrates how projected amortization 40 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook should phase in loans as they enter the repayment period and phase out loans as their repayments complete. States should examine their repayment requirements and timelines when completing this information. For example, states have different requirements defining when repayments begin, and on what schedule they are due. This tab should consider the specifics of each loan (e.g., interest rate, loan term, the first year of repayment, and the current balance on each loan) to develop accurate repayment estimates. Amortization should also be estimated for future loans. Figure A-13 includes sample data for principal and interest payments on future loans. For simplicity, this model uses a lump sum loan amount for each year from the "Projected Lending" field in the "Results" tab (pictured later in Figure A-18). It also uses basic assumptions on the interest rate, loan term, and the general disbursement schedule provided in the "Disbursement Schedule" tab to estimate when amortization will begin. This model assumes that loans will begin repayment six years after being executed. A A c D E F G H 1 J i 9 Future Principal and Interest Payments on Current and Future Loans 10 Water System Name Loan Number Status Loan Amount Interest Rate Term (yrs) First Year Repayment 2019 2020 46 FY2019 Loans n/a FUTURE $90,000,000 0.02 20 2025 $0 $0 47 T-Y2020 Loans \ n/a FUTURE $39,000,000 0.02 20 2026 $0 $0 48 / FY2021Loans n/a FUTURE $56,000,000 0.02 20 2027 $0 $0 49 / FY2022Loans n/a FUTURE $25,000,000 0.02 20 2028 $0 $0 50 FY2023 Loans n/a FUTURE $18,000,000 0.02 20 2029 $0 $0 51 | FY2024 Loans n/a FUTURE $25,000,000 0.02 20 2030 $0 $0 52 FY2025 Loans n/a FUTURE $38,000,000 0.02 20 2031 $0 $0 53 FY2026 Loans n/a FUTURE $55,000,000 0.02 20 2032 $0 $0 54 \ FY2027Loans n/a FUTURE $65,000,000 0.02 20 2033 $0 $0 55 \ FY202S Loans J n/a FUTURE $64,000,000 0.02 20 2034 $0 $0 56 FY2029Loans / n/a FUTURE $58,000,000 0.02 20 2035 $0 $0 57 Subtotal Principal and Interest $15,465,426 $15,954,680 Results Inflows Outflows Amortization ProjectedDisbursements LoanSummary DisbursementSchedule ฉ : l< 0 Figure A-13. Amortization of future loans. Some states collect interest payments on actively disbursing loans. If so, these payments should be captured in the CFM. Just as the model should capture expected disbursements and amortization of future loans, it should also capture the expected interest payments when these loans are actively disbursing. As demonstrated in the "Projected Interest Payments on Future Loans" row (row 92] of Figure A-14, these interest payments can be substantial. 41 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook 67 68 69 | 70 I 71 72 73 74 75 76 77 78 | 79 80 8! 82 83 84 85 | 86 | 87J 88 89 j 90 91 i 92J 93 Interest on Loans Actively Disbursing Water System Name Loan Number Status Loan Amount Interest Rate Term (yrs) First Year Repayment 2019 2020 2021 2022 1 System 1 SRF 123 Closed $6,000,000 0.02 20 2017 $0 $0 $0 $0 System 2 SRF 124 Closed $3,000,000 0.02 20 2017 $0 $0 $0 $0 System 3 SRF 125 Closed $5,000,000 0.02 20 2017 $0 $0 $0 $0 System 4 SRF 126 Closed $8,000,000 0 30 2018 $0 $0 $0 $0 System 5 SRF 127 Closed $5,000,000 0.02 20 2018 $0 $0 $0 $0 System 6 SRF 128 Open $8,000,000 0.02 20 2020 $160,000 $0 $0 $0 System 7 SRF 129 Open $8,000,000 0.02 20 2021 $156,800 $160,000 $0 $0 System 8 SRF 130 Closed $5,000,000 0.02 20 2019 $0 $0 $0 $0 System 9 SRF 131 Open $6,000,000 0 20 2021 $0 $0 $0 System 10 SRF 132 Closed $5,000,000 0.02 20 2019 $0 $0 $0 $0 System 11 SRF 133 Closed $6,000,000 0 20 2019 $0 $0 $0 $0 System 12 SRF 134 Open $3,-500,000 0.02 20 2022 $64,453 $68,600 $70,000 $0 System 13 SRF 135 Open $800,000 0.02 20 2022 $14,732 $15,680 $16,000 $0 System 14 SRF 136 Open $3,000,000 0.02 20 2022 $55,245 $58,800 $60,000 $0 System 15 SRF137 Open $5,000,000 0.02 20 2023 $77,700 $92,075 $98,000 $100,000 System 16 SRF 138 Open $7,000,000 0.02 20 2023 $108,780 $128,905 $137,200 $140,000 System 17 SRF 139 Open $1,500,000 0.02 20 2023 $23,310 $27,623 $29,400 $30,000 System 18 SRF 140 Open $2,000,000 0 30 2023 $0 $0 $0 $0 System 19 SRF 141 Open $1,500,000 0.02 20 2023 $23,310 $27,623 $29,400 $30,000 System 20 SRF 142 Open $6,000,000 0 30 2024 $0 $0 $0 $0 System 21 SRF 143 Open $4,500,000 0.02 20 2024 $31,838 $69,930 $82,868 $88,200 System 22 SRF 144 Open $3,000,000 0.02 20 2024 $21,225 $46,620 $55,245 $58,800 System 23 SRF 145 Open $2,500,000 0.02 20 2024 $17,688 $38,850 $46,038 $49,000 Projected Interest Payments on Future Loans 0.02 20 $0 $636,750 $1,391,525 $1,408,335 Subtotal Interest on Loans Actively Disbursing $755,080 $1,371,455 $2,015,675 $1,904,335 Results | Inflows | Outflows | Amortization | ProjectedDisbursements | LoanSummary DisbursementSchedule ฉ Figure A-14. Loan amortization on actively disbursing loans. 0 Figure A-15 demonstrates how the example model collects and summarizes outflows from the fund in the "Outflows" tab. In this example, the tab draws the example data from the subtotals of other tabs to calculate total estimated outflows in each period. Row 4 draws from the subtotal of disbursements on current loans (row 44) in the "Projected Disbursements" tab, and row 5 draws from the subtotal of disbursements on future loans (row 63} in the "Projected Disbursements" tab (Figure A-ll). For simplicity, row 7 calculates estimated set-aside funds based on the modeling information defined in the "Results" tab. The formula applies the same percentage of the capitalization grant each year. In this example, the average annual capitalization grant is assumed to be $20 million, and the state takes 15% in set-asides. This results in approximately $3 million in set-asides each year. As each year closes, the DWSRF program can update 42 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook data in this row to reflect the actual amount used for set-asides. Row 11 adds all outflows from the program each year, and this value feeds into the "Results" tab. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Outflows Outflow 2019 2020 2021 2022 2023 2024 Disbursements on Current Loans $14,394,125 $9,928,275 $3,453,250 $1,288,000 $320,000 $0 Disbursements on Future Loans $0 $31,837,500 $69,576,250 $70,416,750 $50,672,000 $32,072,000 Subtotal Disbursements $14,894,125 $41,765,775 $73,029,500 $71,704,750 $50,992,000 $32,072,000 Reserved for Set Asides $3,000,000 $3,000,000 $3,000,000 $3,000,000 $3,000,000 $3,000,000 Other 4 Subtotal Expenses $3,000,000 $3,000,000 $3,000,000 $3,000,000 $3,000,000 $3,000,000 Subtotal Estimated Outflows $17,894,125 $44,765,775 $76,029,500 $74,704,750 $53,992,000 $35,072,000 Adjustments: Actual Disbursements on Loans $12,000,000 $39,000,000 $60,000,000 Difference $2,894,125 $2,765,775 $13,029,500 n/a n/a n/a Keeping afield open for "other" outflows can make the model more flexible to accommodate unexpected expenses. Any regular expenses should be incorporated into the model more permanently. This QA check helps identify significant deviations from disbursement assumptions. While this value does not need to zero out every year, it is a way for the SRF program to keep an eye on the accuracy of the model. A large difference may indicate the need to revise assumptions or update loan data. Results Inflows Outflows Amortization ProjectedDisbursements LoanSummary DisbursementSchedule Figure A-15. Outflows tab with sample data. ฉ This example model uses rows 14 and 15 to ground-truth projected disbursements against actual disbursements. The row titled "Actual Disbursements on Loans" is a manual entiy that should reflect actual disbursements each year, and the "Difference" row calculates the difference between projected disbursements and actual disbursements. As pictured in Figure A-15, the program can manually enter actual disbursement values in the "Actual Disbursements on Loans" row to quickly see how projections differ from actual disbursements. 43 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Figure A-16 demonstrates how the example model collects and summarizes inflows from the fund in the "Inflows" tab. Similar to the "Outflows" tab, this tab draws the sample data from the subtotals of other tabs to calculate total estimated inflows in each period. Row 11, "Principal and Interest Repayments," draws from the subtotal of principal and interest payments on current and future loans in row 57 of the "Amortization" tab, and row 13, "Interest on Loans Actively Disbursing," draws from the subtotal of interest payments on actively disbursing loans in row 93 of the "Amortization" tab. In addition, rows 8, 9,12, and 14 calculate other inflows based on the modeling information defined in the "Results" tab (Figure A-17). Once again, these inputs are generalized for each period for simplicity, but a DWSRF program may choose to develop inputs particular to each period. J,A B C D E F G 1 Inflows 2 6 7 S 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | Results | Inflows Outflows | Amortization | ProjectedDisbursements | LoanSummary DisbursementSchedule Figure A-16, Inflows tab with sample data, Inflow 2019 2020 2021 2022 2023 Beginning of Annual Funds Available $75,000,000 $99,351,381 $98,292,011 $67,056,500 $36,702,310 Federal Capitalization Grants $20,000,000 $20,000,000 $20,000,000 $20,000,000 $20,000,000 State Match $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 Subtotal Grant Payments and State Match $24,000,000 $24,000,000 $24,000,000 $24,000,000 $24,000,000 Principal and Interest Repayments $15,465,426 $15,954,680 $16,743,934 $17,190,378 $18,174,395 Interest Earnings on Corpus $1,125,000 $1,490,271 $1,474,380 $1,005,847 $550,535 Interest on Loans Actively Disbursing $755,080 $1,371,455 $2,015,675 $1,904,335 $1,213,440 Fees and Penalties $900,000 $890,000 $560,000 $250,000 $180,000 Subtotal Estimated Loan and Interest $18,245,506 $19,706,406 $20,793,989 $20,350,560 $20,118,370 Subtotal Estimated Inflows $42,245,506 $43,706,406 $44,793,989 $44,350,560 $44,118,370 This subtotal feeds into the "Summary" tab and reflects all projected inflows to the SRF account each year. States can include fees and penalties as needed. This model assumes a 1% origination fee on all projected loans. 44 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook Row 7 in Figure A-16, "Beginning of Annual Funds Available," captures the DWSRF program's cash balances at the beginning of the period and draws from row 14 in the "Results" tab (pictured later in Figure A-18). This value is included to calculate the "Interest Earnings on Corpus" using the 1.5% interest rate assumed in the basic state and modeling information in the "Results" tab pictured in Figure A-17. This is a rough approximation of what annual interest earnings would be under these assumptions, but a DWSRF program may choose to track these earnings with more accuracy. Figure A-17 shows the sample data and assumptions used in the example model. As depicted in the other tabs, this analysis sets 2019 as the start date and tracks data on an annual basis. The model also assumes an annual capitalization grant of $20 million, state match of 20%, 15% of the capitalization grant taken as set-asides, and 1.5% interest earned on cash in the account. This model also assumes that the program has $75 million in cash at the start of the analysis (i.e., at the close of 2018). A B c D 1 L~ Results 3 Basic State and Modeling Information 4 Analysis Start Date 2019 5 Period Annual 6 Avg. Annual Cap Grant $20,000,000 7 State Match 20% 8 Avg. Set-Asides (as % of Cap Grant) 15% 9 Corpus Annual Interest Rate 1.5% 10 Avg. Annual Fees & Penalties (as % of lending) 1.0% 11 Funds at Start of Modeling Exercise $75,000,000 i? Figure A-17. Basic state and modeling sample data in the Results tab. Figure A-18 shows the results of the model using the sample data. In this example, the program begins with a large cash balance in 2019 ($75 million). Along with the projected inflows from existing loans that are in repayment, the program is anticipated to have a cash balance of approximately $99 million at the end of 2019. Row 17 shows the projected cash balance at the end of each period, and row 18 is an input field where the DWSRF program can enter the value of loans it will award in each period. In this example model, the "Projected Lending" value in a given year represents the total value of executed loans in that year and assumes that the first disbursements are made 45 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook the following year. A DWSRF program should carefully design this feature to accurately reflect its own award and disbursement timeline. As the user enters values into row 19, the values will run through the model and update the inflows, outflows, and cash balance in future periods according to the assumptions defined in the model (e.g., projected disbursement rates, average interest rates, and other loan terms}. Variations in these assumptions (e.g., higher than expected principal forgiveness or deviations from the projected disbursement rate) will require adjustments to the model and possibly to the projected lending values in row 19, A B 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Summary Information 2019 2020 2021 2022 2023 2024 2025 2026 2027 Initial Annual Funds Available $75,000,000 $99,351,381 $98,292,011 $67,056,500 $36,702,310 $26,828,680 $36,036,512 $56,878,856 $78,469,798 Inflows $42,245,506 $43,706,406 $44,793,989 $44,350,560 $44,118,370 $44,279,832 $48,996,344 $53,803,442 $56,597,542 Outflows $17,894,125 $44,765,775 $76,029,500 $74,704,750 $53,992,000 $35,072,000 $28,154,000 $32,212,500 $43,700,000 Annual Lending Capacity (EOY Cash Balance) $99,351,381 $98,292,011 $67,056,500 $36,702,310 $26,828,680 $36,036,512 $56,878,856 $78,469,798 $91,367,340 Projected Lending $90,000,000 $89,000,000 $56,000,000 $25,000,000 $18,000,000 $25,000,000 $38,000,000 $55,000,000 $65,000,000 Manually adjust this number to establish lending goals. In this example model, the "Projected Lending" value in a given year represents the total value of executed loans and assumes that the first disbursements are made the following year. This model looks out 10 years. There is no rule of thumb for how far out to project, but projections further into the future have greater uncertainty. Each SRF program should determine its appropriate cash buffer (i.e., the amount available in the fund at any given time to accommodate unexpected deviations from projections). This model uses a cash buffer of about $8M to $11M. Program Summary Results Average Annual Lending Capacity $69,848,244 Average Annual Cap Grant $20,000,000 Cumulative Cap Grant 2019 - 2029 $220,000,000 Average Annual Projected Lending $53,000,000 Total Project Lending 2019 - 2029 $583,000,000 These are some key stats from the model to demonstrate the effect of lending decisions over a 10 year period. Keep in mind: Lending capacity will likely fluctuate significantly from year to year at first (as excess funds are drawn down) and then settle into a more stable equilibrium. A comparison of the cumulative value of capitalization grants and projected project lending can be a measure to understand the potential difference in loans when using a CFM. Results Inflows Outflows Amortization ProjectedDisbursements LoanSummary 1 DisbursementSchedule ฉ Figure A-18. Program summary results using sample data. In this example model, if the program awards $90 million in loans in 2019, funds for those loans will be disbursed over a 5-year time period (based on the projected disbursement rate), and will begin repayment in the year after the funds are fully disbursed. Consequently, there is a natural ebb and flow to the projected cash balance and lending capacity. As those loans are in disbursement, lending capacity will decrease, and as they enter repayment, lending capacity will increase. The program can adjust projected lending values in row 19 to 46 ------- Drinking Water State Revolving Fund Cash Flow Modeling Handbook maintain a reasonable cash balance (buffer) in each period. Taking the time to input different lending values in row 19 and adjusting assumptions will help a user understand how these values affect the flow of funds and lending capacity of the program. As the user learns more about the model and the DWSRF program, the CFM can be adapted to accommodate the nuances of the program and ultimately provide more accurate predictions. 47 ------- Office of Ground Water and Drinking Water December 2020 EPA 810-B-19-003 www.epa.gov/dwsrf ------- |