Question for multiple conditional results

[Jdub]

Hi there. Working on a model where someone makes an investment & the following payments are then made over a period of time. For example:

• Total investment $1,000 over n # over months ($X/month of varying amounts until the $1K is hit).
• Payments start coming in the following order (over many months):
  • 100% principle retuned
  • IRR of 10%
  • Any remaining payments above the 10% IRR are split between the sponsoring GP and LPs.

Creating a model so mgmt can run a bunch of what if scenarios (i.e. can input varying amounts of payments & distributions in varying months) & see how everything shakes out.

I know how to calculate these in isolation (IRR formula, etc.), but figuring out the formulas to create the distribution waterfall is causing me some angst.

Thanks for any help & happy to provide more detail if it will help.

 

[NARAYANK991]

Hi ,

It would help if you could upload your workbook with the data in it ; it would also help if you could explain your requirement in more detail than just these few words :

figuring out the formulas to create the distribution waterfall is causing me some angst.

A lot of those who are good in Excel are not necessarily conversant with what you mean by distribution waterfall.

Specify what you want to do , in as many steps as possible , describing the inputs that should be used , with specific references to worksheet tabs / columns / rows / cells , and what outputs you are expecting , again with specific references to worksheet tabs / columns / rows / cells , and finally , how the inputs should be used to derive the outputs.

If it is a specific chart you want created , please include a reference link to a similar chart , which can be used as a guide.

Narayan

 

[Jdub]

Thanks so much for the reply. Here is a spreadsheet that I hope explains it. Thanks!

 

[NARAYANK991]

Hi ,

The worksheet gives a few hints , but much more is needed.

I assume that what you want are formulae in cells C34:Z36 ; it is not clear what they should output.

If you can manually show the outputs in these cells , developing the formula would be easier.

I assume the inputs are in cells A27 , A28 and C30 through Z30. Is this correct ?

If you can manually show the required outputs in C34:Z36 , members could suggest the formulae to achieve this.

Narayan

 

[Jdub]

OK. Let's try this. Ideally, everything shaded in light blue are variables that I could play with to run different scenarios. Everything else would essentially be formulas. Please note that I inserted comments in cell L39 & N41 that explained where I had to hard code it, but that I am looking for the functions & formula to do the look up, if then, etc. Also note that there are two sheets. "Model" which is the new info, and "Example" that contains some of the assumtions. Thanks again.