muck Posted January 19, 2009 Share Posted January 19, 2009 (edited) Column A: Time periods, beginning with month 0 and ending with month 120 Column B: % of area under a bell curve that applies to that month for "best case". Column C: "best case" results Column D: % of area under a bell curve that applies to that month for "expected case" Column E: "expected case" results Column F: % of area under a bell curve that applies to that month for "worst case" Column G: "worst case" ................. Noteworthy factors / assumptions: 1) "best", "expected" and "worst" cases have different assumptions for: a) the date that the first cash flows start (i.e., 3, 4 and 8 months out) the date that the peak cash flows occur (i.e., 6, 8 and 16 months out) c) the date the final cash flows occur (i.e., 36, 48 and 96 months out) d) the percentage of total cash flows to occur in the peak month is fixed at 25% ...and... e) the cash flows will fall similar to a bell curve with short tails to the left of the median and much longer out to the end. For example: In the "best case", the first cash flows come in in month three and then ramp up incrementally to peak in month eight, and then tail off until the final cash flow comes in in month 36 ... the "expected case" is months 4, 8 and 48 ... and the "worst case" is months 8, 16 and 96 ... so ... in (for example) month six ... in the "best case" we're looking at peak cash flow, but in "expected case" we're somewhere between 'just getting started' (i.e., month four) and 'peak cash flow' (i.e., month eight) and in "worst case", we've not yet started bringing in cash flow. ................. Objective: Create a single formula which can be used for each successive month (i.e., each of the 120 periods beginning in "month 1" and ending in "month 120") which references these variables that will tell me how much cash flow applies to each period so that I can calculate IRR for these various investments and that the formula will allow changes to the assumptions without doing anything other than changing the assumptions. Here's where I am so far: =IF("current month" < "month of first cash flow" , 0 , IF(AND("current month" >= "month of first cash flow" , "current month" < "month of peak cash flow") , " " , IF("current month" = "month of peak cash flow" , " " , IF("current month" < "month of final cash flow" , " " , IF("current month" = "month of final cash flow" , " " , 0))))) Edited January 19, 2009 by muck Quote Link to comment Share on other sites More sharing options...
westvirginia Posted January 19, 2009 Share Posted January 19, 2009 Hold on, gotta get my crayons and construction paper... Quote Link to comment Share on other sites More sharing options...
CaP'N GRuNGe Posted January 19, 2009 Share Posted January 19, 2009 Ummmm...... 342.1231232341 ? Quote Link to comment Share on other sites More sharing options...
muck Posted January 20, 2009 Author Share Posted January 20, 2009 Here is a picture that could generally be used to describe the anticipated cash flows that I'm trying to model with different rates of increase and decrease and across shorter and longer periods of time Quote Link to comment Share on other sites More sharing options...
westvirginia Posted January 20, 2009 Share Posted January 20, 2009 Here is a picture that could generally be used to describe the anticipated cash flows that I'm trying to model with different rates of increase and decrease and across shorter and longer periods of time I can help you with the CF, because that's similar to impairment calculations that I do, in addition to stuff I remember from college. But I'm not sure of the thinking behind how you calculate your percentage of chance of each criterion. Don't you have to calculate standard deviation and all that? Where's the wiegie board when you need it? Quote Link to comment Share on other sites More sharing options...
muck Posted January 20, 2009 Author Share Posted January 20, 2009 Here's where I am so far:=IF("current month" < "month of first cash flow" , 0 , IF(AND("current month" >= "month of first cash flow" , "current month" < "month of peak cash flow") , " " , IF("current month" = "month of peak cash flow" , " " , IF("current month" < "month of final cash flow" , " " , IF("current month" = "month of final cash flow" , " " , 0))))) Here's where I am so far: =IF("current month" < "month of first cash flow" , 0 , IF(AND("current month" >= "month of first cash flow" , "current month" < "month of peak cash flow") , " " , IF("current month" = "month of peak cash flow" , "Total $ Expected Cash Flow" x "% Recovered in Peak Month" , IF("current month" < "month of final cash flow" , " " , IF("current month" = "month of final cash flow" , " " , 0))))) ...I need to figure out the math for all of the " " sections...most all of which need to have some sort of reference to the 'area under the curve' calculations referenced above ... Quote Link to comment Share on other sites More sharing options...
Brentastic Posted January 20, 2009 Share Posted January 20, 2009 Here's where I am so far:=IF("current month" < "month of first cash flow" , 0 , IF(AND("current month" >= "month of first cash flow" , "current month" < "month of peak cash flow") , " " , IF("current month" = "month of peak cash flow" , "Total $ Expected Cash Flow" x "% Recovered in Peak Month" , IF("current month" < "month of final cash flow" , " " , IF("current month" = "month of final cash flow" , " " , 0))))) ...I need to figure out the math for all of the " " sections...most all of which need to have some sort of reference to the 'area under the curve' calculations referenced above ... I'm confused what you are looking for. Do you have a columns that provides the 'Total $ expected cash flow' and the '% recovered in any given month'? And also, for all of your null (" ") sections, is that just your code for 'what do I put here' or do you actually want it to return null in those scenarios? Quote Link to comment Share on other sites More sharing options...
muck Posted January 20, 2009 Author Share Posted January 20, 2009 I'm confused what you are looking for. Do you have a columns that provides the 'Total $ expected cash flow' and the '% recovered in any given month'? And also, for all of your null (" ") sections, is that just your code for 'what do I put here' or do you actually want it to return null in those scenarios? Sorry ... it is a bit confusing ... Columns? No. Cells? Yes. Null section = code for what do I put here, not actually looking for a 'null' answer Quote Link to comment Share on other sites More sharing options...
SuperBalla Posted January 20, 2009 Share Posted January 20, 2009 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.