The purpose of this workbook is to demonstrate how to
calculate duration, modified duration, and convexity
step-by-step using the standard formulas.
The workbook can handle bonds (or other loans) with
maturities up to 30 years with payment frequencies of
annual, semiannual, quarterly, or monthly. I make use of
conditional formatting to make the table look nice, and
data validation to make sure that users don't enter nonvalid data. The "Calculator" worksheet is protected to
avoid inadvertant changes. You can unprotect it without
needing a password.
Please note that the formulas used in this workbook only
work on a payment date. They do not work between
payment dates. Also note that if you just need to
calculate duration or modified duration then Excel
provides the Duration() and MDuration() functions.
Timothy R. Mayes, Ph.d.
Associate Professor of Finance
Metropolitan State College of Denver
Please see http://www.tvmcalcs.com for more Excel
solutions.
Bond Price
Face Value
Coupon Rate
Life in Years
Yield
Frequency
$0.23
1,000
0.00%
30
30.00%
2
$0.18 If Yield Changes By
1000 Bond Price Will Change By
0%
30 Modified Duration Predicts
31% Convexity Adjustment
2 Total Predicted Change
Macaulay Duration
Modified Duration
Convexity
30.00
26.09
691.87
Actual New Price
Predicted New Price
Difference
Period
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Cash Flow
$0.23
-
PV Cash Flow
Duration Calc
Convexity Calc
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
1,000.00
0.23
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
13.69
13.69
()
()
()
()
()
()
()
()
()
()
()
()
()
()
631.27
631.27
()
()
()
()
()
()
()
()
()
()
()
()
()
()
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
(Total )
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
()
1.00%
-0.05
-22.92%
-0.06
0.01
-0.05
-26.09%
3.46%
-22.63%
$0.18
$0.18
$0.00
+0.3771%
DMac is annual
i is periodic rate
m is payment frequency
This workbook is Copyright 2009 by Timothy R. Mayes, Ph.D.
Please visit: http://www.tvmcalcs.com/
You are free to use and redistribute it as long as this notice is kep intact.
However, I retain all rights to the worksheet.
This workbook is Copyright 2005 - 2007 by
Timothy R. Mayes, Ph.D.
Please visit: http://www.tvmcalcs.com/
You are free to use and redistribute it as long as
this notice is kep intact. However, I retain all
rights to the worksheet.
