Adding DCF Analysis with NPV to Multifamily Real Estate Model and General IRR Cash Flow Waterfall

 I figured that it would be helpful to list out significant changes that I make to the financial models and spreadsheets here on smarthelping, so here we are. This was an upgrade to the multifamily real estate model and general cash distribution IRR hurdle waterfall. They are two separate Excel templates and I've been meaning to include a DCF Analysis for them for some time. That has not been implemented.


Update 1:

Let's talk about the multifamily investment spreadsheet first. The most recent version of this was an addition of up to four property analysis instead of just a single property, but on that update I still hadn't add a DCF Analysis. Now I have. For each property, there will be a configuration for the discount rate to be entered and it will apply to all cash flows for the Limited Partner and General Partner. The output is for each property as well as a consolidated NPV on the entire analysis of up to four properties. The user will then be able to see the resulting NPV (net present value) of each cash flow stream as well as the present value of all future cash flows.

Note, on this financial model the user isn't bound to just joint venture deals. It can be configured that some properties are fully owner / operator funded or all of them. Simply adjust the equity contribution and cash flow distribution tiers accordingly (100%/0%).

This spreadsheet is included in the real estate Excel sheets bundle.

Update 2:

The second update was just on a standard general cash flow distribution waterfall model. That template is designed to fit on the end of any financial model (real estate or otherwise) and show how cash is split when there are up to three IRR (internal rate of return) hurdles. It did show equity multiple and IRR at exit, but not the DCF Analysis for each side of the deal. Now, at the summary tab, a DCF Analysis has been included to show the present value of future cash flows and the net present value (NPV) of each party based on a defined discount rate.

Having a discount rate lets the user apply their own risk metric to the deal (higher the discount rate, the more perceived risk). Note, on both of these financial spreadsheet I did already have a final IRR, which is actually related to the DCF Analysis because if you plug in the IRR as the discount rate, it will result in a NPV over zero. So, the higher the IRR, the better as that means you really have to discount the future cash flows a lot to get them to equal the initial investment required.

This spreadsheet is included in the joint venture Excel sheets bundle.