Option Portfolio Manager User Guide

This is the user manual for the Option Portfolio Manager.

Enable Calculations and Macros

If you have just downloaded the manager and opened it for the first time, you may find the dropdown boxes empty and calculations not working.

This is when your Excel opens the file in Protected View or a similar restricted regime where macros and calculations are blocked for security. Usually you will see a notice in place or below your top Excel menu, with a button to "Enable Editing", "Enable Macros" or "Enable Calculations" – the wording varies, depending on your Excel version and settings.

Click that button (in some cases you also need to click another button afterwards to enable another thing) and everything should work normally.

Sheets

There are six sheets:

• Main: Main sheet where you set inputs and legs, and view results. Yellow cells are inputs – you can overwrite them. Green and other cells should not be changed.
• ChartData: Calculations for chart and risk profile. Values of individual chart points are in cells A12-D112. Do not change anything in this sheet.
• LiveData: Implementation of live market data access. Set for Interactive Brokers by default. You may modify this sheet to use another data source.
• BgCalc: Background calculations, input validation, dropdown box items – generally nothing of interest to users. Do not change anything in this sheet.
• Preferences: Some constants and settings may be modified by changing the yellow cells in this sheet.
• Help: Version information and useful links.

You can change inputs (yellow cells and dropdown boxes) in the Main sheet and settings (yellow cells) in the Preferences sheet.

You can also modify the LiveData sheet to plug in live data feed from your broker or another data source.

The other sheets (ChartData, BgCalc) are for viewing only. Do not change anything in these sheets, unless you want to change the way the manager works, and know what you are doing.

The Yellow vs. Green Cell Rule

As in all other Macroption calculators, cell background color indicates whether it is an input (yellow), formula (green), or label (grey). There is a simple rule:

• You can change the values in yellow cells (in Main and Preferences).
• Do not change green and other cells.

Some sheets (mainly ChartData and BgCalc) also contain cells with other colors. Generally all shades of blue and green are formulas, while yellow cells are constants. That said, users should not change cells of any color in these sheets.

Main Sheet Overview

Almost everything important happens in the Main sheet:

• The chart is in the top left corner.
• Below the chart in rows 16-18 you can enter global inputs (the inputs which are the same for all legs).
• In row 19 (cells N19-V19) you can see total position value, profit/loss, and aggregate Greeks.
• Individual legs are set in rows 21 and below.
• To the right of the chart (cells N2-P11) you can select what the chart will display.
• In cells N13-O14 you can override the automatic X scale.
• On the very right (cells R2-T14) you can see the Risk Profile with details on maximum profit, maximum loss, risk-reward ratio, and break-even points.

Inputs

There are two kinds of inputs needed to calculate portfolio value, P/L, and Greeks:

1. "Global" inputs, which are same for all legs, such as underlying price or interest rate.
2. Leg specifications, such as strike or expiration.

Global inputs are set below the chart in rows 16-18 in the Main sheet.

Some are required (the manager will show an error if these inputs are empty), others are optional.

If there is a problem with any of the inputs, see message in cell H18 (Main sheet) for hints.

Underlying Type (T16)

Select type of underlying security from the dropdown box in cell T16:

• Stock = for stock and ETF options.
• Index = for index options.
• Currency = for forex options, where the underlying is a currency.
• Futures = for futures options, where the underlying is a futures contract.

It is important to select the correct type, because it affects how some of the other inputs are treated.

Underlying Price (D17)

This is the price of the underlying security at the moment of valuation (usually now):

• Stock price for stock options.
• Index value for index options.
• Spot exchange rate for currency options (make sure the direction and units match the option strikes).
• Futures price for futures options (expiration matters – the futures contract must be the one that is the underlying for the options).

Type in the underlying price in the yellow cell D17.

When using live data, if you leave cell D17 blank, the live data feed price will be used. If you enter anything in cell D17, it will override the live price.

You can always see the underlying price actually used in the calculations in cell D18.

Valuation Date and Time (F17-G17)

You can enter valuation date in the yellow cell F17 and valuation time in G17. This is the moment for which you want to know the portfolio value, P/L and Greeks.

Both date and time inputs are optional. If you keep them empty, the current date and time will be used.

You can also set the date only, and keep the time empty (the manager will use the current time on the given date), or vice-versa (the manager will use the given time on today's date).

You can see the date and time actually used in the green cell H17 (do not overwrite this cell).

Volatility (L19)

Enter volatility in the yellow cell L19. This input is required.

Volatility is entered annualized (as it is typically quoted in most sources). If you are unsure which number to use, see the volatility tutorials.

You can override volatility for individual legs in column K.

Interest Rate (L16)

Enter the risk-free interest rate in the yellow cell L16.

For currency options, this is the "domestic" interest rate (of the currency in which the options are traded – in which you pay option premium).

This input is optional. If empty, 0% is used. Negative interest rates are permitted.

Yield (L17)

The yellow cell L17 is for the yield input (optional, default 0%).

For stocks or indices, this is the (continuously compounded, annualized) dividend yield.

For currency options, this is the "foreign" interest rate (of the currency that is underlying of the options – that you buy when exercising a call). Negative rates are permitted.

For futures options, this input is not available and has no effect (because both the underlying futures contract and the options do not give their holder a right to receive dividends).

Discrete Dividends (N17-P17)

With Stock selected as underlying type, you can alternatively enter dividends as discrete payment (discrete dividends are not available for the other underlying types).

Enter dividend amount, per share, in the yellow cell N17.

Enter the ex dividend date in cell O17 (required if dividend amount > 0) and ex dividend time in cell P17 (optional – if empty, default is same time as option expiration time).

Discrete dividends override the continuous dividend yield input. When dividend amount > 0, yield is ignored (considered 0%) in the calculations.

When applicable, you can see the current status of dividend inputs in cell R17.

Option Contract Size (Y17)

Option contract size is how many shares or units of the underlying security is represented by one option contract.

By default this is set to 100, as for US traded stock options, where one option contract represents 100 shares of the underlying stock.

You can change this globally in the yellow cell Y17 and override it for individual legs in column X.

Underlying Contract Size (Y18)

Underlying contract size is how many shares or units of the underlying is represented by one unit of underlying size.

By default this is set to 1, like one share of stock or one unit of a currency.

In some cases you may want to use a different number, like futures contract size when working with futures options (when the underlying is a futures contract), or lot size if you want to enter your underlying position sizes in stock or currency lots.

It is important to always have this setting in mind when entering the position size of individual legs in column C. The resulting position size is the product of leg position size and contract multiplier.

You can override the contract multiplier for individual legs in column X.

Option Expiration Time (AA17)

The yellow cell AA17 sets the expiration time of option contracts.

You can override it for individual legs in column Z. For example, when your portfolio includes options with different expiration times (e.g. AM and PM settled) on the same underlying.

It is important to use the same timezone for option expiration times and for valuation time. When trading options in a different timezone, you may want to set expiration time in your own timezone (e.g. 22:15 instead of 16:15 if you trade US options from Europe), which will allow you to enter valuation time conveniently in your own timezone (keep in mind that when the valuation time input is empty, the current time is used, which is determined by your computer's timezone).

Leg Specifications

Besides the global inputs, you need to specify details for each leg in rows 21 and below. Again, yellow cells are for inputs, while green cells must not be changed.

Leg Type (Column E)

Each leg can be a call option, a put option, a position in the underlying, or none/unused. This is set in column E.

The leg type is determined by the first character of anything you type in, case insensitive. Anything that starts with...

• C (such as "C", "c", "CALL", "Call", "call", "Call option", or even "cxwert") is a call option.
• P (as well as "Put", "put option" etc.) is a put option.
• U (as well as "Underlying", "underlying", "und" ... but not "Stock" as that does not start with "U") means the leg is a long or short position in the underlying.
• N (as well as "None", "nothing" etc.) or empty means that leg is None (unused).

The selected leg type determines which of the other leg inputs are required and which must be kept empty.

Position Size (Column C)

When a leg is used (leg type is C/P/U), position size in column C is required.

It is the number of option contracts if the leg is an option (C/P) or the number or underlying contracts or shares if the leg is a position in the underlying (U).

Positive number means a long position, negative a short position.

For example, 200 for a leg with type U means long 200 shares, -5 for a leg with type C means short 5 option contracts.

It is important to set the option contract multiplier and underlying contract multiplier right, as the position size in column C is multiplied by these to get correct position value and Greeks.

When a leg is unused (leg type is N or empty), position size must be empty, otherwise an error is raised.

Strike (Column D)

Strike (exercise price) is required for options (all legs with type C/P) and must be empty for other legs.

The units used in the underlying price input must match the units in strike. This is very simple for stock options, where strike is typically expressed as stock price per share, but may be less clear for some currency options (both the direction and multiplier must match between strike and underlying price, i.e. don't use the EUR/USD rate as underlying price and USD/EUR rate as strike).

Expiration Date and Time (Columns F, Z)

Expiration date in column F is required for options (all legs with type C/P) and must be empty for other legs.

Expiration time is set in the global input in cell AA17, which you can override for individual legs in column Z (the reason why expiration date and time are in such different places is to save screen space – it is assumed that the expiration time override will not be needed very often).

Actual expiration time used for the leg is shown in column AA. Expiration date and time is the sum of columns F and AA, and it is shown in column AB. The next two columns show time to expiration in days (AC) and years (AD).

Number of days to expiration is also shown for your reference in column G (green cell – do not overwrite).

Entry Price (Column H)

Entry price in column H is the price paid (if long position) or received (if short position) when opening the leg. It must be in units of the underlying (typically per share, not per option contract).

It should be net of commissions if you want the resulting profit or loss also reflect them.

Technically, entry price is not required. If you keep it empty, cell H18 will display a warning, but the calculations will still work. It will be assumed that there was zero cash flow at leg entry (so profit or loss of the open position will equal position value).

Exit Price (Column I)

Similarly to entry price, exit price is the price received when closing a long leg or paid when closing a short leg (per unit of underlying, net of commissions).

If exit price is empty, the leg is considered open and its value is calculated by pricing the security held (option or underlying).

If exit price is not empty, the leg is considered closed. Current valuation of the options or underlying has no effect on a closed leg's value or P/L. The leg's value is calculated as the difference between exit price and entry price (multiplied by position size and contract multiplier).

To close a leg, type in an exit price. Do not change the leg's position size to zero. The original position size is needed to calculate the closed leg's cash flow (profit or loss).

The manager can't model a partially open and partially closed leg. If you exit a leg partially – e.g. sell 3 contracts out of 5 long – you need to split it into two legs: The closed portion will have position size = 3 contracts and non-empty exit price. The part that is still open will have size = 2 contracts and empty exit price.

If the leg is an expired option (expiration date and time is at or after valuation date and time) and exit price is empty, automatic exercise or assignment is assumed if the option is in the money (determined by the relationship between underlying price and the leg's strike). Expired out of the money options assume zero exit price. That said, it is always best to explicitly enter exit price for expired legs.

Leg Volatility Override (Column K)

In the yellow column K you can override the global volatility input for an individual leg. If empty, global volatility from cell L19 is used. You can see the volatility actually used for the leg in the green column L.

Leg Multiplier Override (Column X)

In the yellow column X you can override the global underlying or option contract multiplier for an individual leg (for instance, when your portfolio includes both standard size and "mini" options on the same underlying).

Leg Expiration Time Override (Column Z)

In the yellow column Z you can override the global option expiration time for an individual leg (for instance, when your portfolio includes both AM and PM settled options on the same underlying).

Leg Errors and Warnings

To avoid confusion, the manager is very strict with leg specifications. When a leg is entered incompletely (e.g. option without a strike or expiration date), it raises an error instead of just ignoring that leg. Any errors are shown in cell H18, with reference to the particular leg and cell.

When the message in cell H18 begins with "Warning", it is a softer kind of error, where the manager assumes a default value for a missing input (e.g. zero for entry price) and the calculations work correctly.

Support & Feedback

If you have any questions or feedback, or encounter any problems or unexpected behavior, please feel free to contact me.