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