Financial Modeling

The University 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 U ’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 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 U constructs portfolios using only historical information between January 2016 and December 2019, and cannot short sell any risky assets. The risk- free rate is constant at 0%.

1. 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.8. 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 2019.
2. How should U 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.)
3. How should U incorporate INSUR into its risky portfolio? Refer to this portfolio as P2. Compare the compositions of P1 and P2 and explain how and why the weights are different.
4. Although 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 U ’s cost of debt, you collected yield curve data from bonds issued by institutions with risk profile 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 U 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.

5. The CFO also wants you to help construct the optimal complete portfolio for U That is, you need to decide how to allocate capital between the risky portfolio and the risk-free asset and whether U should issue bonds to finance its investment. To ensure that the University can maintain its normal operation, U ’s complete portfolio has to offer a monthly return of 1% or higher. How should U construct its complete portfolio when (1) INSUR is available, and (2) INSUR is not available? Continue to assume that the risk-free rate is 0%. Note that U 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.

Deliverable:
Write up a report that answers the above five questions. Make sure the results are presented in a logical order. You should begin by briefly introducing the purpose of this report. Next, provide detailed 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:

6. In Q1, monthly volatility is estimated using a decay factor of 0.8. Is this a reasonable choice? Explain how you would estimate the decay factor and show your results. You can collect additional data if necessary.
7. Use more than one models to estimate UE’s cost of debt in Q4. Do you obtain very different estimates from different models? Explain why these models generate very different (or similar) results.
8. Do you have any suggestions for U ’s CFO? These can range from asset class choices, estimation methods, risk management practice, or anything that can help U improve its investment decision. You need to assume that U cannot short sell any risky assets when making suggestions. Provide empirical/numerical evidence to support your suggestions.

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.02, 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) 200 times the insurance premium, and (2) the monthly standard deviation on the S&P 500 return in excess of 0.02. If the return on S&P 500 is positive or if the monthly standard deviation does not exceed 0.02, 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.02
(???????? − 0.02) × 20000% ???????? ???????? < 0 ???????????? ???????? > 0.02

, where rt 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.8 and historical monthly return prior to month t (i.e., rt is not used to estimate t).
As an example, suppose an investor holds INSUR for the month of January 2022. The monthly return on S&P 500 for January 2022 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 20,000% × (0.0415 – 0.02) = 430%.

Appendix B: Data File Details

The Excel file “Data.xlsx” has two worksheets. The first worksheet “SP500” provides monthly historical data on the S&P 500 Index since January 2010. You can use data prior to 2016 for volatility estimation but not for portfolio construction.
The second worksheet “Yields” provides yield to maturity on various zero-coupon bonds with various maturity. These bonds are issued by institutions with risk level similar U s. You do not have to explain data source for these yields in your writeup. In this worksheet, column A is time to maturity in years. Column B is (annual) yield to maturity in percentage terms. As an example, data in row 2 show that a zero-coupon bond that will mature in 0.0896 years has an annual yield of 3.1571%.