Calculating Call and Put Option Payoff in Excel

A collar is an options strategy which is protective in nature, which is implemented after a long position in a stock has proved to be profitable. A protective put is implemented when you are bullish on a stock, but want to protect yourself from losses in case the stock price decreases. Subscribe to Trading Tuitions. Please kindly drop a mail. Underlying price is the price at which the underlying security is trading on the market at the moment you are doing the option pricing. Leave a Reply Cancel reply Your email address will not be published.

Options Basics. An option is a financial instrument in the form of a contract used to buy or sell an equity instrument such as a stock, or exchange fund.

Black-Scholes Parameters in Excel

I have decided to enter the strike, initial price and underlying price inputs in cells C4, C5, C6, respectively. The result will be shown in cell C8. While not necessary for a simple calculation like this one, it is a good idea to somehow graphically differentiate input and output cells, especially when you are building a more complex spreadsheet. It will make the sheet much easier to use and reduce the risk of you or someone else accidentally overwriting your formulas in the future.

It is best to do this consistently across all your spreadsheets. Personally, I always make the background of input cells where user is expected to enter values yellow and the output cells which typically contain formulas and should not be overwritten green — just my habit, you can of course use different colors, fonts, borders, or other formatting.

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:.

Now we need to implement this formula in Excel. It is very easy, because Excel has the MAX function, which takes a set of values separated with commas and returns the greatest of them. In our example, the formula in cell C8 will be:.

With the inputs in our example 45 and 49 , cell C8 should now be showing 4. You can test different values for the underlying price input and see how the formula works.

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.

Learn more in the article on the SUM function. You can either copy the formula , or use the fill handle to copy the formula to adjacent cells. For example, when you copy the formula in cell B6 to C6, the formula in that cell automatically changes to update to cell references in column C. Cell references may change if they have relative references. For more information, see Copy and paste a formula to another cell or worksheet.

You can always ask an expert in the Excel Tech Community , get support in the Answers community , or suggest a new feature or improvement on Excel User Voice. Calculation operators and order of operations in Excel. Overview of formulas in Excel. How to avoid broken formulas.

Find and correct errors in formulas. Excel keyboard shortcuts and function keys. Excel functions by category. Create a new workbook Video Insert or delete a worksheet Video Move or copy worksheets or worksheet data Video Print a worksheet or workbook Article Use Excel as your calculator Article Fill data automatically in worksheet cells Video.

Expand your Office skills. Get new features first. Was this information helpful? How can we improve it? The Product The Article. Thank you for your feedback!

Payoff Formula Inputs and Outputs

Calculating Call and Put Option Payoff in Excel This is the first part of the Option Payoff Excel Tutorial. In this part we will learn how to calculate single option (call . You can calculate the market implied volatility for each option by simply typing in the market price of The greeks for Stock positions were previously displaying as Put options. Stock To stay up to date with changes to this workbook and other modifications to the website, please feel free and choose Excel Options, which is located at. Free and truly unique stock-options profit calculation tool. View a potential strategy's return on investment against future stock price AND over time. Your trade might look good at expiry, but what about next week? Options Profit Calculator. Options Profit Calculator provides a unique way to view the returns and profit/loss of stock.