Basic Debt Calculation



OverviewThere are two sources of capital used to finance business ventures, debt and equity. Debt holders (lenders) are first to be paid but receive a finite return on their investments whereas equity holders (owners) receive a residual return on their investment after debt holders have been repaid.There are two types of lenders that finance affordable housing, conventional lenders (aka “hard money lenders”) and subordinate lenders (aka “soft money lenders”). Conventional loans have mandatory debt service and usually have first lien hold position in the property. Subordinate lenders, such as Cities, Counties, and certain state programs are repaid from residual receipts.Bonds are similar to loans in that they both result in capital being provided to the venture based on the promise to make a series of payments. A business may sell bonds to investors or borrow money from a lender to raise capital—both are considered debt. Some conventional loans are due before the principal has been repaid through payments (aka “have a balloon payment”). These are common in affordable housing as many lenders anticipate there being a capital event after 15 years of operation. For example a loan that is, “due in 17 with a 30 year am”, means the loan will be sized as if the principal amount would be repaid in 30 years but the loan is nonetheless due after 17 years. Being due early does not affect the sizing of the loan or the payment.Affordable housing partnerships typically aim to borrow the most conventional debt possible and therefore need to calculate the largest supportable loan. Lenders impose two constraints when sizing a loan: debt service and loan to value. The maximum loan amount will be the lesser of these two constraints.Since subordinate loans don’t have mandatory repayments they typically don’t have constraints like conventional debt.Warning: Subordinate loans need to pass a “true debt test” for their interest payments to be tax deductible. Assuming they bear a low interest rates they typically pass the test.In the following pages we will explain how to calculate the maximum supportable conventional loan based on the loan to value constraint and the debt service constraint. This white paper is accompanied by an Excel file showing the calculations in action and a video showing the calculations being used. Enjoy! Calculating the DCR ConstraintTo determine the loan amount based on a debt service constraint use the present value function.=-PV(rate, nperiods, payment)Coding: Putting a negative sign in front of the function makes the resulting loan amount positive. Be sure the rate, number of periods, and payment are all converted to monthly expressions to model interest being compounded monthly.Example: rate = 6%, amortized over 30 years, annual payment = 200,000=-PV(6%/12, 30*12, 200000/12)Coding: Type “6%” rather than “0.06” so the formula is easier to read. Type “30*12” rather than “360” so the formula is easier to troubleshoot. Don’t actually type assumptions in the formula but rather cell references to assumptions.=-PV(LoanRate/12, LoanTerm*12, LoanPayment/12)Coding: Name cells with assumptions so the formulas are more readable. “LoanTerm” is much easier to understand than “Sources!K19”.Conventional lenders require mandatory payments meet a debt coverage ratio (DCR), meaning only a portion of operating income can be underwritten as debt service. DCRs are stated as a ratio like 1.15 : 1.00 meaning you must have $1.15 of operating income for every $1.00 of debt service. Since all DCRs are stated in relation to $1.00, the second portion of the ratio is often left unsaid. For example, “the lender is underwriting to a 1.15 DCR.”Example: What is debt service when net operating income (NOI) is 230,000 and DCR of 1.15?= NOI/DCR=-PV(6%/12, 30*12, (230000/1.15)/ 12)=-PV(LoanRate/12, LoanTerm*12, (NOI/DCR)/12)Warning: Be sure the sum of all mandatory debt service is less than NOI/DCR or you will be overstating your supportable debt. Tranche B loans based on project based subsidies can be particularly tricky.Calculating the LTV ConstraintSince the property serves as the lender’s collateral, they won’t loan the borrower more money than the property is worth. This ensures they can recoup their investment through foreclosure and liquidation. To set this constraint, lenders will state a maximum percentage that the loan can be of the property’s value.Example: What is the loan constrained to if the lender imposes a maximum of 85% Loan to Value?=85% * 4000000=LoanMaxLtv * PropertyValueThe property value is typically determined using the formula for a perpetuity.=Perpetual Cash Flow / Capitalization RateExample: What is the property value assuming an NOI of 240,000 and a Cap Rate of 6%?=240000 / 6%=NOI / CapRateCalculating the Loan AmountSince the loan amount will be the lesser of the two constraints, the maximum supportable loan will determined using the Minimum function:=MIN( -PV(rate, nperiods, (NOI / DCR) ) , (NOI / CapRate) * LTV) Coding: Don’t nest complex formulas inside of formulas. Make separate cells for the primary calculations and for the final calculation. This will make auditing your models easier.Example: rate = 6%, 30 year am, 230,000 NOI, DCR = 1.15, CapRate = 5.5%, max LTV = 85%=MIN( -PV(6%/12, 30*12, (230000/1.15)/12) , (230000/5.5%) * 85% )=MIN(LoanConstraintDcr, LoanConstraintLtv)Coding: For flexibility add a third constraint in case the borrower or lender want to specify an arbitrary maximum loan amount.=MIN(LoanConstraintDcr, LoanConstraintLtv, LoanConstraintCustom)Coding: Named cells get sorted alphabetically so name them in a way that they will be grouped by topic. Notice all three named cells in the previous example start the same.Calculating the Loan PaymentOnce you know your loan amount you can calculate your actual payment. Warning: Don’t assume the payment equals your maximum debt service (NOI/DCR) because your loan could be constrained by LTV or a custom input.Calculate amortized payment amounts using the PMT function.Example: What is the payment with: loan amount 4,000,000, rate = 6%, and 30 year am? =-PMT(LoanRate/12, LoanTerm*12, LoanAmount) * 12Coding: The negative sign preceding the function makes the amount positive. Multiplying by 12 converts the payment to an annual expression.When your interest rate is based on an index like 30 day LIBOR, type the rate as a formula, “=1.25% + 2.5%” instead of the resulting rate “3.75%”. This will make updating the assumption in the LoanRate cell easier.These calculations are useful in determining your permanent conventional loan amount and payment. Construction loans and other bridge financing are calculated differently.Warning: When calculating the operating cash flow (aka “the waterfall”), the interaction of mandatory debt service, net operating income, subsidies, and cash flow can get confusing. Example: Rental Income = 400k, subsidy = 200k, tranche A is 30 year am, tranche B is 15 year am. Error: Tranche A is sized using 600k operating income and tranche B is sized using 200k, thereby double counting subsidy income and overstating conventional debt. Be sure the income on the cash flow is only being used once and the sum of all mandatory debt service falls within your debt service coverage ratio.Sometimes you will be surprised to see your actual DCR above the amount you specified in the DCR cell. This is because the loan is being constrained by LTV or a custom amount.PracticeCalculate the following amounts:Property valueLTV based loan constraintMaximum debt serviceDCR based loan constraintLoan amountAnnual debt serviceBased on the following assumptions:NOI = 300kCapRate = 5.25%DCR = 1.15MaxLTV = 80%Rate = 4.75%Term = 30 yrsCoding: Name all of the assumption and calculation cells to make your formulas more readable (e.g. PropertyValue and LoanAmTerm).Set the font color for all assumptions to blue and leave the font color for all calculations black. This will remind model users which cells can be edited and which should not.Questions?Call your helpful housing analysts at Kingdom Development, Inc., a California nonprofit public benefit corporationwilliam@951.538.6244rusty@714.357.1637 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download