All I need is an excel document with little a few words to elaborate the working right below the excel working.

timer Asked: Dec 17th, 2018

Question description

You will be provided with a SPECIFIC investment objective"you run an endowment fund of $1,000,000 and it must go into perpetuity, meaning that it must last for at least the next 100 years. Additionally, you must pay out 5% of the principal every year for scholarships to deserving students."

You will also be provided a list of asset classes with their expected 5-year expected returns and their expected standard deviations.

  • 1)Year 0: Based upon your specific investment objective, build a diversified portfolio containing all the provided asset classes. Within each asset class, you will include a specific security, (stock, bond, etc) an ETF and a mutual fund. You will start with a $1,000,000 in cash and this cash is to be fully allocated among the asset classes while keeping in mind your risk tolerances and return expectations. This will be your Strategic Asset Allocation.
    1. Explain in narrative form why you added the specific securities that you selected. For example, why did you choose a high dividend stock, or a value mutual fund, or a stock with a high P/E.
    2. Calculate the weighted Expected Return of your new portfolio. Is it enough to accomplish your objective?
    3. Calculate the expected annual income. Does this meet your objective?
    4. Calculate the weighted Standard Deviation of your portfolio. Can you bear this risk or do you need to decrease or increase this risk?
    5. Report if your first attempt has the potential to meet your objectives in terms of income, growth of principal and risk. If not, tell me what changes need to be made.
  • 2)We added some bonds to the portfolio with the intention of potential reducing the overall risk of the portfolio. Let’s prove this “risk reduction” by reviewing the revised standard deviation of this portfolio that now holds more fixed income. Does your fixed income provide risk reduction?
  • 3)Year 1: Assume after the first year, your portfolio values have changed based on changes in the market. All Domestic stocks have increased by 10%; International stocks increased by 20%; Emerging markets increased 23%; while bonds have declined by 5%.
    1. Now, calculate the new value of your portfolio incorporating these market changes and update these values in an Excel spreadsheet.
    2. Then rebalance your portfolio back to the original asset allocation weights set with your SAA. Note any capital gains are subject to a 15% capital gains tax
  • Year 2: Introduce some constraints to your portfolio and show me the effects on expected performance and standard deviation
    • Examples may include NO emerging Markets
    • Withhold $200,000 in cash to buy a house
  • Year 4: The markets have now stabilized but you are fearful of another decline in equities and you want to reduce the overall risk of the portfolio. Thus, show me how can use options on your stocks to reduce risk. Also, show me how you can use the futures markets to decrease the risk of the portfolio.
  • With regards to the mutual funds you selected, select three of these funds and compile the historical 5 year performance returns. Additionally, show me the Sharpre Ratio and the Alpha (the difference of the performance of the fund versus the appropriate benchmarch. Over these same 5 years, did active or passive management return more favorable results?
  • With regards to a select bond position in your portfolio, calculate the duration of your bond as of the date of purchase and talk to me about the potential return effects with an increase and a decrease of 100 basis points in interest rate.
  • Year 5: After 5 years of managing your portfolio, it is time to review the performance of your portfolio and to evaluate if this portfolio is meeting your objectives. After 5 years, the historical returns of your asset classes has been the following:

Asset Class

5 year annualized returns

US Bonds


High Yield Bonds


Large Cap Equity


Small Cap Equity


International Equity


Emerging Markets Equity


US Inflation Linked Bonds






Alternative Investments


Thus, after managing your diversified portfolio for 5 years, are you on track to reach your original investment objective? If yes, show me proof. If no, what changes are needed

Tutor Answer

(Top Tutor) Studypool Tutor
School: UCLA
Studypool has helped 1,244,100 students
flag Report DMCA
Similar Questions
Hot Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors