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 or put) profit or loss for a given underlying price. This is the basic building block that will allow us to calculate profit or loss for positions composed of multiple options, draw payoff diagrams in Excel, and calculate risk-reward ratios and break-even points.

Understanding Option Payoff Formulas

Before we start building the actual formulas in Excel, let’s make sure we understand what an option payoff formula is. It is a function that calculates how much money we make or lose at a particular underlying price.

For example, it answers the following question:

I have bought a $45 strike call option for $2.35. What will my profit or loss be if the underlying ends up at $49 at expiration?

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:

  1. Strike price of the option = 45
  2. Initial price for which we have bought the option = 2.35
  3. Underlying price for which we want to calculate the profit or loss = 49

The output is of course the profit or loss that we want to calculate.

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.

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.

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:

If you don’t understand why, see detailed explanation and examples in Call Option Payoff Diagram, Formula and Logic.

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:


… where cells C4 and C6 are strike price and underlying price, respectively.

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.

But we are not finished yet.

Call Option Profit or Loss Formula

Because we want to calculate profit or loss (not just the option’s value), we must subtract our initial cost. 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.65, which is the profit made from a $45 strike call, purchased for $2.35, when the underlying stock is at $49 at expiration.

You can again test different input values. For any underlying price smaller than the strike price (C6 < C4), the result is always equal to negative initial price (C5).

Note: A common mistake is to put the “-C5” inside the brackets, which would effectively subtract the option’s initial cost only in one or the other scenario. Because we pay for the option regardless of its eventual outcome, we must put the “-C5” at the very end, outside the brackets, so it applies under all scenarios.

Put Option Profit or Loss Formula

For put options the logic and formula is almost the same, with just one little difference: Inside the brackets in the MAX function in the first half before the comma, the order of strike price and underlying price is reversed, because a put option’s value grows when underlying price goes down below the strike price. In other words, a put option’s value is the greater of:

Let’s create a put option payoff calculator in the same sheet in column G. The put option profit or loss formula in cell G8 is:


… where cells G4, G5, G6 are strike price, initial price and underlying price, respectively.

The result with the inputs shown above (45, 2.35, 41) should be 1.65.

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.

Next Steps

In particular, our calculator only works for long call and long put positions, but can’t be used for short call or short put. 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).

But before we do that, let’s merge our call and put calculations into one – it will not only make the spreadsheet more convenient for practical use, but also allow us to only make all the future changes just once, rather than for calls and puts separately.

We will merge our call and put calculations in the next part of the tutorial.

Related pages

when do vix options expirecalculating volatility for black scholesblack scholes putexcel macro averagemsft historical pricesarithmetic and geometric averageswhat is skew in statisticsvariance and standard deviation practice problemsstock covariance calculatorwhat does implied volatility meancalculating the intrinsic value of a stockhedge deltafx option pricercalculate the sample variancewhat does macd meanvariance covariance var exampleexcel formulas standard deviationoptions futures and other derivatives solutionssec 13f filingvix bloomberglist of section 13f securities13f securitiesyahoo finance quotes csvnormdist excel examplesample mean standard deviation calculatorreward to variability ratio formulahow to calculate a sample varianceoption stradlecalculate dividend yieldvxzhow to create an equation in excelyahoo futures indicesstandard deviation of stock calculatoraverage real return formulaunbiased standard deviation calculatorfx function in excelvix future quoteimplied volwhat is coefficient of skewnesscalculators with negativesweighted average yield formulahow to calculate percent deviationvxx optionsoptions long straddlehow to buy the vixbutterfly option examplehow to calculate formula in excelhistorical vixrsi valuecalculate standard deviation on excelskewness meansexcel formula explainedhedge fund 13fhow to calculate ending inventory using average costhow to calculate intrinsic value of a stock13f databaseln x excelcall option intrinsic valuesigma calculator with stepsadvantages and disadvantages of mean median and modedow jones weightinghedge funds definition with examplesnatural log in excel13f portfolionotepad with fontsvix futures contractnikkei index definition13f reportingcost variance calculationpricing spreadsheetsharp measureoptions long straddleimplied volatility vbastraddle position optionssquare root excel formulahow to use natural log in excelpercentile calculator standard deviation mean