September 6, 2000
How to
organize cash flow estimates
in a
spreadsheet file
Note: Please see the related working paper Performing reestimates with the revised credit subsidy calculator which has additional details. The additional details on reestimates will be added to this document in the near future.
Topic Page
What does this paper cover? .. 2
What changes are introduced with this release? 2
What are the responsibilities of organizations and
individuals that use the CSC? 5
What spreadsheets are supported? . 6
How does the CSC find your cash flow estimates in the spreadsheet? . 6
A direct loan example 7
What are keywords?
.. 7
A loan guarantee example . 8
Can formulas be used in spreadsheet cells?
... 8
Can comments be placed spreadsheets?
8
How can cash flows be associated with
disbursement periods?
10
How can the timing and frequency of cash
flows be specified?
11
Can rate
assumptions be specified in a spreadsheet?
. 16
How does the purpose affect the calculation methods? . 17
How
are cohorts distinguished in the cash flows?
. 17
Table 1. Keywords for cash flows as a whole ... 17
Table 2. Keywords for direct loan programs . 22
Table 3. Keywords for loan guarantee programs .. 25
Table 4. Listing of obsolete keywords .. 28
Table 5. Listing of all keywords and shortest abbreviation . 29
What does this paper cover?
This paper describes how cash flow estimates can be organized in a spreadsheet for the Credit Subsidy Calculator (CSC). It is intended primarily as a technical reference. It may prove useful as well to someone who has no previous experience with subsidy calculation.
Other working papers for the CSC include:
How the subsidy and its components are derived from cash flow observations
Description
of the basket-of-zeros discounting method and the derivation of present value
factors from the yield curve
How the single effective rate is calculated
Error messages and warning messages: how the CSC checks the cash flow
observations
What changes are introduced with this release?
Some important things didnt change. Spreadsheets used with the previous version of this software will work with the 1999 release, without modification.
Definitions of the components are refined. The definition of the components of the subsidy has evolved recently. The current definitions are:
Financing/interest subsidy costs are defined as the portion of the subsidy attributable to subsidizing the borrowers interest costs by charging lower rates than the discount rate (in certain direct loan programs) or by direct interest subsidy payments (in certain loan guarantee programs). For direct loans, this is calculated as the excess of the amount of the loans disbursed over the present value of the interest and principal payments required by the loan contracts. For loan guarantees, this is calculated as the present value of estimated interest supplement payments, before adjustment for defaults.
Defaults, net of recoveries, subsidy cost, defined as the portion of the subsidy attributable to defaults, net of recoveries. It is calculated as the sum of discounted cash flow observations for defaults and recoveries.
Fee subsidy cost, defined as the portion of the subsidy percentage attributable to up-front and annual fees paid to the government. Because these fees are inflows to the Government, this subsidy component makes the total subsidy either less positive or more negative. It is calculated as the sum of the discounted fee-related cash flow observations, before adjustment for defaults.
Other subsidy costs, defined as the residual subsidy cost not attributed to financing, defaults net of recoveries, or fees. It is calculated as a residual.
In previous definitions, it was not clear whether the fee component should be gross or net of defaults. With this release, the definition is that fees should not reduced for effects of estimated defaults. For loan guarantee programs, a new line is added to explicitly identify the amounts of fees lost. For direct loan programs, the existing line for default adjustments may be used for the loss of principal and interest payments and fees due to defaults.
Also, in previous definitions, it was not clear whether the interest supplement payments (loan guarantee programs) were gross or net of the effects of defaults. In this release, the interest supplement payments are should be reported before adjustment for defaults. A new line if available for separated reporting of the interest supplements that are not paid on defaulted loans.
There is a new way to distinguish cohorts. The previous release distinguished cohorts in a spreadsheet by new lines with enacted budget authority or obligations or commitments.
For compatibility purposes, these lines will continue to mark the beginning of a new cohort. However, you may make the beginning of a new cohort more explicit by using the new cohort keyword. This will allow you to explicitly state the year of the cohort and to eliminate the obsolete lines. While these changes are not required, they may help to simplify the appearance of your spreadsheets and make them more easily understood.
No need to specify current year. The previous release expected the first cohort to be the current year and the second cohort to be the budget year.
Solely for compatibility with the previous release, the first cohort will be treated as the current year unless you choose otherwise. If you wish to omit the current year from your spreadsheets, you can do so in two ways.
First, you can explicitly label the cohorts by year (see the cohort keyword, above). This method can be applied to spreadsheets on an individual basis
Second, you can direct the software to omit the current year in all spreadsheets. To do this, start the CSC and go to the menu. Under Preferences choose General preferences and click the box dealing with the treatment of the current year. If the box is checked, the first cohort in the spreadsheet will be treated as the budget year, unless it is explicitly labeled with the cohort keyword.
Spreadsheet output. You may now get an extensive collection of data in an output spreadsheet. As explained in other working papers for the CSC, there are seven displays of data available for on-line viewing and printing. These displays can also be placed in an output spreadsheet. The spreadsheet can be specified in your cash flow spreadsheet, in the dialog boxes of the CSC, or on the command line (if you use the CSC in command line mode).
New frequencies. The previous release accepted data only in annual form. Column of data was taken to be amounts of inflows or outflows that occur in successive fiscal years. This release, each column of data can be a year, semiannual period, quarter, or month.
There is a new ways to specify timing for individual lines. In previous releases, the timing of cash flows (whether annual cash flow observations occur at the beginning, middle, end or throughout the period) was indicated by a timing line, which applied to the rows that followed. Timing lines could be repeated as often as needed. This approach, in some instances, could result in a proliferation of timing lines, especially when most lines were of one type of timing and a few other rows differed from the common pattern.
This release allows timing and other specifications that pertain to a single line to be shown on that line. Such specifications do not affect other lines.
No limit on number of columns or number of rows. Previous versions had limitations on the number of columns of data that could be used for cash flow observations. The current release has no limitations other than those imposed by spreadsheet software (generally, 255 columns). Similarly, the limitation on the number of row that can be used is the limit imposed by the spreadsheet software. Older versions of spreadsheet software were limited to 8192 rows and more current versions are limited to 65,535 rows.
The CSC will the maximum number of rows supported by your spreadsheet software. For example, if your software supports 65,535 rows, the CSC will read up to 65,635 rows of data.
New approach to discounting, old discount line ignored. This release introduces a new method for discounting, called the basket-of-zeros method. A paper that describes this method is available. See Description of the basket-of-zeros discounting method and the derivation of present value factors from the yield curve.
With the introduction of this method, the old discount rates line is no longer used. You do not need to remove these lines from your spreadsheets, though. This release will quietly ignore any discount rate lines in your spreadsheets.
How obsolete items are handled. Generally, obsolete items (which are listed toward the end of this document) are quietly ignored. In some instances, merely ignoring obsolete lines might result in errors of interpretation. When there is such a possibility, the CSC will emit a warning message that identifies the obsolete item that was ignored.
What are the responsibilities of organizations and individuals that use the CSC?
The responsibilities of organizations and individuals that use the CSC for Federal budget or financial reporting purposes include, but are not limited to, the following:
1. Ensuring that there are no errors, omissions, or defects in the inputs that would materially distort the calculations made by the CSC. Though the CSC provides messages to identify certain instances where input data items may be questionable, these message are for informational purposes. The CSC does not test all potential error conditions. Further, the absence of warning or error messages should not, by itself, be taken as an endorsement of the inputs or an indication of their quality or acceptability;
2. Correctly installing the calculator, ensuring that it has not been corrupted, ensuring that access is appropriately controlled, providing an appropriate level of computer security, and ensuring that system date and time values are set correctly;
3. Choosing the appropriate scale (e.g., millions of dollars or thousands of dollars) for cash flow values. In particular, subsidy percentages may be distorted when a large scale, such as millions of dollars, is used and the cash flow estimates are generally in magnitudes of a few thousand dollars and are rounded so that they have few significant digits. In such instances, the resulting subsidy may differ significantly from a subsidy calculated from cash flow observations with more significant digits. In all instances where such distortions might occur, it is the responsibility of the individual or organization preparing the cash flow estimates to use a scale with a sufficient number of significant digits;
4. Choosing the appropriate level of detail for cash flow observations (whether to use aggregated cash flow observations that combine disbursement years or to use individual disbursement year cash flow observations); and,
5. Choosing the appropriate frequency for cash flow observations (whether to use monthly, quarterly, semiannual, or annual frequencies) with the understanding that the aggregation of cash flows may yield approximate, rather than exact, results.
What spreadsheets are supported?
The CSC takes cash flow estimates from spreadsheets. The spreadsheet formats that can be read are WK1, WK3, WK4, 123, and XLS. These file formats are supported by a variety of software packages. The specific software used to prepare that file is unimportant, so long as the file conforms to the published standards for the format used.
The New CSC can read all spreadsheets designed for the old Credit Subsidy Model.
How does the CSC find your cash flow estimates in the spreadsheet?
The data needed to calculate a subsidy must be organized, as described below, and placed in a named range within the spreadsheet. Commercial spreadsheet software packages provide the means to associate a name with a rectangle of rows and columns. Range names are not case sensitive.
The CSC will consider data within the named range only. The CSC will not consider data that are outside of the named range.
When the CSC looks for named ranges within a spreadsheet, several things might happen:
If operating in command line mode, the range specified on the command line will be used. If it cannot be found, processing will end with an error message;
If operating in a window, the CSC will open the spreadsheet and find the defined range names. Then,
If there is only one range defined, it will use that range.
If there is more than one range defined:
The CSC will compare the range names to the default names you have specified, in the order listed. If it finds a range name that matches one or your default names, it will use that range.
If none matches your default names, the CSC will present a list of the ranges that are defined in the spreadsheet and you can make a selection from the list.
The minimum size of a named range is two columns and six rows. If the range is smaller, it is not possible to have a meaningful result and an error message will be displayed.
There is no maximum size of a range, other than those limitations inherent in the spreadsheet software.
A direct loan example
The following is an example of how a very simple direct loan program would appear in a spreadsheet. Lets suppose that a credit program is as follows:
Term: 1 year, with equal semiannual payments of principal, plus accrued interest.
Borrowers rate: 5 percent
Fees charged: None
The cash flows in a spreadsheet would look like this:
|
|
|
|
|
A |
B |
C |
|
|
|
|
1 |
Name |
DL example |
|
2 |
Description |
Anything |
|
3 |
Program type |
Direct |
|
4 |
Timing |
Simple annual |
|
5 |
Disbursements |
240 |
|
6 |
Principal payments |
120 |
120 |
7 |
Interest payments |
12 |
6 |
8 |
End |
|
|
|
|
|
|
The timing is described in more detail below. In this example, it indicates that disbursements occur at the beginning of the period and receipts (such as principal and interest payments) occur at the end of the period. Consequently, interest is paid on the entire loan for the first year and for half the loan in the second year.
In this example, the named range should include cells A1 through C8.
What are keywords?
Keywords are used in the left-most column of the data. They indicate the kind of data that will be provided in the columns to the right. For example, the keyword principal payments on line 6, indicates that the columns to the left contain the amounts of principal payments that are projected in successive years.
Keywords may be abbreviated to the shortest length that will distinguish them from other keywords. The table below lists keywords, their minimum abbreviation, and acceptable aliases. Keywords may contain any mixture of upper and lower case letters. For example, the following are equivalent: output, OUTPUT, Output, OutPut.
A loan guarantee example
The following is an example of how a very simple loan guarantee program would appear in a spreadsheet. Lets suppose that a credit program is as follows:
Term: 2 years
Loan level: $500 million
Timing: Collections and payments are made on the first of the month.
Default assumptions: 20 percent of amounts guaranteed, in the second year
Fees charged: 1 percent of amount guaranteed
The cash flows in a spreadsheet would look like this:
|
|
|
|
|
G |
H |
I |
|
|
|
|
3 |
Name |
LG example |
|
4 |
Description |
Anything |
|
5 |
Program type |
Guaranteed |
|
6 |
Timing |
Annual, begin |
|
7 |
Disbursements |
500 |
|
8 |
Annual fees |
5 |
5 |
9 |
Payments on defaults [end] |
|
100 |
10 |
End |
|
|
In this example, the named range should include cells G3 through I10. Note that payments on default are assumed to take place at the end of the year (as shown by the [end] on line 9). This technique is discussed in more detail below.
Can spreadsheet formulas be used in the cash flows?
Spreadsheet formulas can be used without restriction. When the CSC reads the spreadsheet and encounters a formula, it uses the result of the most recent evaluation of the formula by the spreadsheet software. It does not recalculate formulas.
Can comments be used in the cash flows?
A comment is any row in which the left-most cell is blank or contains text that begins with an asterisk or a double dash. When the CSC finds a row like this, the entire row is ignored. This feature can be used to annotate spreadsheets or to make a row of data invisible without actually deleting it from the spreadsheet.
For example, here is a loan guarantee example with comments added:
|
G |
H |
I |
3 |
Name |
LG example |
|
4 |
Description |
Anything |
|
5 |
Program type |
Guaranteed |
|
6 |
|
1998 |
1999 |
7 |
Commitments |
500 |
|
8 |
Disbursements |
500 |
|
9 |
Upfront fees |
5 |
|
10 |
Payments on defaults |
|
100 |
11 |
End |
|
|
Row 6 has a blank cell in the left-most column (column G). As a result, the entire line will be ignored.
Heres another example of how comments could be used:
|
A |
B |
C |
1 |
Name |
DL example |
|
2 |
Description |
Anything |
|
3 |
Program type |
Direct |
|
4 |
Obligations |
240 |
|
5 |
Disbursements |
240 |
|
6 |
Principal payments |
60 |
180 |
7 |
*Interest payments |
6 |
12 |
8 |
Interest payments |
3 |
6 |
9 |
End |
|
|
In this example, row 7 will be ignored. This example shows how you might keep two or more alternatives in a spreadsheet for use when the occasion arises. Incidentally, in this example the named range should include cells A1 through C9.
How can cash flows be associated with
disbursement periods?
Because cash flow observations are discounted to the time of disbursement, (as required by the Federal Credit Reform Act), a relationship between disbursements and cash flow observations needs to be established. This can take place in several ways:
If a single disbursement take place, it is immediately clear how the cash flows should be discounted. For example, if disbursement takes place at the beginning of year 1 and annual fees are received at the beginning of years 1-10, then the first fee coincides with the disbursement and is not discounted. The second fee occurs one year after disbursement and is discounted 1 year, and so forth.
If multiple disbursements take place and cash flow data are provided for each disbursement period, then each set of disbursement period cash flows is related to a single period of disbursements and the methods described above may be used.
If multiple disbursements take place and the cash flow observations are NOT provided for each disbursement period, the CSC must make some assumptions about the relationship between cash flow observations and disbursements. The methods that the CSC uses are described in the working paper How the subsidy and its components are derived from cash flow observations.
The third method is the least desirable. Any assumptions about the relationship between disbursements and cash flow observations that the CSC might make would not consider important facts about individual programs. If possible, it should be avoided.
Consider the following example:
|
A |
B |
C |
1 |
Name |
DL example |
|
2 |
Description |
Anything |
|
3 |
Program type |
Direct |
|
4 |
Timing |
Simple annual |
|
5 |
Disbursements |
100 |
100 |
6 |
(1) Principal payments |
0 |
100 |
7 |
(1) Interest payments |
5 |
5 |
8 |
(2) Principal payments |
0 |
100 |
9 |
End |
|
|
In this example, two loans are disbursed in successive years. The first loan is for a term of two years and pays interest at five percent; the second is for a term of one year and pays no interest. Principal is due in full at the end of the term of the loan.
The timing is described in more detail below. In this example, it indicates that disbursements occur at the beginning of the period and receipts (such as principal and interest payments) occur at the end of the period.
On line 6, the (1) at the beginning of Principal payments indicates that the values on that line are associated with the first period of disbursements. It is clear, then, that the first loan pays interest of five percent and that the second loan pays no interest.
Similarly, on lines 7 and 8, the (1) and (2) show the disbursement period to which the cash flows belong.
As a general rule, subsidies will always be more accurate when the relationship between disbursements and cash flow observations is shown explicitly, as above.
How can the timing
and frequency of cash flows be specified?
Cash flow estimates can be made by:
YEAR The values in successive columns would pertain to successive years
SEMIANNUAL The values in successive columns would pertain to successive semiannual periods
QUARTER The values in successive columns would pertain to successive quarters
MONTH The values in successive columns would pertain to successive months
Cash flows can be a mixture of frequencies. For example, disbursements and upfront fees could be shown on a quarterly basis and default payments could be shown on an annual basis. If a frequency is not specified, the default value is annual.
Within any frequency, the receipts and payments can be assumed to occur at the beginning of the period, at the middle of the period (equivalent to occurring throughout the period), or at the end of the period. If timing is not specified, the default value is middle of the period.
In the absence of a specification about the frequency and timing of cash flows, they will be treated as annual frequency and occurring throughout the year.
The following keywords may be used to specify the timing, frequency, and starting point of a row of cash flow estimates. As described below, these keywords can be used on a timing line or placed in square brackets with the keyword.
Keywords for frequency of cash flows:
ANNUAL
YEAR
SEMIANNUAL
QUARTER
MONTH
Keywords for timing of payments within the period:
BEGINNING
MIDDLE
END
Keywords from previous versions of the model are supported. These keywords imply that the frequency is annual and that the pattern within the annual amounts is one of the following:
BEGINNING-OF-MONTH Transactions occur in equal amounts at the beginning
of each month of the year
MID-MONTH Transactions
occur in equal amounts at the middle of each month of the year
END-OF-MONTH Transactions occur
in equal amounts at the end of each month of the year
BEGINNING-OF-QUARTER Transactions occur in equal amounts
at the beginning of each quarter of the year
MID-QUARTER Transactions
occur in equal amounts at the middle of each quarter of the year
END-OF-QUARTER Transactions occur in
equal amounts at the end of each quarter of the year
BEGINNING-OF-SEMI Transactions occur in equal
amounts at the beginning of each semi-annual period of the year
MID-SEMI
Transactions
occur in equal amounts at the middle of each semi-annual period of the year
END-OF-SEMI Transactions
occur in equal amounts at the end of each semi-annual period of the year
BEGINNING-OF-YEAR Transactions occur at the
beginning of the year
MID-YEAR Transactions
occur at the middle of the year
END-OF-YEAR Transactions
occur at the end of the year
CONTINUOUS Transactions
occur continuously throughout the year and do not tend to be clustered at any
point in time, such as those listed above.
When this keyword is used, the quarterly distribution factors are used.
There are several places where the timing and frequency of cash flows can be specified. Several examples follow to illustrate the alternatives.
Example: Determining the starting point when no information is given
|
A |
B |
C |
1 |
Name |
DL example |
|
2 |
Description |
Anything |
|
3 |
Program type |
Direct |
|
4 |
Obligations |
240 |
|
5 |
Disbursements |
240 |
|
6 |
Principal payments |
60 |
180 |
7 |
Interest payments |
3 |
6 |
8 |
End |
|
|
The frequency of the cash flows and the periods of time they represent are not stated. In this case, the cash flows are assumed to be annual and occur continuously throughout the year. The values in the first column are determined from the value for budget year, which is set in the preferences section of the CSC. By default, the first set of cash flows is assumed to belong to the current year or the year before the budget year, the second to the budget year, the third to the year after the budget year, and so forth. In this case, the cash flows belong to the current year. If the budget year in the preferences section were 2000, the first column of these cash flows would be treated as taking place in 1999.
Example: Specifying the budget year in the spreadsheet
|
G |
H |
I |
3 |
Name |
LG example |
|
4 |
Description |
Anything |
|
5 |
Budget year |
2000 |
|
6 |
Program type |
Guaranteed |
|
7 |
Commitments |
500 |
|
8 |
Disbursements |
500 |
|
9 |
Upfront fees |
5 |
|
10 |
Payments on defaults |
|
100 |
11 |
End |
|
|
In this example, the budget year is specified and has the same effect as described in example 1 (where the default value was 2000). The first column of these cash flows would be treated as taking place in 1999.
Example: Specifying the frequency of the cash flows in the spreadsheet with a timing
keyword
|
G |
H |
I |
3 |
Name |
LG example |
|
4 |
Description |
Anything |
|
5 |
Timing |
Qtr, beginning |
|
6 |
Program type |
Guaranteed |
|
7 |
Commitments |
500 |
|
8 |
Disbursements |
500 |
|
9 |
Upfront fees |
5 |
|
10 |
Payments on defaults |
|
100 |
11 |
End |
|
|
On row 5, the timing of the cash flows is stated to be quarterly, with transactions occurring at the beginning of the quarter. The year is not stated and would be determined as described in example 1. Thus, the disbursement (row 8) takes place on October 1, 1998 (the beginning of the first quarter of fiscal year 1999), and the payment on default (row 10) takes place on January 1, 1999 (the beginning of the second quarter of fiscal year 1999).
Example: Specifying the starting point for cash flows in the spreadsheet by modifying a
keyword
|
G |
H |
I |
3 |
Name |
LG example |
|
4 |
Description |
Anything |
|
5 |
|
|
|
6 |
Program type |
Guaranteed |
|
7 |
Commitments |
500 |
|
8 |
Disbursements |
500 |
|
9 |
Upfront fees |
5 |
|
10 |
Payments on defaults [2015] |
100 |
100 |
11 |
End |
|
|
In this example, the defaults described in example 1 apply: fees, commitments, and disbursements take place on October 1, 1998. On row 10, the starting point is specified as 2015. Thus the first data column (column H) is treated as 2015 and the next column (column I) is treated as 2016, for this row only. The default payments of 100 are assumed to take place throughout the years 2015 and 2016.
Example: Specifying the frequency and timing
|
G |
H |
I |
3 |
Name |
LG example |
|
4 |
Description |
Anything |
|
5 |
|
|
|
6 |
Program type |
Guaranteed |
|
7 |
Commitments |
500 |
|
8 |
Disbursements |
500 |
|
9 |
Upfront fees [qtr, beginning, 1997:2] |
5 |
5 |
10 |
Payments on defaults [2015] |
100 |
100 |
11 |
End |
|
|
This example is identical to the previous example, except for the changes on row 9, where the upfront fees are specified as taking place quarterly and at the beginning of the quarter and that the first column refers to the January-March quarter of FY 1997. Thus, the fees in H9 were received on January 1, 1997, and the fees in I9 were received on April 1, 1997.
The format to indicate starting point for cash flows for annual cash flows
Y where Y is a four-digit year
Example: [ 1996 ]
The format to indicate starting point for cash flows for semiannual, quarterly, or monthly annual cash flows
Y:P where Y is a four-digit year and P is the period within the year
Examples: [ monthly, 1997:1 ] for monthly frequency; refers to
October 1996
[ qtr, 1997:1 ] for quarterly frequency; refers to
October-December, 1996
[ qtr, 1997:3 ] for quarterly frequency; refers to
April-June, 1997
[ semiannual, 1997:1 ] for semiannual frequency; refers to October 1996 - March 1997
Y where Y is a four-digit year
Examples: [ monthly, 1997 ] for monthly frequency; refers to
October 1996
[ qtr, 1997 ] for quarterly frequency; refers to
October-December, 1996
[ semi, 1997 ] for semiannual frequency; refers to
October 1996 - March 1997
How can rate assumptions be specified in your spreadsheet?
You can specify the interest rates assumptions to use in your spreadsheet. To do so, use the keyword rate assumptions in the left-most column of the range and indicate the rates to use in the next column to the right. See below for further details.
However, there are some reasons why the rate assumptions choice may not take effect.
First, if the purpose is budget then only the budget assumptions for the appropriate budget year can be used. See the next topic for additional deails.
Second, the rate assumptions chosen in the rate selection dialog box may take precedence. In the General preferences dialog (invoked from the system menu by selecting Actions-Preferences-General preferences) you can select whether the dialog box choices or the spreadsheet choices take precedence.
How does the purpose affect the calculation methods?
The purpose may be set to budget or analysis with the following results:
Analysis: There are no restrictions placed on the choices you make for interest rate assumptions.
Budget: Only the budget assumptions for the budget year indicated in your spreadsheet (or in the dialog boxes if none is given in the spreadsheet) will be used. Attempting to a rate other than the appropriate budget assumptions will result in a warning message.
Note that purpose can be specified in the spreadsheet and in the General preferences dialog. If specified in both places, precedence is established by the choice under conflict resolution in the General preferences dialog.
How are cohorts distinguished in the cash flows?
The previous release distinguished cohorts in a spreadsheet by new lines with enacted budget authority or obligations or commitments.
For compatibility purposes, these lines will continue to mark the beginning of a new cohort. However, you may make the beginning of a new cohort more explicit by using the new cohort keyword. This will allow you to explicitly state the year of the cohort and to eliminate the obsolete lines. While these changes are not required, they may help to simplify the appearance of your spreadsheets and make them more easily understood.
Keywords to describe the cash flows as a whole
This group includes such items as the name of the program, whether the program involves direct loans or loan guarantees, and the names of files to use for output reports. The complete list of keywords and how they should be used is shown in a table below. The rows with these keywords must precede the detailed cash flow rows.
Table 1. -- Keywords for cash flows as a
whole |
|
||||||||||||||
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
||||||
|
|
Number |
|
|
|
|
|
|
|
||||||
|
|
of value |
|
Description of the |
|
|
|
|
|
||||||
Keyword |
|
columns |
|
value columns |
|
Limitations |
|
Req'd? |
|
||||||
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
||||||
Amounts |
|
1 |
|
Units of
measure for the cash flows |
|
Must be
one of the following: Dollars Thousands Millions Billions |
|
No |
|
||||||
Budget
year |
|
1 |
|
The budget
year for which these estimates were prepared. Generally, the first cohort of data is the "current
year" (the year before the budget year) and the second cohort of data is
for the budget year. |
|
Must be a
four-digit year. Must be 1992 or
later. |
|
No |
|
||||||
Cohort |
|
1 |
|
Signals
the beginning of a new cohort of cash flows (see discussion of How cohorts
are distinguished in the cash flows) |
|
Four-digit
fiscal year for the cohort |
|
No |
|
||||||
Compute
single effective rate |
|
1 |
|
Enter
yes (or simply y) to have the single effective rate calculate. Enter no or simply omit this line if you
do not want the single effective rate computed. |
|
Must begin
with y or n |
|
No |
|
||||||
Description |
|
1 |
|
Any
description of this set of cash flows that might be useful. |
|
None |
|
No |
|||||||
Import
file |
|
1 |
|
The
filename to use for an output file in "file-import" format. This format is used to import CSC output
data into a spreadsheet. (See
"Output") |
|
Must be a
valid filename. If present, the
output file will be created. If
absent, no output file is created and no message will be issued. |
|
No |
|||||||
Loan type |
|
1 |
|
When
disbursements are made in multiple periods, use this line to indicate whether
the construction or multiple like loan pattern should be used. |
|
Must be: Construction Multiple like loans Construction
is assumed when this line is omitted. |
|
No |
|||||||
Method |
|
1 |
|
When
disbursements are made in multiple periods, use this line to specify that the
prorata method should be used in all cases.
If you omit this line, the CSC will use the reverse spendout method
(if possible) or the pro-rata (otherwise). |
|
Must be: Pro-rata Choose Choose
is assumed when this line is omitted. |
|
No |
|||||||
Name |
|
1 |
|
Name of
the credit program. This name will be
used extensively on output displays |
|
Must be
the first row of the cash flows. If
blank, "(none)" will be used.
Otherwise, no restriction. |
|
Yes |
|||||||
Output
file |
|
1 |
|
The
filename to use for a printable output. (See "Import") |
|
Must be a
valid filename. If present, the
output file will be created. If
absent, no output file is created and no message will be issued. |
|
No |
|||||||
Program
type |
|
1 |
|
Identifies
the spreadsheet as a direct loan or a loan guarantee program. |
|
Must be
"direct" or "guaranteed" (without the quote marks). |
|
Yes |
|||||||
|
Purpose |
|
1 |
|
Designates
the purpose of the calculations. The
CSC will use methods appropriate to the purpose. |
|
Must be
one of the following: Analysis Budget See How
the purpose affects the calculation methods above. |
|
No |
|
|||||
|
Q1-inflow Q2-inflow Q3-inflow Q4-inflow |
|
No limit |
|
Quarterly
factors to divide annual cash inflow data, on a percentage basis, into
quarters. Used for every year,
except, possibly, the first year. See
Q1-init-inflow, below. |
|
Individual
quarters must be in the range 0.1 to 99.0 and the sum of the factors for the
four quarters must add to 100.0.
Applicable only to annual cashflows that use the
"continuous" timing option. |
|
No |
|
|||||
|
Q1-outflow
Q2-outflow
Q3-outflow
Q4-outflow |
|
No limit |
|
As above,
for annual cash outflow data. |
|
Same as
above |
|
No |
|
|||||
|
Q1-init-inflow
Q2-init-inflow Q3-init-inflow Q4-init-inflow |
|
1 |
|
Quarterly
factors to divide annual cash inflow data into quarters, for the first
year only. See Q1-inflow, above. |
|
Same as
above |
|
No |
|
|||||
|
Q1-init-outflow
Q2-init-outflow
Q3-init-outflow Q4-init-outflow |
|
1 |
|
Same as
above for annual cash outflow data. |
|
Same as
above |
|
No |
|
|||||
Rate
assumptions |
|
1 |
|
Designates
the rate assumptions to use if the basket-of-zeros method will be used. Has no effect if the single effective rate
is used (See Single effective rate, below) |
|
Must be
one of the following: Actual Budget Custom The last
two may take additional parameters.
See How rates are selected above. |
|
No |
|
||||||
Sheet file |
|
1 |
|
Specify
the name of the output file in wk3 format |
|
Filename
with a wk3 extension. |
|
No |
|
||||||
Suppress
warnings |
|
1 |
|
Number of
the warning message to suppress.
Error messages cannot be suppressed. |
|
Warning
messages are in the range 20-99. |
|
No |
|
||||||
Timing |
|
1 |
|
Specifies
the within-period timing of the cash flows
See discussion above. |
|
See
discussion above. |
|
No |
|
||||||
Keywords to identify cash
flows for direct loan programs
This group includes keywords specifically for direct loan programs. These keywords can be used ONLY for programs identified as direct loan programs.
Table 2. -- Keywords for DIRECT LOAN cash
flows |
||||||||||
|
|
|
|
|
|
|
|
|
||
|
|
Number |
|
|
|
|
|
|
||
|
|
of value |
|
Description of the |
|
|
|
|
||
Keyword |
|
columns |
|
value columns |
|
Limitations |
|
Req'd? |
||
|
|
|
|
|
|
|
|
|
||
Default
effect on cash flows |
|
No limit |
|
Estimated
effect of defaulted loans on the principal, interest, and fee lines shown
above. These values should be the
amount by which scheduled principal, interest, and fees differ from actual
cash flows, due exclusively to loan defaults. |
|
Any
negative values |
|
No |
||
Disbursements |
|
No limit |
|
Disbursement
of direct loans, by fiscal year.
Include disbursement from obligations shown on the previous line
ONLY. Do not include disbursement of
loans obligated in other program years. |
|
Any
positive values |
|
Yes |
||
End |
|
None |
|
When the
subsidy model finds an END statement, it will stop scanning the spreadsheet
for more data. |
|
None |
|
No |
||
Interest
payments, scheduled |
|
No limit |
|
Scheduled
interest payments by borrowers, as specified in the loan contract, by the
time period in which these payments are scheduled to be received. Do NOT include adjustments for defaults or
prepayments. Include interest
payments from disbursements shown in previous disbursement lines ONLY. |
|
Any
positive amount. Negative numbers are
not allowed. |
|
No |
||
Losses
other than default |
|
No limit |
|
If actual
cash inflows of principal, interest, and fees are lower than the amounts
given by the contractual terms of the loans for reasons OTHER THAN DEFAULTS,
show those amounts here. |
|
Any
negative values |
|
No |
||
Obligations |
|
No limit |
|
Amounts of
direct loans obligated from authority provided, by fiscal year |
|
Any
positive values |
|
No |
||
Other
outflows |
|
No limit |
|
See
Outflows, other than disbursements |
|
Any
positive values |
|
No |
||
Other
losses |
|
No limit |
|
See
Losses other than default |
|
Any
negative values |
|
No |
||
Outflows,
other than disbursements |
|
No limit |
|
Any
outflows other than direct loan disbursements that are directly related to
the loans being disbursed. Do not
include administrative expenses. |
|
Any
positive values |
|
No |
||
Payments
on defaults |
|
No limit |
|
See
Default effect on cash flows |
|
See
Default effect on cash flows |
|
No |
||
Prepayments,
net |
|
No limit |
|
Estimated
effects of changes in the payment schedule from the terms of the contract due
to net prepayments. |
|
Increases
to inflows and reductions to outflows are shown as positive amounts;
increases to outflows and reductions to inflows are shown as negative
amounts. |
|
No |
||
Principal
payments, scheduled |
|
No limit |
|
Scheduled
principal payments by borrowers, as specified in the loan contract, for the
time periods in which these payments are scheduled to be received. Do NOT include adjustments for defaults or
prepayments. |
|
Any
positive values. Generally, the sum
of the scheduled principal payments should equal the loan disbursements. |
|
No |
||
Recoveries |
|
No limit |
|
Estimated
effect of losses recovered, by fiscal year. These amounts result from
disposition of collateral, actions by the borrower to restore the loan to
good standing, or similar actions. |
|
Any
positive values |
|
No |
||
Scheduled
interest payments |
|
No limit |
|
See
Interest payments, scheduled |
|
See
Interest payments, scheduled |
|
No |
||
Sched.
Interest payments |
|
No limit |
|
See
Interest payments, scheduled |
|
See
Interest payments, scheduled |
|
No |
||
Scheduled
principal payments |
|
No limit |
|
See
Principal payments, scheduled |
|
See
Principal payments, scheduled |
|
No |
||
Sched.
Principal payments |
|
No limit |
|
See
Principal payments, scheduled |
|
See
Principal payments, scheduled |
|
No |
||
Timing |
|
1 |
|
Specifies
the within-period timing of the cash flows
See discussion above. |
|
See
discussion above. |
|
No |
||
Keywords to identify cash
flows for loan guarantee programs
This group includes keywords specifically for loan guarantee programs. These keywords can be used ONLY for programs identified as loan guarantee programs.
Table 3. -- Keywords for LOAN GUARANTEE
cash flows |
||||||||
|
|
|
|
|
|
|
|
|
|
|
Number |
|
|
|
|
|
|
|
|
of value |
|
Description of the |
|
|
|
|
Keyword |
|
columns |
|
value columns |
|
Limitations |
|
Req'd? |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Annual
fees received |
No limit |
|
Amount of
fees received by the Government on an annual basis from guarantee commitments
shown on line Commitments. Do NOT include any adjustment for defaults. See lost
fees below. |
Any
positive values |
|
No |
||
Commitments
(face amount) |
No limit |
|
Total
amount of loan guarantee commitments made from this year's authority, by
fiscal year. This is the total face
value of the loans on which the Government has placed a full or partial
guarantee. |
Any
positive values |
|
Yes |
||
Default
payments |
No limit |
|
Amounts
paid by the Government to private lenders on claims due to defaults by the
borrower. |
Any
positive values |
|
No |
||
Disbursement
(of loans by private lenders) |
No limit |
|
Disbursements
of federally guaranteed loans by private lenders, by fiscal year. Include disbursements from loan guarantee
commitments shown on the Commitments line ONLY. |
Any
positive values |
|
Yes |
||
End |
None |
|
When the
subsidy model finds an END statement, it will stop scanning the spreadsheet
for more data. The END statement can
appear on any line in the range. |
None |
|
No |
||
Inflows
(other) |
No limit |
|
Amounts of
contractual inflows to the Govt, not included elsewhere, related to the
commitments (except for cancellations). Do not include fee receipts. Do NOT
include any adjustment for defaults. |
Any
positive values |
|
No |
||
Interest
subsidy payments |
No limit |
|
Payments
of interest to the private lender, made by the Government on behalf of the
borrower, as required by the loan contract. These
amounts should reflect the contract payments and should not be adjusted for
defaults. See saved interest
supplements below. |
Any
positive values |
|
No |
||
Lost fees |
No limit |
|
Amounts of
fees, annual or upfront, that are lost due to defaults |
Any
negative values |
|
No |
||
Miscellaneous
recoveries |
No limit |
|
Amounts of
miscellaneous recoveries made after default claims are made |
Any
negative values |
|
No |
||
Other
inflows (-) |
No limit |
|
See
Outflows, other |
Any
negative values |
|
No |
||
Other
inflows (+) |
No limit |
|
Any
payment received by the Govt related to the amounts shown on the
commitments line that are not shown on other cash flow lines. |
Any
positive values |
|
No |
||
Other
outflows |
No limit |
|
See
Outflows, other |
Any
positive values |
|
No |
||
Other
recoveries |
No limit |
|
See
Miscellaneous recoveries |
Any
negative values |
|
No |
||
Outflows
(other) |
No limit |
|
Any other
payment made by the Govt related to the amounts shown on the commitments
line, such as maintenance of collateral.
Do no include administrative expenses. |
Any
positive values |
|
No |
||
Payments
on defaults |
No limit |
|
See
default payments |
Any
positive values |
|
No |
||
Premiums
received |
No limit |
|
Amounts of
premiums received by the Govt, in addition to guarantee fees, related to the
amounts shown on the commitments line. |
Any
positive values |
|
No |
||
Saved
interest subsidies |
No limit |
|
Amounts
shown on the interest subsidy payments line that are not made because of
defaults. |
Any
negative values |
|
No |
||
Recoveries
on defaults |
No limit |
|
Amounts
recovered from disposition of collateral, reinstatement of loans to good
standing, or principal and interest payments from borrowers on loans taken
over from private lenders. |
Show
recoveries as negative amounts (because default payments are positive
amounts.) |
|
No |
||
Timing |
1 |
|
Specifies
the within-period timing of the cash flows. See discussion above. |
See
discussion above |
|
No |
||
Upfront
fees received |
No limit |
|
Amount of
fees received by the Govt when loans are disbursed. |
Any
positive values |
|
No |
Obsolete keywords
Several keywords used in previous version of the CSC are no longer needed, due to changes in methodology. These keywords do not cause errors, but are quietly ignored and have no effect on the calculation of the subsidy.
Table 4. -- Obsolete keywords |
||||||
|
|
|
|
|
||
|
|
When this |
|
|
||
|
|
keyword |
|
Description of the |
||
Keyword |
|
was last used |
|
value columns |
||
|
|
|
|
|
||
|
|
|
|
|
||
Account
type |
|
Release 8 |
|
To
distinguish between DISCRETIONARY and MANDATORY accounts. |
||
Calculate |
Release 9 |
|
Used to
set the level of detail in output displays. |
|||
Comparable
maturity |
Release 9 |
|
To determine
the interest rate that should be used to discount cash flows. |
|||
Deflators |
Release 8 |
|
To specify
the deflators to be used in baseline estimates. |
|||
Discount
rate |
Release 9 |
|
Used to
specify a discount rate to use. There
were no discount rates built into previous versions of the model. |
|||
Enacted
Subsidy BA |
Release 8 |
|
Budget
Authority for this program enacted, or estimated to be enacted, for the
"current year." |
|||
Estimate
type |
|
Release 8 |
|
To
distinguish whether to make estimates for POLICY, CURRENT SERVICES, or BUDGET
EXECUTION estimates. |
||
Outlays
from BA prior to current year |
Release 9 |
|
To provide
data that are needed to prepare complete estimates of total outlays, outlays
current, and outlay prior by fiscal year.
|
|||
Percent of
loan guaranteed |
Release 8 |
|
Average
percentage of the face amount of loans that are guaranteed by the Federal
Government. |
|||
Rounding |
|
Release 9 |
|
To provide
analyst control over the level at which rounding takes place. |
||
Table 5.-- Alphabetical listing of
keywords and shortest abbreviations |
||||
|
|
|
|
|
Keyword |
|
Category |
|
Shortest abbreviation |
|
|
|
|
|
Account
type |
|
Obsolete |
|
AC |
Amounts |
|
General |
|
AM |
Annual
fees received |
|
Loan
guarantee |
|
AN |
Budget
year |
|
General |
|
B |
Calculate |
|
Obsolete |
|
CA |
Cohort |
|
General |
|
COH |
Commitments
(face amount) |
|
Loan
guarantee |
|
COMM |
Comparable
maturity |
|
Obsolete |
|
COMP |
Compute
single effective rate |
|
General |
|
COMPU |
Default
effect on cash flows |
|
Direct
loan |
|
DEFA |
Default
payments |
|
Loan
guarantee |
|
DEFA |
Deflators |
|
Obsolete |
|
DEFL |
Description |
|
General |
|
DES |
Disbursement
of loans by private lenders |
|
Loan
guarantee |
|
DISB |
Disbursements |
|
Direct
loan |
|
DISB |
Discount
rate |
|
Obsolete |
|
DISC |
Enacted
Subsidy BA |
|
Obsolete |
|
ENA |
End |
|
General |
|
END |
Estimate
type |
|
Obsolete |
|
EST |
Fees and
other income |
|
Direct
loan |
|
F |
Import file |
|
General |
|
IM |
Inflows
(other) |
|
Loan
guarantee |
|
INF |
Interest
payments, scheduled |
|
Direct
loan |
|
INT |
Interest
subsidy payments |
|
Loan
guarantee |
|
INT |
Loan type |
|
Obsolete |
|
LOA |
Losses
other than default |
|
Direct
loan |
|
LOS |
Lost fees |
|
Loan
guarantee |
|
LOST |
Method |
|
General |
|
ME |
Miscellaneous
recoveries |
|
Loan
guarantee |
|
MI |
Name |
|
General |
|
N |
Obligations |
|
Direct
loan |
|
OB |
Other
inflows (-) |
|
Loan
guarantee |
|
Other inflows (-) |
Other
inflows (+) |
|
Loan
guarantee |
|
Other inflows (+) |
Other
losses |
|
Direct
loan |
|
Other L |
Other
outflows |
|
Loan
guarantee |
|
Other O |
Other
recoveries |
|
Loan
guarantee |
|
Other R |
Outflows
(other) |
|
Loan
guarantee |
|
OUTF |
Outflows,
other than disbursements |
|
Direct
loan |
|
OUTF |
Outlays
from BA prior to current year |
|
Obsolete |
|
OUTL |
Output
file |
|
General |
|
OUTP |
Payments
on defaults |
|
Loan
guarantee |
|
PA |
Percent of
loan guaranteed |
|
Obsolete |
|
PER |
Premiums
received |
|
Loan
guarantees |
|
PRE |
Prepayments,
net |
|
Direct
loan |
|
PRE |
Principal
payments, scheduled |
|
Direct
loan |
|
PRI |
Private
lender disbursements |
|
Loan guarantee |
|
PRI |
Program
type |
|
General |
|
PRO |
Purpose |
|
General |
|
PU |
Q1-inflow
through Q4-inflow |
|
General |
|
Q1-I through Q4-I |
Q1-init-inflow
through Q4-init-inflow |
|
General |
|
Q1-INFLOW- through Q4-INFLOW- |
Q1-init-outflow
through Q4-init-outflow |
|
General |
|
Q1- OUTFLOW- through Q4-OUTFLOW- |
Q1-outflow
through Q4-outflow |
|
General |
|
Q1-O through Q4-O |
Rate
assumption |
|
General |
|
RA |
Recoveries |
|
Direct
loan |
|
REC |
Recoveries
on defaults |
|
Loan
guarantee |
|
REC |
Repeat |
|
Obsolete |
|
REP |
Rounding |
|
Obsolete |
|
RO |
Saved
interest supplement |
|
Loan
guarantee |
|
SAV |
Scheduled
principal payments |
|
Direct
loan |
|
Scheduled P |
Sched.
principal payments |
|
Direct
loan |
|
Sched. P |
Scheduled
interest payments |
|
Direct
loan |
|
Scheduled I |
Sched.
interest payments |
|
Direct
loan |
|
Sched. I |
Sheet file |
|
General |
|
SH |
Skip |
|
Obsolete |
|
SK |
Suppress
warnings |
|
General |
|
SU |
Timing |
|
General |
|
T |
Upfront
fees received |
|
Loan
guarantee |
|
U |