Excel & Business Math 45: Future Value, Present Value and Periodic Payments for Annuities

Excel & Business Math 45: Future Value, Present Value and Periodic Payments for Annuities


Welcome to Excel and Business
Math, video number 45. Hey, in this video,
we’ve got to see how to calculate the future
value, the present value, and something new, the PMT
or the periodic payment for annuities. Now, the last few
videos, we did calculate future value and present value. But it was for a lump sum. And once we learn the formulas
and the Excel functions for annuities, it opens up all
sorts of amazing possibilities. We can answer financial
questions like these. What is my savings plan worth? How much to pay for a machine
that will last eight years? How much can I withdraw
each month in retirement? How much to deposit each
month to become a millionaire? Or how much is my
home mortgage loan? Now, an annuity. What does that mean? It’s pretty simple. Notice this diagram here. And it looks like we’re saving
$4,000 at the end of each year. And why is it an annuity? Because the amount of money
each period is the same and the time period between
each period is the same. Once we have cash flow
patterns like this, we have amazing formulas like
this that look complicated, but because they can answer
such useful financial questions like all of these, it’s
not really complicated. It’s amazing and beautiful. Now, we will learn the
math in this video, but we’ll also see that behind
the scenes, the PV, FV, and PMT functions are all programmed
to do the heavy lifting math for us. Now, as we look
through this list here– savings plan, retirement
plan, home mortgage loan. These are all typical
consumer financial products. And all of these usually fit
the pattern of an annuity. Now, again, this formula
looks complicated. But the beauty of
an annuity is we have one single formula that
calculates future value, present value, and PMT. Now, before we look at our
math formulas and functions for annuities, we actually
want to look at one or two examples of cash flow
financial calculations that are not annuities. And we’re actually going to
start off by going and looking at PDF notes. Now, the PDF notes
and the Excel workbook can be downloaded in the
link below the video. We want to go to
page 2, and we want to talk about calculating
future value of cash flows. In our first
example, we’re going to have a savings plan
with irregular cash flows, not annuity periodic cash flows. And what that means is,
we need to calculate how much of our savings account
will be worth in the future if we deposit $10,000 in
year 0, $20,000 in year 3, and $15,000 in year 6. What would be the worth of
these cash flows in the future? Because they’re not
periodic cash flows, we actually have to take
each individual lump sum present value amount, calculate
the future value, And then add. If we go to the next
page, here’s a picture. Here’s our cash flows times
0 to 6, and here’s a picture. Notice each one of these amount
is a present value amount, and at time 0, we have
to take that $10,000. But when we make the future
value calculation right here, we actually have to
do it for 8 periods. So that $10,000 at
our assumed 6% period rate grows to $15,938.48. So when we have
irregular cash flows, we’re going to take
each individual amount, do our future value
lump sum calculation, get the list of all of the
future value of cash flows at time 8, and then add them up. And remember when we’re doing
future value calculations we’re adding all the interest. All right, now we need
to go over to Excel and see how to make these
calculations in Excel. We’re on the sheet
EX1, and our goal is to calculate future
value of a savings plan with irregular cash flows. You will deposit
cash flows listed below right here
into your savings account that pays 6%
compounded yearly. What is the future
value after 8 years? Now, I listed all the variables. There’s the APR, 6%. Number of compounding
periods per year, 1. Number of years, 8. Now, we’re going to have to
calculate our period rate, and even though it’s 1, we’re
going to make this calculation. There’s the annual rate divided
by the number of compounding periods and enter. So that’s our period rate. Now, here is all of the years. Here’s the date. So at the end of
each year, we’re putting in a certain amount– 10, 20, and then 15. Now, if you have
irregular cash flows, what we’re going
to have to do is calculate the future value of. This $10,000 will be
sitting in the savings account for 8 years. This will be sitting
in for five years. And this will be sitting
in for two years. That means before
we can calculate the individual future
values of each and then add, we have to calculate the
total number of periods. And I want a formula
here and here that will automatically change if I
throw a new number right there. So total number of
periods– well here I need, 8, 7, 6 and so on. So I’m going to say equals,
well there is the total, and lock it with the F4
key, minus years 3 cells to the left as a
relative cell reference. Control Enter, double
click and send it down. Go to the last cell and hit F2. Looking good. Now we can make our
future value calculation. I need to calculate future value
for each individual number. So I come over and use
the future value function. The rate– that’s this
period rate up here. And I’m going to use the F4 key. Comma, NPEP. Well it changes as we
copy our formula down, so I’m clicking on that
relative cell reference. Comma, we do not use
PMT, because that’s only for annuities with
periodic payments. Comma, we’re doing individual
lump sum calculation, and since that’s
the amount we’re depositing, from
our point of view, it’s a negative cash flow. So minus and click on that
relative cell reference. We do not need type at all. Close parentheses. Control Enter, double
click and send it down. And notice it works
fine for the cells where there’s nothing,
well the future value of 0 is of course 0. And the beauty of this
solution is if I change this, and I put $10,000 here,
instantly that updates. Control Z. Now I need to come
to the bottom and F2, verifying that the cell
references are looking good. They are. Enter Enter, Alt Equals
to get to sum function. And I’m going to highlight
those cash flows and Enter. And there it is. There is the future
value of these lump sum amounts deposited
into our bank account. These present value
lump sum amounts– 1, 2, 3– add up to $59,556. Now, each one of
these, that one number, that’s the future value
in year 8 of this $20,000. Now, we can check
with FV function, and actually off to
the side, there’s the PDF notes for
function and math formula for lump sum
future value calculation from the last video. We’re going to check. There’s our present value
times, in parentheses, 1 plus our period
rate, and I’m going to lock it with the F4 key,
close parentheses, caret because the exponent is going
to be different each time. This amount will get eight
years, Control Enter. Double click and send it down. I’m going to copy this over. Come to the last cell, hit F2. Cell references looking good. So we have checked and
verified with our math formula. All right, so this
is our first example. And if we don’t have
periodic cash flows, our cash flows are for
a different amounts and the time between
each is different, than we can use individual
lump sum calculations. Now we want to go back
over to our PDF notes. And for our example
2, now we need to see how to calculate the
present value of cash flows. And here’s something
that probably none of us have thought of before– how much to pay for a machine
that a business is going to use with irregular cash flows? So our goal here is, we’re
about to buy a machine. And we did our homework. We estimated that the machine
will deliver $50,000 cash flow at the end
of the first year, but then it’s not going
to deliver the same amount each year. It’s not until the third
year that it gives us another $25,000. Year five it gives us $50,000. And in the final year, year
eight, it gives us $10,000. So if we know that these
are the cash flows, this is actually
a net cash flow. So this would be a
net positive cash flow that the machine is
yielding for the business. The question is,
how much do we pay? Well, if we knew
what the interest is that the business
expects to earn, we could calculate the present
value of each one of these back to times 0 [INAUDIBLE] and that
would be the maximum amount that we want to pay
for that machine. Now, if we come down to page
6 in these PDF notes, time 1 to 8, those are the cash
flows that the machine that’s going to generate. And here’s our timeline from
year 8 all the way back to 0. And our goal is to calculate
the total present value. Well, each one of the
future value amounts has to be used in our
present value calculation. So for each amount, this
$10,000 cash flow at year 8, we have to take out all of the
interest for that one $10,000 and get back to that cash
flow’s present value. Now, in a problem like
this, the business is going to have a very
high interest rate. Now, it’s not really
called an interest rate. This would be a rate of return
for the company’s investment. So for each future value
amount, individually we have to make our present
value calculation. Once we do all of those,
we have a list at time 0. And we can add– and that’s the maximum
amount that we would want to pay for that machine. Now, let’s go over to Excel
and see how to do this. Now we’re on the sheet EX2,
and our goal on this sheet is to calculate the present
value of irregular cash flows. And this type of problem is
called an asset valuation calculation. Now, an asset is
defined as anything a business owns or controls that
will provide future benefit. That is the general
term to describe things like a machine, trucks,
buildings, and so on. So we’re trying
to value an asset, and our asset is a machine. Here’s the description of
what we’re trying to do. How much should you pay for
a machine for your business if it is estimated that it will
generate the cash flows listed below? And there they are. Your business requires
that the machine helps the business earn a 17% return. And you can think of this
like interest or an investment return. Remember, a business
is operating to make a return similar to you
putting money in a bank account and trying to get
an interest return. Now, here’s our variable– 17% is our APR, our annual rate,
number of compounding periods per year, and years. Our period rate in
every single time, even if the number of periods
for compounding per year is 1, every time we’re going to make
this calculation divided by 1. That way, when you get to
more complicated calculations, you’re already used to always
calculating period rate. Enter. And of course we
get the same thing because anything
divided by 1 is 1. Now, for each cash flow,
here’s a $50,000 cash flow. Notice we’re getting
it in year 1. Our goal is to calculate
the present value, and bring it back to time 0. Remember, on our diagram
we saw just a second ago, that’s $10,000 bucks
we’re getting in year 8, so we actually have to
calculate the present value of that $10,000 all
the way back to time 0. So this column will be
filled with present values of these cash flows at time 0. Then we’ll add, and that total
will be the maximum amount we should pay for that machine. All right, so you’re ready? In our math calculation
that we learned last video for present value of
a lump sum, there’s the PDF from the last video. All right, so we’re going to
use equals PV function and Tab. The rate– it’s always
the period rate. I’m going to hit the
F4 key to lock it. Comma, NPER. Well that’s simply years for us. Every one of these cash flows,
year 1 all the way to 8, we just use a number of years. That’s the total number
of compounding periods for that particular cash flow. Comma– we do not have
annuity periodic payments. So we skip this comma. We have future values, and
those values are positive. If that’s a cash
flow that’s positive, the present value
function will be polite and deliver that
answer as a negative, because it means we must
expend that cash flow and time 0 to earn this at time 1. We do not need type. Close parentheses,
Control Enter. double click and send it down. I’m going to copy it up to the
time 0 just to be consistent. Now I go to the last
cell and hit F2. I’m verifying that the cell
references are correct. Now look at that? Time 0, that $50,000
is worth $42,735.04. Remember, it’s worth that
much because we took out all of the 17% interest rate
that we’re expecting to earn. That’s why when we get down
to year 8, $10,000 at time 0, we’re willing to pay $2,847.82
so that we earn a 17% return. Now, total present
value at time 0– Alt Equals. That’s our keyboard
for the sum function. I redirect and hit Enter,
and that’s the maximum amount we should pay for this machine. That means if we paid
exactly that amount, we would earn our 17% return. Now, if the machine has
a price tag of $72,500, would you buy it? Absolutely yes. Because $72,500– remember,
that’s the max amount we’re willing to pay. So of course, if the
machine costs less, we’re going to buy it. On the other hand, if the
machine costs $85,500, we’re not going to
buy it, because that’s our maximum amount, and
that’s more than it. Now, if we buy the
machine for $72,500, because it’s less than
our present value at 17%, that means that we would
earn a higher rate. Similarly, if we
bought it at $85,500, we would have a
return less than 17%. Now, calculating the exact
return on these particular cash flows, that’s something we
don’t get to do in this class. But I do have a playlist of
financial videos for a class I teach at Highline– 110 videos all
about finance even in more detail than this class. The link is below
in the description if you want to check that out. Now, I’m going to
definitely check to make sure that this
calculation was correct. And we’re going to do our
present value calculation. We take future value as a
relative cell reference, divided by, in parentheses,
1 plus our period rate, and lock it with the F4 key. Close parentheses caret, and
there’s our number of periods. Control Enter. Double click and send it down,
and I’m going to copy it up. I should have just
started it here each time, but I like to
start where we have a concrete example of 50,000. Now, I go to the
last cell and hit F2, I’m verifying that all the
cell references are working. Alt Equals, redirect and Enter. And we have verified. Now, there’s another
way we can verify too, because if in fact that’s the
present value that we deposit or expend to get this
future value of $50,000, at exactly 17%, If we do
our future value calculation or use the future value function
at a period rate of 17%, F4 to lock it, comma,
number of periods, relative cell
reference, comma, comma, and there’s our
negative present value. If I do this
calculation, I better get exactly that $50,000,
and sure enough I do. And I’m going to copy it down. Copy it up. Make sure to go to the
last cell and hit F2. Sure enough we’ve
verified yet another way that our calculations
are correct. All right, so we had
our first two examples. And in these first two examples,
we had non periodic cash flows. Now we want to go over
to the sheet annuity and define what an
annuity is, and then look at five examples of annuities. Now, the definition
of an annuity– an annuity is a financial
debt or investment vehicle that contains periodic
cash flows that meet this definition. 1– equal amount of
cash flow each period. 2– time between each
cash flow is equal. Now, there’s actually two types
of annuities– end annuities and begin annuities. Now an end annuity is
defined as cash flow that occurs at the end of
each period, also called ordinary annuities. Begin annuities, as
you might have guessed, cash flows that occur at the
beginning of each period, also called due annuities. Now, in this class, we’ll
only look at end annuities. And the lucky thing is, most
consumer financial products are end annuities. And think about
withdrawals from your check into some savings plan. You don’t have it withdrawn
right when you sign up. It’s at the end of the
first period, either a week or bi-weekly or monthly. Also, consumer loans for
your house and your car– those payments are not taken
out when you get the loan, they’re taken out at the end
of the first period and then subsequent periods. Now here’s our five
financial annuity examples we’re going to do. And I actually have a
picture of the cash flow patterns for each one of these. This is a savings plan where
we deposit a certain amount at the end of each year. Now, notice $4,000,
$4,000, so the amount of the periodic payment is
exactly the same each time, and the time between
each payment is a year– so that meets the
definition of an annuity. Now notice, we know with
the periodic payment is, and we need to
calculate future value. Our next example– here’s a
picture of our asset valuation annuity cash flow. And here it at– at time 0, we
need to know the maximum amount we should pay for
a particular asset. And we’ve estimated
cash flows at the end of each year of $10,000. Same amount, same time period
between each cash flow, this meets the
definition of an annuity. Notice we know the amount of
the positive future cash flow and we’re trying to
calculate present value. Next example– here’s
a cash flow pattern for a retirement, where
we know how much we have on the day we
retire, and we need to calculate the amount that
we can withdraw each month. It meets the definition,
because the amount is going to be the
same each period, the time between each
payment is the same. So we’re trying to calculate
the periodic payment given some present value amount. Next example– we all want to
be a millionaire when we retire. But the question is, how
much money do we deposit at the end of each period? Now actually, right
there, that’s better. The question marks are coming
at the end of each period. So how much to withdraw? That’s our periodic payment
we’re going to calculate. And we know the future value. Our last example–
here we’ve borrowed $500,000 for our
house, and we need to determine how much
we’re required to pay at the end of each month. It meets the definition– the
amount is exactly the same, the time between each
payment is the same. All right, we’re going to go
to example three on sheet EX3, and our first example
is our savings plan. $4,000 at the end
of each year, and we need to determine future value. So our goal at the
end of each year, you plan to deposit $4,000
into a savings plan that pays 5% compounded yearly. What is the future value of
this savings plan in 18 years? Now, here’s a picture
of our PDF notes, both the math formula
and the Excel formula. There’s also a
picture down here, because we’re going
to have to put all the parentheses
in the right place when we’re doing
our math formula. Ah, but did we even ask
the right question first? Does this meet the
requirements of an annuity? Yes it does in both
cases– same amount, same time between each amount. Annual rate is 5%. n is 1. 18 years. And there’s our PMT, periodic
cash flow or periodic payment. Now, if we’re depositing
this, it’s very important. We know the direction of
the cash flow for our Excel function. So it’s definitely negative. We don’t have a present
value for this problem, but you absolutely could. Now, if we were
doing it with math, we’d have to do two
separate formulas. But the actual
future value function has an argument for that. So we’re not going to do
that one here, but you could. You could simply put a
negative amount there and future value function for
Excel would work perfectly. The period rate– I’m going to take the
annual rate divided by number of compounding
periods per year. Total number of
compounding periods– there’s 18 years times 1
compounding period each year. And here it is, the drumroll,
the first time we’ve seen future value function. Well actually, it’s
not the first time, it’s just the first time
we’re using it for an annuity. Now, we’re already
familiar with this. So the rate, that’s the
period rate, comma, NPER, that’s the total number
of compounding periods, comma, and here’s the
first time we’ve used PMT. It’s got to meet the
definition of periodic payment, and it does. Before we click on it,
we have to enter a minus, because that’s a cash flow
coming out of our wallet. Comma, if you had some amount in
this account right now at time 0, you’d put a negative and then
click on present value cell, but we don’t. Comma, now we finally get
to use this type argument, or at least we have to be aware
of this fifth argument in all of our financial functions. Notice it’s easy–
it’s got a prompt– 0 is for end of the
period, our end annuity. 1, that means begin annuity. Now here’s the cool thing. Since most consumer financial
products are end annuities, the default for the type
argument is end annuity. So you get to do one of two
things if it’s an end annuity– you either put a 0 there, which
I never do, because guess what? If you leave type argument
completely not in the function, meaning backspace,
then it will assume that you want an end annuity. Now, we don’t have
a present value. So I’m also going to
backspace on that. By the way, you always have
a hint in your screen tips. If the argument has a
square bracket around it, it means if you
know the default, and you want the default,
you just leave it out. The default for present
value, of course, is 0. And the default for
type is end annuity. So that’s all we have to do to. Close parentheses. You’ve got to be kidding me. That seems so simple
for the complicated math it’s going to do. So I when I hit Enter, there’s
the future value amount. Now I’m going to use this
in subsequent formulas. We are definitely required
to round this amount, so I’m going to, right after
the equal sign, use our round. Come to the end, we’re rounding
to the penny, so comma 2. Close parentheses, and Enter. There it is. There is our future value. But the question is, how
much do we deposit in total? If we knew that amount, we
could subtract these two and figure out the
total interest. Well that’s easy enough. How much do we put in? Equal sign. Well I put $4,000
in, how many times? Times our total
number of periods. When I hit Enter, $72,000. Now, total interest. I simply take equals,
there’s how much is in the account at the end,
minus the total amount we put in, and enter. I love it. $40,529.54
That’s a lot of interest. Now, I want to do the
math formula to check this amount right here. Here’s our math formula– equals sign and a need
to get our payment, there’s that $4,000
times open parentheses. Now we’re going to have to
do another open parentheses because I need to
force 1 plus the period rate, close
parentheses, to happen before we do our exponent, total
number of compounding periods. Now we have to
subtract 1 from that and then do close parentheses. So all of that has
to be calculated. Multiplying the PMT times
the numerator of, in essence, our fraction, divide by
n in the denominator, we have our period right. Now, that formula right here– that’s this famous
annuity formula to calculate future value
given some payment amount and the rest of our variables. When I hit Enter over
here, we have checked. Now, we use this function
right here, future value, that is just beautiful. And it’s definitely what we want
to use when we have an annuity. But I want to also show
you just this first time that if we took this
$4,000 and listed it and did the same
calculation as we did for our lump
sum amount, that would give us the same amount. Because if we did take
each individual amount, calculate the future
value and added them, it’d give me exactly the same
as our calculations over here. Now, we’re going to
have to be careful here, because if you look at that
$4,000 for the annuity, it’s at the end of
the first period. If we’re taking this from time 0
and jumping it into the future, it’s not 18, it’s actually 17. So right inside our
formula, for each one of the number of
compounding periods, we’re going to take
the full amount minus the particular year
that that cash flow sits at. So you’re ready? We’re going to use the
future value function. Our period rate, that’s
this 5%, F4, comma, total number of periods. This is where we
have to be careful. I’m going to take total
number of compounding periods with an F4 to lock it minus
the number of compounding periods in this table. So right in NPER, we
make our calculation for total number of periods. Comma, we do not have a
PMT, this isn’t an annuity. Each one of these $4,000s
is going to be treated has a present value lump sum. So comma, to skip it. Present value, it’s already
listed as negative there. That is perfect. We do not need type,
this is not an annuity. Lump sum, deposited at year 1,
the future value is $9,168.07. Double click and send it down. I’m going to copy it up one. Down here F2. And that makes perfect sense,
because if we deposit this at the end of year
18 and we immediately want to know the value of
it, of course it’s $4,000. All right, so you’re ready? Alt Equals. This whole set of
calculations here, when I add them and hit Enter,
I get exactly the same amount as the future value
function for an annuity or our math formula
for an annuity. All right, so this
is the savings plan. We like seeing what
$4,000 invested at the end of each year
is worth in 18 years. So here, we took cash flows
known, the periodic payment, and we calculated
the future value. Now let’s go to example 4. And in this example, we’ll
know future cash flows, and we need to calculate
the present value. Now, this is our asset
valuation problem. And we’ve estimated
that the cash flows from this machine or
this asset we’d like to buy are going to be $10,000
at the end of each year. So we have our positive
future cash flows. It meets the definition
of an annuity. And we need to
calculate present value. More specifically, we
need to ask the question– how much should we pay for
a machine for your business if it is estimated that it will
generate $10,000 in cash flows at the end of each
year for 10 years? Your business requires
that the machine helps the business earn a 17% return. What’s the maximum that we
should pay for this machine? Now, yes, the cash flows
are exactly the same each period and the time between
each cash flow is the same. There’s our annual rate. But really this is called,
in an asset valuation problem, the required return. It’s also referred to
as a discount rate, since we’re taking
future cash flows and calculating the present
value of all those cash flows. Number of compounding periods
per year, number of years. There’s our periodic
cash flow, our PMT, it’s definitely
going to be positive. We’re not going to
calculate the future value. We’re going to
calculate present value. Now, our period rate– there it is, 17% divided by
number of compounding periods. And Enter. Total number of
compounding periods– 10 times number of
compounding periods per year. All right, so the
present value– here’s our Excel
function over here. And here’s our math formula. Notice it’s slightly different. We have to say 1 minus
1 plus the period rate. And look at that– the
exponent is a negative number. Then we divide the whole
thing by the period rate and then multiply it by our PMT. Now let’s calculate using
equals PV Tab period rate. There it is, comma, total number
of compounding periods, comma. And there it is– PMT, and it is
definitely positive. Since all of the cash
flows are positive, the result from present
value will be negative. That means the
most that we should expend to pay for this asset. We do not have any
future value amount, lump sum, and since
this is an end annuity, we just leave that off
and the PV function will assume we want end annuity. Close parentheses and Enter. And there it is, that’s
the maximum amount we should pay for future cash
flows at the end of each year. Now the question is, if that’s
the maximum amount we should pay, if the machine or the asset
cost $45,500, should we buy it? Absolutely yes. This is the max we
should pay, that’s under, so we will buy it. $52,500– no way, we’re
not going to buy it. Now we’re definitely going
to check the present value using this formula
right here, equals, and there’s our payment
times open parentheses. And now inside the
parentheses I need to isolate yet another
calculation, 1 plus our period rate. Close parentheses. Caret, and now we need
a negative exponent, minus in front of that number,
number of total compounding periods. Now we have our
negative exponent, and we must subtract one. Finally close parentheses. Everything inside the
parentheses there– that’s the numerator. So we divide by the period rate. And now there is
our math formula. When I hit Enter, we get
exactly the same number. Now, it’s just displaying
different decimals, but those are the same. Now for this one we could also
follow what we did in example 3 and calculate the present value
of each future value amount. Now, we wouldn’t
want to do this, but we’re just learning
this for the first time. So it’s always helpful to
do it a few different ways. The goal of this column is
to take these future amounts at each one of
these time periods and calculate present
value at time 0. So I’m going to use
equals present value. The rate– there’s
our period rate. F4, comma, NPER,
since this is year one and I’m trying to
get back to year 0, I simply use the number of
periods, in our case years, as a relative cell reference. Comma, we’re not doing a
payment annuity problem here. This is a lump sum calculation. So comma to skip it. Future value, it’s
definitely positive. We don’t need type at all. Close parentheses. Control Enter. Comes out negative,
as it should. Double click and send it down. Drag it up. Go to the last cell and hit
F2 to verify that the cell references are working. Enter Enter. Alt Equals. Highlight those numbers. And when I hit Enter, I
get exactly the same number as our PV for an
annuity function, our math formula for
annuity, or the long method. All right, so example
three and four we calculated present value,
and in our last example future value, where we
knew the PMT amount. Now let’s go to
example 5, and we want to solve for the PMT amount. Now, here’s our
retirement plan– we have $650,000 and we simply
want to put it in the bank and then for the
next 30 years, just withdraw draw the same
amount each month. It definitely meets all the
requirements of an annuity. We’ve listed our variables. And I am not going
to put this here. This is actually going
to be an annual rate, since we’re putting it in
some investment vehicle. And the contract says we can
get 3.75, number of compounding periods per years. There’s number of years. That’s the present value. It’s definitely going to
be negative, because we’re going to put it in the bank. We’re not calculating
future value, we’re going to calculate the
PMT for a present value amount. Now, our period rate–
we take annual rate and divide it by number of
compering periods per year. So when I hit Enter, a very
small amount per month, total number of
compounding periods, 30 years times 12 months
in each year, we get 360. Now, with these inputs, we
can use, for the first time, the equals PMT function. Tab. Look at that, the
arguments look the same as future value
and present value. The rate always means
the period rate. And there it is, period rate,
comma, total number of periods. There it is. Comma, we definitely have
a present value amount. And it’s definitely negative,
so I’m putting a minus, clicking on the $650,000. Now, we don’t have a
future value amount, but we could put a
future value amount if on the final
day 360th month, we wanted a lump sum amount there. We could put something there. But we don’t. And this is an end annuity. So we don’t even
have to put type in. Close parentheses. That simple formula, when
I hit Enter, tells us, wow, we get to
withdraw $3,010.25. Now let’s check with
our math formula here. Equal sign, and I’m going to
take the present value amount, and I’m going to divide
it by– and again, we need to be careful
with our parentheses. Open parentheses,
open parentheses, we need to be careful
to have those two there. And look at this–
we’re going to take 1 minus the period rate raised
to the negative exponent. 1 minus in parentheses
1 plus our period rate, close parentheses. There’s our exponent, minus– because this is a negative 360. Now we have to
close parentheses. So that whole little
bit right there is going to be the numerator
divided by the period rate. And then we need to
close parentheses, because we need to do
everything in the denominator before the numerator can
do the straight division. When I hit Enter, there is
exactly the same amount. Now actually, I’m
going to drag this off to the side just for the moment. And guess what? We want to calculate
total amount withdrawn. I’m going to move
the column up here. And how do we calculate that? Well, wait a second– that’s how much we withdrew each
period times our total number of periods. That means we withdrew
$3,010.25 360 times. Now I’m going to hit Enter. Now, we should probably
have rounded this. Let’s go ahead and
round, because we used it in a subsequent calculation
and we’ve definitely run the risk of extraneous
decimals, and this is money. When I hit Enter, yes, we get a
significantly different number. So that was important,
because that is an amount that was paid out. Now we can calculate
total interest– Tab, Tab, and that is, hey,
all of the amount we withdrew, minus the original
$650,000 and Enter. Wow, $433,690. All right, in this example,
we knew the present value, and we wanted to
calculate how much we get to withdraw each month. Let’s go to example 6. And this is where we want
some future value amount and we need to figure
out how much to deposit at the end of each month. How much do we need to deposit
at the end of each month to become a millionaire? And we will assume we
can invest in stocks, index funds, and other things,
since it’s such a long time period. And we probably will be
able to, even if we’re investing conservatively, over
a long period of time, get 10%. So there’s the annual rate. There’s a compounding
periods per year, years. There’s our future value– it’s
definitely going to be positive because we want to withdraw it. Our period rate–
there’s 10% divided by number of months in a year. Equals 30 years times 12 months. Enter. Now we can use our PMT,
but we have future value, not present value, equals PMT. The rate, that’s the period
rate, comma, total number of periods, comma, we
don’t have a present value, so we skip it by typing a comma. Future value, we definitely have
that– it’s a million bucks. The annuity type is
end, and PMT will assume if we leave type
out that it is end, so we’re not putting it in. Close parentheses and Enter. Wow, So I have to put
$442.38 in each month. Now I’m definitely going
to round this to the penny, comma to close
parentheses, and Enter. Now I want to do
our math formula. And here is the math
formula right here. It’s also over here. All right, you’re ready? Equals, there’s the
future value amount, and we’re going to divide it by
1 2, 3 different parentheses. Now, our first
inside calculation is 1 plus the period
rate, close parentheses, and this is future value, so we
don’t have a negative exponent. Caret raised to the
total number of periods. Now I need to subtract
1 and close parentheses. so this orange
right here, that’s the numerator before
we divide by our period rate, close parentheses. Now, everything inside
the black parentheses, the denominator for
future value amount. And when I hit Enter, I
get exactly the same thing. Now let’s do the
same calculation here as we just did in
our last example. I want total amount
deposited equals, there’s the rounded amount,
times the total number of periods. So 360 times I
deposited that amount. Now, I want total interest. And since that is a
negative– by the way, that’s the currency number
formatting that I hardly ever use that I happen to
have in some of these cells, but that means negative. So equals will take
the future value. And we will add a negative. And that is going to
be a lot of interest. That is the power of compound
interest over long total number of periods in 30 years. That’s a long time. But if you are diligent
and put that much away, that’s a lot of interest. Now, we have our last example. We’re going to go
over to example 7. And here’s our
cash flow diagram. We know that we borrowed
$500,000 from the bank, and we need to calculate
our periodic payment. It definitely fits the
pattern of an annuity, same amount each period, same
time between each amount. The rate that we were quoted
for our home loan was 4.25%. Monthly, number of years,
there’s the amount we borrowed, and it’s definitely positive. Doesn’t matter if we
give it to the person that we bought the
house from, that amount is between us and the bank. And the bank gave it
to us as a positive. Remember, that
wallet purse trick is the trick we always
do to figure out the direction of the cash flow. We’re not calculating future
value, but we definitely need our period rate– 4.35% divided by 12. Total number of periods– years times 12. I can do this one in
my head, 360, I think. And Enter. All right, now we can use PMT
to calculate a home loan or car loan payment. This is called an
amortized loan. Any kind of consumer
loan almost always fits this pattern,
an end annuity. Equals PMT Tab. The rate, that’s the
period rate, comma, NPER, total number of periods,
comma, present value– you betcha– and
its positive to us. We don’t have a future
value, but guess what? If you’ve ever heard of
a balloon payment, that’s the amount that you pay after
all the periods are over, if you put a full
negative amount there, that means coming out of
your pocket to the bank. You put it there. We don’t have a balloon payment,
and this is an end annuity, so I don’t put either
one of those in. Close parentheses, and Enter. There it is $2,459.70. Each month we have to pay
that on this home loan. Now here’s our math formula. We actually already
did this one time. Equals, there is the
present value divided by, open parentheses,
open parentheses, 1 minus open parentheses
1 plus our period rate, close parentheses,
caret, negative exponent, so I type a minus. There’s the total
number of periods. Close parentheses. So all of that it’s going
to be our numerator. Then we divide it by our
period rate, close parentheses, so all of this will
be the new denominator for the present value amount. When I hit Enter, I get
exactly the same number. Wow, that was seven
amazing examples of calculating present
value, future value, and PMT when there are cash flows. Now, when I scroll
over to the side, there are lots of
awesome homework practice problems for you to try. All right, so what did
we do with this video? Example number 7. We saw how to calculate the
monthly payment for a loan given some present value now. Over example 6,
we knew we wanted to be a millionaire,
so we calculated how much we need to
deposit in the bank at the end of each month. We also calculated
the total interest. Over on example 5,
this example is the one you do after we did
example 7 because this is after you retire,
then once you know the amount, the
future value amount, it then becomes a
present value and then you do your calculation
to figure out how much you get to withdraw
each month in retirement. Example 4, we valued an
asset, in our case a machine. We knew the future
positive cash flows, and it fit the
definition of an annuity, so we calculated
the present value. Over on example 3,
we were saving up, knowing that we deposited
$4,000 for 18 years. And we wanted to calculate
the future value. Examples 3 to 7– those
were all annuities, but it’s also really
important– sometimes your cash flow
patterns do not fit the definition of an annuity. So in this example,
we had cash flows from an asset we were
considering buying. So we had to do individual
present value calculations, and then add to get the present
value of future irregular cash flows. Then example 1,
we started it off, we were saving cash
amounts that did not fit the pattern of an annuity. So we had to calculate
individual future amounts. And then we knew what the
future value of our account was. Wow, that was an epic video. And guess what? This is the last video in our
Excel business math class. So that was a fun
45 video adventure. Now, if you watched
all the way to the end, be sure to subscribe
to this channel, because there’s always
lots more videos and lots more classes to
come from Excel is Fun, and just on a side note, if you
like the finance in this video, I have 110 videos all part of a
free finance class at YouTube, so be sure and click on
the link for that playlist if you’d like. All right, be sure to
click that thumbs up, leave a comment and
sub and guess what? We’ll see for other non
Excel business math videos in the future. See you next video.

19 comments

  1. M for Magic and M for Mike…. I ask Almighty to give me more time just to watch your videos.. what a treasure channel

  2. I badly want to complete your finance series…. really struggling for time…. But I ammmm gonnnaaa surelyyyy do ittttt

  3. Just a request… why don't you make one video covering total Business Case from a good business school case study…

  4. Hello Mike, I have one specific question for you:
    What is the math relationship between the Annual Rate and the Period Rate?
    And what should it be?
    IMHO: that exact relationship is dictated by the fact that at the end of a period (which is a common multiple) the FV should be exactly the same whether you calculate it using the Annual Rate, or using the Period Rate.
    There’s only one formula that makes this latter statement (which is quite trivial, I think) true — and it is not “i/n”.
    Hint: invest one dollar over said period and you get the correct relationship.
    I most sincerely hope this comment finds you and you consider the above reasoning.
    I hope to read your reaction soon. Thank you.

  5. Another excellent and educational video, thank you and nobody does it better! Quick question: Since the rate and nper have always been multiplied/divided by the same period number, they canceled each other. Is there a situation that the rate and nper would be multiplied/divided by different period? (as I am not working in the finance sector)

  6. Thanks Mike, I have learned a lot. I am sure I have to watch some of videos again, but am pleased I've done. I will also watch your other lessons. Thanks again. 👍 oxoxoxx

Leave a Reply

Your email address will not be published. Required fields are marked *