|
Huge problem with "if" formula's
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 |
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(C2C3,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(C2C3,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. " wrote: 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 |
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? " wrote: 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 |
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 |
I tried but I am anything but an expert in Excel.
|
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? -- ve_2nd_at. Randburg, Gauteng, South Africa "Kassie" wrote: 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? -- ve_2nd_at. Randburg, Gauteng, South Africa " wrote: 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 |
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 |
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 |
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 |
|
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. -- ve_2nd_at. Randburg, Gauteng, South Africa " wrote: 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 |
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 |
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 |
|
Kassie, THANK YOU, THANK YOU, THANK YOU...VERRRRRRRRRRY MUCH! It works
great! |
Hi Cal
Told you we would get it right, didn't I? Glad it is working my friend -- ve_2nd_at. Randburg, Gauteng, South Africa " wrote: Kassie, THANK YOU, THANK YOU, THANK YOU...VERRRRRRRRRRY MUCH! It works great! |
| All times are GMT +1. The time now is 05:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com