Question

Company Name	Google
Ticker Symbol	GOOGL
Market Cap
	Closing Price
42760	858.45
42761	856.98
42762	845.03
42765	823.83
42766	820.19
42767	815.24
42768	818.26
42769	820.13
42772	821.62
42773	829.23
42774	829.88
42775	830.06
42776	834.85
42779	838.96
42780	840.03
42781	837.32
42782	842.17
42783	846.55
42787	849.27
42788	851.36
42789	851
42790	847.81
42793	849.67
42794	844.93
42795	856.75
42796	849.85
42797	849.08
42800	847.27
42801	851.15
42802	853.64
42803	857.84
42804	861.4
42807	864.58
42808	865.91
42809	868.39
42810	870
42811	872.37
42814	867.91
42815	850.14
42816	849.8
42817	839.65
42818	835.14
42821	838.51
42822	840.63
42823	849.87
42824	849.48
42825	847.8
42828	856.75
42829	852.57
42830	848.91
42831	845.1
42832	842.1
42835	841.7
42836	839.88
42837	841.46
42838	840.18
42842	855.13
42843	853.99
42844	856.51
42845	860.08
42846	858.95
Dividend	0
Return	0.5
HPR	0.000582
STDEVA	0.0069721
Volatility	0.1106789
.1weight*hpr of each stock	0.0000582

Need to understand the excel formula to be used for calculating the above highlighted.(Answers are provided)

Answer 1

Dividend = 0.

There is no formula to be applied here as no divideds are announced.

Return = Final Price (21-Apr-17) - Initial Price (25-Jan-17) = 858.95 - 858.45 = 0.50

Holding Period Return (HPR) = (Final Price + Dividends - Initial Price) / Initial Price

= ( 858.95 + 0 - 858.45) / 858.45 = 0.000582

STDEVA: For calculating standard deviation you need to calculate returns first. Returns = (Final price / Initial price) - 1.

After intraday returns are calculated use STDEVA function and select all the returns as its values (input). It would give you 0.006972.

Volatility: Volatility is Annualized volatility which is calculated by converting daily standard deviation into an annualized figure. There are 252 trading days per year, on average.

Use the formula "=SQRT(252)*Standard deviaiton" to convert the standard deviation for this period to annualized historical volatility. This will give you 0.110679.

.1weight*hpr of each stock:

Fornula = 0.10 x HPR = 0.10 x 0.000582 = 0.0000582