FINC3017 Assignment 1
Individual Assignment

Due Date: 18th September 2025 before 23:59

This assignment consists of two parts (A and B). All answers are to be entered into the
Excel template provided on Canvas - “FINC3017_Asmnt_1_Solution_Template.xlsx”.
Your answers must be entered into the appropriate cells indicated on the question,
coloured blue. Only these cells can be altered, all other cells have been locked.
This assignment will be marked by computer, so it is essential that you follow
instructions when entering your answers.
Instructions:
• Each student has been provided a unique set of data. You must use your
assigned dataset. If you elect to use an alternative dataset, all answers will be
incorrect, and the mark will be 0.
• If an input is required for a question that you have already computed, it must be
used in future computations. This is specified/implied in each question. This is
to avoid penalising students’ multiple times for the same mistake. Students that
do not follow this instruction will be penalised multiple times.
• All answers placed in the template must be in the form of numerical values (no
formulas) or as one of several choices from a drop-down list. You cannot put text
(including formulas) into the response cells.
• The template is locked and is not to be altered by adding sheets or manually
unlocking it in any way.
• Students who provide solution templates that cannot be marked because they
have not followed the instructions will be automatically penalised 20% of
maximum possible marks.
• Questions that can be solved with an analytic formula should use that formula.
Using other methods may result in answers that are not sufficiently accurate and
hence will be penalised.
• A video has been posted on Canvas illustrating how the template it be
completed. All students must watch this video prior to completing the
assignment.
Part A – All responses to go in template sheet titled “Part A”
In the spreadsheet titled “Part_A_Data_sid.xlsx” you have been provided both the value
of the market portfolio and its associated probability for one period in the future for a
variety of potential future states. Complete the following questions. You may ignore
discounting to present value.
1. Give the current value of the market is 100, compute the:
a. The one period expected value of the market. [B1]
b. The one period expected simple rate of return. [B2]
c. The one period standard deviation of returns. [B3]
2. Assume you are a power utility investor. Your risk aversion parameter has been
provided to you in your data email. Compute the expected utility of holding the
market portfolio. [B5]
3. Now, consider two derivatives traded on the market, a call option and put option
each with the same strike. The specific value you are to use has been provided to
you in your data email.
a. Compute the value of the call option in each of the future states. [C8:C38]
b. Compute the value of the put option in each of the future states. [D8:D38]
4. Using the same utility function as in question 2, compute the certainty
equivalent price for:
a. The call option. [G1]
b. The put option. [G2]
5. If you pay the certainty equivalent price for each option, compute the:
a. Expected return on the call option. [G4]
b. Expected return on the put option. [G5]
c. Standard deviation of returns for the call option. [G6]
d. Standard deviation of returns for the put option. [G7]
6. Compute the risk premium for:
a. The market. [G9]
b. The call option. [G10]
c. The put option. [G11]
7. Select which derivative do you prefer to hold and why. [G13]






Part B – All responses to go in template sheet titled “Part B”
In the spreadsheet titled “Part_B_Data_sid.xlsx” you have been provided the monthly
returns and market capitalisations of 20 stocks listed in the US from January 2021 to
December 2024. You have also been provided, on Canvas, the market return and risk-
free rate for corresponding dates. Using this data, complete the following tasks. Note
that unless otherwise specified, you are not to annualise your results.
1. For each stock in your sample, compute the:
a. Sample mean return. [C3:V3]
b. CAPM alpha. [C4:V4]
c. Standard error of the CAPM alpha. [C5:V5]
d. t-stat for the CAPM alpha. [C6:V6]
e. Two-tailed p-value for the CAPM alpha. [C7:V7]
f. CAPM beta. [C8:V8]
g. Standard error of the CAPM beta. [C9:V9]
h. t-stat for the CAPM beta. [C10:V10]
i. Two-tailed p-value for the CAPM beta. [C11:V11]
j. The expected return on each asset using your CAPM regression results
but with an expected market return of 7% p.a. Ensure that you only
include parameters that are statistically significant at the 5% level.
[C12:V12]
2. Compute the sample covariance matrix for your 20 stocks. [C16:V35]
3. Using your answer to question 1a and question 2, compute the optimal portfolio
of the 20 risky assets only, short sales allowed, for investors with risk tolerance T
= 0, 1, and 2. [C39: V41]
4. Compute the same portfolios as in question 3 but with a minimum position of
2.5% of wealth allowed in each asset. [C45: V47]
5. Compute the risk aversion parameter that corresponds to the tangency portfolio
when short sales allowed. You should use your CAPM regression results to
compute your expected returns assuming an expected market return of 7% p.a.
and the most recently observed risk-free rate. [C50]
6. Using your answer and corresponding inputs to question 5, compute the
tangency portfolio when short sales are allowed. [C54:V54]
7. Compute the tangency portfolio from question 6 when short sales are not
allowed. [C58:V58]
8. Compute the expected returns for the portfolio from question 3 with T = 1. [C61]
9. Compute the standard deviation for the portfolio from question 4 with T = 1.
[C64]
10. Assume that you can invest in both the risk-free asset and the 20 risky assets and
that your risk tolerance T = 0.3. Compute the optimal portfolio when short sales
are allowed. You should use the most recently observed value of the risk-free
rate for this calculation and your answer to 1j. [C68:W68]
11. Compute for the portfolio in question 10:
a. The expected return. [C70]
b. The portfolio standard deviation. [C71]
12. Consider the portfolio constructed in question 3 with T = 0.
a. Identify the asset (number) with the highest weight. [C74]
b. Select the reason that best explains why this asset has the highest
weight. [C75]
13. The marginal contribution to portfolio variance measures how portfolio variance
changes as asset weights change. Assuming very small changes in weight and
using the covariance matrix computed in question 2, identify which asset makes
the highest marginal contribution to portfolio variance for an equally weighted
portfolio. [C78]

学霸联盟