The spreadsheet
is set up in two sections. The first worksheet (Figure 7.1) provides
input formats for entering data into the program. The maturity
date of the bond (YEAR ENDING DATE) is entered in the form of
"month.day.year" as if it were a six-digit number. This
date is used to label the output table and to calculate half-year
interest (if appropriate). The rate at which surplus cash is invested
should be entered as a decimal (i.e., "7%" is "0.07").
There is a place for entering the schedule for repaying the principal
on the bond, and the coupon rate (interest rate) attached to each
repayment. Normally the coupon rate will increase with time for
the same bond issue. Coupon rate should be entered as a whole
number (i.e., a coupon at "7.25%" is "7.25");
coupon rates generally increase by fractions of a point (0.25
and 0.5 are common). The worksheet provides space for a 20-year
bond issue; the repayment schedule may be lengthened or shortened
as needed.
TAX INCREMENT FINANCING WORKSHEET INPUT FORMATS YEAR-ENDING DATE MMDDYY SIZE OF LOAN ...... CAPITALIZED INTRST ...... TAX INCREMENT REV ...... INVEST RATE, SURPL ...... PRINCIPAL REPAYMNT COUPON RATE YR 1 ...... ...... YR 2 ...... ...... YR 3 ...... ...... YR 4 ...... ...... YR 5 ...... ...... YR 6 ...... ...... YR 7 ...... ...... YR 8 ...... ...... YR 9 ...... ...... YR 10 ...... ...... YR 11 ...... ...... YR 12 ...... ...... YR 13 ...... ...... YR 14 ...... ...... YR 15 ...... ...... YR 16 ...... ...... YR 17 ...... ...... YR 18 ...... ...... YR 19 ...... ...... YR 20 ...... ...... Figure 7.1
The second worksheet (Figure 7.2) presents an analysis of the
costs of the bond issue and the schedule by which the total debt
is retired.
TAX INCREMENT FINANCING ANALYSIS TABLE TAX INVESTED CUMLTV DEBT YR DEBT INCREMNT SURPLUS CASH COVER END PRINCIPL INTERST SERVICE REVENUES EARNINGS SURPLUS RATIO 1 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 2 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 3 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 4 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 5 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 6 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 7 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 8 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 9 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 10 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 11 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 12 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 13 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 14 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 1 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 15 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 16 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 17 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 18 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 19 xxxx xxxx xxxx xxxx xxxx xxxx xxxx 20 xxxx xxxx xxxx xxxx xxxx xxxx xxxx Ttl xxxx xxxx xxxx xxxx Figure 7.2
The calculations are all fairly simple, although the procedure
for calculating interest is somewhat roundabout. Since portions
of the debt carry different interest rates, the program calculates
the annual interest for each portion of principal, multiplying
it by the appropriate coupon rate. The annual interest payment
is the sum of the interest on the outstanding portions of the
principal. There is a column to the left of the analysis table,
called CUMULATOR, where the total individual portions of interest
are calculated and totaled. The Analysis Table also calculates
the total principal, interest, debt service, and tax increment
revenues generated by the project.
Because of the logical flow of calculations in the Analysis Table,
the spreadsheet works much more efficiently if it calculates row-by-row
rather than column-by-column. You should set the "order of
recalculation" command to perform this operation.
If you expand the analysis beyond twenty years, remember to insert
the additional rows before the last row of the worksheet (otherwise
the four totals at the bottom of the worksheet will be inaccurate).
This will require a reformulation of the YEAR ENDING entry for
the (otherwise unchanged) last row to reflect the fact that it
is now year 25 rather than year 20 (use the "Edit" command
to make the necessary change).
Finally, if you are following the formulas in the spreadsheet,
you will notice that many of the calculations have "error
traps" in them. The formulas are designed to ignore invested
surplus and debt coverage ratios once the bonds are paid, and
to return "NA" (not applicable) once that occurs. The
formula for INVESTED SURPLUS EARNINGS is also designed to avoid
calculating "negative investment" if there is no surplus,
or even a shortfall.