Calculate returns and correlations, Portfolio Management

Assignment Help:

Place the information described in this stage in the worksheet titled "Analysis".

Step 1) Calculate the arithmetic average periodic return and standard deviation of periodic returns for all four stocks, the S&P 500, your two "combined asset portfolios, and the T-bill.  Use the "average" function for the arithmetic average and the "stdev" function for standard deviation. Also compute the geometric average as

                        =((endingcumul ret/beginning cumul ret)^(1/# of returns)) - 1

Step 2) Calculate the arithmetic averages and standard deviations separately for the sub-periods listed in the 2nd table in the Analysis worksheet.

Step 3)Build a table of correlations similar to the table below for the correlations among ALL of the INDIVIDUAL stock returns and the S&P 500 returns (the following table is an example for several stocks; however, the data is old so don't expect to get these answers).  IGNORE YOUR 2 "COMBINED-ASSET" PORTFOLIO FOR THIS STEP.

173_Calculate Returns and Correlations.png

The formula for calculating the correlations is the "Correl" function.  To use this function, open the function box and then choose the correlation function.  When the dialog box opens, select the data in pairs with the asset appearing in the left column being the "y's" and the assets appearing across the top being your "x's."  Alternatively, just type "=correl(" and follow Excel's prompts. You will need to do this for each asset pair; however, you do not need to complete the top half since it is a mirror image of the bottom half.  Use the available data for each pair of stocks.


Related Discussions:- Calculate returns and correlations

Efficient market hypothesis, ABC company issued an Initial Public Offering ...

ABC company issued an Initial Public Offering with 15% preferences shares of total subscription of 250000USD. The cost of capital for the preference shares is 12%. What is the valu

Adjustable-rate preferred stock - arps, It is a kind of preferred stock whe...

It is a kind of preferred stock where the dividends issued will change with a benchmark, most often a T-bill rate. The price of the dividend from the preferred share is set by a fi

HPY, If the HPY on a 2 year investment is 11.4% and you invested $8,000 at ...

If the HPY on a 2 year investment is 11.4% and you invested $8,000 at the start, what would be the ending value?

Hewlett Foundation Case Study, I need to analyze this case to answer 4 ques...

I need to analyze this case to answer 4 questions using the spreadsheet provided. Due Date: Sept 14

MIS, Comparison of knowledge management system with other systems

Comparison of knowledge management system with other systems

Market Efficiency, what is the random walk and the efficient market hypothe...

what is the random walk and the efficient market hypothesis?

Valuation of firm, Weighted average cost 13% cash flows: 1st Year = $20 mi...

Weighted average cost 13% cash flows: 1st Year = $20 million 2nd Year = $30 million 3rd Year = $40 million FCF grows at 7% after year 3 No of shares - 10 million Marketable securi

A-share, In the category of multi-class mutual funds, this is the class tha...

In the category of multi-class mutual funds, this is the class that is generally characterized by a loaded fee structure. Class A mutual fund units will normally have a front- or r

Student, 2. Compare and contrast the scope and construction of the followin...

2. Compare and contrast the scope and construction of the following three U.S. stock market indices: • the Dow Jones Industrial Average (DJIA); • the Standard and Poor 500 (S&P 500

A-shares, These are the shares in mainland China-based companies that trade...

These are the shares in mainland China-based companies that trade on Chinese stock exchanges like Shanghai Stock Exchange and the Shenzhen Stock Exchange. A-shares are usually only

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd