Q: Couldn't we ask for trailing twelve months (TTM) profit and loss statement or, at a minimum, the same YTD period from last year so we could create projections without relying on annualized data?
A: Absolutely. You can ask for a TTM. However a TTM is not a profit and loss statement that most businesses routinely produce. If you were to receive the TTM or the same interim profit and loss statement for the prior year, you would certainly have better information upon which to base your annualized projection – especially if it were early in the year. Nonetheless, you would still be estimating, which may or may not involve annualizing the current year information as part of your process since you would not want to use the TTM in lieu of a forward-looking one- year projection. Ideally, the prior three years of historical information would already be part of your historical basis, or reference points, for supporting NOI projections.
Q: You mentioned the debt service constant is something we could get with an Excel spreadsheet from Shockproof! Training? Is there an Excel spreadsheet that has inputs for all of the values we discussed as a short cut?
A: The associated debt constant is available via Excel formula or we provide an online worksheet.
Worksheet: Debt Service Constant
Mathematical calculation of the Debt Constant
Its focus is on computing a unique debt service constant for any combination of interest rates, amortization periods, and payment frequencies, which can then be applied to a proposed term loan amount to determine annual debt service on that loan amount. The spreadsheet does not include a separate function for establishing a maximum term loan amount, given a property’s NOI, the lender’s minimum DSC, and the debt service constant for the loan terms.
Recall that the spreadsheet allows you to quickly calculate the unique debt service constant for any combination of interest rates, amortization periods, and payment frequencies. With the debt service constant in hand, you use it to calculate the annual debt service on a specific loan amount by multiplying the proposed loan amount by the debt service constant. If you subsequently want to change loan terms, you simply recalculate the debt service constant in the top of the spreadsheet using the revised loan terms and recalculate the annual debt service in the bottom portion of the spreadsheet.
If you want to change the loan amount while the terms remain constant, you use the spreadsheet to again quickly recalculate the annual debt service given the existing debt service constant applied to a different loan amount.
You can use the debt service constant to calculate the maximum term debt that a given NOI can support once you adjust the given NOI by a DSC ratio. For example, if the DSC minimum is 1.25, divide NOI by 1.25 and multiply the result by the debt service constant to arrive at the maximum term debt that the available risk adjusted NOI will support. There are examples of maximum term debt calculations on Slides 22 and 25 in the Session 6 slide deck.