Waterfall Equity Models Different on Monthly vs. Annual Basis
Hi all,

I'm currently making a development proforma for a hypothetical development to work on my Excel modeling skills. The prompt is asking for a 3 hurdle waterfall model, but doesn't describe whether it should be on an annual layout or a monthly layout. I've done both just for practice but the contributions and distributions are widely different even though they use the same DCF model. Is this typical with waterfalls when you vary the time layout (monthly vs. annual)?

If I see such a prompt that doesn't describe whether to do an annual or monthly waterfall, would I be fine with just doing an annual layout?

Would appreciate any insight! Thanks!
[{"id": "hohti8l", "text": "Shouldn't it depend on whether your underlying cashflow is monthly or annual?", "replies": [{"id": "hoizgqr", "text": "My cashflow model is on a monthly basis but for the annual waterfall, I just summed up cashflows every 12 months to get an annual cashflow", "replies": [], "depth": 2}], "depth": 1}, {"id": "hoi8si8", "text": "Look at the rate your pref and hurdles accrue at. The monthly rate is not as simple as dividing the annual rate by 12. It really depends on what the OA dictates for the waterfall and whether the rates are monthly or annual. If annual and your accruing monthly the rate is getting compounded monthly which will lead to a higher return than the waterfall prescribes - you need to adjust for the difference in annual vs monthly compounding.", "replies": [{"id": "hoihwov", "text": "To this point, the formula for monthly is not r/12 where .05/12 = .0041 because (1 +.0041)^12 will equal greater than 5% at 5.11% \n\nYou have to use (1 + .05^(1/12) -1) which equals .00407.   Therefore (1 + .00407 )^12 -1= 5%\n\nThis formula accounts for the compounding effect of monthly compounding in order to get to the proper effective rate. Also yes monthly and annual compounding will have different rates.\n\nAlso make sure you are using xirr for the monthly. The irr function assumes 1 year increments which is too wide. u/certifiedcloser\n\nIf the prompt doesnt say annual or monthly do annual. Its way faster and harder to screw up. When practicing do monthly but on a modeling test do annual unless explicitly stated or given cf that are monthly\n\nEdited: moved decimal", "replies": [{"id": "hoj48gs", "text": "u/unbeardedclam u/According_Surround_7 you two are a godsend, thank you so much!", "replies": [{"id": "hojg28k", "text": "Happy to help", "replies": [], "depth": 4}], "depth": 3}], "depth": 2}], "depth": 1}]