Computing Historical Volatility in Excel

The value of financial assets varies on a daily basis. Investors need an indicator to quantify these changes that are often difficult to predict. Supply and demand are the two principal factors that affect changes in asset prices. In return, price moves reflect an amplitude of fluctuations, which are the causes of proportional profits and losses. From an investor's perspective, the uncertainty surrounding such influences and fluctuations is called risk.

The price of an option depends on its underlying ability to move, or in other words its ability to be volatile. The more likely it is to move, the more expensive its premium will be closer to expiration. Thus, computing the volatility of an underlying asset helps investors to price derivatives based on that asset.

One way to measure an asset's variation is to quantify the daily returns (percent move on a daily basis) of the asset. This brings us to the definition and concept of historical volatility. Historical volatility is based on historical prices and represents the degree of variability in the returns of an asset. This number is without a unit and is expressed as a percentage. (For more, see: "What Volatility Really Means.")

If we call P (t) the price of a financial asset (foreign exchange asset, stocks, forex pair, etc.) at time t and P (t-1) the price of the financial asset at t-1, we define the daily return r (t) of the asset at time t by:

r (t) = ln (P (t) / P (t-1)) with Ln (x) = natural logarithm function.

The total return R at time t is:  

R = r1 + r2 + r3 + 2 + ... +rt-1+ rt, which is equivalent to:

R = Ln (P1 / P0) + ... Ln (Pt-1 / Pt-2) + Ln (Pt / Pt-1) 

We have the following equality:
Ln (a) + Ln (b) = Ln (a*b)

So, this gives:

R = Ln [(P1 / P0* (P2 / P1)* ... (Pt / Pt-1]

R = Ln [(P1. P2 ... Pt-1. Pt) / (P0. P1. P2 ... Pt-2. Pt-1)]

And, after simplification, we have R = Ln (Pt / P0).

The yield is usually computed as the difference in relative price changes. This means that if an asset has a price of P (t) at time t and P (t + h) at time t + h> t, the return (r) is:

r = (P (t + t) -P (t)) / P (t) = [P (t + h) / P (t)] – 1

When the return is small, such as just a few percent, we have:

r ≈ Ln (1 + r)

We can substitute r with the logarithm of the current price since:

r ≈ Ln (1 + r)

r ≈ Ln (1 + ([P (t + h) / P (t)] - 1))

r ≈ Ln (P (t + h) / P (t))

From a series of closing prices for instance, it is enough to take the logarithm of the ratio of two consecutive prices to compute daily returns r (t).

Thus, one can also compute the total return R by using only the initial and final prices.

To fully appreciate the different volatilities over a period of a year, we multiply this volatility by a factor that accounts for the variability of the assets for one year.  

To do this we use the variance. The variance is the square of the deviation from the average daily returns for one day.

To compute the square number of the deviations from the average daily returns for 365 days, we multiply the variance by the number of days (365). The annualized standard deviation is found by taking the square root of the result:

Variance = σ²daily = [Σ (r (t)) ² / (n - 1)]

For the annualized variance, if we assume that the year is 365 days, and every day has the same daily variance, σ²daily, we obtain:

Annualized Variance = 365. σ²daily
Annualized Variance = 365. [Σ (r (t)) ² / (n - 1)]

Finally, as the volatility is defined as the square root of variance:

Volatility = √ (variance annualized)

Volatility = √ (365. Σ²daily)

Volatility = √ (365 [Σ (r (t)) ² / (n - 1)].)

The Data

We simulate from the Excel function =RANDBETWEEN a stock price that varies daily between 94 and 104.

Computing the Daily Returns

In column E, we enter "Ln (P (t) / P (t-1))."

Computing the Square of Daily Returns

In column G, we enter "(Ln (P (t) / P (t-1)) ^2.”

Computing the Daily Variance

To compute the variance, we take the sum of the squares obtained and divide by the (number of days -1). So:

- In cell F25, we have "= sum (F6: F19)."

- In cell F26, we compute "= F25 / 18" since we have 19 -1 data points for this calculation.

Computing the Daily Standard Deviation

To compute the standard deviation on a daily basis, we compute the square root of the daily variance. So:

- In cell F28, we compute "= Square.Root(F26)."

- In cell G29, cell F28 is shown as a percentage.

Computing the Annualized Variance

To compute the annualized variance from the daily variance, we assume that each day has the same variance, and we multiply the daily variance by 365 with weekends included. So:

- In cell F30, we have "= F26* 365." 

Computing the Annualized Standard Deviation

To compute the annualized standard deviation, we only need to compute the square root of the annualized variance. So:

- In cell F32, we have "= ROOT (F30)."

- In cell G33, cell F32 is shown as a percentage.

This square root of the annualized variance gives us the historical volatility.