GEK2507

1
Compound Prosper!

• GEK2507

Frederick H. Willeboordse frederik_at_chaos.nus.edu.s
g
2
Bonds

• Lecture 9

3
Todays Lecture

• Excel
• Inflation
• What are Bonds in finance
• How can we use Excel to calculate a reasonable
  bond price.

A bond from 1863
4
Excel Basics Date Math
Syntax
Calculating the number of days between two dates
or doing math on dates is tricky.Hence Excel
stores dates to number of days since Jan 1,
1900. This is done with the DATE function.
How the date is displayed in a cell depends on
how the cell is formatted. In the top picture it
is formatted as a date and in the bottom
picture it is formatted as a number.
5
Excel Basics Date Math
This allows us to easily calculate the number of
days from one date to another.
6
Excel Basics Series Fill
Result
1) Enter the first value
3) Go to the Fill-Series Menu
4) Choose how you want to do the fill
2) Select a range to fill
7
Inflation
Inflation is the phenomenon that goods become
more expensive (and hence that thus their price
inflates). As a consequence either one dollar
can buyless goods, or one needs more dollars to
payfor the same item. Hence inflation has an
effect inverse to that of receiving interest.
8
Inflation
Effect of inflation on the value of 1000 in
current dollars.
Inverse of the future value formula. Same as
present value formula!
9
Inflation
Note the subtle point - Though close, there is a
difference between 1000 (1-r) and
1000/(1r) Eg. 1000 0.97 970 and 1000/1.03
970.87 This may look like a small difference,
but differences can add up!
Note
10
Inflation
From this calculation we see that in terms of
todays buying power our original 1000 will only
be worth 744 dollars in ten years. But we had
also seen in the lecture on values that our 1000
will grow to 2594 if invested at 10 a year. Oh
thats only 256 dollars less so in 10 years,
after inflation,we still should have 2594
256 2338 dollars Not too bad... WRONG!
11
Inflation
If we know that we have 2,594 dollars in 10 years
then we need to discount this back to today with
the prevailing inflation rate in order to see how
much that is in todays dollars.
Hence well only have 1930
12
Inflation
For this case it is more useful to combine the
two calculations
Indeed the same
13
Inflation
Couldnt we just say Effective Interest
Interest Inflation?
Its different!
14
Inflation
Its different because interest and inflation are
multiplicative factors. Or 1/1.03 1.1
1.06796 unequal 1.07! Its small but
nevertheless important.
15
Bonds
In finance, a bond is a loan contract where an
investor lends a government or a company a
certain amount of money and then receives
interest at regular intervals for some period of
time. The contract has a certain length and the
originally stated loan amount is paid back in
full at the end of the contract.
The US Treasury
16
Bonds

• The are some terms one should be familiar with
• Face Value This is also called Par Value,
  Maturity Value or Principal. This is the
  amount that needs to be paid back at the end.
• Maturity Date The date on which the principal
  needs to be returned.
• Coupon Rate The percentage of the regular
  interest.

17
Bonds
Bonds also have certificates like stocks. And
also just like stocks, bonds can be traded. Hence
the prices of bonds can fluctuate quite a bit. In
fact because theres a fixed regular interest on
the bond and because market interest rates can
change, the calculation of a reasonable price for
a bond is not trivial. More on this later Even
when the bonds are issued, one does not
necessarily have to pay exactly the principal
amount. There can be a premium or a discount.
18
Bonds
An important concept in finance is that of a/the
discount rate. In the case of bonds this equals
the interest rate that the Federal Reserve
charges other banks on short term loans. The
discount rate is an important reference rate for
other interest rates. Why is it called the
discount rate? It has to do with the present
value of an investment that will pay off in the
future.
19
Bonds
If e.g. we know that we will get 1000 dollars for
a bond next year, and if the current interest
rate is 5, how much would the bond be worth
now. If the interest rate is 5, the investment
will be worth 105 in one year. Hence 1000
dollars (the end value of our investment) 105
of current value. In other words current value
1000/105 952 dollars. How about if we get
1000 dollars in two years?
20
Bonds
Time for Excel!
Inverse of the future value formula. Same as
present value formula!
Why the 1?
21
Bonds
Hence, for a bond which will give us 1000 dollars
in 10 years time, 614 dollars is a reasonable
price. Technically speaking, this is a zero
coupon bond since we are not getting any interest
paid each year. Lets do the calculation again
for the case that we get a yearly interest
payment.
22
Bonds
The face value of the bond is 1000 dollars and at
the end of the year we get 50 dollars interest
(the interest rate is 5). This is a bit trivial
so dont think about it too much! In one year our
investment will be worth 105 but we will also
have 1050 dollars. As before current value
(end value)/105 1050/105. This is of course
just 1000 dollars! The same would be true for
ten years.
23
Bonds
Hence 10 year 1000 dollar bond with 5
interest paid yearly Current Value 1000. 10
year 1000 dollar bond with no interest paid
yearly Current Value 614. Perhaps this makes it
a bit clearer why its called the discount
rate. And now bang the big point
24
Bonds
Interest rates change!!!!! When a bond is
issued, its interest rate is set to the
prevailing rate of that moment. It should come
as no surprise that the value of a bond will also
be influenced by the difference between its
originally set (and let me stress fixed) coupon
rate and the currently prevailing rate.
25
Bonds
Let us first illustrate this with an extreme
example During freak-year 1, the interest rate
is 50. A 1000 dollar 10-year bond is issued and
it will pay 500 dollars a year in interest. A
year later were in Japan during the nineties
mode and interest has dropped to 1. Since a new
bond will only give us 1 a year (1 dollar vs the
500 dollars of the freak-bond), the freak-bond
must be worth a lot. Roughly 9 (the remaining
years) 500 principal 5500 dollars.
26
Bonds
Let us look at this a bit more carefully We
have a bond that will give us 1000 dollars in 9
years time that will also pay us 500 dollars of
interest a year. Since the interest received can
be reinvested at the end of the nine years we
should have a bit more than the 5500 dollars.
The CBOT in action.
Naturally bonds can be traded.
27
Bonds
With Excel we find that at the end we have
Interest on Bond
Cash from previous year current interest
This is the cash we end up with
28
Bonds
Now we can discount this back to today
And we find that the current value is 5,197
dollars.
29
Bonds
Excellent! Again, a conceptually far from trivial
problem could be solved with Excel by
UNDERSTANDING it. Of course we can also put
this in equations
Bond Value
Present Value of Coupons
Present Value of Face Value
With T Time to Maturity, r Current Interest
Rate
30
Bonds
Lets try that out
This is straightforwardly calculated in Excel
(Excel is a great general purpose calculator)
31
Bonds
Now we can discount this back to today
And we find that the current value is 5,197
dollars. Hey! That is the same value we found
before.
32
Bonds
Good to know that the Math works! Excel has a
large number of built-in functions. Hence if one
tries to do something that appears to be quite
standard, its always a good idea to try and see
whether theres a corresponding Excel
function. And indeed the function is PV
33
Bonds
Using the Excel built-in function
PV()
And we find that the current value is . 5,197
dollars. Yes, we did it again!
Note the minus, an Excel convention
34
Bonds
Hence we have found three ways to solve our
problem

• Understand the problem and use simple Excel
  functions
• Use Math
• Use the black-box Excel built-in function

Which one is the best?
35
Bonds
Thus far we have determined the current value of
the bond based on the present and past interest
rates. However, on the bond market one does not
directly trade in yields but in bonds. This
basically reverses the problem as If I buy a
bond at certain price, what is its yield?
36
Bonds
We can again use the Solver to do the job
The key thing to realize is that the Premium is 0
at the correct yield.
37
Bonds
And (should I say naturally) theres also an
Excel function that can do the job
RATE()
Face Value
Number of years left
Purchase Price
Coupon
38
Key Points of the Day

• Bonds
• Discount Rate
• Using Excel to Understand and Calculated Yields
  and Values