Huge problem with "if" formula's

[calquestions]

Hello again, I am working on a 2 investor spreadsheet with 4 different
tiers of distributions and return. One tier of return is exclusively
for the preferred investor (Inv1) which I called "Required Return" and
rest are 1st, 2nd, and 3rd waterfall levels (split levels). In the
following scenario, Inv. 1 must get a 10% Required Return each and
every period starting from period number one. If for some reason, the
cash flow for one or more periods does not come out to the 10% Required
Return, that amount gets carried over to the next period, until the
full amount is paid. For example, during period 1, there was only $50
CF which would amount to a $50 carry-over to the next period. During
period 2, the CF was $90, which gives Inv1 the entire amount and
leaving a carry-over of $10 for a total of $60 carry-over. During
period 3 a CF of $200 was realized which pays off the 10% Required
Return for that period as well as the carry-over amount of $60 and
leaves us with $40 to be split between tier 1 and 2 (1st and 2nd
waterfall levels) . Just to give you an idea, I used the following
information for the tiers:

DISTRIBUTION WATERFALL RATES(AKA SPLIT)
FROM TO INV1 INV2
1st level waterfall 10.00% 13.00% 80.00% 20.00%
2nd level waterfall 13.00% 20.00% 50.00% 50.00%
3rd level waterfall 13.00% 100.00% 80.00% 20.00%



Expected Return Rate for Inv1 = 10%

EXAMPLE:
A B C D E F
13 Period 0 1 2 3 4
14 CF -$1,000 $50 $90 $200 $120
15 Expected Return $100 $100 $100 $100
16 Return to Inv1 $50 $90 $160 $100
17 Inv1 1st waterfall $0 $0 $24 $16
18 Inv2 1st waterfall $0 $0 $6 $4
19 Inv1 2nd waterfall $0 $0 $5 $0
20 Inv2 2nd waterfall $0 $0 $5 $0
21 Inv1 3rd waterfall $0 $0 $0 $0
22 Inv2 3rd waterfall $0 $0 $0 $

Now the real questions...for some reason I cannot correctly write any
formulas that can calculate the following:
If an insufficient CF exists any given period (meaning that it does not
cover the required return, then the CF is return to Inv1 and the
difference between the Expected Return and CF is carried over to the
next period until the entire amount that is carried over is paid. I
must also make sure that cash flow for the consecutive period also
covers the Expected Return for that given period before the carry-over
amount is paid, or else just use that CF for that period to pay as much
as possible. If you look at the example, since the CF in period 1 did
not cover the Expected Return, that entire CF was given to Inv1 and $50
was carried over to the next period. During period 2, the CF was also
not sufficient enough to cover the Expected Return; hence that amount
was also given to Inv1. During period 3, the CF is not only sufficient
to cover the Expected Return but also the carry-over amount of $60 from
previous periods giving Inv1 $160 return and the remainder will be
split between Inv1 and Inv2 using the distribution and split
percentages provided. The CF from period 4 also covers the Expected
Return but only $100 is given to Inv1 as Expected Return and the rest
will be split.
I cannot figure out how to write a formula that does everything that I
need it to do which to figure out if the CF is sufficient...if not
carry-over amount until amount is paid. The formula on the example
above would be from cells C16 through F16. In addition to all that, I
need to also calculate a split of any amount that is left over after
all the Exprect Return and carry over is paid per period as required by
the distribution and split percentages provided for both Inv1 and Inv2.
I calculated the splits and distributions given to both Inv1 and Inv2
for periods 3 and 4 which I hope will explain it a little as to what I
need to do. I know this is very complicated, at least to me so if you
have any questions please feel free to ask.
I would appreciate any input.
Thank you in advance.
Cal

 

[Kassie]

Hi

to start off, you need two additional helper rows, lets call them Shortfall
and Surplus.
Now enter the following formulae. (I take it that Col A holds the Row
Labels, and that row 1 holds the Column Headings)
Col A
1 Period
2 CF
3 Expected Return
4 Return to investor 1
5 Shortfall
6 Surplus
7 Inv1 1st Waterfall
8 Inv 2 1st Waterfall
9 Inv 1 2nd Waterfall
10 Inv 2 2nd Wqterfall
11 Inv 1 3rd Waterfall
12 Inv 2 3rd Waterfall

Now, in C2, you have 50
in C3 you have 100
In C4 enter the following formula:
=IF(C2>C3,C3,C2)

In C5 enter the following formula:
=IF(C2<C3,C3-C4,0)

In C6 enter the following formula:
=IF(C2<C3,0,IF(C2>C3,C2-C3))

In C7 enter the following formula:
=IF(C6=0,0,IF((C2*100)/-$B$2<10,0,IF((C2*100)/-$B$2<13,(C6*0.8))))

In D2 you have 90
In D3 nothing
In D4 enter the following formula:
=IF(D2>$C$3,IF(D2+C5>$C$3,C5+$C$3,D2),D2)

In D5:
=IF(D2<$C$3,($C$3-D2)+C5,IF(D2<($C$3+C5),C5-(D2-$C$3),0))

In D6:
=IF(D2<$C$3,0,IF(D2+C5<$C$3,0,D2-D4))

In D7:
=IF(D6=0,0,IF((D2*100)/-$B$2<10,0,(D6*0.8)))

Copy across to the other columns.

You will however notice that my calculations differfrom yours in E7.

Although your description is quite comprehensive, it is not clear regarding
the split issue, and that is the problem here. If you could enlighten me on
the split issue, I am sure we can find the solution.

 

[Kassie]

Hi again, and after having had a second look at your data, I see that you
split 75/25 between waterfalls 1 and 2. You did not give any indication as
to how you arrive at this split. At what stage will you spill over into
Waterfall 3, and what ratios will you then use?
I therefore need to know how you decide on the number of waterfalls to split
into.
I need to know what ratios to apply
I need a bit more insight into the 10% - 13%; 13% - 20% and 13% - 100%
issue. Are these figures correct, or do I need to change 13% - 100% to 20% -
100%?
% of what are you referring to here. % of CF, % of CF remaining after Inv 1
gets his share, or what?

As I said in my first post, you will need helper rows, which you can
obviously hide, if you don't want to display same. I think the same will
apply between the waterfalls as well. If you still do not come right, let me
have the replies to these questions?

 

[calquestions]

Thank you for replying. The splits are based on the CF that is
received. For example, if the initial investment is $1000, the required
return by the preferred investor is 10%, the first waterfall is 10%-13%
with a split of 80% preferred investor/20% secondary investor (% of
total return for period 1), second waterfall is 13%-total CF, and the
first CF was $150, then the preferred investor would automatically
receive his $100 share ($1000*10%). The remaining $50 would get split
into two different wateralls, 10%-13% of the CF which would give us an
amount of $30 that will be split into 80% preferred investor
($30*80%=$24) and 20% for secondary investor ($30*20%=$6). From here,
the remaining $20 or 13%to total CF, would be split into 50% preferred
investor or ($20*50%=$10), and 50% secondary investor ($20*50%=$10).
That gives us the total amount of $150 CF for period one. This must be
done for every period so if the CF for all periods were the same
($150), then the preferred investor would always get $134
($100+$24+$10) and the secondary investor would always get $16($6+$10)
With this being said, I also have another problem that I must deal
with. If for example during the first period the CF does not come out
to the required rate of return by the preferred investor, then that
amount gets carried over to the next period until the required return
has been satisfied. So if the CF for first period was $80, the
preferred investor would the entire amount. However, $20 will get
carried over to the next period. So if the CF for period two was $130,
then the preferred investor would get $120 ($100 for required rate and
$20 that was carried over from period one) to fulfill his required
return, and the remaining $10 would get split 80/20, $8 for preferred
investor and $2 for secondary investor, since it falls into the 10%-13%
return. That gives us the total CF for period two of $120+$8+$2=$130.
If however the CF for period two does not meet the required return,
then that amount also gets carried over. So if period two also only had
a CF of $80, then $40 more will need to paid to the preferred investor
before moving down to the splits which makes it even more confusing.
This type of scenerio could go on for upto 10 periods where the
required return is less than the preferred rate of return during the
first 9 CF, then the 10th CF could be 3-4 times the initial investor in
which case all the required returns and carry-over amounts will still
need to be paid before waterfalls and the splits.
I tried helper rows but I am truely stumpped as to what to do. I must
have tried several dozen different ways of writing formulas and helper
rows, yet I still cannot come up with a formula or a method of
automatically calculating this type of scenerio. I would really
appreciate any suggestions you could provide.
Thanks again,
Cal

 

[Kassie]

Hi Cal

I now have a better understanding of the waterfall issue, however, still not
clear how you decide to give 10% or 13% ("the first waterfall is 10% - 13%"),
13% or 20% and so on. Once I have grasped that, I am pretty sure we can do
it.

However, I am concerned that you say the helper rows did not help? Have you
used my formulae as suggested?

 

[calquestions]

I tried but I am anything but an expert in Excel.

 

[Kassie]

Hi Cal

Maybe I have lost track as to where on your sheet you work. Give me the
cell references, as I did in a previous post to you. I can then write the
formulae so that you can just copy them off the post into your sheet, without
having to make any alterations.

Also. I need to know how you split the surplus funds between the various
waterfalls. As I said, clearly you used a 75%/25% split in the example.
This would however mean that you will never be able to split into a 3rd
waterfall?

Also, please elaborate on the 10%-13% issue?

 

[calquestions]

EXAMPLE:
CELLS WITH DATA

B4 = 10% PREFERRED INVESTOR'S REQUIRED RETURN
SPLIT %
WATERFALLS PREFERRED INV/SECONDARY
INV
B C d E F
7 1ST WF 10% to 13% 80% 20%
8 2ND WF 13% to 20% 50% 50%
9 3RD WF 20% to Total CF 80% 20%



A B C D E F
13 Period 0 1 2 3 4
14 CF -$1,000 $50 $90 $200 $120
15 PRFD RTN $100 $100 $100 $100
16 PRFD RTN NOT RCD $50 $10 $0 $0
22 PRFD RTN RCVD $50 $90 $160 $100
23 1ST LEVEL WATERFALL
24 TO PREFERRED $0 $0 $24 $16
25 TO SECONDARY $0 $0 $6 $4
28 2ND LEVEL WATERALL
29 TO PREFERRED $0 $0 $5 $0
30 TO SECONDARY $0 $0 $5 $0
33 3RD LEVEL WATERFALL
34 TO PREFERRED $0 $0 $0 $0
35 TO SECONDARY $0 $0 $0 $0

 

[Kassie]

Hi Cal,

You still haven't answered my question, but lets take it from the start to
where I can take you without that answer. Once you are satisfied that that
works, we'll take it a step further, until we solved your problem. I want
you to copy (Block a formula, press <Ctrl><C>)the following formulae from my
answer, and then paste them into your spreadsheet, in the cells indicated
(Select the cell, then press <Ctrl><V>.

Are you ready:

Cell C16 =IF(C14>C15,C15,C14)
Cell D16 =IF(D14>$C$15,IF(D14+C22>$C$15,C22+$C$15,D14),D14)
Cell C22 =IF(C14-C15,C15-C16,0)
Cell D22 =IF(D14<$C$15,($C$15-D14)+C22,IF(D14<($C$15+C22),C22-(D14-$C$15),0))
Cell C23 =IF(C14<C15,0,IF(C14>C15,C14-C15))
Cell D23 =IF(D14<$C$15,0,IF(D14+C22<$C$15,0,IF(D14-D16<0,0,D14-D16)))

Now highlight cells D16 to D23. Move your cursor to the bottom righthand
corner of cell D23. You will notice that it changes into a + sign. Click
and hold your mouse button and drag it across to your right , say up to
column O, then release the mouse button. You will notice that you are
building up a huge deficit in Row 22. If you now enter CF's from E22, F22
and so on, this deficit will change.

This part of your sheet should now be working.

Now, explain to me how you decided to split $30 in Waterfall 1, and $10 in
Waterfall 2. In other words, what criteria did you use to arrive at these
figures. Concentrate on this one isue only.

 

[calquestions]

Sorry about my explanation skills...it seems that this project is a
little beyond me but I'll do my best to explain just this particular
portion.
We have a CF of $200 for the third period. The preferred return of 10%
must be deducted from that CF before we move on to the splits so I
deducted $100 which is 10% of the initial investment of $1000. Then I
deducted any carryover from previous periods that did not meet the
required return, in this case there was a carryover of $50 from the
first period since our obligation was $100 but we only provided $50,
and $10 from the second period (obligation of $100 but only paid $90).
This carryover amount ($60) must be added to the third period's
required return to satisfy the required return for the preferred
investor. This leave $40 ($200-$100-$60 = $40). It sounds funny but
once all these requirements are met and the preferred investor has
received his 10% required return per year not including interest, I can
now calculate the amount left over ($40) at the related waterfall
levels, which in this case happens to be the first two waterfalls.
You'll notice that although there is a CF of $200, we calculate the
$40 starting at the 10-13 percent waterfall. This is not a mistake. Our
10% return is paid for the period and we subtracted the carryover
(which is ignored) making it seem as if though we had a CF of $140
instead of the $200...so we start our calculation using the waterfalls
using the $140 amount. Since the first waterfall level is between 10%
and 13% of the total investment and we already met required return of
$100 (10%) for the period, I must then calculate the waterfalls for the
remaining $40, which gives us $30 at 80/20 split for the first
waterfall. This amount is then split between the two investors giving
the preferred investor 80% of that $30 or $24 and giving the secondary
investor 20% of the $30 or $6. The remaining $10 is part of the second
waterfall but it does not reach the third waterfall (because the >20%
threshold is not reach to move to the third water). Since the split for
the second waterfall is 50/50, the preferred investor will get 50% of
that $10 or $5 and the secondary investor will get the other 50% of the
$10 ($5).
I'm not sure if this explanation is logical enough but I hope this
helps. I'm going to try the formulas you provided me with and let you
know how far they take me.
Thanks again for your tremendous help.
Cal

 

[Kassie]

Hi Cal

Thanks for that. I now understand how you calculate these. I will work on
it and advise you later

 

[Kassie]

Hi Cal

Doing the waterfalls were actually very easy. I need to know just the
answers to the following questions:

Am I correct in stating that the ratio of allocation between Waterfall 1 and
Waterfall 2 is 75% to 25%? In other words, with the $40, 75% ($30) goes to
Waterfall 1, where it is split 80-20.

Secondly, if there is an overflow into waterfall 3, what ratio of
allocation is done to devide the returns between Waterfalls 1, 2 and 3?
E.g., do you now allocate 60%, 30% and 10% to each of the waterfalls? E.g.
if you have a CF of $300, having given Inv 1 his $100, you remain with $200,
to be shared across waterfalls 1, 2 and 3. Will you do a 80-20 split on $120
in waterfall 1, a 50-50 split on $60 in waterfall 2, and an 80-20 split on
$20 in waterfall 3?

Let me reiterate:

Is a 75-25 allocation between Waterfalls 1 and 2 correct?
What allocation should I use if Waterfall 3 also comes into play.

I have used 60-30-10, and my formulae handle this perfectly.

 

[calquestions]

Well, the 75-25 allocation between WF 1 & 2 is not correct. As noted in
our previous conversations, WF is between 10-13 percent of the total
investment. For the example you gave of $300, there would be an $100
from that for the required return for the preferred investor. The first
WF comes after that $100 is paid. From here since we have 10-13 percent
for the first WF and an 80/20 split for the between the preferred and
secondary investors just on that amount, which would be $300 CF minus
$100 preferred investor's RR equals $200. The $200 is calculated at 1st
WF rates and splits which would give us basically just 3% of the total
investment or $30 to be split amongst the investors ($24 for preferred
and $6 for secondary). We then move to the second WF since our first WF
has already been max'd out remembering that we still have $170 to be
dispersed from that CF ($300-100-24-6 = 170). Our second WF is from
13-20 percent or 7 percent of the total investment which is to be split
50/50. 7% of investment would give us a figure of $70, giving $35 to
each investor. This amount again is subtracted from the leftover amount
from the CF leaving us with only $100 that is spilled over to the 3rd
waterfall ($300-100-24-6-35-35 = 100). For the 3rd WF we used 20% and
above with an 80/20 split. Since we have $100, its rather easy to
figure this one out...$80 dollars going to the preferred investor and
$20 going to the secondary. At the end we should have something like
the following:

TO PREFERRED INVESTOR
Preferred return = $100
1st waterfall = $24
2nd waterfall = $35
3rd waterfall = $80
TOTAL FOR PREFERRED INVESTOR = $239

TO SECONDARY INVESTOR
1st waterfall = $6
2nd waterfall = $35
3rd waterfall = $20
TOTAL FOR SECONDARY INVESTOR = $61

TOTAL FOR CF DISBURSED = $239 + $61 = $300 (This amount is original CF
of $300)

Believe it or not, this is one of the easier projects that I have to
work on. It gets a lot more complicated than this when you include IRR,
NPV, compounding interest on carryover amounts, etc... I know this is a
tough one to work on and believe me when I tell you that I REALLY do
appreciate the time and effort you are putting into this.
Thanks again,
Cal

 

[Kassie]

OK Cal

You did not say anything about the formulae I already posted, so I take it
you did not like them. However, this now works correctly, using the 3% and
7% splits, as described in your last post. I therefore give you all the
formulae again.

I want you to set up your sheet as follows:

Row 5 to Row 9 remains as is

Rows 13 to Row 16 remains as is.

Now you will have to insert rows, to get the following layout:

Row 22 remains the row holding RR actually received

Row 23 I called Surplus
Row 24 I called Waterfall 1 Quota
Row 25 Inv 1 1st Waterfall
Row 26 Inv 2 1st Waterfall
Row 27 I called Overflow
Row 28 I called Waterfall 2 Quota
Row 29 Inv 1 2nd Waterfall
Row 30 Inv 2 2nd Waterfall
Row 31 I called Waterfall 3 Quota
Row 32 Inv 1 3rd Waterfall
Row 33 Inv 2 3rd Waterfall

Once you have your sheet set up like this, you have to start copying and
pasting formulae.

Cell C16 =IF(C14-C15,C15-C22,0)
Cell D16
=IF(D14<$C$15,($C$15-D14)+C16,IF(D14<($C$15+C16),C16-(D14-$C$15),0))
Cell C22 =IF(C14>C15,C15,C14)
Cell D22 =IF(D14>$C$15,IF(D14+C16>$C$15,C16+$C$15,D14),D14)
Cell C23 =IF(C14<=$C$15,0,IF(C14>C15,C14-C22))
Cell C24
=IF(C23=0,0,IF(C14<=(-$B$14*0.1),0,IF(C14<=(-$B$14*0.13),C23,-$B$14*($D$7-$C$7))))
Cell C25 =IF(C24>0,ROUND(C24*0.8,0),0)
Cell C26 =IF(C24>0,C24-C25,0)
Cell C27 =IF(C23>C24,C23-C24,0)
Cell C28 =IF(C27=0,0,IF(-$B$14*($D$8-$C$8)<C27,-$B$14*($D$8-$C$8),C27))
Cell C29 =IF(C28>0,C28*0.5,0)
Cell C30 =IF(C28>0,C28-C29,0)
Cell C31 =IF(C27>C28,C27-C28,0)
Cell C32 =IF(C31>0,ROUND(C31*0.8,0),0)
Cell C33 =IF(C31>0,C31-C32,0)

Once you have done all that, copy cells C23 to C33 over to Column D. Now,
Block all your formulas in Column D, and drag them over to as far right as
you want to go.

You are ready to start inputting your actual returns. I would however
suggest that you use some test data first, just in case. I played around
with actual returns quite a lot, and to me it seemed correct, but one never
knows!

 

[Kassie]

=IF(C14-C15,C15-C22,0) is of course incorrect. It should read
=IF(C14<C15,C15-C22,0)

 

[calquestions]

Kassie, THANK YOU, THANK YOU, THANK YOU...VERRRRRRRRRRY MUCH! It works
great!

 

[Kassie]

Hi Cal

Told you we would get it right, didn't I?

Glad it is working my friend