OMB
      Home
       
Office of Management and Budget
Executive Office of the President
  Site Search     
 

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.

 

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