 # Dynamically updating multiple scenario cashflow

• Hi All,

I have a small problem I would like some help with if possible. Basically what I have is the below (dummy numbers):

The original cashflow line is what I am working off.

I need to produce two alternative rows below this line which do the following:

Row 9: Subtract all positive values AFTER the financing date (31 March in this case, this has to be able to change at a later point if required) until the amount subtracted has reached the Threshold amount (Cell B1). I need to ONLY subtract positive amounts and ignore negative amounts. Then once the threshold amount has been matched, I want to add back in the total threshold amount on the closing date (Cell L5, however this may change also so that will need to be taken into account). I have shown this row as I would like to see it output as an example.

Row 11: I want to ignore all values until you reach the financing date (31 march). Then ON the financing date, subtract the entire threshold, then proceed to ignore any values until the closing date (31 September) has been reached, then add the full threshold back in ON the closing date. It's the same case above where the financing date and the closing date can change in the future so this would need to update dynamically. I have also shown an example row (row 11) where the inputs I would require are shown.

Let me know if any of the above needs clarification,

Thanks

(Other forum where this was asked: http://www.excelforum.com/exce…set-limit-is-reached.html )

## Files

• Re: Dynamically updating multiple scenario cashflow

in Row 9 you can use this formula

=IF(AND(C5>=\$G\$3,C5<\$C\$3,C7>0),MAX(0,MIN(C7,\$C\$1+SUM(\$B\$9:B9)))*-1,IF(C5=\$C\$3,SUM(\$B\$9:B9)*-1,0))

note this formula assumes a T=0 column which is specifically inserted and is now Col B and therefore other values have been right shifted by one column

in Row 11 you can use this formula

=IF(C5=\$G\$3,\$C\$1*-1,IF(C5=\$C\$3,\$C\$1,0))

copy paste into other cells

hope that helps

• Re: Dynamically updating multiple scenario cashflow

Thank you for the response. I'm not 100% on the t=0 comment, can you please clarify?

Thank you.

• Re: Dynamically updating multiple scenario cashflow

Quote from normilet;739749

Thank you for the response. I'm not 100% on the t=0 comment, can you please clarify?

Thank you.

Assuming I just insert a new column so that column B is now empty, the formula now just outputs 0 for every cell in both rows.

• Re: Dynamically updating multiple scenario cashflow

not possible....see the attached

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!