Modeling Monthly Distributions vs Annual Distributions in a Joint Venture Cash Flow Waterfall

 I've done a lot of joint venture real estate models for clients and also built standalone templates. Some I've done based on monthly distributions and others on an annual period-only schedule. Let's talk about how the calculations vary.

The below templates all have some kind of joint venture cash flow waterfall logic to them. Note, in general the 'rate' is almost always defined as the annual rate and then depending on your periods being calculated, the annual rate is adjusted accordingly. It is not often that you will see a rate quoted as monthly or quarterly.

Annual Cash Flow Waterfall Templates:

In all of the below listed models, the rate being entered for the hurdle is assumed to be annual and the periods are assumed to be annual. If you wanted to extend the waterfall out for more periods, you can do that, but you have to change the rate to what matches the period. So if you drug the model out for 60 periods, and each period represents 1 month, then the annual rate is now a monthly rate and needs to be changed accordingly. If the rate is non-compounding simple interest, you can divide by 12, but if the rate is a compounded periodic rate (like for the IRR hurdles) then you are going to have to do a fancier formula to convert to the resulting monthly compounded rate that will equal the annual rate.

Convert Annual Rate to Monthly by.... (( r + 1 ) ^ 1/12) - 1 where 'r' equals the annual rate. Note, if you were changing to quarterly, you would just replace the '12' in the formula with a '4' since there are 4 quarters in a year. All the logic should then run off the monthly rate that you get from doing the equation above.

Note, if you were doing an IRR hurdle and you simply divided the rate by 12, then you would get an IRR that is a bit higher than the hurdle rate, but if you use the formula above, the resulting IRR is much closer. We are talking about 10.45% vs. 9.99% when you are targeting 10%. The 9.99 is much more accurate.

    Monthly Cash Flow Waterfall Templates:

    In all of these models, you can define an annual rate, and the fancy formula is built in and will convert the annual rate to the proper monthly rate for the IRR hurdles. Usually there is no need to convert a monthly model into an annual because the monthly is the more granular one already and in all of these you will see an annual summary that shows the results of the monthly cash flows.

    However, if you did want to convert the waterfalls in these models to annual only, you would just remove the formula and directly reference the annual rate in each.

    Here in these models there is no need to worry about any rates. You can drag the formulas over as much as you want and the logic will stay intact. This is because the cash flow distributions are based on things other than a defined annual rate. They run off the total cash returned to the LP in some form or fashion. 
    This template actually has 3 different waterfall options that can be configured (pref equity, pref return, 3 tier IRR hurdles: Mobile Home Park Underwriting Spreadsheet

    Note, if you want to use a given waterfall above and attach it to a different model, that is very easy to do because all the generic joint venture templates that don't have any pre-existing business logic on top of them simply need you to plug in the cash flows in / out where you see the yellow inputs and the logic will do the rest.

    Article found in Joint Venture.