Question

The following table lists the month-end prices from May 2017 to July 2018 of the Spider S&P 500 Index ETF (SPY) and iShares 20+ Treasury Bond ETF (TLT), respectively. During this time period the average annual yield for the one-month Treasury bills is 1.292% or 0.1077% per month. Apply the following equation to calculate the monthly returns from June 2017 to July 2018 for SPY and TLT, respectively. The monthly return for month n: rn = (Current month-end price – Previous month-end price) / Previous month-end price = (Pn – Pn-1) / Pn-1 Monthly Returns Data Date Adj Close Price Pn: SPY Adj Close Price Pn: TLT 5/31/2017 235.858 120.4887 6/30/2017 237.3616 121.4428 7/31/2017 242.2404 120.6433 8/31/2017 242.9471 124.7557 9/29/2017 247.8424 121.8577 10/31/2017 253.6826 121.8107 11/30/2017 261.4366 122.716 12/29/2017 264.6073 124.9404 1/31/2018 279.5203 120.8728 2/28/2018 269.3568 117.1966 3/29/2018 261.9736 120.548 4/30/2018 263.3276 118.0308 5/31/2018 269.7288 120.3966 6/29/2018 271.28 121.1741 7/31/2018 281.33 119.433

Using the monthly returns, find the values of the following inferences about SPY and TLT, respectively. AAR GAR HPR Standard deviation Sharpe ratio VaR (1%) and Var (5%)

Answer 1

The following table lists the month-end prices from May 2017 to July 2018 of the Spider S&P 500 Index ETF (SPY) and iShares 20+ Treasury Bond ETF (TLT), respectively. During this time period the average annual yield for the one-month Treasury bills is 1.292% or 0.1077% per month. Apply the following equation to calculate the monthly returns from June 2017 to July 2018 for SPY and TLT, respectively. The monthly return for month n: rn = (Current month-end price – Previous month-end price) / Previous month-end price = (Pn – Pn-1) / Pn-1 Monthly Returns Data Date Adj Close Price Pn: SPY Adj Close Price Pn: TLT 5/31/2017 235.858 120.4887 6/30/2017 237.3616 121.4428 7/31/2017 242.2404 120.6433 8/31/2017 242.9471 124.7557 9/29/2017 247.8424 121.8577 10/31/2017 253.6826 121.8107 11/30/2017 261.4366 122.716 12/29/2017 264.6073 124.9404 1/31/2018 279.5203 120.8728 2/28/2018 269.3568 117.1966 3/29/2018 261.9736 120.548 4/30/2018 263.3276 118.0308 5/31/2018 269.7288 120.3966 6/29/2018 271.28 121.1741 7/31/2018 281.33 119.433

Using the monthly returns, find the values of the following inferences about SPY and TLT, respectively. AAR GAR HPR Standard deviation Sharpe ratio VaR (1%) and Var (5%)

1. SPY Monthly Return =  (Current month-end price – Previous month-end price) / Previous month-end price = (Pn – Pn-1) / Pn-1 ( Data Taken from Adj Close Price)
2. AAR :   Average of Monthly Return : Used EXCEL Average Function
3. GAR :
   • First we calculate ( 1 + Monthly Return ) for Both SPY and TLT ( 1+r) Value
   • Then Multipplied Each Values i.e : ( 1+r1) * ( 1+r2) * ( 1+r3) ...... (1+r14) [ To do this conveniently we have used Excel Product Function ]
   • Then Used : GAR : [( 1+r1) * ( 1+r2) * ( 1+r3) ...... (1+r14) ] ^ (1/N) - 1 : HPR Formula Here N = 14
4. HPR : Holding Period Retutn : ( Price at Po - Price at Pn ) /  Price at Po
5. St Dev = Using Std Dev Excel Function on Each Period Return  
6. Sharpe = ( Rp - Rf ) / St Dev
   • For Rf = We have taken AAR of T Bill Monthly Return
   • For Rp =  We have taken AAR of SPY / TLT Return
7. Var (1%) = - 2.33 * Std. Dev
8. Var (5%) = - 1.66 * Std. Dev

Answer :

	SPY	TLT
AAR	1.225%	-0.085%
GAR	1.199%	-0.106%
HPR	19.279%	-0.876%
St Dev	2.363%	2.167%
Sharpe Ratio	47.301%	-3.907%
Var (1%)	-5.51%	-5.05%
Var (5%)	-3.90%	-3.58%

Excel Calculation :