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(C14C15,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(C14C15,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(C240,ROUND(C24*0.8,0),0)
Cell C26 =IF(C240,C24-C25,0)
Cell C27 =IF(C23C24,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(C280,C28*0.5,0)
Cell C30 =IF(C280,C28-C29,0)
Cell C31 =IF(C27C28,C27-C28,0)
Cell C32 =IF(C310,ROUND(C31*0.8,0),0)
Cell C33 =IF(C310,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!
--
ve_2nd_at. Randburg, Gauteng, South Africa
" wrote:
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