1. Using Excel to Price a Bond
In Excel, the present value or PV function can be used to find
the price of a bond. The PV function has five inputs,
the rate is the yield per period. The yield to maturity of this
bond is 6%. As the bond pays it's coupon annually and the yield to
maturity as an annual yield, the rate per period is 6%.
NPER stands for the number of periods. This is a five year annual
bond, so the number of periods is five. PMT stands for payment,
and when pricing a bond, this is the coupon expressed as a dollar
amount. They coupon rate is 5%, but par value is 100.
So 5% multiplied by 100 gives a coupon payment of
$5, but we'll just enter this as five in the PMT formula.
If there is the future value, pricing a bond, this is the par
value on the maturity date. For this bond, the par value is $100,
which again, we'll just input is 100 into the PMT formula.
Finally, type describes the timings of the cash flows.
For a bond, the coupon payments are paid at the end of the
period, so the type is zero. Let's switch to Excel and see this
formula for ourselves. So here I am back in bond pricing template workbook,
and I'm going to show you how to use the PV function to
price a bond. Now, we can already see the price of the bond
when the yield 6%, the 5%, annual coupon bond is priced at 95.79, we
can trade that using discount cash flow, so let's use the PV function
to see if we can get the same price. The rate is the
rate per period, and in this case, it's the yield to maturity,
6%. The number of periods, but it's a five year annual bond,
so the number periods, five, the payment are the coupon payments,
which is $5 per year, and the future value is the par value of
the bond when the bond matures, remember that bond holder gets the 100
back, so the future value was 100, so what's the price?
So let's use the PV function, and again, remember to put a negative at the
front to make sure that the prices are positive, we have the rate per period,
we have the number of periods, we have the payment and we have
the future value, and as always in this course, the type zero,
that's just assumed that their coupon payments happen at the end of the
period. And we can see that we get the same price 95.79.
2. Let's practice!