Price Probability Calculator User Guide

This is the user manual for the Price Probability Calculator.

Use the Right Version

From the download page you can download four different files, for different versions of Excel or other spreadsheet software:

• If you have Excel 2007 or newer, also including Excel for Mac, use the default version, PPC.xlsm.
• If you have Excel 2003 or older, use PPC_Excel_97-2003.xls.
• For OpenOffice, LibreOffice, Google Sheets, and similar apps which don't support Excel VBA and macros, use the open document format file PPC_ODF.ods.
• If you have an Excel app on iPad or iPhone, use PPC_NoVBA.xlsx (the ODF version should also work, but this one is better for iPad and iPhone apps).

The first two (VBA powered) versions work the same; they only differ in small visual details (e.g. colors and chart line design).

The other two versions (ODF, NoVBA) lack dropdown boxes and buttons, as these unfortunately require VBA. This makes some tasks slightly less convenient (e.g. you type in an input instead of selecting it in a dropdown box), but all calculations work the same.

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 start price, period length, or interest rate), 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.
• BgCalc: Background calculations, various constants, and items for dropdown boxes (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, BgCalc) 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 BgCalc 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:

• Inputs such as instrument type, start price, period length, or dividends are entered on the left (the yellow cells in column C).
• End prices are entered in the yellow cells F5, G5.
• The resulting probabilities (of price being below, above, or between the end prices at period end) are shown in the green cells I5-K5.
• Below these end prices and probabilities there are chart controls (cells F7-K10), which set what is displayed in the chart.
• Below the chart controls there is the chart itself.
• Chart scale can be adjusted in the bottom right corner in the yellow cells K26, K27.

Entering Inputs

All inputs (except end prices) are entered in the yellow cells in column C.

Only some inputs are required (start price, volatility) – if these input cells are blank, the calculator will show an error.

Other inputs are optional. If they are not entered, default values will be used and no errors will be shown.

All inputs are validated. The green cells in column D, to the right of each yellow input cell, show:

• The same value as the one entered in the yellow cell in column C if it is OK.
• Default or automatically calculated value if the yellow cell in column C is blank and the input is not required.
• Error message if a required input is missing or an invalid value has been entered.

Besides column D, error messages are also displayed below the chart in cells F26-I27. If nothing is shown there, all inputs are OK.

Basic specifications for each input follow.

Instrument Type

Available instrument types are:

• Stock (also for ETFs, ETNs)
• Index
• Currency
• Futures

It is very important to select the right instrument type, as this selection affects calculations (for example, the probabilities can be slightly different for futures vs. stocks due to their different cash flows and the way time value of money works).

Instrument type is selected in the dropdown box in cell C3.

In the versions without VBA, it is typed in manually in the yellow cell C3. It is relatively loose – for example, "I", "ind", "index", "Index" will all set instrument type to Index (see hints at the bottom of Main sheet in cells B33-D46, non-VBA versions only). If nothing is entered, instrument type is Stock by default.

Start Price

Start price is entered in the yellow cell C4.

This is the stock price (if instrument type is Stock), index value (Index), exchange rate (Currency), or futures price (Futures – make sure to enter the futures price, not spot price) at the beginning of the period, typically now.

This input is required. If missing, the calculator will show an error.

Volatility

Volatility is entered in the yellow cell C5. This input is required.

It is volatility as typically understood in finance – standard deviation of returns, annualized.

Note that volatility, as well as interest rate and dividend yield, must be entered in correct units. For example, if volatility is 20%, actual cell C5 value must be 0.2 (20%, which may show as "20%" or "0.2"), not 20 (which would be 2000%).

Period Length

For maximum flexibility, period length can be set in various ways:

• As start and end date (and optionally time) in the yellow cells C8-C11.
• As number of days in cell C12.
• As number of years (formatted as percent of year) in cell C13.

If more of the above are entered, number of days overrides the dates, and number of years overrides everything.

If none of them are entered, default period length is used. This can be changed in the DefaultPeriodLength setting (cell C9 in the Preferences sheet).

Interest Rate

Interest rate is entered in the yellow cell C15.

It is the risk-free interest rate for the period, annualized.

If instrument type is Currency, two interest rates enter the calculations. The "domestic" interest rate (e.g. USD interest rate for the EUR/USD pair) is entered in cell C15. The "foreign" interest rate (e.g. EUR rate for EUR/USD) is entered in cell C17 in place of dividend yield (mathematically, foreign interest rate for currencies affects the probabilities in the same way as a continuous dividend yield).

Dividends

If instrument type is Stock, dividends can be entered in two different ways:

• As continuous dividend yield in the yellow cell C17.
• As a discrete dividend in cells C20-C24.

If dividend amount (cell C20) is greater than zero, discrete dividend overrides the continuous dividend yield input (cell C17) – you will see "Not Used" in the green cell D17 for continuous dividend yield. A status message informing whether continuous or discrete dividend is used is also available in cells C19-D19.

Discrete dividend only has effect on the probabilities if the ex-dividend date falls within the period (see status message in cells B25-D25). Time to ex dividend can be entered in three different ways, following similar logic as period length:

• Ex dividend date (and optionally time) in cells C21, C22.
• Number of days from start date to ex dividend date in cell C23.
• Number of years (formatted as percentage) from start date to ex dividend in cell C24.

Like in period length, number of days overrides ex dividend date, and number of years overrides everything. If nothing is set (cells C21-C24 are all empty), the stock is assumed to go ex dividend exactly at period end (time to ex dividend equals period length).

End Prices

The calculator can work with one or two end prices, entered in the yellow cells F5, G5.

If two different end prices are set, the calculator will show the probability of price being:

• Below the lower end price (cell I5).
• Between the two end prices (cell J5).
• Above the higher end price (K5).

If only one end price is set (or if both end prices in cells F5, G5 are the same), probability between end prices is obviously zero.

If no end price is set (cells F5, G5 are both empty), the calculator will show probabilities below and above the start price (in other words, end price is set equal to start price).

Model Assumptions

The calculator makes the same assumptions as classical financial models, such as Black-Scholes:

• Price makes random walk.
• Returns are normally distributed. Prices are lognormally distributed.
• Volatility and interest rates are constant throughout the period and known in advance.
• Market is frictionless (perfect liquidity, zero transaction costs, no restrictions to short selling, ability to trade infinitely small amounts).
• No arbitrage is possible.

As with other financial models, some of the assumptions are clearly not valid in the real world.

Users must be aware that many assets' return distributions have historically deviated from normal distribution, often showing considerable positive or negative skewness and positive kurtosis. As a result, while the calculated probabilities tend to be fairly accurate for moderate price changes, in some cases the model may underestimate probabilities (i.e. risk) of extreme moves. In other words, even when the calculator shows a probability of 0.00% or 100.00%, do not assume such a move is impossible.

Probability Calculation

The following explains the logic of the calculation of probability that price (actual end price) will be below a particular level (reference end price = the end price input) at period end. Some of these intermediate calculations are available in cells D26-D30 in the Main sheet, while others are done in the BgCalc sheet.

These are the steps:

If there are discrete dividends within the period, adjust start price for dividends by subtracting present value of dividend (cell D26) from start price (cell C4). The result (start price excluding dividend) is in cell D27. It equals start price if there are no discrete dividends.

Calculate mean end price (price at period end if there was no volatility, cell D29), based on interest rate, continuous dividend yield (if applicable), and period length. How exactly interest rates affect this calculation depends on instrument type. Mean end price equals start price if interest rate is zero and there are no dividends.

Calculate period volatility (period standard deviation, cell D30) from the annualized volatility input and period length, based on the square root of time principle.

Calculate logarithmic return as natural log of the ratio of reference end price and mean end price.

Calculate how many standard deviations from the mean it is, based on period volatility from cell D30.

Find probability of an observation being below this many standard deviations from the mean, based on normal distribution (the cumulative distribution function, or NORM.DIST with Cumulative=TRUE in Excel). This is the resulting probability.

Chart Controls

Besides calculating probabilities of price being above or below a certain level at period end, the calculator can also display a chart which shows how the probabilities will change as different inputs change.

Generally, the chart can model the effect of a single input or the combined effects of two different inputs, using any of the inputs set in the yellow cells.

Chart controls are in cells F7-K10. There are four dropdown boxes:

• Chart Mode (cell G7): Single Line or Multiple Lines.
• Chart Lines (cell G8, only active in Multiple Lines mode): This selects which input will be different for different chart lines. For example, if Chart Lines is set to Start Price, each line will use different start price input. This line-specific input is set in the yellow cells J8-J10 (and K8-K10 if applicable).
• X-Axis (cell G9): Which of the inputs will be on the X-axis (must be different from the Chart Lines selection).
• Y-Axis (cell G10): Show probability either above or below a certain end price.

Chart Controls in Non-VBA Versions

Dropdown boxes are not available in software which does not support VBA. Instead, chart controls are set by typing in values.

• Chart Mode is not used (it is set via Chart Lines).
• Chart Lines (yellow cell G8) can by an integer from 0 to 13. 0 means Single Line chart mode. 1-13 are different input numbers, listed below on this page or directly in the calculator in cells F33-H46 (in non-VBA versions only).
• X-Axis (G9) can be an integer from 1 to 13, with same input numbers as in Chart Lines.
• Y-Axis (G10): Either 1 for probability above end price or 2 for probability below.

Input numbers for Chart Lines and X-Axis:

1. Start Price
2. End Price
3. Start Date
4. End Date
5. Number of Days
6. % of Year
7. Volatility
8. Interest Rate
9. Dividend Yield
10. Dividend Amount
11. Ex Dividend Date
12. Days to Ex Dividend
13. % of Year to Ex Dividend

Chart Scale Adjustment

The X-axis range is calculated automatically based on the inputs selected, with the objective of covering all interesting points. It is possible to adjust the range (e.g. zoom in or zoom out) by overriding the automatic X-axis minimum and maximum in the yellow cells K26, K27 below the chart.

Support & Feedback

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