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
Nov 8, 2019 - 1:24pm

Would it typically be expected that you model out the sources and uses of funds? Would you know of a good reference I could use to model this after?

If there is a waterfall component to this, will it not make sense unless I include a sources and uses of funds section? Or can I just use the levered cash flows without specifying whether a given cash flow is from equity or loan?

Thanks, sorry if this question doesn't make sense.

Nov 8, 2019 - 2:23pm

You are getting a little ahead of yourself. The project will not have any cash to go through the waterfall until the project is complete. Look up a construction period flow of funds model. I suspect adventures in CRE has one. The bank doesn't allow you to draw on your loan unless you have requisitions to pay and you have exhausted your equity contributions. You are going to be charged interest on what you have drawn on the CL on a monthly basis. When you're done building you will get a perm loan to pay down your CL and capitalize any necessary reserves. Any additional proceeds from the loan would then go through the waterfall.

Array
  • 2
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
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).

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.

Jan 21, 2020 - 11:05am

Sit voluptatum facere facilis tempore omnis iste. Perferendis voluptatem repellendus aperiam hic. Quisquam sed animi consectetur quidem. Adipisci rerum nesciunt exercitationem harum enim. Est unde fugit est magni. Voluptatem non officiis et eum.

Dicta placeat nam quasi asperiores omnis. Accusamus distinctio rerum et enim nemo. Quo cumque voluptatibus possimus non ea dolorem.

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