Description

DEPARTMENT OF ECONOMICS AND FINANCE

ECON*2560: Introduction to Finance

SPREADSHEET ASSIGNMENT

One of the requirements of this course is to submit a spreadsheet assignment. Here are the details.

Note that you will need the spreadsheet template posted on courselink for your calculations.

Consider investing in five assets: a short bond, a long bond, a stock, the market portfolio of all

stocks, and gold. The following table lists the returns to the five assets in five different scenarios

and the probability of each scenario occurring.

Scenario Probability Short Bond Long Bond Stock Market Gold

Depression 0.10 0.01 0.02 -0.25 -0.10 0.15

Recession 0.20 0.03 0.04 -0.05 -0.05 0.10

Normal 0.20 0.03 0.04 0.15 0.10 0.00

Growth 0.40 0.03 0.04 0.20 0.15 -0.05

Boom 0.10 0.05 0.06 0.25 0.20 -0.05

Note that returns are shown in decimal (not in per cent) form.

1. Compute the expected return, variance and standard deviation of all five assets.
2. Form three portfolios:

 Portfolio 1 is equally weighted: it invests 20% of your wealth in each of the five assets.

 Portfolio 2 invests with weights 1/3 in the short bond, 1/3 in the market and 1/3 in gold.

 Portfolio 3 invests with weights 1/2 in the long bond 1/2 in the market.

1. a) Show the returns of the three portfolios for each scenario.
2. b) Compute the expected return, variance and standard deviation of the three portfolios.
3. Compute:
4. a) the covariance of each of the three portfolios with the market
5. b) the correlation of each of the three portfolios with the market
6. c) the beta of each of the three portfolios.

2

Format

You must submit an Excel file with the spreadsheet to receive a mark on this assignment. You may

not submit your answer on paper.

Use the spreadsheet template posted on courselink, which shows the information in the table above.

The template has empty cells, which are highlighted, indicating where you need to do the

calculations. Your job is to fill in the highlighted cells using formulas in Excel that deliver the

results. Your grade will depend on (i) whether the formulas you use in Excel are correctly

implemented; and (ii) whether the solution is correct. Just showing the numerical solutions without

using formulas will receive a failing mark.

Group work and submission

This is a group assignment. Each group will have a maximum size of five students. It is

completely up to you to decide who participates in your group. If you wish to do the assignment on

your own, you may do so. However, I strongly encourage you to work in a group of maximum five

students. Groups of five students have a better chance of doing well. In any event, I will not

monitor who belongs to what group. I expect that each submission will clearly state the name,

student number and username of each member of the group that has contributed to the assignment.

You must submit your assignment using dropbox on courselink by 5pm on Friday, October 25,

2019. 2019. In order to be able to make a submission, all group members need to join a unique empty

group in dropbox. Specifically, I will create a number of empty groups in dropbox. Then, you need

to join one of them, making sure that only your group members have joined this specific empty

group. You will not be able to make a submission without joining an empty dropbox group.

Late submissions will receive a penalty that is equal to 10% of the assignment mark for every

calendar day the assignment is late.

Recall that the assignment counts for 15% of your final grade.

Good luck!