Description
Data from January 2000 to the most recent observation are in the excel, you can use any preferred method to
convert monthly data to quarterly frequency:
Market index returns data on a value-weighted market portfolio including dividends rein- vested (variables
vwretd) and excluding dividends (variable: vwretx)1. This is based on the US Total Market Index produced by
the CRSP that comprises nearly 4,000 constituents across mega, large, small and micro capitalizations,
representing nearly 100% of the U.S. investable equity market.
Further variables descriptions are provided by the CRSP2.
1. Explain how to construct the dividend-yield from the variables provided.
2. Implement a routine in Excel or an alternative software to construct the dividend-yield from the series on
monthly and quarterly frequencies. Plot both constructed series over time.
3. What is the average value of dividend-yield during {2000-01 to 2009-12}, and during 2018, 2019 and 2020?
Summarize the average values in a table.
Date of Observation Value-Weighted Return-incl. dividends
1/31/2000
-0.03962196
2/29/2000
0.03176028
3/31/2000
0.05349902
4/28/2000
-0.05951937
5/31/2000
-0.03886035
6/30/2000
0.05157642
7/31/2000
-0.01767922
8/31/2000
0.07498387
9/29/2000
-0.0511544
10/31/2000
-0.02448012
11/30/2000
-0.1023654
12/29/2000
0.02044955
1/31/2001
0.03957528
2/28/2001
-0.09908441
3/30/2001
-0.07040813
4/30/2001
0.08383495
5/31/2001
0.01045007
6/29/2001
-0.01756139
7/31/2001
-0.0182836
8/31/2001
-0.05897984
9/28/2001
-0.09149703
10/31/2001
0.02784657
11/30/2001
0.07878883
12/31/2001
0.01785356
1/31/2002
-0.01596615
2/28/2002
-0.02169973
3/28/2002
0.04469838
4/30/2002
-0.04960013
5/31/2002
-0.01050955
6/28/2002
-0.07025918
7/31/2002
-0.08112465
8/30/2002
0.007948902
9/30/2002
-0.09992281
10/31/2002
0.07493966
11/29/2002
0.06128161
12/31/2002
-0.05333441
1/31/2003
-0.02336659
2/28/2003
-0.01541721
3/31/2003
0.01032112
4/30/2003
0.08276761
5/30/2003
0.06347126
6/30/2003
0.01634584
7/31/2003
8/29/2003
9/30/2003
10/31/2003
11/28/2003
12/31/2003
1/30/2004
2/27/2004
3/31/2004
4/30/2004
5/28/2004
6/30/2004
7/30/2004
8/31/2004
9/30/2004
10/29/2004
11/30/2004
12/31/2004
1/31/2005
2/28/2005
3/31/2005
4/29/2005
5/31/2005
6/30/2005
7/29/2005
8/31/2005
9/30/2005
10/31/2005
11/30/2005
12/30/2005
1/31/2006
2/28/2006
3/31/2006
4/28/2006
5/31/2006
6/30/2006
7/31/2006
8/31/2006
9/29/2006
10/31/2006
11/30/2006
12/29/2006
1/31/2007
0.02311219
0.02496536
-0.009120235
0.06036079
0.0165941
0.04547635
0.02304194
0.01544306
-0.01066161
-0.02422193
0.01406833
0.02161092
-0.03769773
0.0027029
0.02055616
0.01780559
0.04821379
0.03516963
-0.02654589
0.0226461
-0.01694364
-0.02520629
0.037954
0.01152899
0.04335747
-0.005958301
0.01059976
-0.02086558
0.04032636
0.003457624
0.04007189
-0.001638828
0.01906455
0.01300004
-0.03103197
-0.00039055
-0.001911548
0.02504146
0.01942518
0.03715301
0.02373648
0.01085692
0.0193868
2/28/2007
3/30/2007
4/30/2007
5/31/2007
6/29/2007
7/31/2007
8/31/2007
9/28/2007
10/31/2007
11/30/2007
12/31/2007
1/31/2008
2/29/2008
3/31/2008
4/30/2008
5/30/2008
6/30/2008
7/31/2008
8/29/2008
9/30/2008
10/31/2008
11/28/2008
12/31/2008
1/30/2009
2/27/2009
3/31/2009
4/30/2009
5/29/2009
6/30/2009
7/31/2009
8/31/2009
9/30/2009
10/30/2009
11/30/2009
12/31/2009
1/29/2010
2/26/2010
3/31/2010
4/30/2010
5/28/2010
6/30/2010
7/30/2010
8/31/2010
-0.014006
0.01295354
0.03983381
0.03895335
-0.014747
-0.03178957
0.01159279
0.04082203
0.02591044
-0.04936184
-0.004405199
-0.06221771
-0.02169688
-0.01044434
0.05119902
0.02393505
-0.07843585
-0.01341312
0.01059709
-0.09817162
-0.1848375
-0.08468544
0.02223056
-0.077475
-0.1001751
0.08681294
0.1094825
0.06779663
-0.003020451
0.08179244
0.03151435
0.04528819
-0.02807887
0.0570858
0.02848637
-0.03718551
0.0347535
0.06368819
0.02003908
-0.07926682
-0.05080204
0.07038396
-0.04288652
9/30/2010
10/29/2010
11/30/2010
12/31/2010
1/31/2011
2/28/2011
3/31/2011
4/29/2011
5/31/2011
6/30/2011
7/29/2011
8/31/2011
9/30/2011
10/31/2011
11/30/2011
12/30/2011
1/31/2012
2/29/2012
3/30/2012
4/30/2012
5/31/2012
6/29/2012
7/31/2012
8/31/2012
9/28/2012
10/31/2012
11/30/2012
12/31/2012
1/31/2013
2/28/2013
3/28/2013
4/30/2013
5/31/2013
6/28/2013
7/31/2013
8/30/2013
9/30/2013
10/31/2013
11/29/2013
12/31/2013
1/31/2014
2/28/2014
3/31/2014
0.09166859
0.03860637
0.005121486
0.06721339
0.01919018
0.03816834
0.003342544
0.02860191
-0.01495748
-0.01845294
-0.02255024
-0.05759661
-0.08502997
0.1142145
-0.006273366
0.003670611
0.05413952
0.04125253
0.02403937
-0.006840229
-0.06564105
0.03818082
0.01030569
0.02633639
0.02653238
-0.01406426
0.006219397
0.01257959
0.05418909
0.008284338
0.03531997
0.01494205
0.01915481
-0.01507128
0.05268972
-0.02576067
0.03751395
0.03990784
0.0249728
0.02615955
-0.03004685
0.04622539
0.004544167
4/30/2014
5/30/2014
6/30/2014
7/31/2014
8/29/2014
9/30/2014
10/31/2014
11/28/2014
12/31/2014
1/30/2015
2/27/2015
3/31/2015
4/30/2015
5/29/2015
6/30/2015
7/31/2015
8/31/2015
9/30/2015
10/30/2015
11/30/2015
12/31/2015
1/29/2016
2/29/2016
3/31/2016
4/29/2016
5/31/2016
6/30/2016
7/29/2016
8/31/2016
9/30/2016
10/31/2016
11/30/2016
12/30/2016
1/31/2017
2/28/2017
3/31/2017
4/28/2017
5/31/2017
6/30/2017
7/31/2017
8/31/2017
9/29/2017
10/31/2017
0.001569083
0.02026029
0.0279905
-0.02056651
0.040225
-0.02520873
0.02117386
0.02111406
-0.003662393
-0.02720654
0.05597443
-0.01040972
0.008776248
0.01029037
-0.01923786
0.01211042
-0.05998238
-0.03379287
0.07400566
0.002410283
-0.02222754
-0.05714742
0.000612666
0.07055376
0.01172495
0.01435388
0.002929358
0.0388489
0.002806437
0.003015416
-0.02156556
0.04054275
0.01877218
0.02217304
0.03262344
0.002064205
0.009656754
0.009334882
0.00957965
0.02029371
0.001593433
0.02375789
0.0193113
11/30/2017
12/29/2017
1/31/2018
2/28/2018
3/29/2018
4/30/2018
5/31/2018
6/29/2018
7/31/2018
8/31/2018
9/28/2018
10/31/2018
11/30/2018
12/31/2018
1/31/2019
2/28/2019
3/29/2019
4/30/2019
5/31/2019
6/28/2019
7/31/2019
8/30/2019
9/30/2019
10/31/2019
11/29/2019
12/31/2019
1/31/2020
2/28/2020
3/31/2020
4/30/2020
5/29/2020
6/30/2020
7/31/2020
8/31/2020
9/30/2020
10/30/2020
11/30/2020
12/31/2020
0.02725955
0.01212895
0.05063845
-0.03948133
-0.01844534
0.004691885
0.02616445
0.005365018
0.03160357
0.03023302
0.000424629
-0.07404541
0.01851215
-0.08989003
0.08829391
0.03272421
0.01296229
0.03789327
-0.06167856
0.06727886
0.01185431
-0.02033933
0.01603297
0.01925594
0.03499652
0.02849068
-0.001727776
-0.07791807
-0.1417326
0.1296766
0.05373875
0.02529908
0.05552897
0.06844219
-0.03505649
-0.0201785
0.1237074
0.0450485
Value-Weighted Return-excl. dividends
-0.04026281
0.03065738
0.05251539
-0.06030956
-0.04001972
0.05077882
-0.01839924
0.07380283
-0.05189474
-0.02515454
-0.1035736
0.01945508
0.03878729
-0.1001829
-0.07136414
0.08282977
0.00906384
-0.01833635
-0.01925034
-0.06035458
-0.09262974
0.02682869
0.07735266
0.01657296
-0.01692053
-0.0230658
0.04372984
-0.05050359
-0.01195787
-0.07151491
-0.08230956
0.006404565
-0.1013867
0.07352219
0.05961177
-0.05497705
-0.02457643
-0.01724329
0.008900909
0.08118021
0.06177783
0.01479703
0.02176413
0.02348628
-0.01060684
0.05888152
0.01505241
0.04381967
0.02201894
0.01395802
-0.01198528
-0.02556819
0.01238394
0.02004881
-0.03879197
0.001057545
0.01909884
0.0165788
0.04432026
0.0334138
-0.02739947
0.02080992
-0.01857793
-0.02658976
0.03615067
0.00990455
0.04225137
-0.007775977
0.009331496
-0.02197352
0.03807307
0.001884278
0.03896635
-0.003552439
0.01759782
0.0115297
-0.03301048
-0.001886871
-0.002954858
0.02296836
0.01810966
0.0361071
0.02137383
0.009153746
0.01845201
-0.01599629
0.01147923
0.0384695
0.03666588
-0.01611019
-0.03279221
0.009641075
0.03930644
0.02476975
-0.05130902
-0.006492862
-0.06332137
-0.0236357
-0.01218628
0.0498759
0.02162099
-0.08024865
-0.01475972
0.008608096
-0.09995864
-0.1863233
-0.08742493
0.0191642
-0.07886716
-0.1033621
0.08355775
0.1076485
0.06514654
-0.004962949
0.0803737
0.0293556
0.04365066
-0.02933328
0.0546304
0.02646142
-0.0381976
0.0326673
0.06200508
0.01892778
-0.08135476
-0.05254165
0.06868844
-0.04501186
0.08978857
0.03708644
0.002922298
0.06488308
0.01823712
0.03623631
0.00175974
0.02748892
-0.01696588
-0.02021594
-0.02368923
-0.05973662
-0.0867193
0.1128047
-0.008940745
0.001424069
0.05299218
0.03898003
0.02215733
-0.008057034
-0.06805556
0.03607925
0.009053921
0.02373691
0.02458327
-0.01551108
0.002978485
0.009030519
0.05298286
0.006080531
0.03355191
0.0137677
0.0165944
-0.01708809
0.05125463
-0.0279378
0.03571621
0.03862302
0.02284766
0.02410272
-0.03115196
0.04399028
0.002823278
0.000406248
0.01805166
0.02600383
-0.02186009
0.0382218
-0.02690579
0.01992689
0.01894781
-0.005800315
-0.02827728
0.05365804
-0.01222414
0.007629222
0.008127779
-0.02117797
0.01089808
-0.06204914
-0.03572967
0.07261116
0.000141218
-0.02446551
-0.05831643
-0.001810042
0.06818433
0.0104908
0.01193912
0.000857896
0.03760161
0.000468768
0.001237543
-0.02280981
0.03809416
0.0166373
0.02114902
0.03052057
0.000193882
0.008538043
0.00698502
0.007698955
0.01891019
-0.000644627
0.02215116
0.01820993
0.02493248
0.01027303
0.04968561
-0.04134996
-0.02031177
0.003584003
0.02386825
0.003581647
0.03046789
0.02776544
-0.001211907
-0.0751131
0.01617109
-0.09212437
0.08706728
0.03066619
0.01114004
0.03670005
-0.06389814
0.06528302
0.01070147
-0.02237188
0.01416891
0.01808529
0.03298323
0.02624421
-0.002848516
-0.07986767
-0.1436851
0.1284081
0.05168838
0.02352204
0.0544082
0.06679808
-0.03651516
-0.0212179
0.1220191
0.04324383
Purchase answer to see full
attachment
