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
-
Shouldnt those who build data warehouses trim the data before they load it to data warehouses? Why do they usually not cut any data from transactions?
-
Determine the volume of water contained in a 1 in diameter (nominal) chlorinated polyvinyl chloride (CPVC) pipe with a standard dimension ratio (SDR) of 13.5 per foot of length. Use actual pipe...
-
As in Example 2.1, camera recycle times might use the sample space \(S=R^{+}\), the set of positive real numbers. Let \[ E_{1}=\{x \mid 10 \leq x <12\} \quad \text { and } \quad E_{2}=\{x \mid 11 0\}...
-
Regular Dividends the balance sheet for apple pie Corp, is shown here in market values terms. There are 5,000 shares of stock outstanding. The company has declared a dividend of $1.20 per share. The...
-
I. TITLE: TEMPERATURE MEASUREMENT II. OBJECTIVES This simple experiment on temperature measurement is aimed to compare and explain the measured temperature data from the standard temperatures of...
-
Player Company acquired 60 percent ownership of Scout Companys voting shares on January 1, 20X2. During 20X5, Player purchased inventory for $20,000 and sold the full amount to Scout Company for...
-
Two people are taking turns tossing a pair of coins; the first person to toss two alike wins. What are the probabilities of winning for the first player and for the second player?
-
A company is deciding on at least three of five similar processes that should be implemented. The net present value (NPV) and capital required in thousands of dollars for each process are shown in...
-
1. Calculate EFN (reconciled with Income Statement) 2. Prepare a projected income statement and balance sheet for 2019 (reconciled) Cash Marketable securities Accounts receivable Inventory Total...
-
Two identical strings 7, and 12 are fixed at the boundaries. Although they are vibrating with the sam frequencies, they show different mode shapes due to the tension in the strings. (See the figures...
-
Let F2 = {0, 1} with the usual arithmetic modulo 2. The set F2 = {0,1}" consisting of all n-bit vectors (vectors of length n with entries 0 or 1) is an n-dimensional vector space over F2, with the...
-
(Solving for r with annuities) Nicki Johnson, a sophomore mechanical engineering student, receives a call from an insurance agent, who believes that Nicki is an older woman ready to retire from...
-
Case study : Detroit Bikes: Becoming the Biggest Bicycle Manufacturer in North America. Please Answer the following questions. 1. Mike Gentile, the communications director, was tasked with massively...
-
Rewrite Programming Exercise 7.5 using streams. Display the numbers in increasing order. Data from Programming Exercise 7.5 Write a program that reads in 10 numbers and displays the number of...
-
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?
-
What is a P-value for a hypothesis test?
-
You plan a survey to estimate the proportion of students on your campus who carry an iPad regularly. How many students should be in the sample if you want (with 95% confidence) a margin of error of...
-
What is a hypothesis test?
Study smarter with the SolutionInn App