This is part 8 of the Option Payoff Excel Tutorial.
By Joe Duarte A call option provides you with profits similar to long stock, whereas a put option provides you with profits similar to short stock.
I have entered the strikes in a rather unusual order on purpose, as we want to make the calculator work regardless of the order of legs that user enters. We will now calculate the exact figures.
How to Calculate a Stock Option Break-Even Point | Budgeting Money - The Nest
Its direction or slope can only change at the strikes. Knowing these helped us find maximum profit and maximum loss in part 6 and it will now help us find the break-even points. We will use this logic for our Excel calculations.
We can copy the column labels from row 63 to row Although we have already worked with the key points in part 6 when calculating maximum profit and maximum loss, so we can find them in rowsnow we also need them sorted from lowest to highest in order to know the adjacent pairs.
Therefore it is best to start our calculations over from the beginning.
The first task is to identify the strikes and order them from lowest to highest. We want cells CF73 to display the strike for the particular leg the same number as in row 4 — the strike input cellsbut only if this particular leg is being used for the current strategy position size in row 2 is not zero.
We want row 73 to display zero for these inactive legs.
- How to get 25 on an option
- Compare dealing center
- Traditional investing in the stock market involves buying shares when the price is low and selling them when the price is high.
- How to Calculate a Stock Option Breakeven Point | Finance - Zacks
In other words, we want cell C73 to display the first leg strike cell C4 if position size cell C2 is not zero, and display zero if position size is zero.
Make sure you have relative references to C2 and C4 no dollar signs and break even point options the formula to the other legs — to cells D73, E73, F Test it by changing some of the position sizes in row 2 to zero — row 73 should also change to zero.
- Unique ways to make money online
- How much you can start trading options
- Put options give you the right to sell an asset.
- How to Determine the Break-Even Point of a Call Option | Budgeting Money - The Nest
Ranking the Strikes In the next row we will rank the strikes from lowest to highest. EQ function returns the same rank the lowest for all of them. For example, if you have a range of numbers 10, 30, 20, 30, 10 and ask how many times the value 30 is in that range, the function would return 2. Because we also have the -1 at the end of the formula, the resulting rank does not change in this case and it is still 2.
Breakeven Point (BEP)
You should get the ranks 2, 3, 4, 1. The one rank which has changed is the rank of the second 50 strike, which has increased from 2 to 3.
Listing Ordered Key Points Now we have the strikes ranked from 1 to break even point options and each strike has a unique rank. We have everything ready to list all our key underlying price points, ordered from zero to the highest strike in cells B75 to B Put the value zero in cell B Cells B76 to B79 will be strikes with rank 1 to 4, Satoshi course this order.
Put the numbers 1, 2, 3, 4 in cells A76, A77, A78, A79, respectively.
The formula in cell B76 should perform two tasks: Find out, which of the legs has strike with rank 1. Return the strike for that leg. We will use it to find out, where in the range of cells CF74 is the value 1.
Breaking Even with Options
Now we will do the second part — get the strike price for the leg whose number we have just found with MATCH leg 4. We are asking the question: What is the value of the 4th cell in the range CF73?
These functions are well worth mastering, not only for the purpose of this tutorial. You may want to read more about break even point options and play with them a bit. We have now listed zero and the four strikes, ordered from lowest to highest, and we have one last key point to add — the infinite underlying price.
I choose 1, one billion. The result should look like this: I have colored the background of rows just to remember these formulas are different than the other rows.
Calculating Option Strategy Break-Even Points
Finding Break-Even Points The next step is to find where between which of the adjacent key points the break-evens are. Copy the formula from cell H76 to cells HH There are two break-even points: one between 45 and 50, another between 50 and We also know the line between these two points is straight, with constant slope. The result should look like this: The second break-even point is We are approaching the end of the Option Payoff Excel Tutorialbut there is one last part where I will introduce a few more ideas and possible further improvements to our spreadsheet.