Question

Calculate the durations and volatilities of securities A, B, and C. Their cash flows are shown below. The interest rate is 10%. (Do not round intermediate calculations. Round "Duration" to 4 decimal places and "Volatility" to 2 decimal places.)

	Period 1	Period 2	Period 3	Duration	Volatility
A	50	50	60	years
B	30	30	140	years
C	20	20	130	years

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J
2
3
4		Securities	1	2	3
5		A	50	50	60
6		B	30	30	140
7		C	20	20	130
8		Assuming the period is in year.
9
10		Interest rate	0.1
11
12		Calculation of Duration:
13		Macaulay Duration is the weightage average of the time to present value of cash flows.
14
15		Formula for Macaulay duration is as follows:
16
17
18
19
20
21
22		Where, Ct is cash flow at time t, PV(Ct) is the present value of cash flow at time t and T is the total time horizon.
23
24		Calculation of Macaulay duration for Security A:
25		Year (t)	0	1	2	3
26		Cash Flow (Ct)		=D5	=E5	=F5
27		Effective interest rate (i)	=D10
28		Present value factor (P/F,i,n) for each year		=1/((1+$D27)^E25)	=1/((1+$D27)^F25)	=1/((1+$D27)^G25)
29		PV (Ct) = (Ct)*(P/F,i,n)		=E26*E28	=F26*F28	=G26*G28
30		? PV (Ct)	=SUM(E29:I29)
31		Fraction of total Value [PV(Ct)/? PV (Ct)]		=E29/$D30	=F29/$D30	=G29/$D30
32		Year* Fraction of Total Value                                       [t *PV(Ct)/? PV (Ct)]		=E25*E31	=F25*F31	=G25*G31
33		Macaulay Duration of Security A	=SUM(E32:G32)	=SUM(E32:G32)
34
35		Hence Duration of security A is	=D33	Years
36
37		Calculation of Macaulay duration for Security B:
38		Year (t)	0	1	2	3
39		Cash Flow (Ct)		=D6	=E6	=F6
40		Effective interest rate (i)	=$D$10
41		Present value factor (P/F,i,n) for each year		=1/((1+$D40)^E38)	=1/((1+$D40)^F38)	=1/((1+$D40)^G38)
42		PV (Ct) = (Ct)*(P/F,i,n)		=E39*E41	=F39*F41	=G39*G41
43		? PV (Ct)	=SUM(E42:I42)
44		Fraction of total Value [PV(Ct)/? PV (Ct)]		=E42/$D43	=F42/$D43	=G42/$D43
45		Year* Fraction of Total Value                                       [t *PV(Ct)/? PV (Ct)]		=E38*E44	=F38*F44	=G38*G44
46		Macaulay Duration of Security B	=SUM(E45:G45)	=SUM(E45:G45)
47
48		Hence Duration of security B is	=D46	Years
49
50		Calculation of Macaulay duration for Security C:
51		Year (t)	0	1	2	3
52		Cash Flow (Ct)		=D7	=E7	=F7
53		Effective interest rate (i)	=$D$10
54		Present value factor (P/F,i,n) for each year		=1/((1+$D53)^E51)	=1/((1+$D53)^F51)	=1/((1+$D53)^G51)
55		PV (Ct) = (Ct)*(P/F,i,n)		=E52*E54	=F52*F54	=G52*G54
56		? PV (Ct)	=SUM(E55:G55)
57		Fraction of total Value [PV(Ct)/? PV (Ct)]		=E55/$D56	=F55/$D56	=G55/$D56
58		Year* Fraction of Total Value                                       [t *PV(Ct)/? PV (Ct)]		=E51*E57	=F51*F57	=G51*G57
59		Macaulay Duration of Security C	=SUM(E58:G58)	=SUM(E58:G58)
60
61		Hence Duration of security C is	=D59	Years
62
63		Calculation of volatility:
64		Volatility is the standard deviation of cash flows.
65		Excel function for standard deviation can be found as follows:
66
67		Securities	1	2	3	Volatility
68		A	50	50	60	=STDEV.S(D68:F68)	=STDEV.S(D68:F68)
69		B	30	30	140	=STDEV.S(D69:F69)
70		C	20	20	130	=STDEV.S(D70:F70)
71
72		Hence,
73		Securities	Volatility
74		A	=G68
75		B	=G69
76		C	=G70
77