Financial modelling

Paper details: You work for a consulting firm that provides pension investment advice. One of your clients
invited you and your team to analyse his pension fund plan: Fidelity Retirement Master Trust –
Global Equity Fund (to be referred to as FGIF in the text below; ticker symbol: 0P0000A3YJ.HK).
This client would like to learn more about the investment style of this fund and its performance
relative proper benchmarks. Your team have decided to use the following 7 funds to construct
mimicking portfolios as benchmarks:
iShares S&P 500 Growth ETF (IVW)
iShares S&P 500 Value ETF (IVE)
iShares Russell 2000 Growth ETF (IWO)
iShares Russell 2000 Value ETF (IWN)
Vanguard Real Estate Index Fund (VNQ)
Fidelity Select Gold Portfolio (FSAGX)
Vanguard Energy Fund (VGENX)
Perform the following analyses:
1. Collect historical monthly prices of the above funds from 1st January 2015 to 1st July 2022.
Calculate the historical monthly returns. Use these funds to construct a benchmark portfolio
that mimics the behavior of FGIF. Refer to this portfolio as P1. Assume short selling is not
allowed when constructing P1. Report the weights for P1. How would you characterise the
investment style of FGIF based on these weights?
2. Construct another benchmark portfolio, P2, using the above 7 funds to mimic the behavior of
FGIF. Assume your client faces no trading constraints when constructing P2 and report the
weights of P2.
3. As yet another benchmark, your team have chosen the S&P500 Index as the proxy for the
market portfolio. Calculate performance and risk measures to help your client understand the
performance of FGIF, P1, P2, and S&P500 during your sample period. (Note: You are not
required to annualise any performance/risk measures, although you need to be clear about the
data frequency that these performance/risk measures are based on.)
4. Do you recommend your client to hold FGIF?
5. Your client also cares about potential losses under extreme market conditions. Your team have
decided to calculate the 95% and 99% Value at Risk (VaR) of FGIF, P1, P2, and S&P500 using
the variance-covariance method, assuming returns are normally distributed. Report the
estimated VaRs.
6. You client is also considering investing in a BB-rated 5-year semiannual coupon bond with an
annual coupon rate of 2%. The current price of this bond is 97% of face value. Use the
transition probability matrix in the data file and a recovery rate of 60% to estimate the defaultadjusted yield of this bond.
7. Suppose your client is considering investing in the S&P500 Index and the BB-rated bond
mentioned in Q6 to achieve an average monthly return of 0.7%. How can such a portfolio be
constructed?
Deliverable:
Write up a report that answers all the above questions. Make sure the results are logically presented.
You should begin by briefly introducing the purpose of this report. Next, provide detailed
explanations on your data, methods, numerical results, and result discussions. Lastly, the
conclusion section should provide a summary of your findings. Result discussions are very
important. You should aim to provide in-depth analysis. Feel free to add additional sections. Your
report should not exceed 1,500 words (tables, references, and appendices are not included in the
word count). Each student should upload one PDF file to BART. 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:
8. Do you find the weights for P1 and P2 reasonable? Why or why not?
9. Use alternative method(s) to estimate the VaR and explain why your proposed method(s) may
be more suitable.
10. Calculate the 95% and 99% Conditional Value at Risk (CVaR). Do VaR and CVaR tell similar
or different stories about the risk of FGIF, P1, P2, and S&P500?
11. Do you have any suggestions on how your team can improve any of the analyses in Q1~Q8?

–> The detail is in the ‘Individual Assignment 1’ file
Also, look at the format of the sample financial modelling file and must use the excel file ‘Individual assignment data file’

–> need to use British writiaqwng and reference page
Don’t plagiarize