Waterfall Distribution with multiple inflows and outflows
Looking for help to calculate and create an excel model for LP and GP distributions on a quarterly basis considering LPs may come at different times over the period during the first year. Hurdle rate of 8% and anything above that is split 80 to the LPs and 20 to the GP.
someone may correct me here, but i think you will have to just create separate waterfalls for each date of funding. If 5 investors invest on 6/1, thats one waterfall, and if 1 invests on 9/1, thats another waterfall.
Would a monthly capital account for each investor help? With actual distributions and actual contributions?
are you saying commitments for a fund are coming in at different times? or you are calling capital at different points?
Yes
to which part...
This is a doublepost but here you go (have not proofed it):
Fund Economics
Thanks - I will test this one out
you definitely don't need separate waterfalls. PM me for an example/screen shot model. You just need separate "tabs" for each partner, and columns for their funding/distribution amounts with corresponding dates. All you have to do then is layer the formulas on your net contributions / distributions point at the waterfall. Don't over complicate it. When I first was presented with your problem - I built out this stupidly long and complex answer. My then boss basically threw it in the trash, recreated a simple and seamless example in about 20 minutes...
Thanks - I'm new to the forum, can you PM me your email. The forum does not allow me to PM yet. I have a rough model but I think I'm taking it down the complex path like you mentioned.
Yeah every LP should come in pari-passu so you definitely don't need separate waterfalls. Look into the -xnpv function for one of your constraints in the formula, the other one will just be % of cash flow available at said stage.
Waterfall with multiple inflows and outflows (Originally Posted: 05/07/2017)
Looking for help to calculate and create an excel model for LP and GP distributions on a quarterly basis considering LPs may come at different times over the period during the first year. Hurdle rate of 8% and anything above that is split 80 to the LPs and 20 to the GP.
Interested what those in secondaries have to say. Can't you just take gross distributions less invested capital times 1.08^[year of distribution] then multiply by 20%? Or are there complexities around multiple distributions that prohibit this (i.e. a true-up at each distribution/write-down)? IRR calcs assume the interim cash distributions are reinvested at the same rate, no?
Alternatively, you could track each PortCo drawdown as a standalone IRR calculation?
Here you go (have not proofed it):
Fund Economics
Ut ab officia rem aut. Et officia odio quos assumenda totam veniam est. Impedit accusantium natus cumque eos fuga magni. Vel enim tenetur modi in ea ducimus. Molestias dolores explicabo tempore quasi sunt.
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...