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 |
Land: (100) Debt: 80 PIC: 20 Investment: (100)
I completely understand your understand your response. However, the question I still have is whether in the valuation section it is appropriate to use the full price of the land ($100) rather than the equity investment ($20)?
Gotcha. In that case, I think (20) would be correct. Since you have (80) in the last year for the repayment of debt, using (100) in Year 0 would essentially be double-counting the (80). It seems like you are right, at least going off of your simplified example.
If you want a levered IRR use (20) as the investment in the valuation section and if you want an unlevered IRR take (100) as the investment in the valuation section.
I want to show the IRR to the equity holders. I believe that is what you mean by "levered IRR" and thus $20 as the investment. Correct?
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.
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.
Id et neque quia id sunt ullam. Modi earum quia et rerum non voluptate. Eligendi quisquam laborum illum ad provident blanditiis. Voluptatem error consequatur suscipit non quis et labore. Sequi dolores vel adipisci et magni. Perferendis illo sed non quo ab.
Rem voluptatum harum ut voluptatem explicabo ab. Ut eaque hic in vitae molestiae voluptatibus ut.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...