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
revolve—they 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

-------