You are a portfolio manager for the XYZ investment fund. The objective for the fund is to
Question:
You are a portfolio manager for the XYZ investment fund. The objective for the fund is to maximize your portfolio returns from the investments on four alternatives. The investments include (1) stocks, (2) real estate, (3) bonds, and (4) certificate of deposit (CD). Your total investment portfolio is $1,000,000. Investment Returns Based on the returns from the past five years, you concluded that the investment annual returns on stocks are 10%, on real estates are 7% on bonds are 4% and on CD is 1%. Risk Constraints However, you also have to analyze the risks associate with each investment category. A wildly used risk measurement parameter is called Value at Risk (VaR). (Note: VaR measures the risk of loss on a specific portfolio of financial assets.) For example, given a million dollar stock investment, if a portfolio of stocks has a one-day 4% VaR, there is a 5% probability that the stock portfolio will fall in value by more than 1,000,000 * 0.004 = $4,000 over a one day period. In the portfolio, the VaR for stock investments is 6%. Similarly, the VaR for real estate investment is 2% and the VaR for bond investment is 1% and the VaR for investment in CD is 0%. To manage the portfolio, you decided that at 5% probability, your VaR for stocks cannot exceed $25,000, VaR for real estate cannot exceed $15,000, VaR for bonds cannot exceed $2,500 and the VaR for CD investment is $0. Include the objective function and all constraints, including any non-negativity constraints. Then, you should present the optimal solution, based on your work in Excel. Explain what the results mean. Please set up your problem in Excel and find the solution using Solver. Clearly label the cells in your spreadsheet. You will turn in the entire spreadsheet, showing the setup of the model, and the results.
Essentials of Investments
ISBN: 978-0078034695
9th edition
Authors: Zvi Bodie, Alex Kane, Alan Marcus