Excel Finance Class 33: Full Life Retirement Plan PV Annuity & FV Annuity PV & PMT Functions

Updated : Oct 12, 2019 in Articles

Excel Finance Class 33: Full Life Retirement Plan PV Annuity & FV Annuity PV & PMT Functions

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.

Leave a Reply

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