Welcome to Excel in Finance video number 36.
Hey, if you want to download this workbook, Chapter 5,
click on the link below the video
and be sure to scroll all the way down
to the finance class section.
Hey, in this video here, last video
we did PMT, just a basic loan payment.
But here we want to look at all the intricacies.
We're going to look at annuity due,
annuity begin, what happens to the interest rate
when points occur, what happens when you delay your payments,
balloon payments, all in one video.
So really, we're going to learn about a lot more about the PMT
function.
Now here's our situation.
We borrowed this amount.
We have a down payment, so our loan
would be that amount minus this.
We have an interest rate, monthly,
so we have to convert the annual to period,
where we have a loan for five years,
so we better figure out what all the months are,
total period, so that year times 12.
Now we come over here and we do our PMT.
Now, we're going to do the first example at the end.
And what happens if you pay right at the beginning?
What's the difference in payment?
So end is called an ordinary annuity.
Begin is called an annuity due.
Oh, we can use our PMT function.
The rate, that's always period rate.
The NPER, total number of periods.
Present value, this is a positive
coming into our pockets because when we borrow it,
the bank gives it to us.
Bank negative cash flow, you positive,
even if you immediately give it to someone else.
All right, we don't need these last two arguments,
because type, we'll see here by default, it's an end annuity,
so we can leave it out.
So there is our payment, given n.
Now let's do begin equals PMT period rate, RATE.
NPER is total number of periods.
Present value, we're going to have that same positive amount,
comma, to skip the future value, and then comma,
now we need a 1 to indicate begin, or annuity due.
So for this small loan, it doesn't make a big difference,
but we're going to be saving a little bit each payment
by deciding to do it at the begin instead of the end.
Now what about the lender's point of view?
This is all us, right?
It's a minus to us, but that's us paying the bank.
Let's just do a PMT from the point of view of the bank
equals PMT, the rate--
the banker has to use the same rate.
Banker has to use the same NPER.
Present value is the only difference.
On the day that they give out the loan,
that's a subtraction to them, right?
So that's the only difference.
It just comes up positive.
Here it's positive because guess what?
When you pay it, they get it as a positive.
For us, it's a negative.
Next scenario we want to look at-- there's
something wrong with the formatting.
I just-- hey, a balloon payment?
What's a balloon payment?
That's when you have a loan contract
and you make a monthly payment.
And then at the end, you owe some lump sum.
So PMT is great for this.
Equals PMT function, the rate, what's
going to be our period rate, comma,
the total number of periods.
This loan is for three years at 12 months,
so it's 36 months, comma, the present value.
It's still going to be positive, and here's our loan amount,
comma, and future value.
There's some amount left over after we
make all of our payments.
The future value is going to have to be negative
because remember, PMT to us is we're paying it out
of our pocket, boom, boom, every month.
But so is this amount at the end.
We're going to pay that to the bank.
And actually, on the last day you'll
pay this amount and the payment.
Again, type, we don't have to put that in.
We're going to do it as an end annuity, or ordinary annuity.
So there it is.
If we put 0 here, it'll go ahead and calculate it
and it'll say that's our monthly payment.
So we you have no lump sum at the end, then we have $1,354.
If I Control+Z, we have a lump payment at the end,
then we're going to pay a little bit less each month but a big
chunk at the end, all right?
See, there's that formatting messed up in 2010.
It's like a bug.
If I scroll it up and scroll it down, it shows it again.
All right, now this is a different situation.
We're going to-- corporation, I'm
going to go borrow $1 million.
There's our period rate, our total number of periods.
But we're going to delay our payments.
So in essence, we're not going to pay anything for one year.
And then ultimately, we need to figure out what the payment is.
Well, this is all cash flows and all interest or discount rates,
if you're doing present value.
The $1 million, you borrow it.
It's sitting in the account.
And since we're doing it periods per year or quarterly,
each quarter, they're going to add the interest in,
even know you haven't paid anything.
So the first trick is to do future value.
So I'm going to say, hey, what's the future value?
Right, now the first thing is we need our rate.
Now, I want to look over here.
We have annual rate and we have four periods per year.
So this is compounding quarterly.
We also have six years for this loan.
All right, so the rate, it's that one.
We already calculated it.
I already did that formula, this divided by that, comma--
now, NPER I want to think smartly here
about how we make this.
Since this is a template here, notice it says one year here.
But what if we changed it to two?
All right, so the future value is going to grow.
For us, it's one year here.
So just thinking simply, well, there's four periods per year.
But what if I put a 2 there?
What would it be?
It would be 8.
So for an NPER for the future value,
I'm going to say number of compounding periods times
whatever I put in this cell, right?
Right now, it gives us 4.
If I put a 2 here, it'll be 8, comma.
We don't have a payment, but we do have a present value,
and that present value is positive.
We borrowed it.
It comes into our pocket, all right?
And type, we don't need that here.
It's not an annuity.
It's just a lump sum calculation.
All right, so that means after one year, my heavens,
we have $87,000 of accrued interest.
All right, now we can do our PMT.
Then there's two things.
One is the present value is no longer this, it's this.
In addition, on that point, when we put this amount
into our PMT, it's actually positive, right?
Because the way this worked is it
assumed we had a positive amount here, so we owe it on this day.
But again, we did owe it on this day,
but we're going to dump it back in as if it comes back
into our pocket.
The second thing to notice is how many
periods do we have left.
Well, there's six years, and we've already
used four of them, so we certainly
can't use this number.
I'm going to suggest we say oh, six years, all
the years minus the number of years we put it off.
Right now it'll give us five if we do that times this.
So we're never ever going to use this cell in this calculation.
All right, you ready?
Equals PMT.
The rate, we have our period rate, comma NPER.
Now remember, we don't want to just type 5 times 4.
We want to say open parentheses, all of our years
minus the years we're putting it off,
and that subtraction has to be forced first,
so we put it in parentheses times the number of periods.
That's NPER.
All right, present value.
Again, we're going to have to make this positive
because it's as if we were getting a new loan,
so I'm going to put a negative here.
We do not have a balloon payment at the end,
and the type is end, so we just close parentheses.
And there we have it.
That is with putting it off for one
year and also building a pretty cool template,
because if I change this to 2, boom, it all updates perfectly.
Now we did this in two steps, right?
Now I'm actually going to put this as one.
We did it in two steps, which is perfectly all right.
If you didn't want to do it in two steps,
notice this cell is C34, and we used C34 right there.
So you could copy that whole future value function
and paste it right there.
So if you were building this and it's confusing,
you build it in two steps, but then you
can mash the two formulas together.
So it would look something like that.
All I did was copy it from that cell
and put it right there like that.
All right, one last loan payment financial calculation
we want to look at.
I don't have a label here.
This is adjusted APR when there are points and fees.
See, finance people all the time try to do lots of tricks.
Even if they're not doing tricks,
there's lots of things going on in the contract that sometimes
are hard to see through.
So you're quoted this rate at 5%, but there's some points.
And usually, you see points as this.
I wrote it as .01, because we're going
to use it in a calculation.
And there's fees.
So what do they do?
You borrow $200,000, but they take out 1%
and they take out a fee of $750.
So really, the cash you get into your pocket is not $200,000.
On the face of a loan, they're going
to use that amount for your PMT.
So you see here on the face the loan, the present day is that.
But you didn't get to keep all that cash.
They took some out.
So the amount you gave to the person you bought the house for
or whatever you used the cash for
is less than that to $200,000, so that's the idea behind this.
Once we know the actual cash we got to use,
then we can figure out the actual interest rate.
All right, so cash we used equals $200,000,
and I'm going to go--
I know 0.1.
1 I can do it in my mind.
So $200,000 times 0.99 gives me that part of the cash.
So I'm going to say instead of 0.99, how do we get 0.99?
In parentheses, 1 minus this.
And then we also have to subtract from that $750.
So this part, if I highlight it and hit the F9 key just
to show you, that shows you the minus 1%.
And then from that, we can just to subtract that $750.
Now, I hit the F9 key.
I'm going to hit Control+Z. I just wanted to show you what
that part of the formula was doing.
There you go.
And you know, what can I say?
Most of us are signing mortgages that
have those kind of fees that are hidden a little bit.
You know, most of the contracts will show you adjusted APR,
but when they're advertising, most of us
are just thinking, and looking at this.
So now let's figure out the real rate.
And it's higher, right, because you have less cash to use,
same amount of cash going out of your pocket based on that.
So here we go.
What function do we use to calculate period rate?
The RATE function.
NPER, total number of periods, where we still have--
oh, there it is, total number of periods, comma, the PMT.
We've already calculated it based on the PMT,
and it is a minus out of your pocket, comma, present value.
It is a positive, because you borrowed
it and it's coming into your pocket.
Notice that when you're doing these functions
and there's two cash flows, one has to be positive
and one has to be negative.
The future value, we do not have a lump sum balloon
payment at the end.
Type, it is end.
And unless you have some extreme value
and because the function is iterating to calculate it,
we don't have an extreme value.
We don't need to put in a guess, but you could.
You could guess what rate it would be close to,
and you'd put like 5%, or actually, you'd
put the period interest rate that you'd already
calculated up here.
Now, I'm going to actually get rid of the formatting,
or maybe instead I will hold Control and hot click
on that cell and that cell right there,
and I want to increase the decimals.
All right, so the period rate you
can see is a little bit higher, but what I really want
is adjusted APR.
So what do we have to do?
Put this into edit mode.
In fact, why don't I do this?
Why don't I copy this down?
And I'm going to type--
because that is the period rate.
And then here, I'm going to get my copy the formatting button
and now I can go equals this times
our number of compounding periods.
That's all the way over here.
So adjusted APR is 5.12%, to so you can
see it's a little bit bigger.
So those fees, points, fees, et cetera,
actually have an effect.
All right, so that's a lot about loan payments.
All right, we did adjusted APR.
We did a delayed payment.
We did a balloon payment.
We did it from the lender's point of view,
and we did an annuity, ordinary annuity, or end, and an annuity
due, or begin.
All right, lots more tricks in this chapter for multiple cash
flow.
See you next video.