Option Profit Calculator Excel (Template)

Option trading gives you great leverage while trading where you are able to control many more shares for your investment than if you were to just invest in shares. However, with this leverage comes a lot more risk as well.

This Option Profit Calculator Excel template will provide you with the ability to quickly find out your profit or loss given the price of the stock move a certain way at expiry.

MarketXLS provides many stock option related functions. These functions can get you real-time option chains, options history, and live prices. We have created an Option Profit Calculator Excel Template which integrates many these functions to provide a simple profit calculator.

The Excel template has some VBA code in it which call MarketXLS functions to pull the option chains automatically. All you need to do is enter the Symbol of the stock and program will download all active options contracts and their details. After getting the option chain for the stock this program will populate various dropdown, charts, etc for you to simply get started populating the legs of your option strategy.

There are no buttons to click to download the data, all you need to do is change the symbol and the data is automatically retrieved in the background. The template creates a chart that shows what would your P&L position be if all the options in your option strategy were to expire on their respective expiry dates. With this Excel template and options data from MarketXLS you instantly know what is likely to be the maximum profit or loss for your options strategies.

To get started with this template, you will need to have an active MarketXLS subscription with options data. Once you are able to access historical options data and option chain from MarketXLS, you should be able to simply enter your stock symbol as shown below and start choosing your option strategy legs using pre-populated active options contracts with easy to use Excel drop-downs.

The following block allows you to choose different Expiry dates, Strike prices and the trade direction.

Option profit

Depending on your choices the template then makes the option contract symbols and looks up various bits of information from the options table downloaded below.

The Scenario calculator allows you to select the strike prices at various active options and calculates the range of your profit or losses if the price reaches a certain level.

option calculator in Excel

The chart shows your P&L position as the price moves on the x-axis.

To take advantage of this Option Profit Calculator Excel template, and many other MarketXLS’s option-related functions try out MarketXLS today from the link below.

Get Option Prices in Excel with Easy to Use Excel formulas

Use MarketXLS to stream real time or delayed live Pricing (Last, Bid, Ask and so on) for getting Stock Option Prices in Excel. You will need any version of plan of MarketXLS along with a data subscription for Options Pricing. 

This article describes various commonly used Options Pricing functions with Quotemedia's options Data/prices and historical option pricing in Excel. 

1. To Get all option chain of a stock symbol

=QM_List("getOptionChain","Symbol","MSFT") or =qm_getOptionChain("MSFT")

2. To Get all option chain of a stock symbol

To get all the option contracts use the following function


3. To get the last option price of an option symbol

=QM_Last("@MSFT  180629P00090000") or =QM_Last("MSFT180629P00090000")

4. To get the expiry date of a contract
=QM_ExpiryDate("@MSFT  180629P00090500") 

5. To see if the contract is a call or a put
=QM_CallPut("@MSFT  180629P00090500")

6. To extract the strike price of an option

=QM_Strike("@MSFT  180629P00090500")

7. To get the highest price of an options contract
=QM_ContractHigh("@MSFT  180629P00090500")

8. To get the lowest price of an options contract
=QM_ContractLow("@MSFT  180629P00090500")

9. To get the Open Interest for an options contract 

=QM_OpenInterest("@MSFT  180629P00090500")

10. To get the Real time option greeks for an options contract 

=qm_stream_alpha("@MSFT  180629P00090500")

=qm_stream_beta("@MSFT  180629P00090500")

=qm_stream_gamma("@MSFT  180629P00090500")

10. To get the option chains 

=QM_GetOptionChain("Stock Symbol")
Gets all available option contracts for stock, including the expired ones.
=QM_GetOptionChainAtTheMoney("Stock Symbol")
Gets all available 'at the money' option contracts
=QM_GetOptionChainInTheMoney("Stock Symbol")
Gets all available 'in the money' option contracts
=QM_GetOptionChainMonthlies("Stock Symbol")
Gets all available monthly options
=QM_GetOptionChainWeekly("Stock Symbol")
Gets all available weekly options
=QM_GetOptionNearTerm("Stock Symbol")
Gets all available expiring most near term
=QM_GetOptionStructures("Stock Symbol")
Gets all available calls to the left and puts to the right, with Expiry in the middle
=QM_GetOptionChainExpireMaximum ("Stock Symbol", OnDate -"YYYY-MM-DD")
Gets all available options that expire at the maximum "OnDate"
=QM_GetOptionChainExpireMinimum ("Stock Symbol", OnDate -"YYYY-MM-DD")
Gets all available options that expire at the minimum "OnDate"

11. Get the End of the Day History of a specific Option Contract

=QM_getHistory("Option Symbol")

12. Get the Intraday Live History of a specific Option Contract

=QM_getIntradayHistory("Option Symbol")

13. Scan for options

With MarketXLS Option scanner, you can select a few stocks (up to 50 stocks at a time) and load all the option contracts for these symbols into an easy to use screener.


14. Option Profit Calculator

Use our Profit Calculator template to analyse your option strategies

Option Profit Calculator Excel

Simple Pricing * No Surprises * Immediate License Delivery * Support Included * Free Upgrades * 30 Day Money Back Guarantee on Annual Plan * Additional Data Bundles * Valuation Templates

Take your Options research to the next level

400+ Stock Market Functions

Excel Integration

100+ of Technical Calculators

Beautiful Dynamic Charts

Free Upgrades

Social Media Integration

Candle Pattern Scanning

Valuation Templates

Any Excel Version

 You Get

All US Options Prices in Excel

Options Chains

Options Scanner

Option Functions

Real Time Option Data Streaming

Option Profit Calculator Template

MarketXLS Analytical Data

& More