IRR Hurdles and Partnership Distributions

RBradshaw

New Member
Joined
Nov 13, 2005
Messages
4
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

RBradshaw

New Member
Joined
Nov 13, 2005
Messages
4
I have looked at that thread before, though I did not find it helpful for my problem, but did give me some ideas.

Does anyone else have a solution or an example that they could send for my reference?
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Check your PMs. If you already have a model started, please send a copy to me and I will have a look.
 

matty18_3

New Member
Joined
Mar 29, 2006
Messages
5
I'm assuming you've figured this out, but let me know if you haven't. I've got this answer if you need it.
 

Forum statistics

Threads
1,141,715
Messages
5,708,032
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top