# Stock Loss "Cap" Formula

#### StockPrice

##### New Member
I’m trying to create formula that will calculate the loss on a stock given certain conditions. I need to “cap” the loss based on the average price of the stock over the 90 day period after it drops. The cap will either eliminate the loss or lower it, depending on what the 90 day average price is.

An example:
The stock falls from \$50 to \$5.
If the 90 day average price is below \$5, then the cap is moot, and the loss is (50-5=45)
If the 90 day average price is above \$50, then the cap eliminates the loss (i.e. loss is 0).
If the 90 day average price is between \$5 and \$50, say it’s \$35, then the cap partially reduces the loss and I get (35-5=30).

I have all the inputs, I’m just having trouble coming up with a formula which will return the correct answer under every condition listed above. I think it can be done using IF, AND, and possibly NOT statements, but I just can’t get it.

Thanks!

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
=if( cell with 90 day average - or the calculation < the cell with the final price (in your example 50 to 5) , 90 day average - cell with final price,
IF (

How do we know the value is above the example \$50 ,what is the cell with \$50 ?

can you layout the data

you have a cell with
stock price say each day and its dropping
how will the rules work

90day average is OK
latest stock price in example \$5

The stock falls from \$50 to \$5.
\$50 - is this a drop from when the stock was first entered \$50 on the spreadsheet ?

Assuming you have information in three different columns:
Column "A" (or your column) = purchased price, start price, price at beginning of period etc.
Column "B" (or your column) = price, price now, price at end of period, etc.
Column "C" (or your column) = 90 day average
Column "D" (or your column) = Loss or ???

Try this for column "D" or your column: =IF(OR(A4="",B4="",C4=""),"",IF(C4<B4,A4-B4,IF(C4>A4,0,C4-B4)))

Replies
1
Views
541
Replies
3
Views
902
Replies
3
Views
791
Replies
1
Views
108
Replies
2
Views
277

1,196,235
Messages
6,014,146
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back