Tax Increment Financing: Calculations


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.


609

 

1996 A.J.Filipovitch
Revised 11 March 2005