How do you build a sources and uses table for a property acquisition?

The Meridian House acquisition taken from purchase price to equity cheque, with the two errors that understate the equity.

Julian R. Sterling · from Real Estate Financial Modeling

A sources and uses table lists every payment needed to close (the uses), sizes the debt on its own tests, and solves equity as the balancing figure so that sources equal uses. On the illustrative Meridian House acquisition, a 23,500,000 price, 1,527,500 of acquisition costs and a 129,250 arrangement fee make 25,156,750 of uses; a 55 per cent loan-to-value loan provides 12,925,000, so the equity cheque is 12,231,750, 52.0 per cent of the price, not the 45 per cent that price times one minus LTV suggests.

Worked in full in Real Estate Financial Modeling by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →

The sources and uses table is the first block of any acquisition model and the first thing a reviewer reads, because the equity at closing is the denominator of every return the model reports. Get it wrong and the IRR and the multiple are wrong, however good the cash flow below them. It takes five lines and one check.

The assumptions

Meridian House, a multi-let office of 6,200 sq m.
Input	Value
Purchase price	23,500,000
Acquisition costs (transfer tax, agent, legal, survey)	6.5% of price
Loan-to-value limit	55%
Interest rate, interest-only	4.85%
Arrangement fee	1% of loan
Year-one NOI	1,307,440
Lender's minimum interest cover and debt yield	1.30x and 9%

Step 1: list the uses

Uses = price + acquisition costs + financing fees + reserves funded at closing

Acquisition costs = 23,500,000 × 6.5% = 1,527,500. Total acquisition cost = 25,027,500. The fee depends on the loan, so it is calculated after Step 2.

Step 2: size the loan, on every test

The loan is the lowest of what each of the lender's tests allows. On the inputs:

Loan permitted by each test.
Test	Formula	Loan
Loan-to-value, 55%	price × 55%	12,925,000
Interest cover, 1.30x	NOI / 1.30 / 4.85%	20,736,558
Debt yield, 9%	NOI / 9%	14,527,111

Loan-to-value binds at 12,925,000. At that loan the interest is 626,863 a year, cover is 2.09x and the debt yield 10.1 per cent, both well inside their limits. The full method, and how the binding test changes with the rate, is in how to calculate the maximum loan. The arrangement fee follows: 1 per cent of 12,925,000 is 129,250.

Step 3: solve the equity and check

Sources and uses at closing.
Uses	Amount	%
Purchase price	23,500,000	93.4%
Acquisition costs	1,527,500	6.1%
Arrangement fee	129,250	0.5%
Total uses	25,156,750	100%
Sources	Amount	%
Senior loan	12,925,000	51.4%
Equity	12,231,750	48.6%
Total sources	25,156,750	100%

Equity = total uses − debt = 25,156,750 − 12,925,000 = 12,231,750

In Excel, with uses in B3:B5 and the loan in B8: =SUM(B3:B5)-B8 for equity, and a check cell =ROUND(SUM(B3:B5)-SUM(B8:B9),0)=0 that the summary sheet reports. The loan is 55 per cent of the price but only 51.6 per cent of the total acquisition cost, which is the gap the equity fills.

This figure is what the returns sheet divides by. In the complete Meridian House model the levered IRR of 11.07 per cent and the 1.64x multiple are both measured against the 12,231,750 paid in at closing, so any use left out of the table flows straight into an overstated return. The order of work matters too: the fee depends on the loan and the loan on the tests, so the uses side cannot be totalled until the debt is sized.

What if: the leverage and the costs change

Equity at closing by loan-to-value, 6.5% costs.
LTV	Loan	Fee	Total uses	Equity	Equity % of uses
45%	10,575,000	105,750	25,133,250	14,558,250	57.9%
50%	11,750,000	117,500	25,145,000	13,395,000	53.3%
55%	12,925,000	129,250	25,156,750	12,231,750	48.6%
60%	14,100,000	141,000	25,168,500	11,068,500	44.0%
65%	15,275,000	152,750	25,180,250	9,905,250	39.3%

Each point of acquisition costs adds 235,000 of equity: at 4.5 per cent the cheque is 11,761,750, at 8.5 per cent 12,701,750. If the lender also requires six months of interest held in reserve at closing, 313,431 joins the uses and the equity rises to 12,545,181. Reserves are a use of cash on day one even if they come back later.

The common mistakes

Equity = price × (1 − LTV). That gives 10,575,000 and ignores costs and fees: the real cheque is 1,656,750 larger, 15.7 per cent more, and every return computed on the smaller figure is overstated.
Applying the LTV to total cost. 55 per cent of 25,027,500 is 13,765,125, 840,125 more than a lender lending on value will advance.
Leaving the fee out of the uses. It is paid at closing from the sources, usually netted from the loan advance, so it has to be funded by equity.
Hard-coding the equity. Equity is a plug. If it is typed in, sources and uses stop balancing the first time an input moves, and the check cell is the only thing that will notice.

Takeaway

List every use, size the debt on all of its tests, and let equity balance: 12,231,750 on a 23,500,000 purchase here, 52.0 per cent of the price. Put a sources-equal-uses check where the summary reads it. The free workbook for this case builds Meridian House on that architecture, with the check at the top of the summary sheet; how acquisition costs feed the yield is in net initial yield after purchaser's costs.

Questions readers ask

What goes in the uses side of a sources and uses table?

Every cash payment needed to close: the purchase price, transfer taxes and acquisition costs, the lender's arrangement fee, and any reserves the lender requires to be funded on day one, such as an interest or capex reserve. In the example the uses are 23,500,000 of price, 1,527,500 of costs and a 129,250 fee, 25,156,750 in total.

Is the loan sized on the purchase price or the total cost?

A loan-to-value limit applies to the property's value, which at acquisition is normally the price, not the price plus costs. Applying 55 per cent to the 25,027,500 total cost in the example would show a 13,765,125 loan, 840,125 more than the lender will lend. A loan-to-cost limit, common on development, is the one that uses total cost.

Why must sources equal uses?

Because the table describes one closing: every payment out has to be funded by debt or equity. Equity is solved as the balancing figure, so the check is automatic only if every use is listed. A model whose sources and uses do not balance has either left a cost out or double counted a source, and its equity IRR is computed on the wrong cheque.

Read the whole case

Meridian House is the complete worked model of chapter 19 of Real Estate Financial Modeling. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.

Get the book on Amazon →Free companion files

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada

Also on this site

How do you calculate the maximum loan on a commercial property?Size the loan on each test and take the lowest. On $3.6m of NOI the DSCR test gives $37.97m, below $39.0m on LTV and $40.0m on debt yield, so DSCR binds.
How do you calculate net initial yield with purchaser's costs?Receivable rent less non-recoverables, over price plus purchaser's costs. A building quoted at 6.93 per cent verifies at 5.70, 123 basis points lower.
How do you calculate the all-in cost of a commercial mortgage?Take the IRR of the borrower's cash flows, fees included: a 5.75% coupon with a 1% arrangement fee, lender costs and a 0.5% exit fee costs 6.23% all-in.
Should the exit cap rate apply to forward or trailing NOI?Forward NOI, the year after the sale, as at entry. On a 5-year hold with 3% growth, capping trailing NOI cuts exit value by 3.0% and the IRR by 53 bp.
Should you multiply a quarterly IRR by four to annualise it?No: compound it, (1 + q)^4 minus 1. A 3.091 per cent quarterly IRR is 12.95 per cent a year, not 12.36. Times four understates at every positive rate.
How do you build a tornado chart for a property IRR?Flex each input alone and sort by IRR swing. Here the exit cap at +/-50 bp swings a 7.28% levered IRR by 6.10 points, ahead of rental growth at 4.43.

Reading guide: real estate investing, finance and fund management → · All 1340 articles →

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.