Browse By

Macaulay Duration Excel calculation example

Macaulay Duration Excel Calculation Example

A working example of  Macaulay & Modified duration calculation. Earlier we had considered the importance of the Duration risk metric to Asset Liability Management (ALM) and managing interest rate risk. In this post we will look at the specific mechanics of the Macaulay Duration and Modified Duration calculations. Also see duration calculation example for US treasuries.

Let us consider a fixed income instrument with the following terms:

Issue Date = 10th December 2010
Face Amount = 100
Tenor = 1 year
Coupon = 10% per annum
Coupon payment frequency, k = semi-annual = 2
Yield to Maturity (YTM) = 12% per annum

The schedule of payments will therefore be as follows:

Date

Tenor
(in years)

t= Tenor * FrequencyCash Flow
10-June 20110.51(10%/2) * 100 = 5
10-December 201112(10%/2) * 100 + 100 = 105

Price

The price of the instrument on the issue date will be the discounted value of the cash flows, i.e. their present value (PVCF). This is equal to:

PVCFt = Cash flow/(1+YTM/2)^(t)

t

Cash Flow

Present Value of Cash Flow (PVCF)
155/(1+12%/2)^1 = 4.7170
2105105/(1+12%/2)^2 = 93.4496

The price of the instrument at issue is therefore the sum of these two present values, i.e. the present value of total cash flow, PVTCF = 4.7170 + 93.4496 = 98.1666

The price may also be obtained directly using the excel formula “PRICE” with the following input parameters:

Settlement = Issue Date = 10th December 2010
Maturity = Maturity Date = 10th December 2011
Rate = Coupon Rate = 10%
Yld = YTM = 12%
Redemption = Face Amount =100
Frequency = k =2
Basis = Type of day count basis = 1 (Actual/ Actual)

Macaulay Duration

Macaulay Duration is given by the following formula:

Macaulay Duration for this instrument works out to:

(1*4.7170+2*93.4496)/(2*98.1666) = 0.9760

The Macaulay Duration may also be obtained directly using the excel formula “DURATION” with the following input parameters:

Settlement = Issue Date = 10th December 2010
Maturity = Maturity Date = 10th December 2011
Coupon = Coupon Rate = 10%
Yld = YTM = 12%
Frequency = k =2
Basis = Type of day count basis = 1 (Actual/ Actual)

Modified Duration

Modified Duration is given by the following formula:

Therefore modified duration for the following formula will be:

0.9760/(1+12%/2) = 0.9207

The Modified Duration may also be obtained directly using the excel formula “MDURATION” with the following input parameters:

Settlement = Issue Date = 10th December 2010
Maturity = Maturity Date = 10th December 2011
Coupon = Coupon Rate = 10%
Yld = YTM = 12%
Frequency = k =2
Basis = Type of day count basis = 1 (Actual/ Actual)

In this post we saw how price, Macaulay Duration and Modified duration were calculated for the sample instrument using both first principles as well as the EXCEL worksheet formulas. In the next section we will see how Effective Duration is calculated.

Also see Asset Liability Management for Board members – 7 posts, 60 minutes.

Page-Break-Image-medium

One thought on “Macaulay Duration Excel calculation example”

  1. Pingback: Duration and Convexity solved example: The foundation of an ALM (Asset Liability Management) model | Treasury Risk
  2. Trackback: Duration and Convexity solved example: The foundation of an ALM (Asset Liability Management) model | Treasury Risk

Comments are closed.

Comodo SSL