VERY Basic Financial Modeling Question Regarding Time 0 Cash Flows and Initial Investment

I need a small favor. I have a very basic question about discounted cash flow modeling. I think I know the answer, and it is probably obvious, but I have been thinking about it so much that I have confused myself. This issue is very important to a financial model I have created for the acquisition of a property so I want to make sure I get it right.

If you have 10 minutes to spare, please look at the attached spreadsheet and fill in the spaces highlighted in yellow. This is essentially a VERY simplified version of the issue I am having in my real model regarding time 0 cash flows and the initial investment.

I would appreciate responses ASAP because I have people waiting for the real model and I told them I would get it to them today if at all possible.

Let me know if you have any questions.

Thanks, Carlos

Attachment Size
DCF Model 39.17 KB 39.17 KB
 

Correct; the levered IRR would correspond to the equity investor. Use ($20) as the investment. If the equity is coming from more than one source this levered IRR would be a project level levered IRR and you would have to model a waterfall to get LP vs GP IRRs. I am assuming, however, that in this simple model there is one source of debt and one source of equity.

 
Most Helpful

Thanks. Correct. I understand that the IRR is on a project basis, and I do have an equity waterfall to account for varying equity investments, promotes, and prefs.

What I am struggling with, perhaps because I am overthinking it, is that I am showing an equity investment of -$20. However, in the Cash Flow Statement, I am also showing a positive cash flow of $20 as a result of an increase in PIC of +$20. Therefore, in the Valuation, the investment of -$20 and the cash flow of +$20 cancel out. For some this reason this feels like I am negating the -$20 investment and calculating the IRR as if there were no equity investment (because the investment and cash flow cancel each other out). I would attach the spreadsheet that makes this explicit but I dont know if there is a way to add an attachment to a reply. Thx.

 

I believe you are overthinking it. I will attempt to answer what I believe is going on in your head, so I hope this makes sense. The +$20 in the CF Statement that you think is cancelling the -$20 in the investment has already been cancelled out by the -$100 in land and +$80 in debt. Take a look at your formula; you're summing investment with CHANGE in cash not PAID in cash. The value you're summing to the investment in year 0 is 0 because the +20 has already been "cancelled" out. Thus, when you sum your investment with your change in cash your net result is -$20 - the exact amount of your equity investment. I believe your confusion lies in the importance of change rather than paid in the CF statement. Hope this helps.

 

Maiores facilis ea qui earum libero. Est nulla sint provident ratione ab sint. Animi beatae eum incidunt aut molestiae reprehenderit ea.

Error ex quia sint aliquid voluptas quasi occaecati. Natus dolorum incidunt consequuntur reiciendis veritatis.

Velit eaque nostrum nam tempora in ut doloremque molestiae. Consequatur esse dolorem eum perferendis occaecati. Velit atque beatae minus laudantium deleniti est. Sunt amet molestias explicabo repellat odit. Voluptatem praesentium sed consectetur sed voluptas vero officiis. Optio veniam doloremque autem alias nihil. Vel occaecati molestias aut saepe qui voluptates.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”