Hi,
I'm new to excel and have been asked to model a rather complex investment structure. If anyone could help me with this I would be forever grateful. The cash flows are distributed between the limited partner (LP) and the managing partner (MP). There are three hurdles though with different divisions of cash for each. The structure is as follows
For cash flows generating an IRR less than 15%
LP gets 75%
MP gets 25%
For cash flows generating between IRR 15%-20%
LP gets 50%
MP gets 50%
For Revenue reaching over 20%
LP gets 25%
MP gets 75%
So I have my NPV cash flows laid out and have calculated the IRR for each year but can't visualize how to calculate the relevant sums of money for each cash flow. For example, the amount of cash that generates a 15% IRR in year 3 will be different than the amount of revenue that generates a 15% IRR in year 4.
So say there is cash flow for a year that generates a 30% IRR. I would need to first split the cash into three separate sums. The first would be the cash that generates an IRR of up to 15%, then another sum of the cash on top of that that generates between 15-20% IRR, then all the cash over 20%. The sum of these three chunks will equal the total cash flow for given year. Then I can apply the relevant structure to each cash sum (LP gets 75% of first sum, 50% of second sum, and 25% of third sum)
I need to split up the cash flows into the three different groups based on the hurdles so I can apply the correct structure to the relevant part of the cash flow.
Can I get excel to spit out the relevant sums of money for each hurdle level for each year?
Sorry if this is not clear, I am completely new to this and will elaborate if need be.
Thanks,
Colin
Bookmarks