Reference no: EM132452787

Project Part - Calculating Historical Statistics and Building a Fund

In project part, you will use the stocks you chose in part one, calculate returns, run statistics, and build a fund.

Calculate monthly returns for your securities.

Download the spreadsheet "Monthly S&P 500 Total Returns". You can see the format. I am looking for 5 years of monthly returns starting in 1/31/2013 and ending in 12/31/2017. Just like the data in project one, there are many resources to get this data. But, you can get it from Yahoo finance. Navigate to finance.yahoo.com, enter a selected ticker in the search bar, and then navigate to the "Historical Data" section of that page. Adjust the "Time Period" and "Frequency" to reflect the following, and then hit "Apply":

Next hit the "Download Data" button and you will have a spreadsheet of 5 years of daily prices. (Note: if you download this as monthly instead of daily, your calculations will be slightly off because for some reason this doesn't pull the actual final day in the month)

Return is usually calculated close to close. Additionally, for stocks, yahoo adjusts the prices for corporate actions in the "Adj Close" column. So the monthly return is just a simple ratio of this month's Adj Close divided by last month's Adj Close*. For example, the monthly return for Dec 2017 is as follows:

r_12/2017 = (AdjClose_12/29/2017)/(AdjClose_11/30/2017) - 1

Note that the calculation is as of the last trading date of the month. I have uploaded a spreadsheet called "AAPL Yahoo data" with the calculations done as an example. You can use this spreadsheet as a template for your calculations. However, you will be expected to make calculations like these yourself.

Calculate a table of statistics.

Build a table, Table 2, which calculates some statistics on the monthly returns you calculated for your 11 stocks. Please include the Arithmetic Average, the Geometric Average Return, the Continuously Compounded Rate of Return, the Standard Deviation, the Variance, and the Sharpe Ratio.

Build a fund.

Build a table, Table 3, which reports your portfolio holdings as of 12/31/2017. This will be our starting point for much of the analysis we will do in subsequent project parts. For each of your eleven companies, you will need the price per share (as of 12/31/2017). Use the last closing price (not the Adj Close) before 12/31/2017. After that, choose an arbitrary amount of shares. Calculate the value of each holding by multiplying the price by the number of shares. And then, based on the values, calculate the percent weight of each holding. Lastly, Sum up the portfolio values to get a total (and also sum up the weights to verify they equal 100%). I recommend making your total portfolio value between $5 and $10 million and also that you choose enough shares of each security to have a meaningful weight.

Attachment:- Calculating Historical Statistics and Building a Fund.rar