Call option graph excel
For any underlying price smaller than or equal to 45 it should return zero; for values greater than 45 it should return the difference between cells C6 and C4. This is again very simple to do — we will just subtract cell C5 from the result in cell C8. The entire formula in C8 becomes:. Cell C8 should now be showing 1. You can again test different input values. For put options the logic and formula is almost the same, with just one little difference: The put option profit or loss formula in cell G8 is:.
Now we have created simple payoff calculators for call and put options. However, there are still some things we can improve or add to make our spreadsheet more useful. Furthermore, our calculator only shows profit or loss per share, while many people are actually more interested in total dollar profit or loss, especially when working with positions of multiple option contracts.
Therefore, we should improve our calculations to also consider direction long or short , position size number of contracts and contract size number of shares represented by one option contract.
We will merge our call and put calculations in the next part of the tutorial. If you don't agree with any part of this Agreement, please leave the website now. All information is for educational purposes only and may be inaccurate, incomplete, outdated or plain wrong.
Macroption is not liable for any damages resulting from using the content. No financial, investment or trading advice is given at any time. Home Calculators Tutorials About Contact. Tutorial 1 Tutorial 2 Tutorial 3 Tutorial 4. For example, it answers the following question: Payoff Formula Inputs and Outputs In the above example you can identify several inputs that our payoff formula will take — they are the numbers we already know: Preparing the Cells In an Excel spreadsheet, we first need to set up three cells where we will enter the inputs, and another cell which will show the output.
Call Option Value Formula Now we have the cells ready and we can build the formula in cell C8, which will use the inputs in the other cells to calculate profit or loss.
In general, call option value not profit or loss at expiration at a given underlying price is equal to the greater of: In our example, the formula in cell C8 will be: It is a product of three things:. Of course, with a long call position the initial cash flow is negative, as you are buying the options in the beginning.
The second component of a call option payoff, cash flow at expiration, varies depending on underlying price. That said, it is actually quite simple and you can construct it from the scenarios discussed above.
If underlying price is below than or equal to strike price, the cash flow at expiration is always zero, as you just let the option expire and do nothing. If underlying price is above the strike price, you exercise the option and you can immediately sell it on the market at the current underlying price.
Therefore the cash flow is the difference between underlying price and strike price, times number of shares. Putting all the scenarios together, we can say that the cash flow at expiration is equal to the greater of:. It is the same formula. The screenshot below illustrates call option payoff calculation in Excel. Besides the MAX function, which is very simple, it is all basic arithmetics. One other thing you may want to calculate is the exact underlying price where your long call position starts to be profitable.
If you don't agree with any part of this Agreement, please leave the website now. All information is for educational purposes only and may be inaccurate, incomplete, outdated or plain wrong. Macroption is not liable for any damages resulting from using the content. No financial, investment or trading advice is given at any time. Home Calculators Tutorials About Contact. Tutorial 1 Tutorial 2 Tutorial 3 Tutorial 4. We will look at: Call Option Payoff Diagram Buying a call option is the simplest of option trades.
The key variables are: Strike price 45 in the example above Initial price at which you have bought the option 2. Call Option Scenarios and Profit or Loss Three things can generally happen when you are long a call option.