1. Using Excel to Find YTM
Another useful Excel function when it comes to working with bonds is the
rate function. This can be used to find the yield to maturity of
a bond, which without Excel is actually fairly complex mathematically.
For the CFI bond that we've been using in this chapter,
let's start with the number of periods or NPER, which is five and the
payment or PMT, which remember is the coupon and dollars, which is $5.
PV is the present value or price of the bond. The bond's current price is
95.79. But we need to input this as a negative to show this
is a cash flow paid by the investor rather than a cash flow received
by the investor, like the coupons. The future value or FV is still
100 and the type is zero, indicating that the cash flow is currently end
of the period. Again, let's move over to Excel and walk through this
example together. Now, pay attention, because I'm going to ask you to have
a practice by giving you some practice questions after this demo.
So here I am back in the bond pricing template, and I'm back
on the demo sheet, and I want to use the rate function to
show that the yield to maturity of this bond is 6%.
And that's the input that we use when we press the bond using
DCF. So let's start with a number of periods, which is five because of
the five year annual bond. The payment is also five because they're 5% coupon
and the par value is 100. The PV, the present value is the
price of the bond, and we know the price of the bond is
95.79, so I'm just going to pick it up from cell D26.
And the future value, remember is the par value of the bond,
which is 100, not zero, as I mistakenly just put. So, the year out
input is for the right function. So if I start typing rate, open
bracket, let's see what we've got, the number of periods is five,
the payment as five, the PV, the price of the bond is 95.79, and
the future value is 100. And again, we're going to use zero as
the type for the end of the period.
Now we've got an error here, and you might see why.
Alright. If I go along and activate the cell, we've got both the
present value, which is the price you pay for the bond which is
like an outflow, and the future value, which is the power of value
we receive when the bond matures, which is like an inflow both as
positive numbers. But really these are cash flows going in different directions.
So we need to make one of these a negative. And it makes
sense to make the PV the price for the bond a negative number,
because I like to think of the price of the bond as money
going out when we buy the bond. So I'm just going to get
back in the front of I23 put a negative. When I do that,
we see that we've got a yield to maturity of 6%,
which is the same yield to maturity we use when we price the
bond using discounted cash flows.
2. Let's practice!