Calculating Sample Autocorrelations in Excel ˆ A sample autocorrelation is defined as ρ k ≡ ˆ γˆ k cov( Rit , Ri ,t − k ) = . ˆ γˆ 0 var( Rit )

In Excel, the tricky part in calculating sample autocorrelations is calculating the sample covariance term. Suppose you have data as follows: A R_it 0.03 0.02 0.05 -0.01 0.01 0.03 0.05 0.04 -0.02 B R_jt 0.02 0.05 -0.01 -0.01 0.02 0.5 -0.2 0.2 0.1

1 2 3 4 5 6 7 8 9 10

Calculating the variance for series R_it is straightforward: =var(A2:A10)

Calculating the k-th lag covariance for series R_it is similar. Consider the k=1 case. =covar(A3:A10,A2:A9) Now, consider the k=2 case =covar(A4:A10,A2:A8) The pattern should be clear. γˆ ij ,k γˆ i ,0γˆ j , 0 ˆ cov( Rit , R j ,t − k ) ˆ ˆ var( Rit ) ⋅ var( R jt )

ˆ A sample cross correlation is defined as ρ ij ,k ≡

=

.

Calculating the sample variances is straightforward. Calculating the sample covariances is done as follows.

For k=1, =covar(A3:A10,B2:B9) For k=2, =covar(A4:A10,B2:B8) The pattern should again be clear. ˆ ˆ Note, however, that ρ ij ,k ≠ ρ ji ,k . For γˆ ji, k , the sample covariances are calculated as follows. For k=1, =covar(B3:B10,A2:A9) For k=2, =covar(B4:B10,A2:A8) Useful Q+A: Q. I'm confused on question #1. I understand that I need to do a chow test on the excess return model but do I set up two separate regressions (one before and one after the break point) or do I use delta? Also if I use delta, how do I get excel to compute a regression with two variables? Additionally, which data do I use on excel to calculate the RSS (sum of residuals squared?)? A. You can either set up two separate regressions or use a dummy variable. The Fstatistic will be exactly the same in both cases. If you use the dummy variable approach, you will have three right hand side variables (r_mt-rf, D_t, and D_t*(r_mt-rf)). To compute a regression with multiple right