RMIT University

School of Economics, Finance and Marketing

BAFI3252 Modelling in Finance

Semester 1, 2025

Assignment information:

Due Date: Week 10, Monday 12^thMay 2025, 5:00PM(Melbourne Time)

Penalties: Marks will be deducted for late submission of this assignment.The penalty is 10% of the total mark for each day late including weekends and RMIT University holidays, if any.

Word limit: N/A

Grading: The marking rubric is available on Canvas.You are encouraged to use your initiative and seek other sources for ideas on what else to include. Full acknowledgement must be made whenever you quote, paraphrase or merely draw on the work of others. This is an individual task, DO NOT copy codes from others and from public resources (i.e., internet).

Submission: You will need to submit your Excel file on Canvas. Save the file as Macro-Enabled Workbook. All the VBA programs/functions need to be clearly labelled.

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.15% (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. Extend Questions 3, 4 & 5 by using considerable VBA programming (calling Solver from VBA is a suitable solution) to automate the process.
   
   

Part B Simulating feasible portfolios

1. Write a VBA subroutine to generate at least 500 feasible portfolios. In your subroutine:
   1. Generate six random numbers
   2. Assign the j^thrandom number divided by the sum of the six random numbers as the weight for stock j.
2. 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.
3. 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.
   
   

Part C User interface and layout

1. Design a friendly user interface with Excel form controls and allow users to choose:
   1. Different level of interest rates. Note: This will affect the Capital Market Line and the tangent portfolio.
   2. The user is able to choose whether to allow short selling.
   3. All inputs and outputs are clearly labelled and shown on the same Excel worksheet and the model is well structured without excess options.
2. 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).
3. Think about other (additional) features you want to add and improve your portfolio model.
   
   

Part 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.