Futures Price Calculator User Guide

This is the user manual for the Futures Price Calculator.

Enable Calculations and Macros

If you have just downloaded the calculator 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 four sheets:

• Main: Here you enter inputs (such as spot price, time to expiration, or interest rates), view results, and control and view the chart.
• ChartData: Here you can inspect the values at every point in the chart, which is drawn using data from this sheet.
• Lists: Items for dropdown boxes, various constants and background calculations (generally nothing of interest to users).
• Preferences: Here you can change settings, such as rounding.

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

The other sheets (ChartData, Lists) are for viewing only. Do not change anything in these sheets, unless you want to change the way the calculator 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.

The sheets ChartData and Lists also contain cells with other colors. Generally blue (like green) are formulas and red 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:

• Calculation inputs such as underlying type, time to expiration, or interest rates are entered on the left (columns B-C).
• Calculation direction (spot to futures price or futures to spot price) is selected in the dropdown box in the top left corner (cell C3).
• To the right of this dropdown box (yellow cell F3) there is the input price: spot or futures price, based on the dropdown selection.
• Further to the right (green cell J3) there is the output price: futures price if input is spot price, or vice-versa.
• Below these prices there are chart controls (cells E5-J8), which select what is displayed in the chart.
• Below the chart controls there is the chart itself.
• Chart scale can be changed using the buttons on the right (column M).

Calculation Direction, Input and Output Price

The calculator works both ways: It can calculate futures price from spot price or vice-versa. Calculation direction is selected in the top left dropdown box in cell C3.

• When "Spot to Futures" is selected, the calculator calculates futures price (output, green cell J3) from spot price (input, yellow cell F3).
• When "Futures to Spot" is selected, the calculator calculates spot price (output, green cell J3) from futures price (input, yellow cell F3).

Other inputs are the same for both directions, but some may slightly differ depending on underlying security type.

Underlying Type

The calculator can price futures on stocks, indices, currencies, or commodities.

Underlying type is selected in the dropdown box in cell C5.

Time to Expiration

For maximum flexibility, the calculator can accept time to expiration in three different formats. This is selected in the dropdown box in cell C7. It works the same for all underlying types.

The options are:

• Start and End to enter valuation date (usually today's date) and expiration date (cells C8-C11)
• Number of Days to enter the number of days remaining to expiration (cell C12)
• Percent of Year to enter the percent of year remaining to expiration (cell C13)

Depending on your selection, the cells right below the dropdown box (C8-C11) will turn yellow, green or grey. Make sure to always overwrite yellow cells only. Green cells contain formulas and their values are calculated from what you have entered in the yellow cells. Grey cells are inactive and not currently used in the calculations.

Entering Time to Expiration as Start and End

If you select "Start and End", you need to enter the valuation date in the yellow cell C8 and expiration date in cell C10. For greater precision, especially with short term futures, you may also enter valuation and expiration time in cells C9 and C11, respectively. If you leave the time cells blank, effective time will be midnight (0:00). With Start and End format selected, number of days to expiration (cell C12, which is now green) is calculated as expiration date and time minus valuation date and time. Percent of year to expiration (green cell C13) is calculated as number of days divided by 365.

Entering Time to Expiration as Number of Days

If you select "Number of Days" in the dropdown box in C7, enter the number of days to expiration in the yellow cell C12. You can enter fractions of days, e.g. 12.25 for 12 days and 6 hours. The cells C8-C11 above (valuation and expiration date and time) will be grey, as these inputs are not needed now. Percent of year to expiration (cell C13) will be green and calculated from number of days entered in cell C12.

Entering Time to Expiration as Percent of Year

If you select "Percent of Year", enter the time to expiration as percent of year (number of days divided by 365) in the yellow cell C13. Cells C8-C11 are grey. Number of days in cell C12 (now green) is calculated from the entered percent of year in C13.

Interest Rates

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

Foreign Interest Rate for Currency Futures

When pricing currency futures, with Forex selected as underlying type, the interest rate input label (cell B15) will show "Domestic Rate". It should be the interest rate of the currency in which the futures contract is traded.

For example, when pricing futures on EUR traded in USD, enter the USD risk-free rate in this cell.

The "foreign", EUR interest rate in this example, will be entered in cell C18, whose label is "Foreign Rate" when Forex is selected as underlying type.

Dividends

Dividends only apply to stock and index futures – when "Stock" or "Index" is selected in the underlying type dropdown box in cell C5.

The calculator can work with dividends entered either as continuously compounded dividend yield or (for single stock futures) as a discrete dividend amount paid at a specific time. Choose the dividend format in the dropdown box in cell C17.

If you select Continuous Yield, enter the yield in the yellow cell C18. No other inputs are needed.

If you select Discrete Amount, enter the dividend amount in the yellow cell C19. In the cells below, you also need to specify when this dividend will be paid.

Like time to expiration, you can enter time remaining until the stock goes ex dividend in three different ways: as date (and optionally time), as number of days, or as percent of year (between valuation date and ex dividend date). Select the format in the dropdown box in cell C20. Depending on this selection, cells C21-C24 below will turn yellow, green or grey, following similar logic to the one described in the time to expiration section.

Note that discrete dividends will only affect the calculations when the underlying goes ex dividend before the futures contract expiration (time to ex dividend is smaller than time to expiration).

Chart Controls

The chart in the Main sheet can display the effect of individual inputs on futures price or spot price, depending on the selected calculation direction.

There are three dropdown boxes which control the chart:

• Chart Mode (cell F5) is either Single Line or Multiple Lines.
• Chart Lines (cell F6) is only visible when Multiple Lines is selected in Chart Mode. It selects the input that will vary between the chart lines. For example, selecting Interest Rate makes each line in the chart represent a different interest rate level. These levels are set in cells I6-I8.
• X-Axis (cell F7) selects which of the inputs will be on the X-axis.

X-Axis Scale

The buttons to the right of the chart (column M) allow quick adjustments of the X scale. You can shift the axis left (lower input levels) or right (higher), zoom in or zoom out, or reset to original scale, which is calculated automatically.

Support & Feedback

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