Spreadsheet notes

From Deh Cho BridgeWiki
Jump to: navigation, search

I put together a little spreadsheet to try and calculate out the cashflows for the Bridge.

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 - Calculates a principal payment. There are some old options built in here, but by default it is just taking the fixed amortization amount and applying the current inflation adjustment factor (Inflation Protection as a percentage of Principal Balance).
  • Actual Principal Payment - There is an old "Capped" option to prevent some of the old amortization schemes from putting the year into deficit, but by default this will just be the Fixed Principal Payment value.
  • 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 Actual 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.
  • PV of Principal/Interest/Total - these three columns calculate the Present Value of the payments, in 2010 dollars.

Calc

The main Calc sheet tries to calculate the yearly revenues, expenditures, and equity returns during the Sublease Term. The Principal and Interest payments are pulled from the Amort sheet. There may be additional columns (possibly hidden) than those described here, mainly for my own debugging or clarity.

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; this is factored in using the "AFORate" variable on the Consts sheet. The extra $15 million GNWT funding is currently introduced as Additional Funds for 2010 - this is activated by the DoExtraLoan control on the Consts sheet.
  • Enhanced Return - if there is a remaining Excess. By default this is up to an additional 15% (so the Base/Enhanced max is 19.5%), but setting the "SuperEnhanced" flag on the Const sheet can change this to a combined Base/Enhanced max of 15%.
  • 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 the same as the inflation rate) is controlled by the PVDiscount setting on the Consts sheet.
  • Annual Return - the annual return rate represented by the DCBC return that year.
  • PV of GNWT Return - this is the Present Value of the GNWT's return (half of the Shared Return) for the 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 the "Conservative" forecast from the original 2002 Traffic Forecasts (likely what the original feasibility was judged against), or either of the "Conservative" or "Probable" traffic estimates from the revised 2006 forecasts. The 2006 "Conservative" case is probably the most likely one right now. For real pessimism, you can also set it to use the actual 2005 traffic counts for all future years.
  • Add extra debt (aka "DoExtraLoan") - if TRUE, this feeds in the amount specified below ("Additional Debt") into the Additional Funds Outstanding column for the year 2010, representing the extra money the GNWT is putting into the project.
  • 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. Odd combinations like a date in 2010 and "Add Extra Debt", or opening dates beyond 2012, aren't tested and will produce wacky side effects.
  • Amortization - chooses one of the amortization schemes I've tried. The default "zero" value should be used - this is a straight amortization of the Base Debt, with payments indexed for inflation. Any other setting is unsupported.
  • Minimum Excess - this tries to adjust the principal repayments so that there is a minimum of at least this amount as Excess Amount. This can be used to ensure there will be at least a partial Enhanced Return each year, and has an effect of drawing up a portion of the large returns in later years into earlier years.
  • DCBC Equity - The amount the DCBC and its partners have as equity. The maximum Invested Equity allowed by the Concession Agreement is $5 million; I'm using a value of $1.4 million for the current situation with the extra $15 million GNWT funding, and $4.3 million for old historical runs before the Kablooey Events.
  • GNWT Equity - in the future I'll probably split out the equity returns in case the GNWT puts in some of its extra funding as equity. Right now this just adds to the DCBC Equity to produce the Invested Equity used in the spreadsheet.
  • Additional Funds Interest - this is the GNWT short term borrowing rate. I'm assuming an arbitrary 3%.

  • SuperEnhanced - I'm assuming the DCBC can receive 4.5% guaranteed Base Return, plus an additional 15% Enhanced Return, for a total of 19.5%, before the Shared Return with the GNWT kicks in. If I'm reading it wrong and the Base and Enhanced returns are 15% in total, just make this setting 0 for FALSE and things will be calculated that way.
  • Annual Inflation - I'm using 2% - this doesn't make much difference since almost all components (except equity returns) is indexed to inflation.
  • PVDiscount - the discount rate used in the "PV of Total Return" columns on the main Calc sheet. I'm using the inflation rate, to convert back to constant dollars.
  • 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.