BEAM046 Financial Modelling
Financial Modelling
BEAM046 Financial Modelling
Individual Assignment
(Late Submission Penalties Will Apply)
Word Limit: 1500 words with a 10% margin (excluding tables,
references, and appendices)
As a non-profit organisation, UE is particularly concerned about the risk associated with its
endowment fund and has requested additional insights on this matter. UE’s risky portfolio invests
in Caterpillar (CAT), Johnson and Johnson (JNJ), Walmart (WMT), Nvidia (NVDA), and Proctor
and Gamble (PG). All analyses will be conducted assuming a risk-free rate of 0% and no exchange
rate fluctuations. Trading fees and transaction costs are assumed to be negligible given the size of
UE’s portfolio. Use historical monthly returns from January 2016 to December 2022 for all your
analyses and historical averages as proxies for expected returns.
1. Construct the following three portfolios using the abovementioned five stocks:
a. P1: the optimal risky portfolio with no trading constraints
b. P2: the optimal risky portfolio constructed under the constraint that the 95% monthly
portfolio return Value at Risk (VaR) is 5%. Estimate the VaR using the variance-
covariance method, assuming normal distribution for monthly portfolio returns.
c. P3: the market portfolio with the following asset weights:
CAT: 7.35% JNJ: 27.32% WMT: 22.70% NVDA: 21.40% PG: 21.23%
Report the asset weights for each portfolio and calculate key performance measures using
historical monthly returns from 2016 to 2022.
2. Estimate the 95% and 99% monthly return VaR for P1, P2, and P3 using both the variance-
covariance method (with the normality assumption) and the historical simulation method.
Report the estimation results.
3. Estimate the 95% and 99% monthly return conditional value at risk (CVaR) for P1, P2, and
P3 using the historical simulation method. Report the estimation results.
4. Discuss the results from the previous two questions. What insights can be drawn from the
estimated VaR and CVaR? Assess the suitability of the two VaR estimation methods given
the data.
5. UE is considering combining its optimal risky portfolio (P1) with a long-term bond to create
a complete portfolio. Your team has gathered bond market data to construct the yield curve.
The data file “Bond_Prices.xlsx” contains information on key features and prices for
government bonds with varying time to maturity. Bond prices are quoted as percentages of
face value. Use these bond data to construct and plot the yield curve.
6. Choose an appropriate model to fit the yield curve constructed in the previous question. You
may fit more than one models, but you will need to choose the most suitable one and provide
justifications for your choice.
7. The bond that UE is considering holding is a 20-year 4.3% annual coupon bond. The first
coupon payment will be made in three months (0.25 years), and the bond will mature in
19.25 years. For simplicity, assume negligible default risk for the bond during UE’s holding
period, and that UE plans to hold this bond until maturity. UE does not expect the return on
this bond to co-move with other risky assets. Use the yield model you chose in the previous
question to estimate the annual yield for this bond.
8. UE’s complete portfolio (P4) consists of the aforementioned bond and the optimal risky
portfolio P1. UE wants P4’s monthly return to have a 95% VaR of 5%, calculated using the
variance-covariance method with the assumption that returns are normally distributed. How
should P4 be constructed? Report the weights for the bond and P1.
9. Both P2 and P4 have a 95% VaR of 5%. Provide a recommendation to UE regarding which
portfolio is more desirable and explain your choice.
Deliverable:
Prepare a report that addresses all the aforementioned questions. Make sure the texts and results
are logically structured. Begin with a brief introduction to the report’s purpose, followed by
methodologies. If your dataset is exactly the same as that of the group assignment, you may skip
the data section. If you collected any new data for this assignment, you should provide a detailed
explanation of the data source. Next, present your analysis results and discussions. Conclude your
report with a summary of your recommendations for UE. Feel free to add additional sections. The
word count limit is 1,500 words, with a 10% margin. Tables, references, and appendices are not
included in the word count. Each student should submit one PDF file. Other forms of document
(e.g., Excel worksheet) will not be accepted.
Optional:
If you wish to earn extra credits, consider incorporating the following into your analysis:
10. UE would like to assess the likelihood that its complete portfolio (P4) can generate a monthly
return of at least 1%, an important threshold to meet its budgetary needs. Your team has
decided to estimate this probability using the bootstrap technique. Resample the 2016~2022
historical returns to generate 100 bootstrapped samples, each with 84 observations. Report
the cross-sample average probability that the monthly return of P4 exceeds 1% and find out
its 95% confidence interval. Does P4 generate sufficient returns to meet UE’s budgetary
needs?
11. UE is concerned about market crash risk due to global economic and political uncertainties.
Your team has decided to use simulation techniques to assess the impact of stock market
crashes. Assume the monthly return for P1, rt, follows the data generating process:
rt = vt + Ct
, where vt follows the normal distribution with mean ̂ and variance ̂2, and Ct takes the
value of (-3%) with 20% of probability and 0 with 80% of probability. ̂ and ̂2 are the
sample mean and variance estimated using P1’s monthly returns between 2016 and 2022. vt
and Ct are assumed to be independent.
Use this data generating process to simulate 5,000 P1 monthly returns. Use the weights found
in Question 8 to construct the complete portfolio P4 to generate 5,000 simulated monthly
returns for P4. Assume the bond held by UE remains unaffected by the stock market crash.
Use the simulated P4 returns to calculate (1) the average monthly return for P4, (2) the 95%
monthly return VaR for P4 using the historical simulation method, and (3) the probability
that P4 can generate monthly return above 1%. Are you confident that P4 is profitable enough
(i.e., monthly returns greater than 1%) for UE?
12. Your team is uncertain about the loss magnitude of a potential stock market crash (i.e., the
value of Ct). Therefore, you want to conduct sensitivity analysis to examine how key
performance measures change with loss amount. Allow the monthly loss amount to vary
from 1% to 10% in increments of 1%. Report how the following three measures change
accordingly: (1) the average monthly return for P4, (2) the 95% monthly return VaR for P4
using the historical simulation method, and (3) the probability that P4 can generate monthly
return above 1%.