MAT 144 GCU Calculate Gain & Loss of Value for Stock as Of Each Date Excel Template

Hi Class,

This should be sort of fun, see how much money you can make … or lose! Start by downloading the attached template.

The video “Tech Hints QR 2-7” in the media resources section of the eBook covers the calculations you’ll be doing for this DQ. (Our template does look a little different, but it is essentially the same.)

Here is a video I made to help: https://youtu.be/iSMKu1B1fVc

Here are specific instructions for completing the DQ:

1. In the initial investment section (A1:F4), select three different stocks to track. Select your stocks from the New York Stock Exchange listings directory at https://www.nyse.com/listings_directory/stock. Use the “Filter” text box to search for specific stocks; click on the stock Symbol in the list at the right to go to the stock quote page for the stock to see more details, including historical prices. Towards the bottom, you can find a chart that will let you input date ranges. You can input the same date for start and end to look up your specific day.
2. Use a purchase date of 1 year ago, for each stock. So if it is Feb 18, 2020, then you look at the price Feb 18, 2019. Find each stock’s price on that date and enter it into the Price per Share field. The table does not contain data points for weekends. If your date is on a non-business day choose a date close to your date listed. Assume that the number of shares purchased is 100 for your first stock, 200 for your second stock, and 300 for your third stock and that the fees are $5, $10, and $15 (total fee) for your first, second, and third stocks. Enter an appropriate formula in the Initial Investment field, and calculate an initial investment total in cell F5.
3. In cells A14:F16, continue your analysis for your first stock. Use the closing price 9 months ago, 6 months ago, 3 months ago, and the latest for the second through fifth date sections. Just get close to these dates, basically, we are looking at quarterly fluctuations. Look up the closing price per share on each of these dates and enter those in column D. Calculate a current value on each date in column E. Calculate the gain or loss to date (starting from the initial purchase date) in column F.
4. Repeat the analysis from step 3 for your second and third stocks, using cells H14:M16 for the second stock and O14:T16 for your third stock.
5. In column V, calculate (in cells V14:V16) the total gain or loss for all three stocks on each of the three dates.

Hint: To get the stock value on a particular date from the stock quote page, scroll down to the stock chart or stock table below it.

Hi Class,

I made a video to help with the website. Check it out if you are having trouble navigating the website.

https://youtu.be/Yt3aDSypgC0