Walt Disney Companys Sleeping Beauty Bonds: Duration Analysis

par value=$100		interest rate=7.55%
					YTM
period	Cash Flow	pv	fv	weight	duration
1	7.55	7.019990702	10171.33619	0.070199907	0.070199907
2	7.55	6.527188007	9457.309337	0.06527188	0.13054376
3	7.55	6.068980016	8793.407101	0.0606898	0.1820694
4	7.55	5.642938183	8176.11074	0.056429382	0.225717527
5	7.55	5.246804448	7602.148527	0.052468044	0.262340222
6	7.55	4.878479263	7068.478407	0.048784793	0.292708756
7	7.55	4.536010473	6572.27188	0.045360105	0.317520733
8	7.55	4.217582959	6110.899005	0.04217583	0.337406637
9	7.55	3.921509028	5681.914463	0.03921509	0.352935812
10	7.55	3.646219458	5283.044596	0.036462195	0.364621946
11	7.55	3.390255191	4912.175357	0.033902552	0.372928071
12	7.55	3.152259592	4567.341103	0.031522596	0.378271151
13	7.55	2.930971262	4246.714183	0.029309713	0.381026264
14	7.55	2.725217352	3948.595242	0.027252174	0.381530429
15	7.55	2.533907347	3671.404223	0.025339073	0.380086102
16	7.55	2.356027287	3413.671988	0.023560273	0.376964366
17	7.55	2.190634391	3174.032532	0.021906344	0.372407846
18	7.55	2.03685206	2951.215743	0.020368521	0.366633371
19	7.55	1.893865235	2744.040672	0.018938652	0.359834395
20	7.55	1.760916071	2551.409272	0.017609161	0.352183214
21	7.55	1.637299927	2372.300579	0.016372999	0.343832985
22	7.55	1.522361624	2205.765299	0.015223616	0.334919557
23	7.55	1.41549198	2050.92078	0.01415492	0.325563155
24	7.55	1.316124574	1906.946332	0.013161246	0.315869898
25	7.55	1.223732752	1773.078877	0.012237328	0.305933188
26	7.55	1.137826826	1648.608904	0.011378268	0.295834975
27	7.55	1.057951489	1532.876712	0.010579515	0.285646902
28	7.55	0.983683393	1425.26891	0.009836834	0.27543135
29	7.55	0.91462891	1325.215165	0.009146289	0.265242384
30	7.55	0.850422046	1232.185183	0.00850422	0.255126614
31	7.55	0.790722497	1145.685898	0.007907225	0.245123974
32	7.55	0.735213851	1065.258855	0.007352139	0.235268432
33	7.55	0.683601907	990.4777821	0.006836019	0.225588629
34	7.55	0.635613117	920.9463339	0.006356131	0.21610846
35	7.55	0.590993135	856.2959869	0.005909931	0.206847597
36	7.55	0.549505472	796.1840882	0.005495055	0.19782197
37	7.55	0.510930239	740.2920392	0.005109302	0.189044188
38	7.55	0.475062984	688.3236069	0.00475063	0.180523934
39	7.55	0.441713606	640.0033537	0.004417136	0.172268307
40	7.55	0.410705352	595.0751778	0.004107054	0.164282141
41	7.55	0.381873875	553.3009556	0.003818739	0.156568289
42	7.55	0.355066364	514.45928	0.003550664	0.149127873
43	7.55	0.330140739	478.3442864	0.003301407	0.141960518
44	7.55	0.306964889	444.764562	0.003069649	0.135064551
45	7.55	0.285415983	413.5421311	0.00285416	0.128437192
46	7.55	0.265379807	384.5115119	0.002653798	0.122074711
47	7.55	0.246750169	357.5188395	0.002467502	0.11597258
48	7.55	0.229428331	332.4210502	0.002294283	0.110125599
49	7.55	0.213322483	309.0851234	0.002133225	0.104528017
50	7.55	0.198347265	287.3873765	0.001983473	0.099173632
51	7.55	0.184423305	267.2128094	0.001844233	0.094055886
52	7.55	0.171476806	248.454495	0.001714768	0.089167939
53	7.55	0.15943915	231.0130126	0.001594392	0.08450275
54	7.55	0.148246537	214.7959206	0.001482465	0.08005313
55	7.55	0.137839644	199.7172669	0.001378396	0.075811804
56	7.55	0.128163314	185.6971333	0.001281633	0.071771456
57	7.55	0.119166261	172.6612118	0.001191663	0.067924769
58	7.55	0.1108008	160.5404108	0.001108008	0.064264464
59	7.55	0.103022595	149.2704889	0.001030226	0.060783331
60	7.55	0.095790418	138.7917145	0.000957904	0.057474251
61	7.55	0.08906594	129.048549	0.000890659	0.054330223
62	7.55	0.082813519	119.9893529	0.000828135	0.051344382
63	7.55	0.077000018	111.5661115	0.00077	0.048510011
64	7.55	0.071594623	103.7341808	0.000715946	0.045820559
65	7.55	0.066568688	96.45205096	0.000665687	0.043269647
66	7.55	0.061895572	89.68112595	0.000618956	0.040851077
67	7.55	0.057550508	83.38551925	0.000575505	0.038558841
68	7.55	0.053510468	77.53186355	0.000535105	0.036387118
69	7.55	0.049754038	72.08913394	0.00049754	0.034330286
70	7.55	0.046261309	67.02848344	0.000462613	0.032382917
71	7.55	0.04301377	62.32309014	0.000430138	0.030539777
72	7.55	0.039994207	57.948015	0.000399942	0.028795829
73	7.55	0.037186618	53.88006974	0.000371866	0.027146231
74	7.55	0.03457612	50.09769385	0.000345761	0.025586329
75	7.55	0.03214888	46.5808404	0.000321489	0.02411166
76	7.55	0.029892032	43.31086974	0.00029892	0.022717944
77	7.55	0.027793614	40.27045071	0.000277936	0.021401083
78	7.55	0.025842505	37.44346881	0.000258425	0.020157154
79	7.55	0.024028363	34.81494078	0.000240284	0.018982407
80	7.55	0.022341574	32.37093518	0.000223416	0.017873259
81	7.55	0.020773198	30.09849854	0.000207732	0.01682629
82	7.55	0.019314921	27.98558674	0.000193149	0.015838236
83	7.55	0.017959016	26.02100115	0.00017959	0.014905983
84	7.55	0.016698294	24.19432929	0.000166983	0.014026567
85	7.55	0.015526076	22.49588962	0.000155261	0.013197164
86	7.55	0.014436147	20.91668026	0.000144361	0.012415086
87	7.55	0.013422731	19.44833125	0.000134227	0.011677776
88	7.55	0.012480456	18.08306021	0.000124805	0.010982801
89	7.55	0.011604329	16.81363106	0.000116043	0.010327853
90	7.55	0.010789706	15.63331573	0.000107897	0.009710736
91	7.55	0.01003227	14.53585842	0.000100323	0.009129366
92	7.55	0.009328006	13.51544251	9.32801E-05	0.008581765
93	7.55	0.00867318	12.5666597	8.67318E-05	0.008066058
94	7.55	0.008064324	11.68448136	8.06432E-05	0.007580465
95	7.55	0.007498209	10.86423185	7.49821E-05	0.007123299
96	7.55	0.006971836	10.10156379	6.97184E-05	0.006692962
97	7.55	0.006482413	9.392434948	6.48241E-05	0.006287941
98	7.55	0.006027349	8.733086888	6.02735E-05	0.005906802
99	7.55	0.005604229	8.120025	5.60423E-05	0.005548187
100	107.55	0.074228202	107.55	0.000742282	0.074228202
101		100	144891.0209	1	14.23520156

8.55%		IR	IR
		8.55%	6.55%
1	7.55	6.95532013	7.08587518
2	7.55	6.40748054	6.65028172
3	7.55	5.90279184	6.24146572
4	7.55	5.43785522	5.85778106
5	7.55	5.00953958	5.49768283
6	7.55	4.61496046	5.1597211
7	7.55	4.25146058	4.84253506
8	7.55	3.91659197	4.54484754
9	7.55	3.60809947	4.26545992
10	7.55	3.32390554	4.00324722
11	7.55	3.06209631	3.75715366
12	7.55	2.82090862	3.52618832
13	7.55	2.59871821	3.30942123
14	7.55	2.39402876	3.10597957
15	7.55	2.20546177	2.91504418
16	7.55	2.03174737	2.73584625
17	7.55	1.87171568	2.56766424
18	7.55	1.72428898	2.40982097
19	7.55	1.58847441	2.26168087
20	7.55	1.46335736	2.12264746
21	7.55	1.34809522	1.99216092
22	7.55	1.24191176	1.86969584
23	7.55	1.1440919	1.75475912
24	7.55	1.05397688	1.64688796
25	7.55	0.97095982	1.54564801
26	7.55	0.89448164	1.45063164
27	7.55	0.8240273	1.36145626
28	7.55	0.75912234	1.27776279
29	7.55	0.69932966	1.19921426
30	7.55	0.64424657	1.12549438
31	7.55	0.59350214	1.05630631
32	7.55	0.54675462	0.99137148
33	7.55	0.50368919	0.93042842
34	7.55	0.46401584	0.87323174
35	7.55	0.42746738	0.81955114
36	7.55	0.39379768	0.76917048
37	7.55	0.36277999	0.72188689
38	7.55	0.33420543	0.67750998
39	7.55	0.30788155	0.63586108
40	7.55	0.28363109	0.59677248
41	7.55	0.26129074	0.5600868
42	7.55	0.24071003	0.52565631
43	7.55	0.22175037	0.49334238
44	7.55	0.20428408	0.46301491
45	7.55	0.18819354	0.43455177
46	7.55	0.17337037	0.40783835
47	7.55	0.15971476	0.38276711
48	7.55	0.14713474	0.35923708
49	7.55	0.13554559	0.33715352
50	7.55	0.12486927	0.31642752
51	7.55	0.11503387	0.29697562
52	7.55	0.10597317	0.27871949
53	7.55	0.09762613	0.26158563
54	7.55	0.08993656	0.24550505
55	7.55	0.08285265	0.230413
56	7.55	0.07632672	0.21624871
57	7.55	0.0703148	0.20295515
58	7.55	0.06477642	0.19047879
59	7.55	0.05967427	0.17876939
60	7.55	0.05497399	0.16777981
61	7.55	0.05064394	0.1574658
62	7.55	0.04665494	0.14778583
63	7.55	0.04298014	0.13870092
64	7.55	0.03959478	0.13017449
65	7.55	0.03647608	0.12217221
66	7.55	0.03360302	0.11466186
67	7.55	0.03095626	0.1076132
68	7.55	0.02851797	0.10099784
69	7.55	0.02627174	0.09478915
70	7.55	0.02420243	0.08896213
71	7.55	0.02229611	0.08349332
72	7.55	0.02053995	0.07836069
73	7.55	0.01892211	0.07354359
74	7.55	0.0174317	0.06902261
75	7.55	0.01605868	0.06477955
76	7.55	0.01479381	0.06079732
77	7.55	0.01362857	0.0570599
78	7.55	0.01255511	0.05355223
79	7.55	0.0115662	0.05026018
80	7.55	0.01065518	0.04717052
81	7.55	0.00981592	0.04427078
82	7.55	0.00904276	0.0415493
83	7.55	0.0083305	0.03899512
84	7.55	0.00767435	0.03659795
85	7.55	0.00706987	0.03434815
86	7.55	0.00651301	0.03223665
87	7.55	0.00600001	0.03025495
88	7.55	0.00552742	0.02839507
89	7.55	0.00509205	0.02664953
90	7.55	0.00469097	0.02501129
91	7.55	0.00432148	0.02347376
92	7.55	0.0039811	0.02203074
93	7.55	0.00366752	0.02067644
94	7.55	0.00337865	0.01940539
95	7.55	0.00311253	0.01821247
96	7.55	0.00286737	0.01709288
97	7.55	0.00264152	0.01604213
98	7.55	0.00243346	0.01505596
99	7.55	0.00224179	0.01413042
100	107.55	0.02941898	0.18891438
		88.3072928	115.240358

(i) Create an Excel spreadsheet with the cash payments associated with the Sleeping Beauties. Who gets how much and when, per $100 of bonds issued? (in your answer please ignore that US debentures usually pay interest semiannually and instead assume annual payments only).

(ii) From the perspective of the bondholder, at the time of the issue which is more valuable, the $300 million principal (or face value) of the bonds or the semi-annual coupon payments?

(iii) Suppose that on the day after the Sleeping Beauties were sold, the prevailing interest rate increased one percentage point, i.e., from 7.55% to 8.55%. What would be the new price of the Sleeping Beauties?

(iv) If the interest rate had dropped by one percentage point, what would be the new price of the Sleeping Beauties? (hint: in (iii) and (iv) evaluate the PV of the Sleeping Beauty cash flows under the new interest rates)