# 这是一篇来自英国的关于财务建模个人作业代写

This assignment is an extension of the Group Assignment. The University of Exeter (UE) hired your team to make recommendations on the construction of its endowment fund. You have decided to use the following ETFs and investment trusts to proxy the six asset categories that UE is considering investing in:

iShares Core FTSE 100 ETF (ISF.L)

iShares Core MSCI Total International Stock ETF (IXUS)

Vanguard Total Bond Market Index Fund ETF Shares (BND)

Vanguard S&P 500 ETF (VOO)

iShares U.S. Real Estate ETF (IYR)

Grayscale Bitcoin Trust (GBTC)

A financial institution contacted UE’s Chief Financial Officer (CFO) and offered an exotic insurance product (denoted by INSUR in the text below) that may help UE manage its investment risk. INSUR matures in one month. If UE chooses to purchase INSUR, it has to pay insurance premium at the beginning of the month. At the end of the month, INSUR makes payment only when the return on the S&P 500 Index is negative and its volatility is above a certain threshold. Appendix A provides details on this insurance product. UE can purchase this insurance every month if it chooses to do so.

In all your analysis, assume UE constructs portfolios using only historical information between January 2016 and December 2020. You can use the historical average return as your proxy for the expected return. Also assume that UE cannot short sell any risky assets when constructing risky portfolios. The risk-free rate is constant at 0%.

- Calculate time-varying monthly variance of return on the S&P 500 Index using the Exponentially Weighted Moving Average (EWMA) method with a decay factor (
**) of 0.85.

Historical data on the S&P 500 Index can be found in the worksheet “SP500.” You can use data before 2016 for volatility estimation purpose. Calculate summary statistics of monthly returns on INSUR using historical data from January 2016 to December 2020.

- How should UE structure its risky portfolio using the six asset categories listed above (i.e., you should exclude INSUR)? Refer to this portfolio as P1. (Note: This question is the same as Q4 in the Group Assignment.)

- How should UE incorporate INSUR into its risky portfolio? Refer to the risky portfolio that includes INSUR as P2. Compare the compositions of P1 and P2 and explain how and why the weights are different.

- Although UE cannot short sell any risky assets, it can issue bonds to finance its investment.

If necessary, UE can issue a 40-year zero-coupon bond. To estimate UE’s cost of debt, you collected yield curve data from bonds issued by institutions with risk profiles similar to UE’s. These yield data can be found in the worksheet titled “Yields,” and Appendix B provides data details.

Use these yield curve data to estimate the yield to maturity of UE’s 40-year zero-coupon bond. You can estimate the yield using more than one yield curve models, but you have to determine which estimate is most proper for Q5 below.

- The CFO also wants you to help construct UE’s optimal complete portfolio, which includes the risk-free asset. That is, you need to decide how to allocate capital between the optimal risky portfolio and the risk-free asset and whether UE should issue bonds to finance its investment. To ensure that the University can maintain its normal operation, UE’s complete portfolio has to offer a monthly return of 0.8%. How should UE construct its complete portfolio when (1) INSUR is not available, and (2) INSUR is available? Continue to assume that the risk-free rate is 0%. Note that UE cannot borrow at the risk-free rate. Its (annual) borrowing rate is the yield to maturity of the 40-year zero-coupon bond that you calculated in Q4. Appendix C provides more details on the complete portfolios.

*Deliverable: *

Write up a report that answers all the above questions. Make sure the results are presented in a logical order. You should begin by briefly introducing the purpose of this report. Next, providedetailed explanations on your methods and numerical results. You can skip the data section if your dataset is exactly the same as that used in the group assignment. If you collect any new data for this assignment, you should provide details on your new data. Lastly, the conclusion section should provide a summary of your recommendations. 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 credit, consider incorporating some of the following into your analysis:

- In Q1, monthly volatility is estimated using a decay factor of 0.85. Is this a reasonable choice?

Explain how you would estimate the decay factor and show your results. You can collect additional data if necessary.

- Use more than one models to estimate UE’s cost of debt in Q4. Do you obtain very different estimatesfrom different models? Explain why these models generate very different (or similar) results.

- Do you have any suggestions for UE’s CFO? These can range from asset class choices, estimation methods, risk management practice, or anything that can help UE improve its investment decision. Assume that UE cannot short sell any risky assets when constructing its risky portfolio. Provide empirical/numerical evidence to support your arguments.

**Appendix A: Details on INSUR **

INSUR is an exotic insurance product that aims to help investors manage its exposure to market risk. This product requires policyholders (i.e., buyers of INSUR) to pay insurance premium (i.e.,purchasing price of INSUR) at the beginning of the calendar month. INSUR matures at the end of the month and makes payment (if any) to policyholders according to the performance of the S&P 500 Index in that month. If (1) the return on the S&P 500 Index is negative and (2) the monthly standard deviation of return on S&P 500 exceeds 0.025, INSUR will reimburse the insurance premium paid at the beginning of the month and provide additional compensation to its policyholders. The additional compensation amount is the product of the following two components: (1) 150 times the insurance premium, and (2) the monthly standard deviation on the S&P 500 return in excess of 0.025. If the return on S&P 500 is positive or if the monthly standard deviation does not exceed 0.025, INSUR does not make any payment at the end of the month and the policyholder loses all her investment. That is, the monthly return of holding INSUR is

-100% 𝑖𝑓𝑟𝑡≥0𝑜𝑟𝜎𝑡≤0.025

(𝜎𝑡− 0.025)×15000% 𝑖𝑓𝑟𝑡<0𝑎𝑛𝑑𝜎𝑡>0.025

, where *r**t *is the return on the S&P 500 Index for month *t*, ***t *is the monthly (non-annualized) standard deviation of return on S&P 500. 𝜎𝑡 2 is estimated using the Exponentially Weighted Moving Average (EWMA) method with a decay factor of 0.85 and historical monthly return prior to month *t *(i.e., *r**t *is not used to estimate ***t*).

As an example, suppose an investor holds INSUR in January 2022. Also assume that the monthly return on S&P 500 for the month is -0.6%, and the monthly standard deviation of return on S&P 500 is 0.0415, calculated using historical data until December 2021. In this case, this investor’s monthly return for holding INSUR is 15,000% × (0.0415 – 0.025) = 247.5%.