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).

I see what you're saying. So you don't see any money from the bank until your equity is already exhausted, giving you the negative cash flows at the beginning? Is that right?

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.

this isn't the case all the time. the bank can lend 100% of construction costs but developer puts 50% of the land cost.

Depending on the scenario that may be true, but that isn't the right way to look at it. Land costs and construction costs are only two costs. Both of those, among other costs add up to your total cost. Equity goes in first for all of these costs. Breaking out which specific costs isn't important.

Hi, I know this is from a few months ago. But I had one more question. In calculating project costs, does loan interest typically get included in project costs? Because if the 60% loan and 40% equity (or whatever the ratio is) only covers the project costs NOT including interests, I'm not sure where the additional money comes from.

Yes, there will be an interest reserve baked into the construction budget. The bank essentially pays itself until there's positive CF.

Thanks. I figured that would be the case. But here's where I'm confused:

Suppose the original project cost without the interest is \$100. If the loan was just based on that, it would a \$60 loan (at 60% LT). However, maybe the interest is \$5 total. So the project cost gets boosted to \$105 and the loan is now based on the \$105. But this would mean higher interest payments, since it's now a \$63 dollar loan. And with these additional interest payments, the total costs are now higher than \$105, so you'd need to adjust for THAT, and make the loan even higher, which has even higher interest payments, etc. This would go on infinitely. And it is self referential which I hear isn't desirable when modeling in excel.

How does this get approached? Does that make any sense?

Not if you accurately estimate the loan interest in the first place. If your trying to calculate an exact interest reserve in excel you'll probably have a circular reference since every change to the construction budget then changes the capital stack/loan amount. Options>Formulas>Enable iterative calculation will take care of this.

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

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.

Voluptatem modi earum facilis modi. Minus qui qui molestiae facere fuga. Et voluptatibus doloribus ullam id. Ad quod illo ut et hic.

Doloribus quibusdam blanditiis explicabo aut. Adipisci corrupti officiis sit ut. Laboriosam eos qui animi nisi nulla qui est.

Possimus debitis non distinctio et. Excepturi ipsum eos illo sapiente consequuntur maiores. Minus repudiandae ut dolorum ducimus.

Et accusamus qui qui sapiente. Voluptatem reiciendis nihil nesciunt voluptas et dicta. Quisquam eos quasi eum sed enim ratione illum. Eius architecto expedita officia autem. Alias fugiat eius est sed dolore.

Vel dolorum ipsum molestiae voluptas aperiam. Libero enim consequatur voluptas odio. Quia enim deserunt consequuntur. Et qui omnis non.

Animi delectus ad voluptatem illo. Adipisci accusamus unde doloribus et. Sequi voluptatem doloremque occaecati eos dolorem recusandae sit. Dolorum sed quia est.