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 didn’t 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 borrower’s 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.

No limit to the number of cohorts. The only limitation to the number of cohorts in a spreadsheet is the number of physical rows that can be represented in the spreadsheet.

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. Let’s suppose that a credit program is as follows:

Term: 1 year, with equal semiannual payments of principal, plus accrued interest.

Borrower’s 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. Let’s 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.

Here’s 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.

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.”

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 Gov’t, 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 Gov’t 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 Gov’t 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 Gov’t, 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 Gov’t 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