Negative Values Only

[calquestions]

I'm trying to use a formula to calculate a fee however the fee is only
calculated for negative values.
Ex. I have a range of Cash Flows F22-F42. The first Cash Flow is
negative which I would charge .75%. So if I have a negative $100,000,
the Fee would be $750. This fee will be charged for every period from
here on. Lets say during the second period the is a positive Cash Flow,
then the fee would remain $750. However, if during the third period the
Cash Flow was a negative $25,000, the fee would increase to $1000,
based on accumulation of the negative cash flows ($75,000 +
$25,000)*.0075= $1000. If the remaining Cash Flows were to be positive,
then the fee would remain the same $1000 for the remaining period.
Basically I just need to use the negative Cash Flows during the
periods. Would I use the "IF" statement and if so, how?
Thank you in advnace.
Cal

 

[Jim Rech]

I think you need this formula in cell G23 and copied downward:

=MIN(F23,0)*0.0075+G22

For G22 copy this up from G23 and edit out the last addition.

--
Jim
| I'm trying to use a formula to calculate a fee however the fee is only
| calculated for negative values.
| Ex. I have a range of Cash Flows F22-F42. The first Cash Flow is
| negative which I would charge .75%. So if I have a negative $100,000,
| the Fee would be $750. This fee will be charged for every period from
| here on. Lets say during the second period the is a positive Cash Flow,
| then the fee would remain $750. However, if during the third period the
| Cash Flow was a negative $25,000, the fee would increase to $1000,
| based on accumulation of the negative cash flows ($75,000 +
| $25,000)*.0075= $1000. If the remaining Cash Flows were to be positive,
| then the fee would remain the same $1000 for the remaining period.
| Basically I just need to use the negative Cash Flows during the
| periods. Would I use the "IF" statement and if so, how?
| Thank you in advnace.
| Cal
|