Question

hi, got this question wrong on my exam and would like the correct answer for the final

1. The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and NVidia (NVDA) for the first half of 2017.

Ticker	6/30/2017	5/31/2017	4/30/2017	3/31/2017	2/28/2017	1/31/2017
ORCL	50.14	45.39	44.96	44.61	42.59	40.11
MSFT	68.93	69.84	68.46	65.86	63.98	64.65
NVDA	144.56	144.35	104.3	108.93	101.48	109.18

1. Create a formula to calculate the monthly rate of return during the first semester of 2017 and for each company. Format the results as percentages with two decimal places.
2. Calculate the total return for the entire holding period, the compound average monthly rate of return, the average monthly rate of return using the AVERAGE function, and the average monthly rate of return using the GEOMEAN function.

Answer 1

ORCL
date	closing price	capital gain = current price-previous price	monthly rate of return =capital gain/previous price
31-Jan	40.11
28-Feb	42.59	2.48	6.18%
31-Mar	44.61	2.02	4.74%
30-Apr	44.96	0.35	0.78%
31-May	45.39	0.43	0.96%
30-Jun	50.14	4.75	10.46%
total return = sum of capital gain		10.03
compound average monthly rate	(end closing price/beginning closing price)^(1/n)-1	(50.14/40.11)^(1/6)-1	3.79%
average monthly rate of return = using average function in MS excel	average of monthly rate of return		4.63%
average monthly rate of return = using geomean function in MS excel	geomean of monthly rate of return		2.97%
MSFT
date	closing price	capital gain = current price-previous price	monthly rate of return =capital gain/previous price
31-Jan	64.65
28-Feb	63.98	-0.67	-1.04%
31-Mar	65.86	1.88	2.94%
30-Apr	68.46	2.6	3.95%
31-May	69.84	1.38	2.02%
30-Jun	68.93	-0.91	-1.30%
total return = sum of capital gain		4.28
compound average monthly rate	(end closing price/beginning closing price)^(1/n)-1	(68.93/64.65)^(1/6)-1	1.07%
average monthly rate of return = using average function in MS excel	average of monthly rate of return		1.31%
average monthly rate of return = using geomean function in MS excel	geomean of monthly rate of return	it can not be calculated using geomean function as it contains negative value and MS excel perform geomean function when all values are positive	#NUM!
NVDA
date	closing price	capital gain = current price-previous price	monthly rate of return =capital gain/previous price
31-Jan	109.18
28-Feb	101.48	-7.7	-7.05%
31-Mar	108.93	7.45	7.34%
30-Apr	104.3	-4.63	-4.25%
31-May	144.35	40.05	38.40%
30-Jun	144.56	0.21	0.15%
total return = sum of capital gain		35.38
compound average monthly rate	(end closing price/beginning closing price)^(1/n)-1	(144.56/109.18)^(1/6)-1	4.79%
average monthly rate of return = using average function in MS excel	average of monthly rate of return		6.92%
average monthly rate of return = using geomean function in MS excel	geomean of monthly rate of return	it can not be calculated using geomean function as it contains negative value and MS excel perform geomean function when all values are positive	#NUM!