This should be done in as an Excel file. Using the simple time value of money...

Free

50.1K

Verified Solution

Question

Finance

This should be done in as an Excel file.
Using the simple time value of money concepts from CorporateFinance course:
Calculate the price of an annual couponâ€bondwith a par value of $1,000, time to maturity of 10 years, couponrate of 10% and
yield to maturity of 12% (bond DC).
yield to maturity of 8% (bond PC).
Do the sensitivity analysis on the price of these two bonds(bond DC & bond PC) by changing the
following (one variable change at a time):
Time to maturity from 1 year to 50 years in increments of 1year.
Coupon rate from 0% to 20% in increments of 1%.
Fix the time to maturity at 5 years and coupon at 10% butchange the yield to maturity from 0% to 30%. This will be just onebond, not the two bonds mentioned above.
Fix the time to maturity at 30 years and coupon at 10% butchange the yield to maturity from 0% to 30%. This will be just onebond, not the two bonds mentioned above.
Calculate the price of two zeroâ€coupon bondwith a par value of $1,000, time to maturity of 10 years, and
yield to maturity of 12% (bond DZ).
yield to maturity of 8% (Bond PZ).
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 1year.
Fix the time to maturity of the zero to 5 years but changeYield to maturity from 0% to 20%
Fix the time to maturity of the zero to 30 years but changeYield to maturity from 0% to 20%
For each sensitivity analysis part, a table and a graph isrequired. The graph should show the relationship between thevariable that you changed and price of the bond. The graph typeshould be the smooth line kind under the charts icon that showsscatter dots.
Under each graph a sentence or two is required to explain therelationship between the variable that you changed and the bondprice.
Finally, in a few sentences that compare the sensitivity of thecouponâ€bond with the zeroâ€coupon bond for similar change in time tomaturity and yield to maturity.
Sensitivity analysis = studying the effect of changing onevariable value on an outcome of interest. From Corporate Finance,this analysis was done for project NPV.

Answer & Explanation Solved by verified expert

3.8 Ratings (609 Votes)

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

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member