PREFACE
In options trading, a price ladder shows how much profit or loss (percent & dollar) is realized relative to current contract price. These figures are calculated by comparing the new price of a contract to the "base" or entry price.
Note: If the details below are too wordy or TL;DR, I provided a link to an explainer video with visual examples.
BACKGROUND
- New price is the point at which a trade is closed (this can be positive or negative, depending on how the trade went relative to the entry price).
- If new price is higher than original price, a profit is realized.
- If new price is lower than original price, a loss is realized.
- Contract prices are always multiplied by 100.
- The entire cost of a trade is calculated in this manner: contract price x 100 x quantity of contracts.
- On a price ladder, contract price moves up or down based on a user selected drop down cell (D2) (meaning, values go up or down by either .01 or .10)
- The values of the "P/L OPEN %" and "P/L OPEN $" on the price ladder are created based on the entry price (the key value from which all calculations are derived).
- As part of risk management, a stop loss prevents a trade from experiencing a loss greater than what is planned in the event the trade goes in the negative direction.
EXAMPLES
Original or "entry" contract price = $7.50 (x100) = $750
Qty contracts = 5
Total contract cost = 750*5 = $3,750
New or "exit" contract price = $8.30
Dollar Profit = ((8.30-7.50)*100)*5 = $400
Percent Profit = (8.30/7.5)-1 = 10.67%
Regarding stop loss, although the planned Stop Loss amount in C2 is $457, the correct contract price for this stop loss amount is 6.60 (A89) because it is okay to be below that amount ($450 in cell C89), not over it.
For example the contract price of 6.50 would exceed the stop loss because at that level, the loss would be $500 (C90) and that exceeds the stop loss amount of $457.
THE PROBLEM
The price ladder has to be created manually and sometimes I may not want to refer to an actual price ladder but rather, have the information I need automatically populated with a mathematical formula.
REQUESTED SOLUTIONS
#1 - To serve as a visual aid, create a function that when values A2:C2 are provided, a price ladder is automatically created (100% to the positive and 100% to the negative, as shown). The solution must account for the user defined input D2 (this value determines how the prices increase or decrease. In the example shown, price increases or decreases every $0.10 but if $0.01 is chosen from the dropdown, then the price ladder should reflect accordingly).
#1a - Since I want the population of the price ladder to be optional to the user, the formula must look for a TRUE value by way of a checkbox (E2). If the checkbox is ticked, populate the price ladder. If the checkbox is empty, do not populate the price ladder.
#1b - On the actual price ladder, the original (entry) price would have conditional formatting of YELLOW (as found in cell A2, which correlates to cell A80 in this example).
#1c - On the actual price ladder, the stop loss price would have conditional formatting of RED (as found in cell G2, which correlates to cell A89 in this example).
#2 - When values A2:C2 are provided, a contract price representing a stop loss level is populated in cell G2. This formula should be independent of the price ladder, meaning, If the price ladder is not present (no vertical or other type of lookup functions), the value would be derived mathematically.
EXPLAINER VIDEO
www.loom.com
ONLINE SHEETS VERSION
docs.google.com
EXCEL VERSION
In options trading, a price ladder shows how much profit or loss (percent & dollar) is realized relative to current contract price. These figures are calculated by comparing the new price of a contract to the "base" or entry price.
Note: If the details below are too wordy or TL;DR, I provided a link to an explainer video with visual examples.
BACKGROUND
- New price is the point at which a trade is closed (this can be positive or negative, depending on how the trade went relative to the entry price).
- If new price is higher than original price, a profit is realized.
- If new price is lower than original price, a loss is realized.
- Contract prices are always multiplied by 100.
- The entire cost of a trade is calculated in this manner: contract price x 100 x quantity of contracts.
- On a price ladder, contract price moves up or down based on a user selected drop down cell (D2) (meaning, values go up or down by either .01 or .10)
- The values of the "P/L OPEN %" and "P/L OPEN $" on the price ladder are created based on the entry price (the key value from which all calculations are derived).
- As part of risk management, a stop loss prevents a trade from experiencing a loss greater than what is planned in the event the trade goes in the negative direction.
EXAMPLES
Original or "entry" contract price = $7.50 (x100) = $750
Qty contracts = 5
Total contract cost = 750*5 = $3,750
New or "exit" contract price = $8.30
Dollar Profit = ((8.30-7.50)*100)*5 = $400
Percent Profit = (8.30/7.5)-1 = 10.67%
Regarding stop loss, although the planned Stop Loss amount in C2 is $457, the correct contract price for this stop loss amount is 6.60 (A89) because it is okay to be below that amount ($450 in cell C89), not over it.
For example the contract price of 6.50 would exceed the stop loss because at that level, the loss would be $500 (C90) and that exceeds the stop loss amount of $457.
THE PROBLEM
The price ladder has to be created manually and sometimes I may not want to refer to an actual price ladder but rather, have the information I need automatically populated with a mathematical formula.
REQUESTED SOLUTIONS
#1 - To serve as a visual aid, create a function that when values A2:C2 are provided, a price ladder is automatically created (100% to the positive and 100% to the negative, as shown). The solution must account for the user defined input D2 (this value determines how the prices increase or decrease. In the example shown, price increases or decreases every $0.10 but if $0.01 is chosen from the dropdown, then the price ladder should reflect accordingly).
#1a - Since I want the population of the price ladder to be optional to the user, the formula must look for a TRUE value by way of a checkbox (E2). If the checkbox is ticked, populate the price ladder. If the checkbox is empty, do not populate the price ladder.
#1b - On the actual price ladder, the original (entry) price would have conditional formatting of YELLOW (as found in cell A2, which correlates to cell A80 in this example).
#1c - On the actual price ladder, the stop loss price would have conditional formatting of RED (as found in cell G2, which correlates to cell A89 in this example).
#2 - When values A2:C2 are provided, a contract price representing a stop loss level is populated in cell G2. This formula should be independent of the price ladder, meaning, If the price ladder is not present (no vertical or other type of lookup functions), the value would be derived mathematically.
EXPLAINER VIDEO
Loom | Free Screen & Video Recording Software
Use Loom to record quick videos of your screen and cam. Explain anything clearly and easily – and skip the meeting. An essential tool for hybrid workplaces.
ONLINE SHEETS VERSION
Sample Price Ladder
Price Ladder Contract Price (x 100),QTY ,Planned Stop Loss,Increment / Decrement,Display Price Ladder?,Position Size,Contract Price Stop Loss,Legend 750,5,457,.10,3,750.00,6.60,Entry Price Stop Loss Contract Price,P/L Open %,P/L Open $ 15.00,100.00%,3,750.00 14.90,98.67%,3,700.00 14.80,97.33%,3,...
EXCEL VERSION
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B5:B155 | B5 | =A5/$A$80-1 |
| C5:C155 | C5 | =((A5-$A$80)*100)*$B$2 |
