You are required to design a spreadsheet to help a portfolio manager carry out the analysis...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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. 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, is defined as St St-1 where S, and S₁.1 are the month-end prices of the stock at month t and t - 1 respectively. (10 marks) = t = 1 (10 marks) The portfolio manager decides to construct two portfolios - Portfolios A and B where Portfolio A is an equal-weighed portfolio. Wk Portfolio B is a variance-weighed portfolio where the allocation of each stock in the portfolio is given by 1 k=N vark 1 k=1 vark where vark is the variance of stock k in the analysis period. Such allocation policy will ensure that the stock 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. You can assume fractional holdings of stocks in the portfolio. (10 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. 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, is defined as St St-1 where S, and S₁.1 are the month-end prices of the stock at month t and t - 1 respectively. (10 marks) = t = 1 (10 marks) The portfolio manager decides to construct two portfolios - Portfolios A and B where Portfolio A is an equal-weighed portfolio. Wk Portfolio B is a variance-weighed portfolio where the allocation of each stock in the portfolio is given by 1 k=N vark 1 k=1 vark where vark is the variance of stock k in the analysis period. Such allocation policy will ensure that the stock 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. You can assume fractional holdings of stocks in the portfolio. (10 marks)
Expert Answer:
Answer rating: 100% (QA)
To assist the portfolio manager with the analysis of the US equity market using the providedpricesxl... View the full answer
Related Book For
Auditing An International Approach
ISBN: 978-0071051415
6th edition
Authors: Wally J. Smieliauskas, Kathryn Bewley
Posted Date:
Students also viewed these business writing questions
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-6. On December 12, Irene purchased the building where her store is located. She paid...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-4. Ivan and Irene paid the following in 2012 (all by check or can otherwise be...
-
Perform the indicated operations and simplify the result. Leave your answer in factored form. X x - 3 x + 1 x2 + 5x 24
-
What types of inquiries about a prospective client should an auditor make to third parties?
-
Suppose that the solution referred to in Exercise 6.8a is not saturated. (a) How many phases and components are present? (b) What is the variance (the number of degrees of freedom) of the system?...
-
The drug barbiturate is well known. It has different forms-mostly different types of barbituric acid. Consider the following list of barbituric acids: \{Candidate 1: 000077-02-1; Candidate 2:...
-
Table 3.1 in the last chapter presents R&E Supplies financial statements for the period 2011 through 2014, and Table 3.5 presents a pro forma financial forecast for 2015. Use the information in these...
-
On a bicycle, Serenity rides for 2 hours and is 24 miles from her house. After riding for 8 hours, she is 90 miles away. What is Serenity's rate?
-
Kevin Brownis the management accountant at and Sons Bank. Some of his colleagues attended a conference recently where they learned that machine learning can be used to accurately predict loan...
-
Use the table given below and answer the following question: 1. Calculate the percentage change in the base currency. 2. Calculate the cross rate between the Colombian peso and the Norwegian Krone....
-
The "war on drugs" has had a very real and significant impact on both communities and the corrections system. How has the "war on drugs" consumed valuable resources and what impact has it had on the...
-
Discuss how can people identify what they are really good at and pursue careers related to their natural abilities.
-
What role do social networks and interpersonal relationships play in both perpetuating and challenging existing social structures?
-
A contractor or a painter who was contracted to perform a specific project. Does their service cover by the Sale of Goods act? what factors required to complete the contract in a short time. Please...
-
Conclusion: Highlight key areas of the paper above and synthesize the information that is included Macro-Level Systems Perspective The target group of single mothers in Wisconsin has been impacted by...
-
The equivalent evaporation of a boiler is found to be 22500 kg per hour. Steam is produced at 20 bar pressure and 250C. The feed water temperature is 36C. 1850 kg of coal/hr having a calorific value...
-
The water in tank A is at 270 F with quality of 10% and mass 1 lbm. It is connected to a piston/cylinder holding constant pressure of 40 psia initially with 1 lbm water at 700 F. The valve is opened,...
-
Current Canadian GAAS through 2010 did not permit the auditor to refer to a going-concern uncertainty in the audit report when the uncertainty was properly disclosed in the financial statement notes....
-
What should an auditor do when violation of securities laws is suspected?
-
What is the concept of substance versus form in relation to financing and investment transactions and balances?
-
Discuss briefly the objective and requirements of the ISO 14001 scheme and Europe's eco-management and audit scheme (EMAS), and list the key differences in the requirements of the two schemes.
-
When the sum of the \(x\) values is equal to zero, the calculation of the coefficients of the regression line of \(Y\) on \(x\) is greatly simplified; in fact, their estimates are given by...
-
It is tedious to perform a least squares analysis without using a computer. We illustrate here a computer-based analysis using the MINITAB package. The observations on page 328 are entered in...
Study smarter with the SolutionInn App