Assignment 1: ECON 410/691, Summer 2021 The purpose of this assignment is to make sure you are familiar with what happened in the stock market in the period of what is called the dot.com or tech...

excel


Assignment 1: ECON 410/691, Summer 2021 The purpose of this assignment is to make sure you are familiar with what happened in the stock market in the period of what is called the dot.com or tech bubble (although stocks outside of this industry were affected as well). This time can be defined as roughly 1995 to 2004. In the 1990s stock prices increased. A few months into 2000 the bubble first “popped” meaning that stock prices decreased quickly. Then generally they decreased more after that. You will also look at the stock market from 1920 to 1960 to get a sense of the market ups and downs at that time, especially during the time surrounding the 1929 market crash and the 1930s economic depression. All finance students should be comfortable finding financial data and using Excel. This assignment has you do both. If you had my ECON 374 or ECON 475 you know we spend a good deal of time doing both. Download the Excel file “Assignment 1 ECON 410-691 template” and use this as your template for the file you will submit with your answers. You will see four tabs (A, B, C, D) in the file. All assignments should be done individually. For this assignment you will submit one Excel file. Any other file format may not be read by Blackboard and therefore cannot be read by me. It cannot be graded if it cannot be read. Part A (2 points) Here’s the set up. You want to analyze an individual stock’s price during the 1995 to 2004 period. Pick one of the following stocks: Cisco Systems (CSCO) Texas Instruments (TXN) Micron Technology (MU) Go to Yahoo! Finance . Where you see the blank search box enter the ticker symbol (the letters such as CSCO) or the company name. Click historical data and use monthly frequency and you want to apply the time period January 1995-December 2004. What you need are the Adjusted Close prices for each month. What you see are the end-of-month prices. The Adjusted Close prices will have adjusted the actual stock prices by correcting for any stock splits and adding back any dividends. This means although the actual stock price at the time is not the same as the price shown due to the adjustments, you can consistently compare these prices and calculate returns over time. In my template spreadsheet I did this for the value of the NASDAQ 100 (^NDX) index of stocks. The NASDAQ index is very tech stock heavy. You can clearly see the rise and fall of the stock market in this picture! Replace my data with your chosen stock’s data. You will also delete my line graph and create the same line graph with your data. Your graph must have a title and have the axes labeled with the dates shown on the horizontal axis. If you have never created a basic Excel graph search YouTube for lots of tutorials to show you how. See the end of Assignment 0 instructions for examples of videos. Be sure to enter your stock’s symbol in cell B2. Part B (3 points) You will examine five mutual funds (portfolios of stocks or bonds). These are five of many funds offered by T. Rowe Price. PREIX is the Equity Index 500 fund that tracks the S&P 500 index of the U.S. stock market. PRSCX is the Science and Technology fund and focuses on these type stocks. PRMTX is the Communications and Technology fund and focuses on these type stocks. PRITX is the International Stock fund. PRCIX is a bond fund that we can compare to the stock funds. Using Yahoo! Finance, enter the symbol in the search box. From there go to Performance. Scroll down to find the monthly quarterly returns investors in these funds would have earned. You will enter the quarterly return for each from 1995 through 2004. Do this in Sheet B. Now use Excel to calculate the average return and the standard deviation of return for these time periods for each of the funds: Average 1995 Q1 through 2000 Q1show answers in cells E44 F44 G44 H44 I44 Average 2000 Q2 through 2004 Q4 show answers in cells E45 F45 G45 H45 I45 Standard deviation 1995 Q1 through 2000 Q1show answers in cells E46 F46 G46 H46 I46 Standard deviation 2000 Q2 through 2004 Q4show answers in cells E47 F47 G47 H47 I47 Calculate these for each of the return columns of the spreadsheet. Use the Excel functions: =AVERAGE(data range) =STDEV(data range) The data range you enter in the ( ) is something like C7:C53 for example, if that is the range of data you want to use. Depending on the version of Excel you have you may need to use STDEV.S(data range) if it is a newer version. STDEV works for older versions. Go to the help section of Excel (and YouTube has plenty of tutorials) if you have never used these functions before and want to know more. The idea is to use Excel to make all calculations, and not to use a calculator and then manually enter your answers! I will look at your spreadsheet to make sure I see that you used the Excel function in the cells, not just a number. Part C (3 points) Here use the five funds from Part B. Go to the sheet for Part C. Assume that you invested $50,000 in each of the five at the end of 1994 Q4. The 1994 Q4 return is irrelevant as you are not investing until the end of the quarter. Then you leave the accounts alone meaning you implicitly reinvest any dividends and capital gains back into the funds. Your investment value over time will fluctuate with the return on the fund. The return shown in Yahoo! includes the dividends and capital gains. Figure out how to get Excel to calculate the value of your investments in each fund at the end of each quarter. Start with the $50,000 at the end of 1994 Q and track this value until the end of 2004 Q4. I will leave it up to you to show me that you can figure out how use Excel to calculate this. I show the beginnings of the spreadsheet, you take it from there creating any columns you need and entering the formulas you need. Use Excel to find the answers, do not use a hand-held calculator to get the numbers and enter these. The idea is to use Excel to make all the calculations. As a hint, here is what the first few rows of answers might look like with some return numbers I made up so you get the idea of what this will look like. Show me that you can figure out how to get Excel to do this. You need to create the End of Quarter Portfolio Values columns for all five mutual fund investments. Where you see XXX in the spreadsheet be sure to enter the fund symbol so it is clear which it is. Initial Investment at the end of 1994 Q4: $50,000 QuarterReturnEnd of Quarter Portfolio Value 1994 Q4$50,000 1995 Q110.5%$55,250 1995 Q2-5.5%$52,211.25 1995 Q315.5%$60,303.99 and so on …. Once you have the five end-of-quarter portfolio value columns, get Excel to create a line chart like you did for Part A. Show the dates on the horizontal axis. All of the end-of-quarter portfolio values are graphed. Be sure to show the beginning $50,000 point for 1994 Q4 for all five funds. There will be five lines on one graph. Be sure to label the axes and have a title. Put the graph to the right of the data columns. It should be clear which line goes with which fund from the way you create and label the graph. Part D (2 points) In this last part you will look at the stock market back further in the time. Robert Shiller has data for the stock market going back many decades. Go to http://www.econ.yale.edu/~shiller/data.htm. Look for a hot link near the bottom of the webpage “long term stock, bond, interest rate and consumption data.” That will take you to the spreadsheet you use. I also put a copy of the Shiller spreadsheet with other files for the assignment just in case you cannot find this online. Column B is the value of the S&P index at the beginning of each year (not adjusted for inflation). Column K adjusts column B values for inflation so that years far apart in time can be better compared. The data in column K are all converted to “2013 dollars” from the way column K is calculated. Use column K here. As in the previous parts, you will plot the column K data. Do this for 1920 through 1960. You will get a good visual of the stock market during this time, especially before and after the 1929 stock market “crash.” As in the earlier parts, create a line graph, give it a title, label the axes. Put this in the Part D of the spreadsheet. Save the Excel file and submit it to Blackboard before the due date. I suggest that you change the name of the file with your work added so you do not submit the blank template file by accident (I have seen that before). And then check that you submitted the file you want to submit. I provide two submissions just in case you make an upload error like that. I only look at the second submission if you submit twice. 2 Data Shiller, R., U.S.Stock Price Data, Annual, with consumption, both short and long rates, and present value calculations. An Update of Data shown in Chapter 26 of Market Volatility, R. Shiller, MIT Press, 1989, and Irrational Exuberance, Princeton 2015.CAPE PDERRLONGCPIRealRCRealPP*P*rP*CRealDReturnln(1+ret)RealEP/EE10P/E10 S&PDividendsEarningsOne-YearLongConsumerReal Real PerRealPPresentPresentPresentRealDonRealEPriceTen-YearPrice CompositeAccruingAccruingInterestGovernmentPriceOne-YearCapitaStockValueValueValueS&PS&PEarningsEarningsAverage Earnings StocktotoRateBondIndexInterestConsumptionPriceof Realof Realof RealDividendCompositeRatioofRatio PriceIndexIndexYieldRate(See DividendsDividendsDividendsOne-YearReal Ten-Year Index10yrpost53ConsumptionConst rMarket rCons disc.EarningsEarningsEarnings 18714.440.260.46.355.3212.4640611.0475042083Tab)187182.0313058481112.01859950563.65037384314.7313849610.13580891470.12734509727.2790537862 18724.860.30.437.815.3612.6543921.0543202086in 2005187288.4405035027114.293957520563.96204423785.33887406510.08861541750.08490662967.6523861612.15 18735.110.330.468.355.5812.9398071.1335110979dollars187390.9388215759116.070752074464.04048104996.14383045990.02158970190.02135994538.564127307711.8837209302 18744.660.330.466.865.4712.3688961.1480763436187486.7583331609117.106059307967.83171173786.60077335790.12279027660.11581690559.201078014110.1304347826 18754.540.30.364.965.0711.5126511.1140547583187590.8106395304117.722195620872.63000674196.36919949380.11284278830.10691781197.64303939269.8695652174 18764.460.30.285.334.5910.8465751.0441389987187694.6887658086118.625268287676.3419643186.3138038374-0.1442791702-0.15581108955.892883581612.3888888889 18773.550.190.35.034.4510.941741.2452051495187774.7133454094119.646332524375.78642416324.74079278870.1488349310.13874832537.48546229812.6785714286 18783.250.180.314.94.349.22908931.169568707187881.0925082283122.409822393491.17243111695.00749044480.28989611740.2545616868.624011321610.8333333333 18793.580.20.384.254.228.27767930.8637832936187999.5934210691125.0696758003104.05774014834.61005764910.22896702680.2061740018.759109533411.5483870968 18805.110.260.495.14.029.99033061.11470108391880117.7869729356128.326628092989.6890927486.35631506730.33873470440.291724917511.979209165313.44736842118.3080360561 18816.190.320.444.793.79.41941980.96955281611881151.3291933331129.935983844596.06518572437.2382516517-0.0672922779-0.06966339399.952596021112.63265306128.575390279518.2147973735 18825.920.320