Quiz 1
Submission:
You only need to submit an excel file, named QuizExcel.PolyUID.LastName.FirstName.xlsm.
For example, your name is George Washington and your ID 1900000D, then the excel file to submit is QuizExcel.1900000D.Washington.George.xlsm
Q1 (EXCEL): Q1 sheet provides you the daily close price of ETF SPY and ETF TLT, representing stock market and debt market, respectively, in the last 5 years. You need to:
• (2pt) Flip the data and display the latest data first without using any formula or built-in function. Currently, the oldest data is displayed the first and latest displayed in the bottom; your task is to reverse the sequence to display the latest data first.
(3pt) Do the reverse by using any built-in excel formula.
• (2pt) Calculate the mean and std of the daily return using all the provided historical data.
Q2 (data table) (5pt): Given the two risky assets and the riskless asset, we build a portfolio. The forflio is composed on x% of riskless asset and (1-x)% of risky assets. Within the risky assets group, y% will be allocated to SPY, and (1-y)% will be allocated to TLT. In summary, riskless asset is x%; SPY is (1-x)*y%; TLT is (1-x)(1-y)%.
Use data table to calculate the portfolio return under different x (0,0.1, ……, 1) and y(0,0.1, ……, 1)
Q2 (VBA) (3pt+3pt+2pt): Consider now you are a financial advisor. Your client consults you for his potential investment plan. He provides the following parameters.
• Initial pension money: $1,000,000
• His yearly drawdown will be $100,000.
• In year 5,6,7,8, he needs to have $50,000 more drawdown to pay for his daughter’s college tuition in New York University.
•
He is considering an investment portfolio with X% of stock (SPY) and 1-X% of riskless asset (yearly return 3%). You need to help him to identify what is the best X%. To simplify the problem, we specify, X can only be (0, 10%, 20%, 30%, 40%, 50%, 60%, 70%, 80%, 90%, 100%).
For future return, we assume the return of SPY follows normal distribution with mean and std derived from those calculated in Q1.
Note that, we use the following formulas:
yearly mean = 250*daily mean
yearly std = sqrt(250)*daily std
You are going to run a number of Monte Carlo simulations for him. The number here is a parameter.
The selection standard is:
• For over 80% of the simulations, he has positive asset by the end of year 10.
• The average remaining asset is the highest
We already provide you a partial VBA program and you need to read it and finish it to achieve the above goal.