Excel Finance Class 92: Period (Holding) Returns For Zero Bond

Welcome to Finance in
Excel video number 92. Hey, if you want to download
this workbook for chapter 10, click on the link
directly below the video and scroll all the way down
to the Finance Excel Class section. Hey, we've been talking about
period returns in chapter 10. Now we want look at a zero
bond, zero-coupon bond. All right, zero-coupon
bond just means it's a deep discount bond. We paid $300 one year ago. And one year ago, we
expected to receive $1,000 paid to us in 15 years.

Well, now it's one year later. And we're looking
out in the market. And we say, oh, the
yield to market is 0.085. Well, we have 14 periods left. So how in the world are we
going to calculate the return? Well, for a zero-coupon
bond, they just add the interest to
the principal amount on their books. So if we can calculate the
present value of future cash flows at right
now, time 1, given that we had this price
from one year ago, if we can calculate the present
value of future cash flows, we can get the price
at this moment, because we have the
yield to market.

And then we can just look at
the difference between price at time 0, price at time 1. So I'm going to actually use
the present value formula. I'm going to say, oh,
this future amount divided by 1 plus– oh, we have compounding
periods two times a year. So I'm going to say the
yield to market– notice this is time 1 right now when we're
calculating our price at time 1. There's our yield to
market divided by 2, close parentheses, caret 2. Now times, and then
we have 14 years left. We got to put
parentheses around there. That is much better. Wouldn't that be great
if it was Control-C? Oh yeah, right, we got
it from here to here. No, Control-Y. All right, we could also use
the present value function. Present value is going to ask
for the rate divided by 2. So there's the rate. The NPER is 14 times
2 comma the PMT. There is none. The future value, it's going
to be a positive to us. So remember, the present
value function does cash flow. So this will turn out negative. All right, now we can
calculate our nominal return using end divided
by begin minus 1.

And we've used that a lot. This is coming up
on the 100th video. And we've probably used that
formula 30 times already in this class. All right, and then as
we learned just a couple video ago, formulas when
you use cell references suck the formatting
from earlier cells. So it's taking from
this one right here. It's taking that dollar
sign and applying it. So we use Control-Shift-Tilde
to go back to general number formatting. All right, so that is the
period return or holding return. And remember we're calculating
all these irregardless of whether we sell or buy
at any particular time, because if we're going to
look at historical returns, we may only have prices. And we need to figure out
what for each year what the total holding period
or period return is.

So for this particular example,
there's the nominal return. All right, see you next video..

test attribution text

Add Comment