This model has been designed to cater to a quick underwriting / review process when looking at the financials of a multi-family real estate deal. It involves assumptions that allow for a joint venture as well as the option to refinance in-between the acquisition and exit period. The pro-forma comes in a detailed sheet and rolls up into a clean executive summary view. The IRR hurdles flow through the waterfall tab cleanly so the sponsor and investor can see how the cash is split in each tier.
The model goes out for a max of 10 years.
You can choose the year the refinance happens or mark that as ‘N/A’ if it is not applicable. The exit year can also be dynamic and will run off the exit cap rate defined.
The rent rolls allow for up to 5 different unit types but can be easily expanded to as many as you want. The only thing that flows out of that area is the grand totals.
The IRR hurdle logic is setup to account for up to 3 hurdles and then a final percentage split is defined after the last hurdle is reached.
You can define all your granular assumptions like rent / expense growth per year and costs per unit on the detailed pro forma tab. If you want to stick in a total annual starting rent and expense figure instead of going through all the detailed assumptions, there is a switch you can change in order to pull from that single input rather than the granular assumptions. This makes for a quicker review if needed.
There is a visual to show the annual cash flows that flow to the sponsor and investor.
If you don’t have a joint venture to model, that is fine and you can 0 out the sponsor assumptions and give 100% to the investor field. The investor field then becomes a proxy for the owner/operator.
There is a debt service coverage ratio in each year that changes dynamically. Also, there are assumptions for any loan pre-payment fees on the original loan and refinance loan as well as selling fees upon exit.
There is a sub-investor view to show what a smaller investment looks like for one entity that is part of the entire investor pool. Their cash flows will populate according to their share in the total pool.
The final IRR for both the sponsor and investor are shows as well as their equity multiple.