How do you compute IRR without any negative cash flows?

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

Comments (29)

Nov 8, 2019 - 12:00pm

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
Nov 8, 2019 - 12:30pm

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?

Thanks for your help.

Nov 8, 2019 - 1:15pm

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
Most Helpful
Nov 8, 2019 - 1:22pm

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

Learn More

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.

Learn more
Jan 20, 2020 - 6:22pm

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.

Jan 21, 2020 - 12:05am

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?

Jan 21, 2020 - 9:09am

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.

Nov 8, 2019 - 12:59pm

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

Nov 8, 2019 - 1:29pm

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.

Nov 8, 2019 - 12:36pm

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.

Nov 8, 2019 - 1:27pm

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.

Nov 8, 2019 - 1:54pm

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.

Jan 21, 2020 - 11:05am

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.

Start Discussion

Popular Content See all

Oncycle is a fucking joke
+188PEby Analyst 2 in IB - Cov
Dating smart girls
+141OFFby Analyst 2 in Consulting
How often do you get "matches" on apps?
+134OFFby Associate 1 in AM - Other
I want to quit already…
+108IBby Analyst 1 in IB - Cov
Have you ever sued your employer?
+95PEby Principal in PE - Other
The Qualifications of an EB
+84IBby Summer Associate in IB-M&A

Total Avg Compensation

September 2021 Investment Banking

  • Director/MD (10) $853
  • Vice President (38) $367
  • Associates (219) $232
  • 2nd Year Analyst (133) $153
  • 3rd+ Year Analyst (30) $147
  • Intern/Summer Associate (103) $143
  • 1st Year Analyst (483) $135
  • Intern/Summer Analyst (376) $82