Caluculation of Formulas

Hello ,

If any one can explain about the below calculation, Please let me know.


=SQRT(1/B$5*SUM(OFFSET(Sheet1!$E$2,MATCH(Sheet1!$N3,Sheet1!$A$2:$A$504,1)-1,0,-B$5)))*SQRT(252)

Please find the attachment regarding this.

Thanks,
Naidu.

Hello ,

Thank you*so much for helping me out today.
I spent lots of time to understand but could not get the logic behind this, now finally learnt the concept.
Very easy to understand the way you explained and I will ask you if I have any doubts further.

Thanks & Regards,
Naidu.

Originally Posted by joeu2004

First, a style comment: 1/B$5*SUM(...) is somewhat ambiguous as to the author's intent. IMHO, it is better to write:

Please Login or Register  to view this content.

which is also slightly more efficient and potentially slightly more accurate numerically.

-----

It would help if you indicated exactly what it is about the formula that you do not understand.

For example, is it the use of MATCH and OFFSET? Is it the multplication of SQRT(252)?

The formula calculates the annualized standard deviation of daily (natural) log returns, which are the values in column Sheet1!E. It is a measure of volatility.

The MATCH expression returns the row index (relative to row 2) of the closest date in column Sheet1!A before or equal to the date in Sheet1!N3.

The notation $N3 means that we use N3, N4, N5 etc as we move down each column of formulas.

In contrast, the notation $A$2:$A$504 means that we always use A2:A504 in each formula.

The OFFSET expression returns the cell range of values (log returns) from column Sheet1!E corresponding to the last B5 (number of) dates ending with the relative row index returned by MATCH.

The notation B$5 means that we use B5, C5, D5 etc as we move across each row of formulas.

In contrast, the notation $E$2 means that we always use E2 in each formula.

The expression SUM(...)/B$5 calculates the (population or exact) variance of the daily log returns referenced by OFFSET.

The expression SQRT(SUM(...)/B$5) calculates the standard deviation of the daily log returns.

The daily standard deviation is annualized by multiplying by SQRT(252) according to the "square root of time" rule, assuming 252 trade days per year (on average).

-----

The (population or exact) variance of a set of data is Sigma((x[i]-xhat)^2) / n, where x[i] is each of n data points, and xhat is the mean of the data. (The operation ^2 is the square of the calculation. Sigma is the sum of the calculations.)

Ostensibly, the values (x[i]-xhat)^2 are calculated in column Sheet1!E, based on the calculation of x[i]-xhat in column Sheet1!D.

However, the calculation in column Sheet1!D seems to be incorrect.

At a minimum, the reference to J4 in Sheet1!D3 should probably be written $J$4, so that J4 is referenced in each formula down the column.

Also, Sheet1!J4 should probably contain the formula =AVERAGE(C3:C510). [Errata]

Hello ,

Thank you*so much for helping me out today.
I spent lots of time to understand but could not get the logic behind this, now finally learnt the concept.
Very easy to understand the way you explained and I will ask you if I have any doubts further.

Thanks & Regards,
Naidu.