Copy Of Bond Price With Excel Functions

Bond price with EXCEL built-in functions Bonds in New Zealand are quoted by yield. This means an investor must determine the actual price of the instrument using the RBNZ (Reserve Bank) formula. In this Excel sheet it is shown how to use Excel built-in functions to determine a bond price. Excel returns the "clean price" without accrued coupon, so we must add it back to find the "dirty price". If your Excel does not recognize the functions below, use Menu Tool - Add-ins.. - click on Analysis ToolPack to define them.

Coupon rate Yield Settlement date Next coupon date Maturity date Shifted Maturity Date Years to maturity Final redemption (%) Coupons per year Day count basis to use

200.0

6.5% NZGOVT 13 6.50% 650 6.59% 16-Jul-02 28-Dec-02 15-Apr-13 28-Jun-13 74 10.951 100.00% of face value 2 Actual/actual

Prices per $100 of face value Clean Price Accrued Interest Dirty Price Duration (years) Modified Duration

$171.1803 $0.3197 $171.5000 7.964 7.710

180.0 160.0 140.0

Dirty Price ($)

Example bond:

120.0 100.0 80.0 60.0 40.0 20.0 0.0 0.00%

5.00%

10.00% Yield

Example quote sheet for NZ bonds

15.00%

ctual price of the instrument

ce. Excel returns the "clean

Analysis ToolPack to define

00%

6.0

5.0

Duration (years)

4.0

3.0

2.0

1.0

10.00% Yield

15.00%

20.00%

0.0 14-Jan-04

6-Jul-09

27-Dec-14 Maturity Date

18-Jun-20

9-Dec-25

-14

Date

Range for Combo Box Selected value 0 US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360

18-Jun-20

1 2

9-Dec-25

Series Duration Graph

28-Jun-08 28-Jun-09 28-Jun-10 28-Jun-11 28-Jun-12 28-Jun-13 28-Jun-14 28-Jun-15 28-Jun-16 28-Jun-17 28-Jun-18 28-Jun-19 28-Jun-20 28-Jun-21 28-Jun-22

28-Jun-08 28-Jun-13

5.01 5.01

Series Yield Graph 0.00% 6.59%

28-Jun-13 28-Jun-13

0 5.01

6.59% 6.59%

0 171.50

$171.50 $99.88 $99.82 $99.76 $99.71 $99.67 $99.62 $99.58 $99.54 $99.50 $99.47 $99.43 $99.40 $99.38 $99.35 $99.32

5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01 5.01

$171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50 $171.50

7.96 8.76 8.65 8.54 8.42 8.3 8.17 8.04 7.91 7.77 7.64 7.5 7.35 7.21 7.07 6.92 6.77

0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15%

171.50 171.50

16% 17% 18% 19%

$171.50 $171.50 $171.50 $171.50

6.62 6.47 6.33 6.18

RBNZ (Reserve Bank Of New Zealand Formula)

1

P=

a

 1i  where: P: FV: i: c: C: n: a: b:

[[ ∑ n

b

k =0

]

C  k  1i 

Market Value of Bond Nominal or face value of bond Annual market yield / 2 (in %) Annual coupon rate in % Coupon Payment (= c/2 * FV) – semi-annual coupon number of full coupon periods remaining until maturity (equals number of remaining coupons minus 1) Number of days from settlement to next coupon date Number of days from last to next coupon date

Formula used in Excel (which is equivalent to the RBNZ formula except for the last term (takes

[   ] [ N

P=

∑

k=1

where: P: FV: r: f: c: N: a: b:

100× c

f

r 1 f

 k−1 b a



Market Value of Bond Nominal or face value of bond Annual market yield (in %) Frequency of coupon payments (usually 2 in for bonds in New Zealand) Annual coupon rate in % Number of remaining coupons Number of days from settlement to next coupon date Number of days from last to next coupon date

FV r 1 f

 N −1



]

C FV  k n 1i   1i 

]

aining coupons minus 1)

pt for the last term (takes off the accrued coupon to find the "clean price")

FV r 1 f





N −1

a b



]

−

[

100×c

b−a × f b

]