Return and variance of a series

On occasion I get asked for the return or variance of a time series. It is not always possible to download these from a database but both of them can be calculated using Excel using basic functions.

1) Return
There are many version and definitions of the return: “In finance, return is a profit on an investment. It comprises any change in value, and interest or dividends or other such cash flows which the investor receives from the investment.” Many other definitions and meanings exist and you can find an overview of some of these on Wikipedia. If I simplify it further the return means the percental change in value of a time series. As an example I have calculated the return for the price of Crude Oil WTI but the same (simplified) principle also applies to stock price series. The formula that I use is:

Return = ((Price on Day2 – Price on Day1) / Price on Day1) * 100

Below you see an example screenshot of this:


2) Variance
The variance is a mathematical concept in probability theory and statistics. The variance measures how far a set of numbers is spread out. A variance of zero indicates that all the values are identical. A small variance indicates that the data tend to be very close to the mean (expected value) and hence to each other. A high variance indicates that the data are very spread out around the mean and from each other. More on variance can be found on Wikipedia as well as in many good Mathematics books.

The formula that I have used, is the one for measuring an unbiased estimate of the population variance from a fixed sample of n observations:

(s2) = S [(xi – x¯)^2] / n – 1

s2 = Variance
S = Summation, which means the sum of every term in the equation after the summation sign.
xi = Sample observation. This represents every term in the set.
= The mean. This represents the average of all the numbers in the set.
n = The sample size. You can think of this as the number of terms in the set.

Below you find an example that I created using the daily Crude Oil WTI prices from 1 January 2000 to 1 May 2014. I used a similar step by step approach as shown in WikiHow:

The Excel formulas that I used were:
1) To get the sum of all the prices: =SUM(B6:B178). The reult is in cell E2.

2) To get the average I used: =AVERAGE(B6:B178). The reult is in cell D2.

3) Price (in B6 to B178) minus the average becomes: price – x¯ etc. For the values in column B it would be: B6 – $D$2. See the result in cells C6 – C178.

4) The square of the price minus the mean would be in column D6 – D178. Example: =(C6)^2

5) The sum of the squared prices of column D is in cell F2: =SUM(D6:D178)

6) The number of prices n is in cell G2: =COUNT(D6:D178)

7) The variance can now be calculated and is put in cell F6: =F2/($G$2)-1

8) The standard deviation is the square root of the variance. I put this in cell G6: =SQRT(F6)

You can also download the example Excel-sheet here.


%d bloggers like this: