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(C14C15,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(C14C15,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.
--
ve_2nd_at. Randburg, Gauteng, South Africa
" wrote:
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