Question

This should be done in as an Excel file.

Using the simple time value of money concepts from Corporate Finance course:

1. Calculate the price of an annual coupon‐bond with a par value of $1,000, time to maturity of 10 years, coupon rate of 10% and
2. yield to maturity of 12% (bond DC).
3. yield to maturity of 8% (bond PC).
4. Do the sensitivity analysis on the price of these two bonds (bond DC & bond PC) by changing the
5. following (one variable change at a time):
6. Time to maturity from 1 year to 50 years in increments of 1 year.
7. Coupon rate from 0% to 20% in increments of 1%.
8. Fix the time to maturity at 5 years and coupon at 10% but change the yield to maturity from 0% to 30%. This will be just one bond, not the two bonds mentioned above.
9. Fix the time to maturity at 30 years and coupon at 10% but change the yield to maturity from 0% to 30%. This will be just one bond, not the two bonds mentioned above.
10. Calculate the price of two zero‐coupon bond with a par value of $1,000, time to maturity of 10 years, and
11. yield to maturity of 12% (bond DZ).
12. yield to maturity of 8% (Bond PZ).
13. Do the sensitivity analysis on the price of these two bonds (bond DZ and bond PZ) by changing the following:

a. Time to maturity from 1 year to 50 years in increments of 1 year.

1. Fix the time to maturity of the zero to 5 years but change Yield to maturity from 0% to 20%
2. Fix the time to maturity of the zero to 30 years but change Yield to maturity from 0% to 20%

For each sensitivity analysis part, a table and a graph is required. The graph should show the relationship between the variable that you changed and price of the bond. The graph type should be the smooth line kind under the charts icon that shows scatter dots.

Under each graph a sentence or two is required to explain the relationship between the variable that you changed and the bond price.

Finally, in a few sentences that compare the sensitivity of the coupon‐bond with the zero‐coupon bond for similar change in time to maturity and yield to maturity.

Sensitivity analysis = studying the effect of changing one variable value on an outcome of interest. From Corporate Finance, this analysis was done for project NPV.

Answer 1

Value of bond ,

V = PV ( cash flow from Coupon ) + PV ( Maturity Value)

Excel function , PV ( rate, nper, pmt , Fv,)

Par Value(FV)	1000
Coupon Rate	10%
Coupon Cash flow(pmt)	10%*1000	$100
Maturity time(nper)	10 years
a. YTM (rate)	12%
Bond value	₹887.00	PV(12%,10,-100,-1000)
b. YTM (rate)	8%
Bond value	₹1,134.20	PV(8%,10,-100,-1000)

Sensitivity analysis

Sensitivity Analysis for Time			Sensitivity Analysis for Coupon rate			Sensitivity Analysis for YTM with maturity of 5 years coupon rate 10%			Sensitivity Analysis for YTM with maturity of 30 years coupon rate 10%
Time	DC[PV(12%,time,-100,-1000)]	PC[PV(8%,time,-100,-1000)]	Coupon rate©	DC[PV(12%,10,-c*1000,-1000)]	PC[PV(8%,10,-c*1000,-1000)]	YTM	DC[PV(YTM%,5,-100,-1000)]	PC[PV(YTM%,5,-100,-1000)]	YTM	DC[PV(YTM%,30,-100,-1000)]	PC[PV(YTM%,30,-100,-1000)]
1	$982.14	$1,018.52	0%	$321.97	$463.19	0%	$1,500.00	$1,500.00	0%	$4,000.00	$4,000.00
2	$966.20	$1,035.67	1%	$378.48	$530.29	1%	$1,436.81	$1,436.81	1%	$3,322.69	$3,322.69
3	$951.96	$1,051.54	2%	$434.98	$597.40	2%	$1,377.08	$1,377.08	2%	$2,791.72	$2,791.72
4	$939.25	$1,066.24	3%	$491.48	$664.50	3%	$1,320.58	$1,320.58	3%	$2,372.03	$2,372.03
5	$927.90	$1,079.85	4%	$547.98	$731.60	4%	$1,267.11	$1,267.11	4%	$2,037.52	$2,037.52
6	$917.77	$1,092.46	5%	$604.48	$798.70	5%	$1,216.47	$1,216.47	5%	$1,768.62	$1,768.62
7	$908.72	$1,104.13	6%	$660.99	$865.80	6%	$1,168.49	$1,168.49	6%	$1,550.59	$1,550.59
8	$900.65	$1,114.93	7%	$717.49	$932.90	7%	$1,123.01	$1,123.01	7%	$1,372.27	$1,372.27
9	$893.44	$1,124.94	8%	$773.99	$1,000.00	8%	$1,079.85	$1,079.85	8%	$1,225.16	$1,225.16
10	$887.00	$1,134.20	9%	$830.49	$1,067.10	9%	$1,038.90	$1,038.90	9%	$1,102.74	$1,102.74
11	$881.25	$1,142.78	10%	$887.00	$1,134.20	10%	$1,000.00	$1,000.00	10%	$1,000.00	$1,000.00
12	$876.11	$1,150.72	11%	$943.50	$1,201.30	11%	$963.04	$963.04	11%	$913.06	$913.06
13	$871.53	$1,158.08	12%	$1,000.00	$1,268.40	12%	$927.90	$927.90	12%	$838.90	$838.90
14	$867.44	$1,164.88	13%	$1,056.50	$1,335.50	13%	$894.48	$894.48	13%	$775.13	$775.13
15	$863.78	$1,171.19	14%	$1,113.00	$1,402.60	14%	$862.68	$862.68	14%	$719.89	$719.89
16	$860.52	$1,177.03	15%	$1,169.51	$1,469.71	15%	$832.39	$832.39	15%	$671.70	$671.70
17	$857.61	$1,182.43	16%	$1,226.01	$1,536.81	16%	$803.54	$803.54	16%	$629.37	$629.37
18	$855.01	$1,187.44	17%	$1,282.51	$1,603.91	17%	$776.05	$776.05	17%	$591.94	$591.94
19	$852.68	$1,192.07	18%	$1,339.01	$1,671.01	18%	$749.83	$749.83	18%	$558.66	$558.66
20	$850.61	$1,196.36	19%	$1,395.52	$1,738.11	19%	$724.81	$724.81	19%	$528.88	$528.88
21	$848.76	$1,200.34	20%	$1,452.02	$1,805.21	20%	$700.94	$700.94	20%	$502.11	$502.11
22	$847.11	$1,204.01				21%	$678.14	$678.14	21%	$477.91	$477.91
23	$845.63	$1,207.42				22%	$656.36	$656.36	22%	$455.94	$455.94
24	$844.31	$1,210.58				23%	$635.55	$635.55	23%	$435.92	$435.92
25	$843.14	$1,213.50				24%	$615.65	$615.65	24%	$417.59	$417.59
26	$842.09	$1,216.20				25%	$596.61	$596.61	25%	$400.74	$400.74
27	$841.15	$1,218.70				26%	$578.39	$578.39	26%	$385.22	$385.22
28	$840.31	$1,221.02				27%	$560.95	$560.95	27%	$370.85	$370.85
29	$839.56	$1,223.17				28%	$544.24	$544.24	28%	$357.53	$357.53
30	$838.90	$1,225.16				29%	$528.23	$528.23	29%	$345.14	$345.14
31	$838.30	$1,227.00				30%	$512.89	$512.89	30%	$333.59	$333.59
32	$837.77	$1,228.70
33	$837.29	$1,230.28
34	$836.87	$1,231.74
35	$836.49	$1,233.09
36	$836.15	$1,234.34
37	$835.85	$1,235.50
38	$835.58	$1,236.58
39	$835.34	$1,237.57
40	$835.12	$1,238.49
41	$834.93	$1,239.34
42	$834.76	$1,240.13
43	$834.61	$1,240.86
44	$834.47	$1,241.54
45	$834.35	$1,242.17
46	$834.24	$1,242.75
47	$834.14	$1,243.29
48	$834.06	$1,243.78
49	$833.98	$1,244.24
50	$833.91	$1,244.67

Kindly re-post the remaining question