DNSC6210 – Decision and Risk Analytics Spring 2021
Prof. Philippe Delquié
Page 1 of 2
Individual Assignment 2

General Instructions

Please read this carefully: You are to work strictly individually on this assignment. You should not receive
or give any kind of help in working on this assignment, and you should not discuss it or exchange any
information about it with anyone. Read the assignment entirely, including the instructions at the end,
before starting to work. Remember to clearly explain how you arrived at your answers.

Due date: The assignment is due on Wednesday 05 May, 11:59pm EST.

What to turn in: Please submit an Excel file containing a single worksheet, as explained in “Instructions”
at the end. At the top of your file, type or ink the following statement: “I, [your full name], attest that I did
not receive or give any help in working on this assignment.”

How to turn in your work: Submit your work on Blackboard by clicking on “Submit Assignment” at the
bottom of the left pane menu. If you submit your work multiple times, only your last submission will be
considered.


Mean-Variance Portfolio Selection with a Risk-free Asset

The accompanying Excel file contains data on yearly rates of return for four stocks (the ticker symbols are
fake). Also, added to the data set is a risk-free asset available for investment, which had a constant 2%
yearly rate of return over the same period. Your task is to formulate an optimization model to construct
portfolios of the 5 securities that minimize the portfolio’s risk for a given level of portfolio expected return,
then use your model to answer a few questions.
a) Using the variance as a measure of risk, set up an optimization model to minimize the portfolio
variance for a specified portfolio rate of return. Assume no short-selling of the assets, that is, the
portfolio’s weights must all be non-negative.
b) Use the model you built in (a) to generate three optimal portfolios that provide expected rates of
return of 3%, 4%, and 5%, respectively. Copy and paste the weights and corresponding mean return
of the three portfolios so obtained in a table somewhere on the same sheet containing your model.
c) Considering only the optimal portfolios obtained in (b) that include the risk-free asset, that is, those
in which the weight of the risk-free asset is non-0, show that the relative proportions of the 4 risky
assets remain the same across those portfolios. For this, you can calculate the ratio of a stock’s weight
to the sum of the 4 stocks weights, and verify that these ratios are the same in each optimal portfolio
that includes the risk-free asset. This illustrates a well-known result in finance known as the Capital
Market Line.
d) An investor has $60,000 to invest and a risk tolerance of $10,000. Add cells to the model you built in
(a) to calculate (i) the expected value, (ii) the variance, and (iii) the certainty equivalent of the
DNSC6210 – Decision and Risk Analytics Spring 2021
Prof. Philippe Delquié
Page 2 of 2
portfolio’s $-gains (not the %-returns) for a $60,000 investment. To calculate the certainty equivalent,
CE, of the investment, you can use the formula: CE = EV – Variance/(2RT) shown in Slide #20 or the
Session 3 slides, as it may provide a good approximation here.
e) Now modify your optimization model from (a) to maximize the investor’s CE of the portfolio’s $-
return. What is the optimal portfolio for the investor? Provide the weights, expected return, and CE
of the optimal portfolio you obtained.


Instructions. Show all your work in a single worksheet. For each question, type text somewhere on
your Excel sheet to explain your answer. For parts (a) and (e) specify your optimization settings. Avoid
using cell notes for typing explanations, because they can become hidden or improperly resized, and
therefore difficult to see, when your file is downloaded from Blackboard. Also, clearly identify your answers
by question number, so that I do not have to guess where to look in your Excel sheet.
Remember to include the honor code statement at the top of you file.

---------- END OF INDIVIDUAL ASSIGNMENT ----------

欢迎咨询51作业君