Welcome to Finance in Excel video number 49.
Hey, if you want to download this workbook for chapter 6
or the PDF for chapter 6, click on the link below the video
and then scroll way down to the finance class section.
Hey, sometimes we're given bond price,
or bond prices are quoted like this--
so 1.00936, or 100.936%.
Or you might see it written like this--
one year 10% coupon bond is priced at 100.936%.
So from this information, we want
to be able to make all of our calculations.
We can assume the face is 1,000.
We can calculate the coupon.
We can calculate all this, including the yield to market.
So let's go ahead over to Excel and see if we can do this.
Now, years to maturity--
we have one.
We're going assume that it's semiannual,
so our number of compounding periods n is 2.
Our face value, assumed to be 1,000.
We have a coupon rate of 10%.
We can calculate our interest payment,
and do that right here-- just equals 10% divided
by our n times our face value.
We'll do this from the point of view of the bond holder.
So this is positive and this is positive.
Of course, this right here is also called the coupon.
And we calculate our price.
Interest payment equals coupon payment.
And that's going to be our present value.
And we can get that-- oh, we have to--
I'm going to move this down here like that,
and right here I'm going to put quoted price.
And then it is either 100% or 6.
1.00936.
We can now calculate.
We'll just take our face times the quoted price.
And that has to be negative, so I'm
going to put it into edit mode and put a negative.
Now, let's see.
So this one and this one are calculated.
I'm going to right-click and un-minimize here.
OK, so we have cash flow one--
that one, that one.
So we have three cash flows, so now we can calculate our yield
to market.
But this will be yield to market divided by n, because it'll
be our period.
So we'll do the rate function, NPER.
Oh, we do not have that.
I'm going to Escape.
I'm going to come right here and right-click Insert, right-click
the row header, not the cell.
If you right-click Insert, you'll get something different.
Right-click Insert.
This will be equals this times this.
And now, we can use our rate function, NPER, total number
of periods.
PMT, that is the positive.
It's the bondholder's point of view.
Present value, that is our price derived from the face
times the quoted price.
Future value, we have that right there.
Type-- since this is n annuity, or ordinary annuity.
It's assumed, if we leave that out, that it is an end annuity,
so we can simply put a close parentheses and Enter.
So now, that looks like 5%, but I'm
going to increase the decimal there.
Of course, we can calculate our yield to market.
And notice that was auto-complete there--
equals this one times our number of periods.
And finally, we can also calculate
our effective annual rate--
=EFFECT.
You're never going to have trouble
with the EFFECT function.
As we mentioned before, if your n, or is an integer--
it's when it's a decimal, like when we did a money tree
example, where we had a decimal here, that EFFECT doesn't
calculate it correctly.
But this is an integer, so no problem.
Now, notice, when we did it this way,
we had a little extra bit hanging out here.
It was quotes straight at 9, but as an approximation,
this works just fine.
So we saw how sometimes we're given a quoted price,
and we got to calculate based on the face
value and the actual quoted price for our actual price.
And from those, we can calculate period yield
to market using rate, and of course,
our effective annual rate using EFFECT.
All right, see you next video.