You are required to design a spreadsheet to help a portfolio manager carry out the analysis of the US equity

market. The spreadsheet you have designed will allow the portfolio manager to evaluate how his portfolio

is performing depending on the strategy he has pursued.

a) Given the daily historical closing prices of some stocks from 2005 to 2020 in file prices.xlsx,

retrieve the month-end dates

1

and prices of the stocks.

(10 marks)

b) Given the following start and end dates of analysis,

start date of analysis = 31-Jan-2005,

end date of analysis = 31-Dec-2010,

make use of the month-end prices bounded by start and end dates to determine the monthly mean

return and volatility of the stocks.

The monthly return of month t, rt is defined as

=

1

where St and St-1 are the month-end prices of the stock at month t and t - 1 respectively.

(10 marks)

The portfolio manager decides to construct two portfolios Portfolios A and B where Portfolio A is an

equal-weighed portfolio.

Portfolio B is a variance-weighed portfolio where the allocation of each stock in the portfolio is given by

=

1

1

where vark is the variance of stock k in the analysis period. Such allocation policy will ensure that the stock

with the lowest (highest) variance has the highest (lowest) allocation in the portfolio.

1 The Excel function MONTH(x) and YEAR(x) can be used to evaluate the month and year of a given date x. Financial Modelling Nov/Dec 2021

An initial wealth of $1,000,000 is used to create the Portfolios A and B respectively on portfolio

construction date. You can assume portfolio construction date is the next trading day after the end date of

analysis.

c) Make use of the data in the prices.xlsx to report the month-end wealth of Portfolios A and B from

the portfolio construction date onwards. You are also required to report the mean return and

volatility of the portfolios.

(10 marks)

d) Plot the monthly portfolio wealth in a diagram, from the portfolio construction date onwards,

allowing the user to select the plotting frequency, in units of months. For example, a user might

prefer monthly plotting while another user might prefer semi-annual plotting.

(10 marks)

You can assume fractional holdings of stocks in the portfolio.

Question (Total 40 marks) You are required to design a spreadsheet to help a portfolio manager carry out the analysis of the US equity market. The spreadsheet you have designed will allow the portfolio manager to evaluate how his portfolio is performing depending on the strategy he has pursued. a) Given the daily historical closing prices of some stocks from 2005 to 2020 in file prices.xlsx, retrieve the month-end dates and prices of the stocks. (10 marks) b) Given the following start and end dates of analysis, start date of analysis = 31-Jan-2005, end date of analysis = 31-Dec-2010, make use of the month-end prices bounded by start and end dates to determine the monthly mean return and volatility of the stocks. The monthly return of month t, n is defined as St rt = 1 St-1 where Stand St-1 are the month-end prices of the stock at month t and t - 1 respectively. (10 marks) The portfolio manager decides to construct two portfolios Portfolios A and B where Portfolio A is an equal-weighed portfolio. Portfolio B is a variance-weighed portfolio where the allocation of each stock in the portfolio is given by 1 vark Wk = vark where varx is the variance of stock k in the analysis period. Such allocation policy will ensure that the stock with the lowest (highest) variance has the highest (lowest) allocation in the portfolio. The Excel function MONTH(x) and YEAR(x) can be used to evaluate the month and year of a given date x. An initial wealth of $1,000,000 is used to create the Portfolios A and B respectively on portfolio construction date. You can assume portfolio construction date is the next trading day after the end date of analysis. c) Make use of the data in the prices.xlsx to report the month-end wealth of Portfolios A and B from the portfolio construction date onwards. You are also required to report the mean return and volatility of the portfolios. (10 marks) d) Plot the monthly portfolio wealth in a diagram, from the portfolio construction date onwards, allowing the user to select the plotting frequency, in units of months. For example, a user might prefer monthly plotting while another user might prefer semi-annual plotting. (10 marks) You can assume fractional holdings of stocks in the portfolio. Question (Total 40 marks) You are required to design a spreadsheet to help a portfolio manager carry out the analysis of the US equity market. The spreadsheet you have designed will allow the portfolio manager to evaluate how his portfolio is performing depending on the strategy he has pursued. a) Given the daily historical closing prices of some stocks from 2005 to 2020 in file prices.xlsx, retrieve the month-end dates and prices of the stocks. (10 marks) b) Given the following start and end dates of analysis, start date of analysis = 31-Jan-2005, end date of analysis = 31-Dec-2010, make use of the month-end prices bounded by start and end dates to determine the monthly mean return and volatility of the stocks. The monthly return of month t, n is defined as St rt = 1 St-1 where Stand St-1 are the month-end prices of the stock at month t and t - 1 respectively. (10 marks) The portfolio manager decides to construct two portfolios Portfolios A and B where Portfolio A is an equal-weighed portfolio. Portfolio B is a variance-weighed portfolio where the allocation of each stock in the portfolio is given by 1 vark Wk = vark where varx is the variance of stock k in the analysis period. Such allocation policy will ensure that the stock with the lowest (highest) variance has the highest (lowest) allocation in the portfolio. The Excel function MONTH(x) and YEAR(x) can be used to evaluate the month and year of a given date x. An initial wealth of $1,000,000 is used to create the Portfolios A and B respectively on portfolio construction date. You can assume portfolio construction date is the next trading day after the end date of analysis. c) Make use of the data in the prices.xlsx to report the month-end wealth of Portfolios A and B from the portfolio construction date onwards. You are also required to report the mean return and volatility of the portfolios. (10 marks) d) Plot the monthly portfolio wealth in a diagram, from the portfolio construction date onwards, allowing the user to select the plotting frequency, in units of months. For example, a user might prefer monthly plotting while another user might prefer semi-annual plotting. (10 marks) You can assume fractional holdings of stocks in the portfolio