Welcome to Excel in Finance, video number 31.
Hey, if you want to download this workbook, Chapter
5 or the PDF for Chapter 5, just click on the link
below this video.
And then scroll way down to the Finance section.
All right, in this video--
well, in this chapter, Chapter 5,
we've been so far talking about all sorts of great things,
but interest rates, future value of multiple cash flows.
But most recently, we were talking about future value
of an annuity.
And so we had this formula right here.
Take our annuity, which means an equal payment,
equal time intervals.
Multiply it by this factor, and it tells us
what our future value is.
Now, that's our calculation we've been doing.
But what if-- so we were saying, if we deposit
50 bucks each month for 40 years, how much do we
have at the end of retirement?
But in this video we want to say,
I want to be a millionaire.
So we'll know the future value amount.
And I want to solve for PMT.
Well, we simply take this formula, divide both sides
by this factor.
These cancel out.
And so you're left with PMT equals that right there.
In Excel, we're simply going to put the rate, the NPER,
the future value and type.
We're not going to do any annuity dues, or begin annuity.
So we'll just always leave this blank.
We're always going to be doing ordinary annuities.
And here's the math formulas here.
Let's go look at an example.
Let's see, do I have an example right here?
Yes.
How much do I have to invest at the end of each month
to become a millionaire?
Now that's sloppy writing there.
But let's look at the time line.
We'll go over to Excel and it's less sloppy.
Here it is.
I want the future value amount to be a million bucks.
And here's all of our time.
Our goal is to solve for this amount here.
So in essence, we have a minus amount.
We're putting this into our investment
vehicle, our retirement plan or our savings plan
or whatever it is.
So over time, we keep putting all these cash flows in.
And at the end-- cha-ching.
We want to get $1 million.
So this is known.
This is what we're solving for.
Let's go over to Excel.
Here's our million bucks.
We're solving for future value--
our annual rate, our number of compounding periods per year,
our number of years.
Let's go ahead and do our period rate--
total annual rate divided by number of compounding periods
per year.
Total periods is years times number of compounding periods.
And now I guess I have to do it once.
I have to show you the nasty formula--
math one.
And then I'll show you the easy function.
And again, for us in this class, it's
just so we have a way to double-check.
In the real world, it's a way to double-check,
if you're in charge of the spreadsheet.
Oh, here it goes.
Well, we know our future value amount.
And we're going to divide it by that factor.
Now I'm going to have to put one, two, three parentheses.
And then do one plus our period rate--
we already have that-- raised to our number of compounding
periods.
And we're going to subtract one.
That, in essence-- this little green bit right here
is the top of the fraction that goes
underneath the future value amount.
Then we're going to have to divide this by our period rate.
Finally, we have to put a closed parentheses in.
The reason why is we need to force that division to go last.
158.13?
That doesn't seem like very much money.
I must have made a mistake.
Well, here, I'm going to double-check with--
and this is our new function, PMT.
Now, in this example, we're in essence
doing a savings plan, how much to save each month to become
a millionaire.
Later we'll see how to use this function for loan payments.
All right, our rate-- it's our period rate.
Our NPER-- that's our total number of periods.
Our present value-- well, we don't have any money
in the account at time zero.
So we're not going to use that.
In the bond chapter and in other examples, we'll use that,
but not here.
The future value-- that's the amount
that we want to solve for.
I'm going to close parentheses and Enter.
So this properly tells us that it's a cash flow out.
So if we want to be a millionaire--
and I do not like the odds given in the lottery
or getting a big inheritance.
My next best option would be to invest 158.13
at the end of each period at 10%, compounded 12 times
a year.
Now, of course, sometimes it's not possible to get a 10% rate.
Not only that, but sometimes maybe you're
only putting in once a year.
Let's just put a one here.
Oh, OK, so that means $2,259 per year for 40 years.
By the way, if you plop this in, at least
at this point in history, you get a good deduction
on your tax return for contributing to a retirement
fund.
Let's change this--
0.07, so 5,000 at the end of the year.
I think that's about the amount you're allowed
to deduct on your tax return.
Let's go back to 12 here, just to see what
the monthly amount-- so 380 dollars.
Eh, that's a lot.
But history shows us that 7% would
be very, very conservative in terms of investing.
All right, now we want to see another example.
Again, we are solving--
we know the future value amount.
And we want to know what the PMT is.
So I'm going to come over to 17.
This is our 17th example.
These are also in the PDF files.
Oh, savings plan for our daughter's college.
We think we're going to need 180,000.
We think we can get 8%, compounded monthly, 18 years.
So we need to know how much to put in each month to save up
for the daughter's college.
And probably this one's got to be a lot more than that,
but we'll start with that.
Period rate, 8% divided by this 12.
Now, look at that.
I'm going to highlight both of these cells,
clicking and hold Control, and click here.
And just click the decrease decimal.
Probably would have been faster to go to the Format Cells
dialog box.
Total periods, 18 years times 12 months.
All right, we're going to assume it's an end annuity.
Again, it's easy in Excel.
You just put a one at that type argument.
I'm not going to do the amount.
I'll just go equals PMT.
The period rate-- boom.
The NPER, that one there.
Present value, no.
We're going to skip over that.
Future value-- that's the amount we want.
The type we can leave out, because it's end.
$374.
If I want to save for my daughter's college education,
I should invest $374 at the end of each period for these given
variables here.
Now let's move this up a little bit--
whoops.
250, and let's assume we were investing and could earn 11%.
Yeah-- so monthly, we're going to keep it at monthly.
We want it to come out of our monthly paycheck.
So there you go.
Those are two examples of how to solve for the PMT
in a future value annuity.
We did it with the PMT function.
And we even did it with the math.
All right, in our next video, we'll
start looking at the present value of an annuity.
All right, see you next video.