I've been working on this modeling exercise in excel for a real estate development. There is an initial construction loan, and initial cash outflow from the land purchase, but the problem is that the construction costs are supposed to me amortized evenly over 12 months. So you have all this cash coming in immediately from the loan (in excess of the amount being spend on just the land and first month of construction), such that the period 1 cash flow is positive. But the IRR formula doesn't work if you don't have at least one negative cash flow value.

What should I do? Should I spread out the disbursement of the loan funding over 12 months as well? If I did it exactly evenly, it wouldn't cover the land purchase in the first month. So what if I subtracted the land value from the total loan, and then disbursed the remaining money of that loan over 12 months? So say the loan is for 4.5m and the land is 3m. You'd initially receive 3m + 1.5m/12, and then 1.5m/12 for the next 11 months.

Does that make any kind of sense?

Thanks for any help

#### Real Estate Modeling Course

• Real-life RE Modeling Tests from actual Interviews
• Various asset classes including multi-family, commercial and more
• Huge discount - until more tests and cases added

You don't generally receive CL proceeds in a lump sum. Your CL will also generally not reimburse you for your land purchase. Even if it does the cash likely went out the door prior to receiving the loan proceeds.

Array
• 1

Oh I see, so if I'm doing this as a modeling test for a REIT or something, typically I should assume that the CL does not factor in land cost? Should I make the CL proceeds received evenly over the construction phase?

Think of it this way... During construction you have two sources of cash, equity contributed and your CL. The required equity contribution will go out the door before you can draw on your CL.

Array
• 1

Bank will occasionally allow you to step up your land cost to value for loan sizing, but I would assume it's at cost. You typically fund all of the equity first before the lender puts in any money, so construction loan proceeds would cover the last \$X of your costs.

Overly simple example:
You buy land for \$100. That will generally come exclusively from equity (negative cash flow). If you have \$400 of additional construction costs, the bank may lend you \$250 (50% LTC). The first \$250 comes from equity - so the land purchase and the first \$150 of construction costs are negative CFs. The remaining \$250 comes from the construction loan (net \$0 CFs as the loan is funded).

There seems to be a fundamental issue with this question. What you are asking doesn't make sense.

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

What doesn't make sense about it?

When you say in your other comment that there is no cash flow during construction phase, I understand that there is no rental income, but aren't the receipt of funding, loan payments, and construction costs considered cash flows? Isn't the IRR computed off all cash flows including those?

What does it mean that the equity contribution account is fully deployed? I'm unfamiliar with this terminology.

Thanks

Say the capital stack is 35% equity and 65% debt (construction loan). The equity contribution will be completely used before the construction loan proceeds kick in. So...in most instances...the equity will cover the costs at closing (including the land acquisition) and the equity account will continue to be drawn down until it is depleted. On most projects...the equity contribution will float all of the costs for the first few months post closing.

Construction loan could include the cost of the land acquisition. You close on the land when you close on the loan...in most instances.

You receive the construction loan proceeds in draws (usually monthly basis) as the project is being developed.

There is no cash flow during the construction phase.

The equity contribution account is fully deployed before the construction loan proceeds are used (again...in most instances). So...the equity portion will often technically be the source of funds used to close on the dirt.

To summarize what the others are saying, there are several different IRRs that can be calculated.

If you are trying to find the IRR for the investors (equity), then the initial contribution is the negative cash flow. Construction loan draws would not be factored into the cash flows and the only cash flows that would show up from the debt side would be proceeds from cash out during refinancing, NCF (after Debt Service), and debt retirement.

If you are trying to find the property level IRR, then both the equity investment and debt draws are negative cash flows. Everything that is tied to NOI pre-debt is considered to be part of the cash flow as well as the disposition value. Debt is completely removed from the equation.

Thanks for your help. I think I need to find the property level cash flows first, because I have to then plug that into to a waterfall model. Does that sound right?

Yes. You have to model out the property level cash flows first. You need that to be correct otherwise the cash flows that go into your waterfall will be incorrect.

Generally no. The waterfall only applies to the equity cash flows and how they are divided out.