Assignment 1 –
Autumn 2021
Upload your solution paper in DigiEx by Thursday, September 23, 12:00 p.m. (at noon). Your solution paper may have up to five pages plus an appendix containing all the R code (with

clarifying comments) you used to produce your results. The appendix has to be uploaded as a separate attachment (as a pdf file) and does not count towards the five pages. For your solution paper, please use the standard BI template paper, and stick to the default font style, font size, line spacing, margins, etc. The template paper is available for downloading here.
Read each question carefully and give precise answers. In your calculations, use four positions after the decimal point.
Please note that I will not respond to any individual emails, messages etc. If you have questions or want me to clarify something, please post a thread in the discussion forum I have created on itslearning.
The student code of conduct applies, i.e., the solution paper must be written and prepared by the corresponding group members only. Collaboration with classmates or other individuals outside the group is not permitted and is considered cheating.
All papers are automatically subject to plagiarism control.
Students need to include a list of all literature used to answer the assignment.
Good luck!
Total assignment points (100points)
Appendix A contains a list of useful R functions.
1. Empirical properties of asset returns (30points)
Follow the steps outlined in Appendices B.1 to B.4 and download the monthly time series for the following assets:
– U.S. stock market index,
– U.S. Treasury bonds maturing in ten years,
– U.S. Treasury bills maturing in three months, and
– U.S. ESG index which targets companies with positive environmental, social and governance (ESG) factors.
Your tasks are as follows:
Import the data into R without modifying the files you downloaded.
For each of the assets, determine the monthly time series of (nominal) log returns over the sample period from January 2005 to December 2020. Make sure that all time series have the same length and contain no missing values (NA). For each monthly time series, calculate the (arithmetic) mean and the standard deviation. Report the results in your solution paper.
Aggregate each monthly log return time series to an annual log return time series. Calculate the (arithmetic) mean and the standard deviation. Report the results in your solution paper.
To visualize the differences between the nominal returns on the four assets at the annual basis, you decide to perform the following experiment. Suppose you invest 1$ at the end of December 2004 in each of the assets and hold it until the end of 2020. Generate a plot which shows how your initial investment of 1$ evolves over time.
Download the (seasonally adjusted) consumer price index (CPI) at the monthly frequency, and use it to calculate the real log returns for each asset at the annual frequency. Calculate the (arithmetic) mean and the standard deviation for the log inflation rate and for each of the other four real time series on an annual basis. Report the results in your solution paper.
To visualize the differences between the real returns on the four assets at the annual basis, you decide to perform the following experiment. Suppose you invest 1$ at the end of December 2004 in each of the assets and hold it until the end of 2020. Generate a plot which shows how your initial investment of 1$ evolves over time.
Calculate the correlations between the four different assets and the log inflation rate for annual nominal returns. Report the results in your solution paper.
Compute the correlations between the four assets for annual real returns. Report the results in your solution paper.
2. Empirical distribution of stock market returns (20points)
Follow the steps outlined in Appendix B.5 and download the daily time series for the U.S. stock market index.
Your tasks are as follows:
Import the data into R without modifying the files you downloaded.
Determine the daily time series of (nominal) log returns over the sample period from January 1, 1926 to December 31, 2020. Provide a plot of the resulting distribution in your solution paper. Report the minimum, the maximum, the (arithmetic) mean, the standard deviation, the skewness, and the excess kurtosis. Are these daily returns normally distributed? Explain your answer.
Go back to the monthly time series for the return on the U.S. stock market index from the first exercise. Import the data into R without modifying the files you downloaded and use the sample period from January 1926 to December 2020. Take a look at the return distribution for monthly returns – but do not include it in your solution paper! How does the return distribution relate to the normal distribution if the return horizon becomes longer? Explain your answer.
3. Predictability of excess returns (50points)
Your tasks are as follows:
Go back to the monthly simple return time series for U.S. Treasury bills maturing in three months you downloaded in exercise one. This will be our proxy for the risk-free rate, Rtf, in this task. Import the data into R without modifying the files you downloaded. Aggregate the monthly simple return time series to an annual simple return time series. Calculate the simple gross return of the risk-free rate, 1 + Rtf. Restrict your sample to post war data, i.e., from 1947 to 2020.
Follow the steps outlined in Appendix B.6 and download the two monthly time series for the U.S. stock market index. Import the data into R without modifying the files you downloaded. Denote the simple value-weighted return including all distributions (e.g., dividend payments) by Rt and the one excluding all distributions by Rtx. Aggregate each monthly simple return time series to an annual simple return time series. Calculate the gross returns for Rt and Rtx:
and 1 + .
From 1 + Rt and 1 + Rtx we can back out the dividend-price ratio (also known as the dividend yield):
.
Calculate the (arithmetic) mean and the standard deviation of the dividend-price ratio in your sample.
From the literature, it is known that this dividend-price ratio has some explanatory power for future excess returns (see, e.g., Fama and French (1988)). To check whether you can confirm this result, determine the market excess return in year t + 1 by subtracting the gross risk-free rate from our proxy for the market return, Rt+1:
.
Calculate the (arithmetic) mean and the standard deviation of the market excess return in your sample. For this calculation, ignore any NAs in your time series.
Use the dividend-price ratio in year , as independent variable, the market excess return in year t + 1 as the dependent variable, and run the following regression:
.
Put differently, you are studying, for instance, whether the dividend-price ratio in 1947 tells you something about the market excess return in 1948.
Report the parameter estimates, standard errors, and the R2 in your solution paper. Calculate the t-stats. Are the parameter estimates statistically significantly different from zero at the 5% level? Go through all steps of the corresponding test. For those that are statistically significant, explain its economic significance.
To extend your analysis from the one-year horizon (k = 1) to the k-year horizon (for k = 3 and 5), calculate the corresponding cumulated excess return, . That means, cumulate the gross market return over the next k years and subtract from this the cumulated gross risk-free rate over the same time span:
.
For k = 3 and k = 5, calculate the (arithmetic) mean and the standard deviation of the corresponding cumulated excess return. For this calculation, ignore any NAs in your time series.
Use the dividend-price ratio in year t as the independent variable, the cumulated excess return in year t + k as the dependent variable, and run the following regression:
.
Put differently, for k = 3, you are studying, for instance, whether the dividend-price ratio in 1947 tells you something about the cumulated market excess return in 1950 which has been calculated over the years 1948, 1949, and 1950. For k = 5, you are studying whether the dividend-price ratio in 1947 contains information about the cumulated market excess return in 1952 which has been calculated over the years 1948, 1949, 1950, 1951, and 1952.
For k = 3 and k = 5, report the parameter estimates, standard errors, t-stats, and the R2 in your solution paper.
Appendix
A Useful R functions
Of course, there are a thousand different ways to solve the tasks in this assignment which might all lead to the same (correct) results. To provide some kind of guidance, you can find here a list of R functions that might come in handy:
as.numeric which is part of the base package
x – c(-1-, -2-, -3-)
as.numeric(x)
[1] 1 2 3
cumsum and cumprod which are part of the base package
x – 1:5
x
[1] 1 2 3 4 5
cumsum(x)
[1] 1 3 6 10 15
cumprod(x)
[1] 1 2 6 24 120
paste which is part of the base package
x – -Hello –
y – -world!-
paste(x, y)
[1] -Hello world!-
year – -2021-
month – -12-
day – -01-
paste(year, month, day, sep=—)
[1] -2021-12-01-
as.Date(paste(year, month, day, sep=—), format = -%Y-%m-%d-) [1] -2021-12-01-
str(as.Date(paste(year, month, day, sep=—), format = -%Y-%m-%d-)) Date, format: -2021-12-01-
days in month which is part of the lubridate package
date – as.Date(-2021-12-01-)
days in month(date)
31
format which is part of the base package
date – as.Date(-2021-12-01-)
format(date, -%Y-) [1] -2021-
merge which is part of the base package
data1 – data.frame(id = 1:6, x1 = c(5, 1, 4, 9, 1, 2), x2 = c(-A-, -Y-, -G-, -F-, -G-, -Y-))
data1 id x1 x2
1 1 5 A
2 2 1 Y
3 3 4 G
4 4 9 F
5 5 1 G
6 6 2 Y
data2 – data.frame(id = 4:9, y1 = c(3, 3, 4, 1, 2, 9), y2 = c(-a-, -x-, -a-, -x-, -a-, -x-))
data2 id y1 y2
1 4 3 a
2 5 3 x
3 6 4 a
4 7 1 x
5 8 2 a
6 9 9 x
merge(data1, data2, by = -id-, all=TRUE) id x1 x2 y1 y2
1 1 5 A NA NA
2 2 1 Y NA NA
3 3 4 G NA NA
4 4 9 F 3 a
5 5 1 G 3 x
6 6 2 Y 4 a
7 7 NA NA 1 x
8 8 NA NA 2 a
9 9 NA NA 9 x
aggregate which is part of the stats package
data – data.frame(x1 = 1:5,
x2 = 2:6,
group = c(-A-, -A-, -B-, -C-, -C-))
data x1 x2 group
1 1 2 A
2 2 3 A
3 3 4 B
4 4 5 C
5 5 6 C
aggregate(x1 ˜ group, data, sum) group x1
1 A 3
2 B 3
3 C 9
B Data
B.1 Monthly returns on U.S. stock market index
As a proxy for the U.S. stock market index, we will use the CRSP stock market index.
1. Open the this homepage and log into WRDS.
2. Click on “CRSP”, and choose “Stock / Security Files”.
3. Select “Stock Market Indexes” and fill out the query form.
(a) Step one is to specify that you want to work with “Monthly” data and to choose “1925-12” until “2020-12” as data range.
(b) In step two you have to select the variables you want to download. Select “Value-Weighted Return (includes distributions).”
(c) The third step involves determining the output format. Pick “Excel spreadsheet (*.xlsx)”, scroll further down, and hit the “Submit Query” button.
4. A the top of the homepage, a green message will pop up that your query was successfully submitted. Click on the query number.
5. In the “Results” row, you find the “output files” of your query. Click on the resulting .xlsx file to download it.
6. Use “CRSP index monthly” as filename.
Note that you downloaded simple returns which are given in decimal numbers, i.e., 0.01 = 1%.
B.2 Monthly returns on U.S. Treasury bonds maturing in ten year
1. Go to the homepage of the Board of Governors of the Federal Reserve System by opening this link and click on “Build package”.
2. Choose “Selected interest rates”, and “Continue” with the next step which involves determining the instrument you want to download.
3. Select “TCMNOM: U.S. government securities/Treasury constant maturities/Nominal” and “Continue”.
4. After that, choose to work with a maturity of “Y10: 10-year” and “Continue”.
5. The next step is to pick “Monthly” frequencies, hit “Add to package”, and as soon as the next homepage opens, click “Format package”.
6. Then you are asked to specify the data range for your package. Switch to “Dates” and choose to work with data from “Apr 1953” to “Aug 2021”.
7. Pick “Excel 2003, or newer” as file type and hit the “Go to download” button.
8. As soon as the new homepage has opened, click on “Download file”.
9. Use “T bond monthly” as filename.
Note the following:
You downloaded simple returns which are given in percent, i.e., 1 = 1%.
You downloaded simple returns with a return horizon of one year which can be transformed into simple returns with a return horizon of one month using the following formula
.
Solving the above equation for RBond,tmonth yields
.
B.3 Monthly returns on U.S. Treasury bills maturing in three months
1. Go to the homepage of the Board of Governors of the Federal Reserve System by opening this link and click on “Build package”.
2. Choose “Selected interest rates”, and “Continue” with the next step which involves determining the instrument you want to download.
3. Select “TB: U.S. government securities/Treasury bills (secondary market)” and “Continue”.
4. After that, choose to work with a maturity of “M3: 3-month” and “Continue”.
5. The next step is to pick “Monthly” frequencies, hit “Add to package”, and as soon as the next homepage opens, click “Format package”.
6. Then you are asked to specify the data range for your package. Switch to “Dates” and choose to work with data from “Jan 1934” to “Aug 2021”.
7. Pick “Excel 2003, or newer” as file type and hit the “Go to download” button.
8. As soon as the new homepage has opened, click on “Download file”.
9. Use “T bill monthly” as filename.
Note the following:
You downloaded simple returns which are given in percent, i.e., 1 = 1%.
You downloaded simple returns with a return horizon of one year which can be transformed into simple returns with a return horizon of one month using the following formula
.
Solving the above equation for RBill,tmonth yields
.
B.4 Monthly prices of U.S. ESG index
Here, we will be using the MSCI USA ESG Select Index.
1. Open this homepage.
2. Scroll down and click on “Performance, Factsheets and Methodology.”
3. Under “MSCI USA ESG Select Index”, hit “Performance.”
4. On the right below the plot, you find “Term.” Choose “Full History” and hit “Update.”
5. In the upper left corner above the plot, click on “Download Data.”
6. Save the Excel file using “ESG index monthly” as filename.
You downloaded the prices of the ESG index.
B.5 Daily returns on U.S. stock market index
As a proxy for the U.S. stock market index, we will use the CRSP stock market index.
1. Open the this homepage and log into WRDS.
2. Click on “CRSP”, and choose “Stock / Security Files”.
3. Select “Stock Market Indexes” and fill out the query form.
(a) Step one is to specify that you want to work with “Daily” data and to choose “1925-12” until “2020-12” as data range.
(b) In step two you have to select the variables you want to download. Select “Value-Weighted Return (includes distributions).”
(c) The third step involves determining the output format. Pick “Excel spreadsheet (*.xlsx)”, scroll further down, and hit the “Submit Query” button.
4. A the top of the homepage, a green message will pop up that your query was successfully submitted. Click on the query number.
5. In the “Results” row, you find the “output files” of your query. Click on the resulting .xlsx file to download it.
6. Use “CRSP index daily” as filename.
Note that you downloaded simple returns which are given in decimal numbers, i.e., 0.01 = 1%.
B.6 Monthly returns on U.S. stock market index including and excluding distributions
As a proxy for the U.S. stock market index, we will use the CRSP stock market index.
1. Open the this homepage and log into WRDS.
2. Click on “CRSP”, and choose “Stock / Security Files”.
3. Select “Stock Market Indexes” and fill out the query form.
(a) Step one is to specify that you want to work with “Monthly” data and to choose “1925-12” until “2020-12” as data range.
(b) In step two you have to select the variables you want to download. Select “Value-Weighted
Return (includes distributions)” and “Value-Weighted Return (excluding distributions).”
(c) The third step involves determining the output format. Pick “Excel spreadsheet (*.xlsx)”, scroll further down, and hit the “Submit Query” button.
4. A the top of the homepage, a green message will pop up that your query was successfully submitted. Click on the query number.
5. In the “Results” row, you find the “output files” of your query. Click on the resulting .xlsx file to download it.
6. Use “CRSP index monthly task3” as filename.
Note that you downloaded simple returns which are given in decimal numbers, i.e., 0.01 = 1%.