Please bear with me. This is short question with a very long set up.
I am currently building a proforma that models cash flows from investment real estate. I am running into a problem with properly modeling the IRR hurdles and subsequent partnership distributions.
In my model, equity is divided between up to four different investors with different preferred returns (aka, required rate of return) and equity splits on up to four different hurdles.
An example would be:
Investor 1 contributes 80% of the equity and has a 10% preferred return.
Investor 2 contributes 10% of the equity and has a 10% preferred return.
Investor 3 contributes 7.5% of the equity and has a 10% preferred return.
Investor 4 contributes 2.5% of the equity and has a 10% preferred return.
The hurdles are 11%, 13%, 15%, and 15+%. The respective splits when these hurdles are hit are as follows:
Investor 1: 70%, 60%, 50%, 40% respectively
Investor 2: 15%, 20%, 25%, 30% respectively
Investor 3: 10%, 12.5%, 15%, 17.5% respectively
Investor 4: 5%, 7.5%, 10%, 12.5% respectively
Each period every investor is due their preferred return times their initial equity amount. E.G. if Investor 1 contributed 8Mil then his periodic preferred return amount would be 800K.
Depending on what type of partnership structure, i.e. pari-passu or non pari-passu, each investor is paid their preferred return, or carries a balance on the amount that was not paid. Let's assume this venture is a cash cow and preferred returns will always be paid for every investor.
Now to the meat of the question. At some period in the future the IRR of Investor 1’s preferred returns will be greater than the first hurdle. At this time, Investor 1 should receive a payment that will yield him an IRR of exactly 11% and the remaining balance will be distributed at the new distribution percentages across the other 3 investors. If in a period Investor 1’s preferred return IRR hurdle has not been met but after all investors preferred returns have been paid and there is cash remaining and Investor 1’s portion of that remaining cash pushes him over the IRR hurdle, then again, I need Excel to calculate exactly how much of the excess cash should be paid and then distribute the remaining cash to the other investors.
This cycle continues until all hurdles have been met and at that period, all distributions will remain constant.
I need a formula(s) that will calculate the IRR of the preferred return payouts and then give me the exact amount in that period to return exactly the IRR hurdle and then distribute the remaining cash.
I can supply anyone with my model so that they can see exactly how I am trying to model the distributions. Please send me an email or private message, or post your email and I will send it your way.
I have been struggling with this formula for over a week and have tried many variations but none have been clean (requiring as few dummy lines as possible) or produced accurate results.
Thanks.
I am currently building a proforma that models cash flows from investment real estate. I am running into a problem with properly modeling the IRR hurdles and subsequent partnership distributions.
In my model, equity is divided between up to four different investors with different preferred returns (aka, required rate of return) and equity splits on up to four different hurdles.
An example would be:
Investor 1 contributes 80% of the equity and has a 10% preferred return.
Investor 2 contributes 10% of the equity and has a 10% preferred return.
Investor 3 contributes 7.5% of the equity and has a 10% preferred return.
Investor 4 contributes 2.5% of the equity and has a 10% preferred return.
The hurdles are 11%, 13%, 15%, and 15+%. The respective splits when these hurdles are hit are as follows:
Investor 1: 70%, 60%, 50%, 40% respectively
Investor 2: 15%, 20%, 25%, 30% respectively
Investor 3: 10%, 12.5%, 15%, 17.5% respectively
Investor 4: 5%, 7.5%, 10%, 12.5% respectively
Each period every investor is due their preferred return times their initial equity amount. E.G. if Investor 1 contributed 8Mil then his periodic preferred return amount would be 800K.
Depending on what type of partnership structure, i.e. pari-passu or non pari-passu, each investor is paid their preferred return, or carries a balance on the amount that was not paid. Let's assume this venture is a cash cow and preferred returns will always be paid for every investor.
Now to the meat of the question. At some period in the future the IRR of Investor 1’s preferred returns will be greater than the first hurdle. At this time, Investor 1 should receive a payment that will yield him an IRR of exactly 11% and the remaining balance will be distributed at the new distribution percentages across the other 3 investors. If in a period Investor 1’s preferred return IRR hurdle has not been met but after all investors preferred returns have been paid and there is cash remaining and Investor 1’s portion of that remaining cash pushes him over the IRR hurdle, then again, I need Excel to calculate exactly how much of the excess cash should be paid and then distribute the remaining cash to the other investors.
This cycle continues until all hurdles have been met and at that period, all distributions will remain constant.
I need a formula(s) that will calculate the IRR of the preferred return payouts and then give me the exact amount in that period to return exactly the IRR hurdle and then distribute the remaining cash.
I can supply anyone with my model so that they can see exactly how I am trying to model the distributions. Please send me an email or private message, or post your email and I will send it your way.
I have been struggling with this formula for over a week and have tried many variations but none have been clean (requiring as few dummy lines as possible) or produced accurate results.
Thanks.
