CDIAC provides information, education and technical assistance on public debt and investments to local public agencies and other public finance professionals. 1
CDIAC provides information, education and technical assistance on - - PowerPoint PPT Presentation
CDIAC provides information, education and technical assistance on - - PowerPoint PPT Presentation
CDIAC provides information, education and technical assistance on public debt and investments to local public agencies and other public finance professionals. 1 The California Debt and Investment Advisory Commission 0 Technical Webinar Series
The California Debt and Investment Advisory Commission Technical Webinar Series
Bond Math 2
The Economics of Bonds
Housekeeping
- Feedback Button
- Questions and Answers
- Polling Questions
2
The California Debt and Investment Advisory Commission Technical Webinar Series
Bond Math 2
The Economics of Bonds
Piecing Together Bond Finances
- Introduction of Speakers
Robert G. Friar, Jr.
Managing Director, The PFM Group
Kenneth D. Fullerton
Managing Director, The PFM Group
3
The California Debt and Investment Advisory Commission Technical Webinar Series
Robert G. Friar, Jr.
Managing Director, The PFM Group
·O~er
27
years of experience in Municipal Finance ·Has been in~ol~ed
in all
aspects of the business across the country, as both an underwriter
and financial
ad~isor
- Currently
assists clients in the structuring
and financing of
complex airport projects
Kenneth D. Fullerton
Managing Director, The PFM Group
- O~er
30
years of experience in Municipal finance
- Has
pro~ided
financial
ad~ice
- n o~er
100
financings totaling o~er
$15 billion for airport
clients.
- Current
airport
clients include Chicago, New York, Washington, Tampa, San Jose, Oakland, Salt lake, Reno, Columbus, Pro~idence,
- Ft. Myers, Pittsburgh, Memphis and
many
- thers
4
Pie c ing T
- ge the r
Bond F inanc e
Pr e se nte d by Ke nne th D. F ulle r ton & Robe r t G. F r iar , Jr .
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
1 2 3 4 5 6
T
- pic s
T he Yie ld Curve
Wha t it is a nd why it ma tte rs
Bo nd Pric ing
Pa r, pre mium a nd disc o unt b o nds
Othe r T ype s o f Bo nds
Ca pita l a ppre c ia tio n a nd ze ro c o upo n b o nds
Bo nd Re de mptio ns a nd Ac c rue d I nte re st
Ho w b o nds a re re de e me d
Spre a dshe e t F
- rmula s
Built in func tio ns fo r do ing b o nd c a lc ula tio ns
Co nc lusio n
6
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
- 1. T
he Yie ld Cur ve
Definition: A curve on a graph in which the yield of fixed-interest securities is plotted against the length of time they have to run to maturity.
- Unde r no rma l c o nditio ns, inte re st ra te s o n b o nds with sho rte r ma turitie s a re lo we r tha n
b o nds with lo ng e r ma turitie s.
Normal Yield Curve
(Upwardly Sloping)
4.000% 3.500% 3.000% 2.500% 2.000% 1.500% 1.000% 0.500% 0.000%
Years to Maturity
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
7
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
T he Yie ld Cur ve
(c o ntinue d)
T he re a re a numb e r o f the o rie s tha t a tte mpt to e xpla in why ra te s te nd to b e hig he r o n lo ng e r ma turitie s tha n o n sho rte r o ne s (a n “upwa rdly slo ping ” yie ld c urve ). T wo o f the se a re :
- L
iq uidity Pre mium: All thing s b e ing e q ua l, inve sto rs fa c e g re a te r unc e rta inty ho lding lo ng e r te rm b o nds tha n sho rte r te rm b o nds. Ma ny mo re unpre dic ta b le thing s a re like ly to ha ppe n in the ne xt te n ye a rs tha n in the ne xt two a nd inve sto rs de ma nd hig he r ra te s to c o mpe nsa te the m fo r this risk.
- Ma rke t E
xpe c ta tio ns: T his the o ry sa ys tha t inve sto rs, in g e ne ra l, e xpe c t inte re st ra te s in the future will b e hig he r tha n the y a re to da y. T he y the re fo re de ma nd a n inte re st ra te o n lo ng e r ma turitie s tha t wo uld g ive the m the sa me e xpe c te d to ta l re turn ha d the y a lte rna tive ly inve ste d in a sho rte r ma turity a nd the n re inve ste d a t the hig he r future ra te tha t the y a re e xpe c ting .
8
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
T he Yie ld Cur ve
(c o ntinue d)
- T
he yie ld c urve c a n, a nd do e s, c ha ng e o ve r time a s e c o no mic c o nditio ns c ha ng e . I t c a n fla tte n so tha t sho rt te rm ra te s a nd lo ng te rm ra te s a re the sa me .
Flat Yield Curve
6.000% 5.000% 4.000% 3.000% 2.000% 1.000% 0.000%
Years to Maturity
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
9
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
T he Yie ld Cur ve
(c o ntinue d)
T he yie ld c urve c a n e ve n b e c o me inve rte d whe re lo ng te rm ra te s a re lo we r tha n sho rt te rm ra te s. An inve rte d yie ld c urve is c o nside re d to b e a ne g a tive e c o no mic indic a to r. I nve rsio n indic a te s tha t inve sto rs b e lie ve inte re st ra te s will b e lo we r in the future a nd tha t
- fte n me a ns tha t e c o no mic a c tivity is a t le a st like ly to slo w a nd ma y e ve n me a n tha t a
re c e ssio n is c o ming . A tig hte ning o f sho rt te rm inte re st ra te s b y the F e de ra l Re se rve in
- rde r to slo w the e c o no my o r to re ig n in infla tio n c a n le a d to a n inve rte d yie ld c urve .
Inverted Yield Curve
6.000% 5.000% 4.000% 3.000% 2.000% 1.000% 0.000%
Years to Maturity
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
10
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
T he Yie ld Cur ve
(c o ntinue d)
- So fa r in 2011 the US T
re a sury yie ld c urve ha s fla tte ne d c o nside ra b ly:
2011 Treasury Yield Curve
Treasury Yields 6.000% 5.000% 4.000% 3.000% 2.000% 1.000% 0.000% January October 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
Years to Maturity
11
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
- 2. Bond Pr
ic ing
Pa r, Disc o unt a nd Pre mium Bo nds
- T
he yie ld o n a b o nd is a me a sure o f the re turn, o r e a rning s, re a lize d b y ho lding the b o nd. T he yie ld is de te rmine d b y the ma rke t, the o ve ra ll supply a nd de ma nd fo r the b o nd. As we sa w in the pre vio us se c tio n the yie ld will usua lly va ry de pe nding o n the fina l ma turity
- f the b o nd.
- T
he pr
ic e to b e pa id fo r a b o nd a lso va rie s b a se d o n the c oupon tha t is a tta c he d to the
b o nd. T he c o upo n is the a c tua l ra te o f inte re st tha t is pa id to the b o nd inve sto r. A 5% c o upo n o n a $100 b o nd pa ys $5/ ye a r.
- Par
Bond
- A par
bond is a b o nd tha t c a n b e purc ha se d a t 100% o f its unde rlying va lue . A b o nd tha t
ha s a princ ipa l a mo unt o f $100 a t ma turity will se ll fo r $100 rig ht no w if its c o upo n is the sa me a s the c urre nt ma rke t yie ld. Of c o urse , ma rke t yie lds c ha ng e a ll the time so a b o nd tha t is tra ding a t pa r to da y ma y no t b e tra ding a t pa r to mo rro w.
12
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
Bond Pr ic ing
Pa r, Disc o unt a nd Pre mium Bo nds
- One o f the mo re c o nfusing a spe c ts o f b o nds is the re la tio nship b e twe e n pric e a nd yie ld:
- I
f yo u o wn a b o nd a nd the mar
ke t yie ld r ise s the pr ic e of the bond falls.
- I
f yo u o wn a b o nd a nd the mar
ke t yie ld falls the pr ic e of the bond r ise s.
- T
his is a table that shows what happe ns to the pr ic e of a bond with a 5% c oupon as mar ke t yie lds c hange
Years to Market Maturity Coupon Yield Price 5 5.00% 4.00% 104.491 5 5.00% 4.25% 103.347 5 5.00% 4.50% 102.217 5 5.00% 4.75% 101.101 5 5.00% 5.25% 98.913 5 5.00% 5.50% 97.840 5 5.00% 5.75% 96.781 5 5.00% 6.00% 95.735
13
5 5.00% 5.00% 100.000 PAR D I S C O U N T P R E M I U M
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
Bond Pr ic ing
(c o ntinue d)
Disc ount Bond
I f ma rke t yie lds rise to 6%, the b o nd with a 5% c o upo n is no w pa ying a ra te tha t is le ss va lua b le in the ma rke t. T he pric e o f this le ss va lua b le b o nd will the re fo re fa ll a nd it will tra de a t a disc ount to pa r, sa y 95% o f its o rig ina l pa r va lue if it is five ye a rs until the b o nd ma ture s. By tra ding a t a disc o unt this b o nd will ha ve a n o ve ra ll yie ld o f 6% (the ra te de ma nde d b y the ma rke t): 5% fro m the c o upo n a nd a n a dditio na l 1%/ ye a r fro m the disc o unt a s the pric e o f the b o nd will slo wly a ppro a c h 100% o f its pa r va lue a t ma turity. One re a so n a n inve sto r mig ht pre fe r a disc o unt b o nd: Ca ll pro te c tio n. I n a ma rke t e nviro nme nt whe re inte re st ra te s a re fa lling it will ta ke lo ng e r fo r a b o nd with a lo we r c o upo n to b e pro fita b ly c a lle d b y the issue r o f the b o nd whe n c o mpa re d to a hig he r c o upo n b o nd.
14
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
Bond Pr ic ing
(c o ntinue d)
Pr e mium Bond
I f ma rke t yie lds fa ll to 4%, the b o nd with a 5% c o upo n is no w pa ying a ra te the is mo re va lua b le in the ma rke t. T he pric e o f this mo re va lua b le b o nd will the re fo re rise a nd it will tra de a t a pr
e mium to pa r, sa y 105% o f its o rig ina l pa r va lue if it is five ye a rs until the
b o nd ma ture s. By tra ding a t a pre mium this b o nd will ha ve a n o ve ra ll yie ld o f 4% (the ra te de ma nde d b y the ma rke t): 5% fro m the c o upo n a nd -1%/ ye a r fro m the pre mium a s the pric e will slo wly de pre c ia te to 100% o f its pa r va lue a t ma turity. One re a so n a n inve sto r mig ht pre fe r a pre mium b o nd: L e ss vo la tility. I f inte re st ra te s rise a fte r yo u purc ha se the b o nd the va lue o f the b o nd will fa ll…b ut no t a s q uic kly a s a lo we r c o upo n b o nd wo uld. T his is b e c a use mo re o f the va lue o f the b o nd is in the stre a m o f e a rly c o upo n pa yme nts to b e re c e ive d in the ne a r future ra the r tha n the fina l ma turity a mo unt. T he pre se nt va lue o f pa yme nts to b e re c e ive d e a rlie r (in this c a se , the c o upo n pa yme nts) do e sn’ t c ha ng e a s muc h a s pa yme nts re c e ive d la te r (like the princ ipa l a mo unt a t ma turity) fo r a g ive n c ha ng e in inte re st ra te s.
15
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
- 3. Othe r
T ype s of Bonds
Ca pita l Appre c ia tio n a nd Ze ro Co upo n Bo nds
- Mo st fixe d ra te b o nds pa y se mi-a nnua l inte re st a t the sa me ra te o ve r the ir e ntire life …the
c o upo n ra te o n the b o nd. T his type o f b o nd is c a lle d a “Cur
r e nt Inte r e st Bond.”
- So me b o nds do n’ t pa y a ny inte re st until ma turity. Sinc e the y do n’ t ha ve a ny c o upo n
pa yme nts inve sto rs b uy the m a t a disc o unt to the ir pa r va lue . F
- r insta nc e , in a ma rke t
whe re o ne ye a r b o nds yie ld 5.00%, a b o nd tha t ma ture s in o ne ye a r b ut ha s no c o upo n pa yme nts wo uld se ll fo r 95.238% o f its pa r va lue . (5% o f 95.238 is 4.762 so tha t the inve sto r will b e e a rning 5.00% o n the $95.238 he o r she spe nds to b uy the b o nd, g e tting $100 b a c k a ye a r fro m no w).
- Bo nds tha t o nly pa y inte re st a t ma turity a re c a lle d “Capital Appr
e c iation” o r “Ze r
- Coupon”
b o nds. Ma the ma tic a lly, the y a re ide ntic a l. T he diffe re nc e is tha t the initia l purc ha se pric e is c o nside re d to b e the princ ipa l a mo unt fo r a Ca pita l Appre c ia tio n Bo nd (o r “CAB”) while the va lue a t ma turity is c o nside re d to b e the princ ipa l a mo unt fo r a Ze ro Co upo n Bo nd. I n e ithe r c a se the va lue o f the b o nd inc re a se s o ve r time until ma turity.
16
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
Othe r T ype s of Bonds
Ca pita l Appre c ia tio n a nd Ze ro Co upo n Bo nds
T he ta b le b e lo w sho ws two diffe re nt de b t se rvic e pa yme nt stre a ms fo r a $10 millio n b o nd issue d with a 5% yie ld. T he first is issue d a s a c urre nt inte re st b o nd a t pa r (so the c o upo n is 5.00%), the se c o nd a s a ze ro c o upo n b o nd o r CAB.
Debt Service Payments on a … Zero Accreted Coupon Value of Current Interest Bond Bond or Zero Coupon Year Principal Interest Total CAB Bond or CAB 10,000,000 1 500,000 500,000 10,500,000 2 500,000 500,000 11,025,000 3 500,000 500,000 11,576,250 4 500,000 500,000 12,155,063 5 500,000 500,000 12,762,816 6 500,000 500,000 13,400,956 7 500,000 500,000 14,071,004 8 500,000 500,000 14,774,554 9 500,000 500,000 15,513,282 10 10,000,000 500,000 10,500,000 16,288,946 16,288,946 10,000,000 5,000,000 15,000,000 16,288,946 17
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
- 4. Bond Re de mptions a nd Ac c rue d Inte re st
Se ria l, T e rm Bo nds a nd Sinking F und Pa yme nts
Ove r vie w
Whe n b o nds a re initia lly so ld b y a n issue r the y a re so ld with a c o upo n ra te a nd a re g ive n a ma turity da te …the da te whe n the b o nds a re sc he dule d to b e pa id o ff b y the issue r o f the b o nds. T he re a re two type s o f struc ture s tha t the se b o nds c a n ta ke .
Se r ial Bonds
T he se b o nd a re stra ig htfo rwa rd. T he y a re struc ture d a s yo u wo uld e xpe c t a b o nd to b e struc ture d: a kno wn c o upo n ra te a nd a spe c ific ma turity da te . Whe n yo u b uy this b o nd yo u kno w wha t inc o me yo u e xpe c t to re c e ive until the ma turity da te whe n yo ur
- rig ina l princ ipa l a mo unt will b e re turne d to yo u (a ssuming a ll g o e s we ll!).
18
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
Bond Re de mptions a nd Ac c rue d Inte re st
(c o ntinue d)
T e r m Bonds
T he se b o nds do n’ t pa y b a c k the e ntire princ ipa l a t ma turity. I nste a d, a po rtio n o f the princ ipa l is pa id b e fo re the fina l ma turity. T he se pa yme nts a re c a lle d “sinking fund pa yme nts.” Bo nds a re so ld a s te rm b o nds usua lly fo r c o nve nie nc e …it is e a sie r to se ll a te rm b o nd o f $10,000,000 with a fina l ma turity da te o f 1/ 1/ 2015, b ut with sinking fund pa yme nts fro m 2012 thro ug h 2014, tha n it is to se ll fo ur se pa ra te b o nds with $2,500,000 ma turitie s fro m 2012 thro ug h 2015. L a rg e inve sto rs (like b o nd mutua l funds) a lso like b uying la rg e r ma turitie s a s the y a re mo re liq uid a nd e a sie r to tra de . He re is wha t the struc ture lo o ks like fo r this te rm b o nd:
Payment Principal Date Amount 1/1/2012 2,500,000 Sinking Fund Payment 1/1/2013 2,500,000 Sinking Fund Payment 1/1/2014 2,500,000 Sinking Fund Payment 1/1/2015 2,500,000 Final Maturity 10,000,000 19
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
Bond Re de mptions a nd Ac c rue d Inte re st
(c o ntinue d)
T e r m Bonds (c ontinue d)
T he o wne r o f a te rm b o nd is no t sure e xa c tly whe n the b o nd, o r a po rtio n o f the b o nd, is g o ing to b e re de e me d. T he b o nds tha t a re pic ke d to b e re de e me d o n a sinking fund pa yme nt da te a re usua lly c ho se n ra ndo mly a nd the o wne rs a re info rme d tha t a po rtio n o f the ir b o nd ha s b e e n pic ke d to b e re de e me d. I n the pre vio us e xa mple a ho lde r o f a $1,000,000 b o nd c o uld ha ve a ll, o r so me , o f the b o nd “c a lle d a wa y” o n Ja nua ry 1st o f 2012, 2013, o r 2014 with a ny re ma ining princ ipa l re pa id o n Ja nua ry 1, 2015 (its fina l ma turity da te ).
20
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
Bond Re de mptions a nd Ac c rue d Inte re st
(c o ntinue d)
Bond Re de mptions and Ac c r ue d Inte r e st
Bo nds a re e ithe r re de e me d a t ma turity, o n a re g ula rly sc he dule d sinking fund pa yme nt da te (fo r te rm b o nds), o r o n a n o ptio na l c a ll da te . Mo ne y fo r the pa yme nt is tra nsfe rre d fro m the issue r o f the b o nds to a “pa ying a g e nt” who ha ndle s g e tting the mo ne y to the b o nd ho lde r in e xc ha ng e fo r the b o nd whic h is the n c a nc e le d. At the time the b o nd is re de e me d inte re st o n the b o nd is a lso due . T his is the ac c r
ue d inte r e st tha t ha s a c c umula te d sinc e the la st inte re st pa yme nt da te o n the b o nds.
E ve ry da y tha t pa sse s inte re st “a c c rue s” o n a b o nd a nd is a n o b lig a tio n tha t the issue r
- f the b o nd ha s to the o wne r o f tha t b o nd.
Bond Humor Q:What is the difference between a bond and a bond trader? A: A bond matures.
21
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
- 5. Spr
e adshe e t F
- r
mulas
- Spre a dshe e t pro g ra ms ha ve b uilt in func tio ns fo r c a lc ula ting b o nd pric e s, b o nd yie lds, a nd
- the r fina nc ia l va lue s. I
n E xc e l the se inc lude :
=Price() Calculates the price of a bond =Yield() Calculates the yield on a bond =IRR() Calculates the Internal Rate of Return
- n a bond issue
=PMT() Calculates the annual payments required in order to pay off a bond issue
T he ne xt fe w pa g e s c o nta in de sc riptio ns o f the se func tio ns a nd ho w the y a re use d.
22
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=Pr ic e ()
Ca lc ula te s the pric e o f a b o nd (g ive n the yie ld)
- T
he pric e o f a b o nd , a t its mo st b a sic , is simply the pre se nt va lue o f a ll the future pa yme nts to b e re c e ive d
- n tha t b o nd . T
he d isc o unt ra te use d to c a lc ula te tha t pre se nt va lue is the yie ld o n the b o nd . T he a rg ume nts tha t the func tio n ta ke s (se pa ra te d b y c o mma s) a re :
=Price(delivery date, maturity date, coupon, yield, value at maturity, number of coupon payments/year, day count basis)
delivery date The date the bond is paid for (also called the “settlement date”). maturity date The date the final principal payment is received on the bond. coupon The periodic interest received on the bonds (5% coupon on a $100 bonds pays $5/year). yield The rate used to calculate the present value of the future payments to be received value at maturity The value of the bond at maturity (per $100 face value). For municipal bonds you would usually just enter 100. number of coupon For municipal (and the majority of other) bonds you receive semi-annual payments so payments/year you enter 2. day count basis Different kinds of bonds have different methods for counting the days between dates, whether it is the actual number of days or a simplified way assuming 30 day months. Municipal bonds use the 30 day month/360 day year convention so you enter 0 in this field (although you can leave it blank in which case Excel assumes it is 0).
23
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=Pr ic e ()
Ca lc ula te s the pric e o f a b o nd (g ive n the yie ld)
- E
xample Pr
- ble m:
Yo u a re b e ing a ske d if yo u a re inte re ste d in b uying a munic ipa l b o nd. Yo u a re no t to ld wha t the pric e o f the b o nd is b ut yo u a re to ld tha t the yie ld is 4.50%, the c o upo n is 5.00%, tha t the ma turity da te is Ja nua ry 1st, 2030 a nd the de live ry da te is Se pte mb e r 19th, 2011. Ho w c a n yo u use this info rma tio n to c a lc ula te the pric e o f the b o nd?
24
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=Pr ic e ()
(c o ntinue d)
- E
xample Pr
- ble m:
25
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=Yie ld()
Ca lc ula te s the yie ld o n a b o nd (g ive n the pric e )
- T
he yie ld o n a b o nd is the e a rning s ra te yo u re c e ive if yo u purc ha se it. T e c hnic a lly, it is a n Inte rna l Ra te o f Re turn (IRR): the ra te tha t, whe n use d to pre se nt va lue a ll the future pa yme nts to b e re c e ive d fro m the b o nd e q ua ls its purc ha se pric e . T he a rg ume nts tha t the func tio n ta ke s (se pa ra te d b y c o mma s) a re :
=Yield (delivery date, maturity date, coupon, price, value at maturity, number of coupon payments/year, day count basis)
delivery date The date the bond is paid for (also called the “settlement date”). maturity date The date the final principal payment is received on the bond. coupon The periodic interest received on the bonds (5% coupon on a $100 bonds pays $5/year). price The price paid for the bond. value at maturity The value of the bond at maturity (per $100 face value). For municipal bonds you would usually just enter 100. number of coupon For municipal (and the majority of other) bonds you receive semi-annual payments so payments/year you enter 2. day count basis Different kinds of bonds have different methods for counting the days between dates, whether it is the actual number of days or a simplified way assuming 30 day months. Municipal bonds use the 30 day month/360 day year convention so you enter 0 in this field (although you can leave it blank in which case Excel assumes it is 0).
26
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=Yie ld()
Ca lc ula te s the yie ld o n a b o nd (g ive n the pric e )
- E
xample Pr
- ble m:
T his time whe n yo u a re o ffe re d the munic ipa l b o nd yo u a re to ld the pric e b ut no t the yie ld. Yo u a re to ld tha t the pric e o f the b o nd is 106.179, the c o upo n is 5.00%, the ma turity da te is Ja nua ry 1st, 2030, a nd the de live ry da te is Se pte mb e r 19th, 2011. Ho w c a n yo u use this info rma tio n to c a lc ula te the yie ld o n the b o nd?
27
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=Yie ld()
(c o ntinue d)
- E
xample Pr
- ble m:
28
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=IRR()
Ca lc ula te s the Inte rna l Ra te o f Re turn/ T rue Inte re st Co st o f a b o nd issue
- Whe n a b o nd issue is so ld it is ma d e up o f a numb e r o f se pa ra te b o nd ma turitie s tha t a re so ld a t o ne
time . Just a s we sa w tha t the yie ld o n a b o nd is the inte re st ra te tha t e q ua te s the pre se nt va lue o f a ll future pa yme nts to b e re c e ive d o n a b o nd to its pric e , the IRR e q ua te s the pre se nt va lue o f the pa yme nts to b e ma d e o n AL L the b o nd ma turitie s issue d to the NE T pric e re c e ive d fro m the sa le o f a ll the se pa ra te b o nd s (ne t o f the c o sts a sso c ia te d with issuing the b o nd s). In e sse nc e , the IRR is the o ve ra ll inte re st ra te o n the b o nd issue a nd in the munic ipa l b o nd wo rld this is c a lle d the T rue Inte re st Co st (T IC).
=IRR(values, guess)
values A series of amounts entered in the separate cells of the spreadsheet. There must be at least one negative and one positive value. guess A yield that gives the IRR formula a place to start its calculations. If omitted Excel assumes 10% (0.10).
29
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=IRR()
(c o ntinue d)
- E
xample Pr
- ble m:
As a n e xa mple , we will a ssume a simple b o nd issue with just two se pa ra te ma turitie s. I n the spre a dshe e t, we e nte r the c a sh flo ws to b e pa id o n e a c h b o nd so ld, a dd up tho se pa yme nts b y da te pa id, a dd up the to ta l a mo unt re c e ive d fro m the sa le o f the b o nds a nd the n c a lc ula te the I RR.
- Our simplifie d b o nd issue is so ld o n Ja nua ry 1st, 2012. T
he first b o nd ma ture s o n 1/ 1/ 2014, ha s a c o upo n o f 4.00%, a yie ld o f 4.25% (a nd the re fo re a pric e o f 99.525), a nd a pa r va lue o f $500,000. T he se c o nd ma turity is o n 1/ 1/ 2015, ha s a c o upo n o f 4.75%, a yie ld
- f 4.50% (a nd the re fo re a pric e o f 100.694), a nd a pa r va lue o f $750,000.
- T
he c o sts a sso c ia te d with issuing the b o nds (pa yme nts to la wye rs, unde rwrite rs, a nd fina nc ia l a dviso rs) is 1.00% o f the to ta l pa r va lue . With this info rma tio n we c a n c a lc ula te the o ve ra ll T rue I nte re st Co st (I RR) o f the b o nd issue .
30
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=IRR()
(c o ntinue d)
- E
xample Pr
- ble m:
31
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=PMT ()
Ca lc ula te s the a nnua l pa yme nts re q uire d to pa y o ff a b o nd issue
- T
he pa yme nt func tio n is a ha nd y fo rmula fo r e stima ting the a nnua l pa yme nts re q uire d to pa y o ff a b o nd issue . It is no t a s a c c ura te a s a c o mple te d e b t se rvic e sc he d ule whic h inc o rpo ra te s a ll o f the d e ta ils o f the b o nd s tha t ma ke up a n e ntire b o nd issue b ut is d o e s pro vid e a ra the r q uic k, simple a nd a c c ura te e stima te
- f the a nnua l pa yme nts re q uire d to pa y o ff a b o nd issue o f a g ive n size with e q ua l a nnua l pa yme nts.
=PMT(interest rate, years to maturity, bond issue size, 0, 0)
interest rate An estimate of the average interest rate of the bond issue. years to maturity How long the bond issue will be outstanding. bond issue size The total amount of bonds to be issued.
32
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=PMT ()
Ca lc ula te s the a nnua l pa yme nts re q uire d to pa y o ff a b o nd issue
- E
xample Pr
- ble m:
Sma llville is c o nside ring a pro po sa l to b uild a ne w lib ra ry tha t c o sts $10 millio n. I t wa nts to q uic kly ma ke a g e ne ra l e stima te o f wha t the a nnua l de b t se rvic e wo uld b e o n a b o nd issue d to b uild the lib ra ry.
- We a ssume the b o nds will ha ve a 30 ye a r fina l ma turity, the a ve ra g e b o nd ra te
will b e 5.00% a nd the a nnua l pa yme nts o n the b o nds will b e le ve l o ve r the life o f the b o nds.
33
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA
=PMT ()
(c o ntinue d)
- E
xample Pr
- ble m:
34
zyxwvutsrqponmlkjihgfedcbaZYXWVUTSRQPONMLKJIHGFEDCBA Conc lusion
Ple a se c o nta c t us if yo u ha ve a ny o the r q ue stio ns:
K e n F ulle rto n (727)319-9292 F ulle rto nK @ pfm.c o m Ro b e rt F ria r (303)786-8088 F ria rR@ pfm.c o m
The California Debt and Investment Advisory Commission Technical Webinar Series
Thank You
for
Participating
For more information about CDIAC's upcoming webinars and seminars please visit
www.
treasurer .ca.gov
1
cdiac
- r call (916) 653-3269
To sign up for CDIAC’s seminar notification service visit: www.treasurer.ca.gov and click on “Sign Up to Receive Information on CDIAC Seminars and Publications”