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.

Wow great to see that

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

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

Amazing!!!! this is GOLD. Thanks Mike đź™‚

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

Great vid. Thank u

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.

Thanks Mike, very helpful…!

Fantastic video.. u really made it simpler..

Hi thank you so much sir y'r a genius

can you make vedios for macro and VBA please?

One more EXTRAORDINARY video. Thank you, Mike!

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)

Amazing video; thanks really appreciate the download great education

Thanks Mike for this amazing video

Another great video!

Very clearly explained. Thanks.

Thanks!

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