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.