Excel Homework
1. It is believed that stock prices and bond prices tend to move in different directions. The
argument is that whenever demand for stocks is up (down), the money is shifted to (from) riskier
stocks from (to) safer bonds, thus bringing bond prices down (up) and vice versa. We will check
whether there exists significant evidence behind this theory. Find the Excel file HW4.xls in the
Data folder on Blackboard. The Problem1 sheet in that file contains daily levels of the 10-year
Treasury bond yield and the S&P 500 index. You are asked to compute the percentage of
occasions separately for each month when the stock index and the 10-year Treasury bond price
went in different directions. You solution must consist of the plot of the monthly percentages of
such occasions on the Y axes, and the respective time periods on the X axis.
2. Find the Excel file HW4.xls in the Data folder on Blackboard. The Problem2 sheet in that file
contains historical daily data for price and dividends on a bond fund. Construct a series of
historical cumulative return without dividends, a series of historical cumulative return with
dividends and a series of historical cumulative total return, i.e., when all dividends are
automatically reinvested. Plot a chart showing all three constructed series.
