Calculation Examples of Real-Time Index

RealVol Index

The following is a step-by-step description of the real-time realized volatility calculation methodology named RealVol real-time index. Note: The following refer to Exhibits 3 and 4.

Exhibit 3 is an example of steps 1–6 of a total of 14 (Exhibit 4 displays steps 7–14) required to calculate the real-time RealVol index. In this example, we assume that “today” is 2 March 2012, that we have 1RVOL index data every day to 1 March 2012 (as was already described in exhibit 2), that the current time is 9:35 AM on 2 March, and that the current underlying price is 1371.26.

The following is a step-by-step description of the RealVol real-time index calculation methodology. Note: The rows of the spreadsheet do not correspond to the counter “n” in the formula. In the below examples, the first day (n=1) can be found in row 22. For n=2, this corresponds to row 23. Similarly, n=3 is in row 24, etc., until we get to n=22 in row 43. For clarification, as we move through time, 2 March 2012 closes, and we move to trading on 3 March 2012, the “n” values get renumbered such that the first day (n=1) can now be found in row 23, with n=22 in row 44, etc.

In cell L43, we place the current URP; in this case it is 1371.26.
In cell M43, we place the date and time of the close from the previous day. In this case, we assume 1 March 2012 at 4:00 PM.
In cell N43, we place the current date and time; in this case it is assumed to be 9:35 AM on 2 March 2012.
In column O, we calculate the days’ weights. First, today’s weight is calculated according to the specific portion of a day. In this case, 9:35 AM is 73.26% of the way through the trading day (based on the closing time yesterday of 4:00 PM). Internally, Excel calculates this value with the formula “=N43-M43”. After we determine the weight of day 22 (in this case, 73.26%), we subtract that value from 100%, and the result becomes the weight accorded to day 1 (found in cell O22). The formula in cell O22 is “=100%-O43”. The value in this case is 26.74%. Each of the rest of the days 2–21 has a 100% weight. Thus, we have 22 data points but with the cumulative weight of 21 data points (weight of day 1 and weight of day 22 total 100% of a single day’s weight together, not separately).
In column P, we calculate the close “today” divided by the close “yesterday.” For n=1 through n=21, this was already calculated in column C (see Exhibit 2). The only value not yet calculated is “today’s value” divided by “yesterday’s” close. Note: “today’s” value is not yet the close. We use the current URP to provide a real-time indication of the 1RVOL index. In cell P43 is the formula “=L43/B42”.
In column Q is the continuously compounded return. Since this was already calculated for days 1–21 in column D, the only calculation remaining is for “today’s” value. Cell Q43 contains the formula “=LN(P43)”.
In column R are the squared returns. Since this was already calculated for days 1–21 in column E, the only calculation remaining is for “today’s” value. Cell R43 contains the formula “=Q43^2”.
In column S are the weighted squared returns. Take the weight of each cell in column O and multiply it by its corresponding squared returns. Cell S22 contains the formula “=E22*O22”. This formula is copied down the column except for the very last row (“today”). This formula is slightly different from the one above because we are calculating the squared return using the current URP instead of the close. Therefore, in cell S43, we use the formula “=R43*O43”.
In column T is the sum of the weighted squared returns. The formula in cell T43 is “=sum(S22:S43)”.
In column U is the average of the 21 days’ returns. To get the average, divide the value in cell T43 by 21. The formula in cell U43 is “=T43/21”.
In column V is the annualization factor. To annualize the value, just multiply the value in cell U43 by 252. The formula in cell V43 is “=U43*252”.
In column W is the square root. The formula in cell W43 is “=SQRT(V43)”.
In column X is the removal of the percentage sign by multiplying the result by 100. The formula in cell X43 is “=W43*100”.
In column Y is the RealVol index. The formula in Y43 is “=X43”.