Financial Modelv1.0.4

Capital Stack: Sources and Uses, Loan-to-Cost and Loan-to-Value by Layer

Add up everything an acquisition costs at closing, fund it with a senior loan, mezzanine debt, preferred equity and common equity, check that sources equal uses, and measure each layer against cost and against value.

Licensed under CC BY 4.0. Share and adapt it, commercially too, as long as you credit Globalbiz CRE Academy. What is open and what is not: License.

Learning objective

Learners will total the uses of funds in an acquisition and fund them with senior debt, mezzanine debt, preferred equity and common equity so that sources equal uses. They will measure each layer of the capital stack against total cost and against property value.

This is the complete model, not a sample or a preview. Every step, input, formula and download on this page works on its own, and nothing here requires an account or a paid subscription.

What counts as a use, and what loan-to-cost measures against

Every dollar paid at closing is a use here: the purchase price, the acquisition closing costs, the repairs and improvements, and the reserves funded upfront, interest and working-capital reserves included. Loan-to-cost is measured against that total. A CMBS sources and uses table does the same: Country Club Plaza (Benchmark 2019-B9) lists the purchase price, reserves and closing costs as its uses, funded by the mortgage loan, a mezzanine loan and the sponsor's cash. The other side has a case, and two major sources take it. Fannie Mae's Guide (Part II, Section 202.03C) counts acquisition costs up to 3% of the price but excludes the funded Replacement Reserve, operating reserves, interest rate cap costs, loan interest and prepaid items. HUD's Form 92264-A takes reserves for replacement out of the acquisition cost and treats working capital, an initial operating deficit and escrows as cash the borrower brings on top. On that view reserves are the sponsor's money parked in an account, not the cost of the property, and a lender that finances its own reserves inflates the base it is measured against. Measured that way, the same loan is a larger share of cost.

Which value the loan-to-value rows use

The loan-to-value rows divide by the appraised value. Two sources use a lower figure for a purchase. Federal bank regulators' Interagency Guidelines for Real Estate Lending Policies (12 CFR part 365, appendix A) say that for a loan to purchase an existing property, value is the lesser of the actual acquisition cost and the estimate of value. Fannie Mae's Guide (Part II, Section 202.03C) caps the value of a property acquired less than 12 months before commitment at the lower of the appraised value and the acquisition price with qualifying repairs and acquisition costs of up to 3% of the price. When the appraisal comes in above that cost, those rules give a higher loan-to-value than this model. In the default deal the appraisal, $21,000,000, is above the $20,000,000 purchase price, so under the Interagency rule a bank would measure the senior loan against $20,000,000 and its loan-to-value would be 66.00%, not the 62.86% this model shows. Fannie Mae's cap adds the $1,000,000 of repairs and the $400,000 of closing costs (within 3% of the price) to the price, which gives $21,400,000; that is above the appraisal, so under Fannie Mae's rule the appraised value stands and the senior loan-to-value stays at 62.86%.

1. Uses of Funds

Start from everything that has to be paid at closing, not only the price.

B7Purchase Price

$20,000,000

The contract price paid to the seller for the property.

B8Acquisition Closing Costs

$400,000

Costs of closing the purchase and the loans: title, legal, third-party reports, transfer and recording taxes, loan and acquisition fees.

B9Repairs and Improvements

$1,000,000

Capital work funded at closing, such as the repairs a property condition report calls for or a renovation budget.

B10Upfront Reserves

$600,000

Cash deposited into reserve accounts at closing: replacement, interest, tax and insurance, working capital.

B11Total Uses

$22,000,000

=PurchasePrice+ClosingCosts+Repairs+UpfrontReserves

Purchase price, closing costs, repairs and upfront reserves added together: all the money the closing needs.

Source

Benchmark 2019-B9 Mortgage Trust, free writing prospectus filed with the SEC (24 Jan 2019), Loan #2, Country Club Plaza, “Sources and Uses”: Purchase Price $74,000,000, Reserves $10,165,359 and Closing Costs $2,089,780 make Total Uses $86,255,139
HUD, Form HUD-92264-A, Supplement to Project Analysis (03/2010), Criterion 7, Section 223(f)

This model runs in your browser. No spreadsheet software of any kind is required. The .xlsx download is optional, and it opens at no cost in LibreOffice Calc and in Google Sheets.

Download as Excel (.xlsx)

Solved modelYour numbers with every formula live. Build it yourselfYour numbers, with every calculation cell left empty. Write each formula from scratch. Answer keyThe expected formula for every cell, to check your build.

Every download uses exactly the numbers on screen, and the cell addresses are the same in all three files. Values are stored at full precision and only displayed rounded.