Spreadsheet notes

From Deh Cho BridgeWiki
Revision as of 03:10, 17 March 2010 by NorthernRaven (Talk | contribs) (Changeable Values)

Jump to: navigation, search

I put together a little spreadsheet to try and calculate out the cashflows for the Bridge. It can be seen (and modified or downloaded) here.

Debt repayment

This is the fuzziest part, since there is little or no information available on the repayment schedule for the debt. The "Amort" sheet tries to come up with a generic repayment schedule so that principal and interest payments can be generated for the main Calc sheet each year.

Payments are made twice a year, at the beginning of June and December. Only interest payments were made until December 1, 2011, when principal payments begin. Because these are "Real Return" bonds, they are indexed to inflation, and I'm not at all clear as to the details of this. My Amort sheet does the following, one row for each payment period, producing 7 interest-only payment periods followed by an additional 70 principal+interest periods ending June 1, 2046.

  • Principal Balance - this is the amount of principal remaining from the previous payment period.
  • Inflation Protection - this is the adjustment to the principal balance to adjust for inflation. The Calc sheet has inflation calculations already, making an assumption of 2% annual inflation and producing an estimated CPI index for each year. The Inflation Protection amount is simply the Principal Balance multiplied by the CPI ratio (payment year CPI / 2008 CPI).
  • Indexed Principal - the sum of Principal Balance and Inflation Protection.
  • Fixed Principal Payment - This is my attempt at a fixing a principal payment amount. It takes a proportional amount of the remaining principal each payment period. So for the first period it would take 1/70th of the remaining principal, the next payment would be 1/69th of the remaining principal, and so on. I have a setting which lets me switch between using the Principal Balance or the Indexed Principal for this calculation.
  • Actual Principal Payment - If the revenues to the DCBC in a year aren't enough to cover the principal-interest payment and other costs, there is a provision that the GNWT will advance additional money, but the DCBC has to pay interest on this, and it is unlikely the repayment schedule would have been designed to cause this. Since some of the repayment options I've tried do cause this problem in the early years, the "Capped" option limits the principal repayment amount for a payment period to the maximum that year's revenues can support without an advance from the GNWT. If I turn the "Capped" option off, this column is simply the Fixed Principal Payment amount.
  • Interest - This is the Indexed Principal amount multiplied by the half of the annual Bond Rate of 3.17% (1.585%).
  • Total - the total payment for the period; this is Variable Principal Payment + Interest. This is the amount used for the Principal+Interest values on the main Calc sheet - each year there is the sum of the two semi-annual payments for the year.
  • Actual Principal Reduction - My assumption is that to retire a portion of the Principal Balance, a proportional amount of the Inflation Protection will have to be paid out with it. This column attempts to calculate how much the actual Principal Balance can be reduced by a payment of the Actual Principal Payment amount. For instance, a $1 million principal payment in 2019 might only reduce the balance by $800,000, because of the inflation protection.
  • Remaining Principal - the starting Principal Balance minus the Actual Principal Reduction. This value transfers to the Principal Balance column for the next payment period.

Calc

The main Calc sheet tries to calculation

Misc

[Some of the columns may be hidden on the spreadsheet.]

  • Year
  • CY - the Concession Year. There are 35 full concession years, plus the beginning Partial Concession Year (I have it as the 0 year).
  • Inflation - I'm just using a simple 2% inflation rate each year, although anyone with a better crystal ball can put their own numbers in.
  • CPI - the previous year's CPI multiplied by the the inflation rate for the year. The 2009 and earlier numbers are actual. I'm just using the annual CPI value, not monthly ones.
  • Tonne Rate - the inflation indexed rate per tonne ($6/tonne in 2002). Not directly used since the the tolls are charged per vehicle.
  • Base Toll Rate - the toll charge for a full tractor-train. Semis and small trucks pay 60% and 25% of this, respectively.

Revenue

  • Base Contribution - the annual contribution from the GNWT
  • Toll Revenue - using one of the traffic forecasts, the number of full, semi and small trucks is multiplied by the appropriate toll to produce the estimated toll revenue.
  • Base Amount - the total of the above; the amount payable from the GNWT to the DCBC. I've ignored the "sales and realty taxes" provision, as an unknown.

Expenditures

  • Annual Operating Costs - $600,000 to start, inflation indexed. It can be renegotiated every 5 years, but I've let it go on untouched.
  • Principal and Interest - aka Debt Service Payment, the amount of principal and interest payments for the year. Guesstimated by the Amort sheet.
  • Base Return Amount - this is the 4.5% guaranteed return on the Invested Equity. The equity amount can be entered on the Const sheet.
  • Annual Requirement - the sum of the above. What the DCBC needs at a minimum.

Results

  • Excess Amount - this is the difference between the Base Amount and the Annual Requirement. If negative, the GNWT will advance this amount as Additional Funds, charging interest. If positive, it is first applied to reducing any Additional Funds Outstanding balance, then paid out as the Enhanced and Shared returns.
  • Additional Funds Outstanding - Additional Funds that have been advanced by the GNWT. Any shortfall (a negative Excess) is added to this balance. The GNWT charges interest on it at its short term lending rate, but I haven't incorporated that yet.
  • Enhanced Return - if there is a remaining Excess, it is paid to the DCBC until their return on Invested Equity reaches 15%. So this would max out at $525,000 for $5 million in equity.
  • Shared Return - if there is still Excess after the above payments, the amount is shared equally between the GNWT and the DCBC.
  • Total DCBC Return - the sum of the Base, Enhanced and Shared Returns; this is the total return to the DCBC each year.
  • PV of Total Return - this attempts to calculate the present value of the Total Return, using the PV() present-value function. This gives some idea of the DCBC returns in 2010 dollars. The discount rate (by default 3%) is controlled by the PVDiscount setting on the Consts sheet.
  • Annual Return - the annual return rate represented by the DCBC return that year.

Changeable Values

The Consts sheet has a number of values which can be changed to revise the calculations for different scenarios. Generally, the yellow cells can be modified, and the others should be left alone.

  • Traffic Case - You can choose either the "Conservative" or "Probable" traffic estimates from the 2006 PROLOG report.
  • Traffic Availability Date - Guess the date the bridge is finished, and see how it affects the calculations. This mainly affects the amount of money for the Partial Concession Year.
  • Invested Equity - The maximum equity allowed by the Concession Agreement is $5 million. Change this to reflect the Atcon equity not being replaced, or other variations.
  • PVDiscount - the discount rate used in the "PV of Total Return" column on the main Calc sheet. My default is 3%, pretty much pulled out of a hat.

  • Immediate Indexing - Real return bonds are inflation indexed. I'm assuming that this indexing began immediately in 2008 after the bonds were issued. But by setting this variable FALSE, you can turn off the indexing until the first principal payment in December 2011.