Constructing the Efficient Frontier:

A Real-World Investment Challenge

FIN405 – Investment Analysis
Spring 2026

Instructions
1. Please hand in your project by email by Sunday, April 26 th, at 11.59 PM. No late
submissions will be accepted.
2. Please hand in one solution set for your group.
3. Please write the names and ID numbers of each member of the group clearly.
4. Your project needs to be typed up and not handwritten.
5. This project consists of two questions. Please use the Excel spreadsheet IA - [Link]
attached to this booklet. You may add rows, columns and tabs as and where you find that
this would improve your answer. Please present the filled-out Excel spreadsheet as part of
your answer, along with a word/PDF document for all non-Excel computations.
6. There are two appendices following the questions which explains how to use an Excel
Solver (Appendix A) and how to construct an efficient frontier in the presence of two risk-
free rates (Appendix B).
7. The project will be graded out of 100 points. The number of points that each problem is
worth is written in brackets.

Statement of Academic Integrity

Ethical and professional behaviour is expected in class and during examinations as well as for
any graded work. Any alleged violation will be reported to the School Academic Committee
and/or to the Dean of Students for possible disciplinary action.
I. Introduction

You have just been hired as a junior portfolio

manager at a prestigious investment firm. Your
team has been assigned a high-net worth client:
Mr. Karim Allam, a 58-year-old Lebanese
businessman looking to optimize his equity
portfolio for maximum returns while managing
risk.

Mr. Allam tells you that he has a moderate risk

tolerance; his goal is to have a complete
portfolio whose volatility does not exceed 18%.

Mr. Allam wants to be diversified: as such, he wants to invest in four stocks from
different industries, but only from highly reputable companies with a strong track
record of performance and a good ESG profile. Very importantly for him – and this is
a matter that is very dear to him – he does not want to invest at all in any company that
has been flagged by the BDS movement as supporting Israel in any way. Finally, at least
two of the stocks he wants to invest in should be dividend-paying stocks.

Your task is to analyze, construct, and present an efficient portfolio using four blue-
chip publicly traded stocks, demonstrating your ability to balance risk and return using
modern portfolio theory.

II. Phase 1: Constructing the Efficient Frontier

This phase will be very similar to what we have done in class, except for one small
complication, which will make our work a bit more realistic. We shall consider that there are
two risk-free rates: a borrowing rate and a saving rate.

A. Please use Refinitiv Workspace to download the historical prices and dividends of four
U.S. publicly traded stocks for the past six years at a daily frequency (for reference, you
should have over 1,500 observations for each stock). Use this data to compute daily returns
in the “Returns” sheet. Please bear in mind your client’s conditions and preferences when
selecting the stocks for his portfolio.
B. Download the market and risk-free rate data: in Kenneth French’s data library website ,
locate “U.S. Research Returns Data (Downloadable Files)” and download the
“Fama/French 3 Factors” at a daily frequency in csv format. The data starts on July 1 st,
1926 and includes four columns: The market excess return (“Mkt-RF”), the small-minus-
big factor (“SMB”), the high-minus-low factor (“HML”) and the risk-free rate (“RF”).
Retain only the first and last column (we won’t need SMB and HML for this project); keep
only the dates that match the dates for which you have stock data in step A above.

C. Now go to the sheet named “Efficient Frontier.” Compute the following:

1. Annualized means, as well as annualized variances and covariances of the individual
stock returns.
2. Annualized mean and variance of the market returns.
3. Annualized saving risk-free rate (this is obtained from the risk-free rate data you
downloaded from Kenneth French’s website earlier).
4. Annualized borrowing risk-free rate, equal to the saving risk-free rate plus 3%.

D. Compute the efficient (and inefficient frontier) of the four risky stocks. Proceed with the
following steps:
1. The purpose of this part is to plot the frontier of the investment opportunity set, both
the part that lies above the minimum variance portfolio (the efficient frontier) and
under it (the inefficient frontier).
2. Use Excel Solver (see instructions in Appendix A) to find the minimum variance
(MVP) portfolio, i.e., the portfolio that minimizes variance irrespective of expected
returns. Compute the mean and standard deviation of the MVP portfolio.
3. Use Excel Solver to find the tangency portfolio with the saving risk-free rate (which in
class we also referred to as the optimal portfolio OP), i.e., the portfolio that maximizes
the Sharpe ratio. Compute the mean and standard deviation of the tangency portfolio.
4. Repeat the previous step to find the optimal portfolio with the borrowing risk-free rate.
5. Use Excel solver to find an efficient portfolio (P1) with a target standard deviation of
your choice: for example, let’s say you want to form a portfolio with a target volatility
𝜎 = 15.00%; finding an “efficient” portfolio means finding the portfolio which offers
the maximum possible return for such a level of risk. Compute the mean return of this
portfolio P1.
6. Repeat step 4 by varying volatility target to find 5 more efficient portfolios (i.e., fill the
table from P2 to P6).
7. Next, we now want to find the inefficient frontier of the investment opportunity. This
time, for portfolios P7 to P12, you want to set a target standard deviation (feel free to
use the same values that you used for P1 to P6) and then find the lowest possible return
you can generate for each level or volatility you set.
8. Do you notice any patterns in weights to the four stocks across these portfolios? If yes,
explain why?
9. Draw the mean-variance frontier from portfolios computed in steps 4, 5, 6 and 7 above.
You may need to reorder the portfolios to be able to generate a smooth frontier.
B. In this step, you shall construct the efficient frontier when the risk-free borrowing and
saving rates are different. Refer to Appendix B below for more detail on how the efficient
frontier would look like when the saving and borrowing rates are different.
1. Compute the new efficient frontier.
2. Plot this efficient frontier on the same

C. Compute the required metrics for the optimal risky portfolio as well as the complete
portfolio.
1. The proportion of the complete portfolio is allocated to the risky asset.
2. The proportion of the complete portfolio allocated to the riskless asset.
3. The complete portfolio expected return.
4. The complete portfolio risk premium.
5. The complete portfolio variance and volatility.

III. Phase 2: Analyzing the Performance

In this part, except where it is indicated otherwise, you are required to perform the following
analysis for the complete portfolio as well as for the market portfolio. Start by filling out the
columns already in place in sheet “Portfolio Returns” (you may need to add extra columns
later to aid your analysis).
A. Plot the returns on a histogram (suggested bin size for the histogram is 0.50% since these
are daily returns). Comment on the results. Do the returns appear to follow a normal
distribution?
B. Suppose that you invested $1,000 in the complete portfolio and $1,000 in the market at
the star t of your sample. Show in a plot how the value of your investment in each stock
and the market would have grown over time (in nominal terms) until the end of your
sample. In other words, show the cumulative total returns from your investment.
Comment on what you observe.
C. Compute the annualized arithmetic mean and standard deviation of the excess returns; use
them to calculate the annualized Sharpe ratio. Does the optimal portfolio outperform the
market?
D. Compute the annualized geometric mean of the excess returns. How close are they to the
arithmetic means you computed earlier?
E. Calculate the one-day 1% value-at-risk (VaR) in two ways:
1. based on the realised returns, and
2. assuming that the returns of the stocks are normally distributed.

What do you conclude from comparing the two values?

F. Your client wants you to compute the 1% Expected Shortfall (also known as the
Conditional Value at Risk or CvaR) based on the realized returns (i.e., not assuming
normality). While the VaR measures the minimum expected loss over a set timeframe at a
specific confidence level, focusing on the threshold, the CVaR calculates the average loss
in the extreme tail scenarios beyond the VaR. Compute the CVaR for the complete
portfolio and
G. Find the alpha and beta of the portfolio only (for obvious reasons!). Comment on the
results.
H. Plot the security characteristic line of your portfolio.
I. Compute the Treynor ratio, defined as the risk-adjusted return per unit of systematic risk
(beta). In other words, it is the ratio of the mean excess return to the beta.
J. Mr. Allam would like to know the Ulcer ratio of both his complete portfolio and the
market portfolio. You’ve never heard of this! Your manager, who knows you are just a
junior analyst, informs you that this ratio is similar to the Sharpe ratio, but the denominator
is not the standard deviation, but the Ulcer Index. Let’s discuss how this is constructed.
1. First, compute, for each day 𝑡 in the time series, the drawdown 𝐷 . The drawdown is
the percentage loss from the previous maximum. For example, if the current level of
the portfolio is $1,100 but the previous maximum was $1,250, the drawdown is 𝐷 =
, ,
= −12%. Generally:
,

𝐿𝑒𝑣𝑒𝑙 − 𝑃𝑟𝑒𝑣𝑖𝑜𝑢𝑠 𝑀𝑎𝑥𝑖𝑚𝑢𝑚

𝐷 =
𝑃𝑟𝑒𝑣𝑖𝑜𝑢𝑠 𝑀𝑎𝑥𝑖𝑚𝑢𝑚
Note that this will result in a series of zero or negative numbers.
2. Then, compute the Ulcer Index as square root of the average squared drawdown.

1
𝑈𝐼 = (𝐷 )
𝑁

K. The client asks you to also report the longest time to recover: this measures how long it
takes for a portfolio to fully recover from a downturn by reaching its previous peak. This
is defined as:
𝑅𝑒𝑐𝑜𝑣𝑒𝑟𝑦 𝑇𝑖𝑚𝑒 = 𝐷𝑎𝑡𝑒 𝑜𝑓 𝑁𝑒𝑤 𝑀𝑎𝑥𝑖𝑚𝑢𝑚 − 𝐷𝑎𝑡𝑒 𝑜𝑓 𝑃𝑟𝑒𝑣𝑖𝑜𝑢𝑠 𝑃𝑒𝑎𝑘
IV. Phase 4: Reporting and Presentation

Compile your findings in a professional report and prepare a presentation for the class. The
report should include your rationale for stock selection, portfolio allocation, and the risk
management strategies employed.

A. Draft a report detailing the client’s needs, portfolio composition, and risk analysis.

B. Prepare a 15-minute presentation summarizing your portfolio construction process and

key insights.

C. The deliverables are:

1. Written Report: Comprehensive report explaining the portfolio construction, stock
selection, and risk management.
2. Excel Model: Submit financial models used for portfolio construction and
optimization. All formulas should be linked, not hard-coded.
3. Presentation: A professional presentation to the class, simulating a client pitch.
Appendix A – How to use the Excel Solver
A. First, make sure that the Excel Solver add-in is enabled. To enable the Solver add-in,
follow the steps below:

1. Click the File tab, and then click Options.

2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.

3. Click Go.

4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.

B. After you load the Solver add-in, the Solver command is available in the “Analyse” group
on the Data tab. When you press the Solver button a new window pops up.

1. The top panel of this window lets you choose a target cell for the “objective
function,” that is the variable you want to optimize. For example, when finding the
minimum variance portfolio, this should be cell J22 in the tab “Efficient Frontier”,
which contains the portfolio standard deviation formula.

2. Below the target cell, you can choose whether your objective is to maximize,
minimize, or set your objective equal to a value that you specify.

3. The next panel contains the decision variables. These are the cells that the Solver can
change to optimize the objective function in the target cell. For example, when
finding the minimum variance portfolio this should be the cells C22:F22 in the same
tab, which contain the portfolio weights.

4. The bottom panel of the Solver can include any number of constraints. One
constraint that must always appear in portfolio optimization is the “feasibility
constraint,” namely, that portfolio weights sum to 1. When we bring up the
constraint dialogue box, we specify that the cell containing the sum of the weights (in
our example, cell G22 when finding the minimum variance portfolio) is set equal to 1.

5. If you are solving for a different portfolio, you may have to set the standard deviation
constraint also. For example, let’s say that you were solving for the portfolio that has
the highest expected return for a given standard deviation of 15.00% in row 24 (this
would be portfolio P1. In that case, the goal of the solver is to maximize the expected
return; importantly, in addition to the constraint on the sum of the weights being equal
to 1, you should also set the constraint that the cell J24 is equal to 15.00% (the 15.00%
figure is used purely for illustrative purposes. Feel free to choose the most suitable
numerical value to plot the efficient frontier).
Appendix B – Efficient Frontier with Different Borrowing and Saving
Rates
When the rates for saving and borrowing are different, the efficient frontier will consist of
three parts:
i. The lower green line which contains combinations of portfolio 𝑇 with saving at the
risk-free rate 𝑟 ,
ii. The portion of the red curve between 𝑇 and 𝑇 , which does not involve borrowing
or saving, and
iii. The upper green line which contains combinations of portfolio 𝑇 with borrowing
at the risk-free rate 𝑟 .

Note: In this particular example, 𝑟 = 3% and 𝑟 = 3%. Your graph may not look exactly
like the one above since your risky-assets-only frontier (i.e., the blue curve) will, in all
likelihood, have different values (i.e., a different minimum variance portfolio and a different
tangency portfolio, and so on). So you may find, for example, that in your graph, 𝑇 and 𝑇
are a lot closer or farther away from each other.