Welcome to Excel in

Finance video number 33. Hey, if you want to download

this workbook for Chapter 5, click on the link

directly below the video and scroll down to the finance

section, finance class section. Hey, we’re talking

about Chapter 5, multiple cash flows, and in

particular, annuity cash flows. So we want to look at

retirement, full retirement. We want to look at our plan. Our plan is we want $3,000

a month in retirement. So from that number all the

way back to our working years, we’re going to calculate

how much we should have on the day we retire

and how much we should be putting in each month. Now, these are all

estimates, right? We have no idea if we’ll

get 6% return in retirement. We have no idea with

certainty that we’ll get 10% through our working years. However, if history

is any guide, these are pretty

conservative numbers. All right, so the first thing

is notice if we’re in retirement and we want a positive

$3,000 every month, these are future cash flows. So it would be like– I’m going to insert a new sheet. I’m going to do Shift+F11,

Shift+F11 and I’m going to say Time 0. Then I’m going to

copy this over, and then I’m going

to skip one and I’m going to finally say whatever

the final time is Time 300, or something like that. So what we want is we need

to figure out at Time 0 what the present value is

of these future cash flows. So there’s the $3,000. They’re all positive. They go all the way out. And really, right

here this should be like dot, dot, dot, right? What is the present value? Well, these are equal

amounts, all positive $3,000, and the time in between

each is the same, a month. And we need to figure that. So that’s present value of

future positive cash flows. I’m going to click

back on 19 and 20. We’ll call this Flow Chart, FC. All right, we’re going

to just use our function. Last video, we saw

how to do the math, but we’re going to

say hey, present value of this future cash flow. Well, what’s the rate? We didn’t do our

period rate, but we’re going to take 6%

divided by 12 periods, because this is monthly in PER. Oh, we’re going to live for

35 years, we think, times 12. So let’s see, 70,

that would be 105. Now I’m going to do a little

trick here just to see, since we didn’t do it

explicitly over here. Highlight that and hit the

F9 key and evaluate it. It’s whoa, 420 months. So when I’m 70, I only have

420 months left till I’m 105. Comma, and the payment

is positive $3,000. The future value–

that number, actually, if you put a number

here, it would be how much would

be in your account on the day you died, right? So if you wanted to leave

your children $80,000, you just put that there. We’re not going to do that here. And type as we

mentioned, if this is end or ordinary annuity,

we can leave that out. Wow, so we only need

$500,000, right? I actually want to

do a trick here. I’m going to put it back

in edit mode and copy this. Escape. I should have put the

formula right here, so I’m going to put it in

edit mode, F2 and Control+V. I just copy that

formula down there. You don’t want to use the fill

handle, because those are all relative cell references. So you have to have about

$500,000 in the bank when you retire if

you want to withdraw $3,000 at the end of each

period for the next 35 years, and you can earn 6%

compounded 12 times a year. Now, a lot of us think,

oh, I need $1 million or something like

that, but you know, $500,000 if you

start early in life like we’ve talked

about in this class, it’s not hard to

save up to $500,000. All right, but check this out. If you know what you

need when you retire, the question is, how much do

you have to put in the bank each month during

your working years? Remember, this is all estimate. And we talked about

in earlier videos, you usually put a small amount

in the early years and a lot more into the bank each

period in the later years, but we want to figure

out an average amount. So on the day you

retire, if this was the positive

this is a negative, so you have to put

it into the bank. Oh, but wait a second. We really need to think of this

in a slightly different point of view. Now that this is

what we want, this is going to become

our future value, and all I did was put a

minus sign and point to that. That is our future value. And so now here we

did a present value of an annuity, where

the cash flows were positive for the

annuity payment. Here, we need to do

future value of an annuity where the PMT is the

amount that comes out of our wallet into

our bank account to save up for

this future value. All right, we’re going to assume

10% compounding periods, 12. If you’re 28 now

and you’re going to retire when you’re 70,

right, how many years? Equals 70 minus 28. So 42 years. This annuity is going

to be for 42 years. All right, let’s go ahead and

I’m going to click right here and do my PMT. This is PMT because

we are doing– and we’ve already

done this before, but now we’re going to do a

future value retirement amount, and we need to know how

much to put in the bank. So rate that’s going

to be our period rate, 10% divided by 12, NPER– well, we already did that one– or years, I mean, times

12, comma, present value. We don’t have a

lump sum in the bank on the day we start saving up,

so we skip over that, comma, future value. There it is, and it is positive. Comma, type– remember,

this is going to be the end, so we don’t have

to put anything. All right, and then enter $67. That doesn’t seem

like very much, huh. Well, the reason why it

doesn’t seem like very much is because the variable that

affects long term investment is years, or time. The amount of time is

gigantic here, 42 years. Right, let’s just say we

start saving when we’re 30. That’s just two years

different, well, that’s not much different. That still goes up. How about when we’re 35? $138. Still, huge amount of

years because 35 times 12. I’m going to keep this at 28. Now let’s just come

down here to cell B19, and we want to do a

little calculating. The question is, how much

did we pay out in total? Of all these numbers

we calculated, how much do we pay out? We know we got $3,000

a month, but what’s the total amount we paid into

this whole retirement process? Well, we simply take– and I’m going to put just that

amount times our total number of periods. Now, I don’t have a cell for

total number of periods, So? I’m going to say

years times months. So those are all the periods

we put in that amount. Hm, $34,000. That doesn’t seem

like very much. Well, let’s look at

how much we received. That is a negative, right? Equals received. Oh yeah, our $3,000. We’ve been saving all doing

our working years just to get that, $3,000 per month. And I’m going to

multiply it by well, we live for– we’re assuming

we live for 35 years times all the months. Wow, look at that. Just right off the bat, that’s

$1 million we pulled out. Well, remember, that $500,000

goes into the bank account and is earning a lot of

interest in the early years. So even compare that to what we

got to pull out, that’s a lot. But now when you compare it

to what we actually put in, that is just unbelievable. I’m going to do SUM

and add these two. And this is a negative amount. That’s a positive. Yep, and that’s

all the interest. That’s big. Now, the moral of the story,

of course, is the present value and future value and annuity

formulas in Excel functions are totally cool. But that’s not really the moral. The moral is start

investing early in life, even if it’s just a little bit. Remember, we said, that’s

the average amount, right? So even if it’s

$10 or $20 a month in the early years and 300, $400

in the later years, save early. Hey, we’re going to come back. We have lots more

amazing finance in Excel tricks, Chapter

5 for multiple cash flows. See you next video.

To calculate the total interest received, could you use the CUMIPMT function? I tried that with your example and get different result than what you did manually. =CUMIPMT(B12/12,B16*12,B11,1,B16*12,0) retrieves $1,717,891. The description for this function sounds like it would work for the example.

Thanks !