Question

You purchased a bond on 12/5/2019 for $108 that makes semi-annual payments of $5. The bond matures on 1/31/2029 and makes its final coupon payment on this date. Par value is 100.

#16) What was the yield to maturity of this bond on the date it was purchased?

#17) What was the bond's duration on the date it was purchased?

#18) How much accrued interest did you pay on the bond? Use (Actual/Actual) to determine the fraction of the coupon paid.

#19) Write a user defined function in VBA called 'TR' that computes the 'Treynor ratio' for a stock. The Treynor ratio can be computed by dividing a stock's alpha by its beta (e.g., Alpha/Beta). Your function will have two inputs, an array of returns for the stock and an array of returns for the S&P 500. Use your function to compute the Treynor ratio for GOOGL (use the prices for GOOGL in tab #1 - #8). Report this value in the Answers Tab using your function.

#20) Write a user defined function in VBA called 'Recommendation' that returns whether the stock you are analyzing is a buy (1), hold (0) or sell (-1). You will have two parameter inputs: 1) a projected price for the stock (as determined by the analyst) and 2) the current market price. If the stock's projected price is more than $10 per share greater than the current market price the function will return a 1 indicating 'buy'. If the stock's projected price is more than $10 below (absolute value) the current market price then the function will return a -1 indicating 'sell'. Otherwise, if the projected price is within $10 of the current market price then the function will return a 0 indicating 'hold'. If Microsoft is currently selling for $183 per share, use the function to compute recommendations for the following projected prices: A) $170, B) $180, and C) $195. Report these output values in the Answers Tab using your function.

Answer 1

	YTM = [Annual interest +(Face value-market price)/n]/(Face value +2*market price)/3
	Here Market Value =	108
	Par value	100
	Annual Interest	10
	Years To Maturity =n=	9.16	years
	YTM = [10+(100-108)/9.16]/(100+2*108)/3
	YTM =8.66%	Ans 16
Ans 17.	Calculation of Duration
a	b	c	d	e
Period	Cash Flow	Discount factor @4.33% semi annually =1/1.0433^n	PV cash floW=b*c	Time period*Cash flow=a*b	PV of time adjusted cash flow=c*e
1	5	0.958	4.7900	5	4.79
2	5	0.919	4.5950	10	9.19
3	5	0.881	4.4050	15	13.215
4	5	0.844	4.2200	20	16.88
5	5	0.809	4.0450	25	20.225
6	5	0.775	3.8750	30	23.25
7	5	0.743	3.7150	35	26.005
8	5	0.712	3.5600	40	28.48
9	5	0.683	3.4150	45	30.735
10	5	0.654	3.2700	50	32.700
11	5	0.627	3.1350	55	34.485
12	5	0.601	3.0050	60	36.06
13	5	0.576	2.8800	65	37.44
14	5	0.552	2.7600	70	38.64
15	5	0.529	2.6450	75	39.675
16	5	0.508	2.5400	80	40.64
17	5	0.486	2.4300	85	41.31
18	105	0.466	48.9300	1890	880.74
	Total=		$                        108		1354.46

	Macaulays duration= sum of PV of time adjusted cash flow/ Sum of PV of cash flows=	12.52	Ans 17
Ans 19
	Last coupon paymnet date	7/31/2019
	Purcchase date	12/5/2019
	No of days past=	127
	Coupon included in Price=127/365*10 =$3.48