Welcome to Excel in Finance video number 56.
If you want to download this workbook, click on the link
below the video and scroll way down to the Finance Excel Class
section, and you can download this workbook.
This is the second workbook for Chapter 6.
All right.
Here, in this video here, we're going
to look at a zero coupon bond.
That's a bond that does not pay regular interest payments
or coupon payments.
There's just a face value it promises
to pay back in the future.
We need to build an amortization table--
a yearly amortization table--
to show how the balance changes and what
the implied interest is.
Well, wait a second.
I thought a zero coupon, we just had a face value,
we got some money in today, and then we
paid this out in the future.
Why are we talking about interest?
Because for tax purposes, a zero--
the bond issuer is going to get to record a deduction
on their tax return, which is a non-cash interest expense,
which will give us a cash in benefit and the bond holder
will have to record a non-cash interest revenue,
which is a disadvantage-- that's cash out--
even though the corporation is not paying--
the issuer is not paying interest
and the bond holder is not receiving any interest revenue.
For tax purposes, you still have to pay taxes.
So our amortization table-- we built a number of amortization
tables so far in this class--
it will help us greatly here, because we
can see each year what are expenses, and then from that,
figure out our tax implications.
Now, the first thing is, for an amortization table,
we need to figure out what the balance is, right?
Because we're going to have some balance less than our face
value we pay at the end, and then over time it
will increase.
Each year, it increases by an amount, which
will be considered interest.
All right.
So we're going to calculate the price of this bond using
present value.
The rate is going to be yield to market divided by 2 times
a year.
Yes, for tax purposes, they're going
to treat it as [? N ?] equals 2 semi-annual payments.
[? NPER ?] years times 2 semi-annual, comma,
there's no payment.
Oh, even though we're going to have implied for our valuation,
there are no payments.
It's just one future value.
Now, I'm going to put a minus here
from the point of view of the corporation.
They're paying that out.
And that'll tell us the present value
positive, which is how much money is
coming into the corporation.
Now, the next step is to calculate the balance.
And I'm going to calculate the interest each period,
and then we'll simply add, just like before when
we did our amortization table.
We've done amortization tables for a discount bond, a premium
bond.
We did amortization table in Chapter 5 for consumer loans.
Ah, but here's the problem with this one.
This is yearly and we're accruing interest
twice a year-- semi-annual.
Well somehow, we need a single rate here.
No problem.
Guess what?
We can calculate the effective annual yield.
Remember, the effective rate is always
going to be greater when the APR or the yield to market
has an [? N ?] greater than one.
But the effective rate-- if we can take our yield to market
and our number of compounding periods per year,
we can calculate the effective rate, which
tells us the actual rate at [? N ?] equals 1.
So I'm going to use the effect.
Now early on in the class, we saw
there can be a trouble with effect if the [? N-- ?]
the number of compounding times per year--
is a decimal.
But here, it's an integer, so no problem.
I'm just going to click nominal yield
to market, comma, an [? NPERY ?] number of compounding
periods per year.
There it is.
We have a rate that gets us all of our interest
with an [? N ?] equal to 1.
So I'm going to say equals the balance times our interest,
right?
And I'm going to lock this by hitting the F4 key.
Just as our earlier amortization tables,
that's all that's added.
For the bonds, we're adding the interest.
Our consumer loan, we were looking
at the interest and principal.
Here, this is a deep discount, so we're just
adding in interest.
Now this is straightforward.
We say whatever the balance was plus the accrued or implied
interest for tax purposes.
Now, we can copy this down.
And sure enough, the balance increases, increases,
increases until on the day we have to pay it back.
The account on our books says $1,000,
and that is what we owe them.
This, in essence, is a yearly book value of your liability.
Now, the main point of this video is now to figure out--
the bond issuer and bond holder have
to deal with this interest for their tax returns.
Now the bond issuer, this is a tax expense.
So guess what?
They get to deduct it.
And it is non-cash.
There's no cash moving.
So that means you get to record $39.55 on your tax return
and reduce your taxable income.
Now, think about this.
What happens when you have a deduction?
Think about $1.
You put $1 as a deduction.
That means you've just avoided $0.35.
So anytime you can avoid paying a tax,
it's as if cash flow is coming in.
So here, our calculation is implied interest.
That's the deduction times our tax rate.
And that is the benefit--
in essence, a cash flow in for the bond issuer.
Now, we're going to have to lock this one right here.
I'm going to hit the F4.
Right?
And so this avoidance of taxes paid, cash flow in benefit
makes zeros attractive for companies
who want to conserve cash in the early years of the project.
Yeah.
Not only does the zero--
because we're accruing interest.
This interest is adding up and then
we pay the bondholder back the full amount at the end, right?
So not only are we avoiding paying out interest
like we would a coupon bond, but we
have this awesome tax savings, which is like a cash in.
All right.
But the poor bond holder has to record their interest revenue.
So get this.
When they record this $39, that's
adding to their revenue on their tax return.
So $0.35 extra for each $1 they add.
This is a marginal tax rate.
So here, it's just minus this.
Remember, we talked about that.
If you type a minus and enter the formula
with a cell reference, it puts the equal sign in.
So these poor investors are recording this as a revenue
even though they didn't get the cash sent to them.
All the cash is sent at the end.
Now, here's the deal.
The tax-exempt investors that need
to know the future value with relative certainty--
like pensions, right?
Because when your pension fund is saving up
because they know how they have to pay off in the future all
these pensions, right?
So these are great when you need to future value
with relative certainty.
And you have a tax-exempt status.
You might also put them in a tax-deferred retirement
account.
All right.
So that's a little bit about zeros,
amortization table for yearly interest--
when we had [? an N ?] equal 2--
and the tax benefit and tax disadvantage
for a bond issuer and a bond holder.
In our next video, we'll actually
compare zeros and coupons, and look at the cash flows
for those two types of bonds.
All right.
See you next video.