Assignment Task

Background information

The objective of this assignment is to learn how the Modern Portfolio Theory works in practice. The portfolio theory shows that an investor can construct a portfolio of multiple assets that will maximize returns for a given level of risk. Likewise, given a desired level of expected return, an investor can construct a portfolio with the lowest possible risk. Portfolio constructions rely on statistical measures such as mean returns, variance, and correlation.

You are given six stocks in this assignment. Based on their historical data, you will first compute summary statistics and variance-covariance matrix for the stocks. You will then draw efficient frontier, identify global minimum variance portfolio, tangent portfolio and simulate portfolios with different weights. You are required to use VBA and advanced Excel features to create a dynamic portfolio model.

Task

Part

A. The basics

1. Use Excel functions to compute monthly summary statistics for the six stocks - mean return, sample standard deviation, sample variance, and correlations (VBA not required).

2. Write your own VBA function to compute the variance-covariance matrix for the six stocks. The function needs to be able to handle matrices of different size. Note: If you are unable to complete the VBA function, generate the sample variance-covariance matrix using Covariance in Data Analysis so you can complete the other tasks. Full marks are earned if your program does not rely on calling Excel worksheet functions like COVAR from VBA.

3. Generate an efficient frontier allowing for short sales. You will need to use matrix multiplications to compute portfolio risk and return.

4. Assume the risk-free interest rate is 0.2% (return per month). Identify the global minimum variance portfolio and the tangent portfolio based on the given risk-free rate on the efficient frontier. Draw the efficient frontier and Capital Market Line.

5. Extend Questions 3 & 4 not allowing for short selling. You need to use Excel Solver for this task. VBA is not required. Note: Optimal portfolios are those with the highest Sharpe ratio given the risk-free rate.

6. The Capital Market Line connects the risk-free rate with the tangency point (i.e. a portfolio with the highest Sharpe ratio)

7. The Sharpe ratio is defined as (portfolio return – risk-free rate) / portfolio standard deviation. In the lecture, we use a constant to represent risk-free rate. Different constant values result in different optimal portfolios.

8. Extend Questions 3, 4 & 5 by using considerable VBA programming (calling Solver from VBA is a suitable solution) to automate the process.

B. Simulating feasible portfolios

• Write a VBA subroutine to generate at least 500 feasible portfolios. In your subroutine:
• Generate six random numbers
• Assign the j ^th random number divided by the sum of the six random numbers as the weight for stock j.
• Plot the simulated portfolios as a scatter graph in the risk-return diagram (together with efficient frontier previously generated in Part A). Both the minimum variance and highest Sharpe ratio portfolios should be highlighted on your graph.
• The user has a choice for basically an unlimited number of simulations. Improve the efficiency and speed of your model is crucial, and we intend to test this feature with more than 500 portfolios.

 C. User interface and layout

• Design a friendly user interface with Excel form controls and allow users to choose:
• Different level of interest rates. Note: This will affect the Capital Market Line and the tangent portfolio.
• The user is able to choose whether to allow short selling.
• All inputs and outputs are clearly labelled and shown on the same Excel worksheet and the model is well structured without excess options.
• Formulas and subroutines are linked so the model can handle changes in data (assuming replacing one stock with another with the same number of observations).
• Think about other (additional) features you want to add and improve your portfolio model.

D. Comments and structure

1. Provide instructions and highlight features (other than those required) of your model. Write your comments in the textbox (insert->textbox).

2. Add comments to your VBA code, especially on any special techniques employed.

3. Properly formatting and indenting your code. To indent code, simply press the TAB key. Press the tab key again to add a second code indention. Generally, you want to add one indentation for each code block such as IF statements and Loops.

This BAFI3252 – Statistics has been solved by our PHD Experts at UnilearnO. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK and US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics and referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.