The Illinois State Collegiate Compendium Academic Lectures in Business and Economics.
This is Business Finance, FIL 190, for spring semester 2024.
Today, the Time Value of Money, round two.
And this is with Excel.
And I'm just going to show you the Excel routines that you would need.
And I have the model already built for you in Excel.
You can download it.
I'll show you where to download it here presently.
Give me just a minute to finish getting the podcasting set up for this.
But you're already seeing in this Excel why you are in a different world from what we
were even five, ten years ago.
And even at this point, most of your classes will use Excel as essentially a talking monkey.
They will not be using the full power of Excel.
And there are so many functions that you'd have to be Rain Man to remember every function
in Excel.
It's that bad.
I am neurodivergent and I can't remember half of the functions from one day to the next.
But let me go in here and I'm going to pull up Canvas and I'm going to go to the student
view here.
And I showed you this before, but let me come online here for a student view and I'm going
to power up the projector.
I'm not saying you are allowed to use Excel.
I'm saying I expect you to use Excel on your quizzes, your homework, and your exams.
It's no longer an option.
It is now to the point where this is the way you're going to stay at least somewhere near
the frontier of the technological revolution that is moving so quickly.
Fortunately, Excel will be the go-to for corporations for the next 10 to 15 years.
And even as companies begin to embrace generative AI, they will still reference back to Excel
for several reasons.
One is simply because so much of the body of our financial literature, our financial work,
is in Excel that we can't dispense with it.
It will become sort of like the slave of AI as time goes on.
We will do things with artificial intelligence, generative artificial intelligence.
And then we will have results or actually even the calculations themselves posted to
Excel.
In the same way, we will write Python.
Python is about to be included in Excel if it's not, I don't know if it's actually the
release is coming out yet.
But you will use Excel to hold up the weaknesses of Python.
Python, the strongest of the new languages for statistical and analytical calculations
is R. R is an abomination hated by God.
It is hard.
But you can use Python and have Python use Excel to do a lot of things and that boosts
it so that it is stronger and it can be used as a, you don't have to use R. So keep in
mind Excel is big stuff.
Now here in your Canvas platform, files, now the one that we do for most of what is in
Chapter 5, and Chapter 4 actually, go to spreadsheets.
Now I want you to prowl down here, present and future values.
And of course I did it that way, you downloaded for God's sake.
Okay.
Okay, enable editing.
No I don't want you to update because that will take until Jesus comes back.
Okay here we go.
Now this will do annuities, lump sums, annuity do, annuity essentially, any kind of annuity,
ordinary annuity, annuity do.
Now just a little background, assuming that you did have here this in accounting, but
I'm going to repeat it.
An annuity is a series of payments.
It's a cash flow.
So in other words, your payments on a car loan are an annuity.
They are a level annuity.
A level annuity is one where the payments are all the same.
A non-level annuity is where the payments are different.
So annuity.
Now the last time I showed you lump sums, technically they are an annuity but there's
only one cash flow.
The one at the beginning or the one at the end.
But talking more generally about annuities.
They are a series of cash flows.
Now the level is the one where they're all the same amount.
Non-level, those are more custom.
Like for example, suppose that I calculate the free cash flow, I project free cash flow
for a company for the next, let's say 10 years.
That would be a non-level annuity and we can bring it back to the present and find something
like an intrinsic value of the company from that.
Non-level, we'll see it later in the course where sometimes you can value a stock as a
series of dividends streaming out until the dividends smooth.
One year they're high, one year the dividends low, maybe the next year there's no dividend.
So in other words, those are non-level.
We deal with those a little later.
They're a little more of a pain in the butt but Excel can do it a lot faster than a person
can.
And so help me, a chat GPT can do it in the blink of an eye.
A level, non-level.
Okay so the level are the ones, payments on loans or a level annuity, well I'll get into
that in a minute.
Now annuities are the ordinary annuity.
Payments are at the end of periods.
That would be like a car loan.
You get your car loan, one month later your first payment's due.
And then when you make your last payment, that's it.
You got the car.
Free title to the car.
Now the annuity due, those are the ones where payments at beginning of periods.
Typically these are the ones you would see for future values.
Now where would you have an annuity where the payments would be due at the beginning
of each period?
Well that's interesting, I never thought of that one.
Yeah, yeah that's not the one I usually use but I like that because they make you pay
your rent right up front for the months that's following.
Huh, not bad.
Knock it off.
No seriously.
Okay I'm going to pick on you.
Here's a classical one.
You are a dynamic handsome young man, right?
You have a significant other.
And then she says three words that change your entire life.
You know what those three words are?
Not I love you.
They are I am pregnant.
Now that happened to me and my immediate thought was to kill myself, you know.
Just end it right there, you know, fuck this.
But instead you go with it and the birth happens.
Now have you ever been to a childbirth?
Yes.
I know, it's exhausting isn't it?
I was tired man.
I mean I was worn out when it was over.
But you finish and you drive, you take off from the hospital when everyone's finally
gone to sleep and you think by gosh I am going to make it good for this kid.
This kid will have go to college at Illinois State University.
My God.
So you first you stop at a burger joint and eat because you know you've got to you know
regenerate all that strength that you had that wasted out.
And then you go to the bank and you say okay every year on the child's birthday I shall
put in $2,000.
So each year starting at year zero you're going to put in $2,000.
And then at the 17th birthday you will put in the last amount and then you will let it
ride and on the kid's 18th birthday you will give the kid a check for what's in there.
That's an annuity due.
It's a classic annuity due.
Financial planning oftentimes works on annuities too because if you are a financial planner
you sit down with a couple and they explain to you what their needs are.
I want new car.
We'd like a new car every three years and we would like to have a really nice home in
seven years and we'd like vacations, really nice vacation every other year and all this.
So you calculate how much each of those is going to be out there in the future and what
the payments will be so that each of those goals is achieved and then you have them write
you a check for the total present value of those future amounts.
When you go to them to tell them what it is you say right there write me this check now.
So in other words these will be annuities due because you will have covered the amount
one period before they need the money.
That's how it will work.
So those will be the same thing.
They will be annuities due and then you will add them up and you'll have a total amount
and you'll divide it among the payments for the different parts of it.
Those are annuities due.
So even though they sound like they're kind of weird they're not really that useful they
are the essence of anything for the future.
Go.
Yeah as a matter of fact 529s yeah some well there are other ways you can do it too.
Don't get me wrong that's the classic way now that's the preferred way now but you can
do them a number of ways as a matter of fact that's one of the powers of financial planning
and plugging our financial planning degree here we do have a financial planning institute
it's one of the top rated in the country and this is the kind of stuff you do.
You work these kinds of plans out for people and this is classic Excel stuff going through
Excel and I've seen chat GPTs that can do this now too.
You even just tell them what the goals are at the different points and they will grab
interest rates or prevailing rates for long term annuities and give you a good answer
and then you can some of them you can say well put this into Excel and so but it's a
lot of it's good stuff because ultimately we're always going to have people who need
financial planners who can do this kind of stuff and although wealth management is for
upper middle class upper class people it also serves in a lot of venues for lower income
people too because we can sit down with them and say okay let's see what it would take
you to put out of your paycheck every month for you to be able to have the down payment
on a home a decent home in five years or in three years okay let's see what else we can
do for you as far as getting some financial organization to your future instead of them
being forced to think about paycheck to paycheck kind of thing so it's not just for the wealthy
this kind of stuff anyway those are annuities all the different things and flavors on
annuities and they are stupidly easy to do in Excel and even on a financial calculator
they're quite easy to do so what we do is and this is where it's a lot different for
me now than it was even 10 years ago considering I was an early adopter we just have Excel
do this and that's what this is all about right here present values and future values
see those zeros and those ones down there that just tells you just to start it off that
those are ordinary annuities one is an annuity do like that that's all there is to it now
the drab I tried to make that peach and it looks more like you know brownish or whatever
light brown don't touch those go in there and look at the formula so for example for
the present value of an annuity well type oh that's an effective rate present value
present value right there it is okay you will see that the way you create Excel don't
ever put a number in a formula ever you call by reference formulas should not have numbers
in them they should have that way the formula you don't have to play with the core you just
change the numbers in here and you'll get what you want the white is where you can change
a number the blue is what comes out of it the Browns are the ones that you don't touch
in these and you'll get anything that you want out of the formula doing this now on
another notice I it will spit out a negative present value I just put it as absolute calculators
and Excel have a funny habit of wanting present values put in as negatives and they want the
ants and they'll spit out answers of present values as negatives and that just looks a
little bit weird so I make it an absolute but as you can see it's just a present value
PV now this is something I don't like about Excel it's always been this way that's actually
the present value of an annuity PV in Excel is is thinking you've got an annuity now you're
saying oh well that wouldn't help me with a lump sum question well yeah it is suppose
that we had just to do a lump sum with the present value suppose that in 15 years you're
going to get a pile of money okay and here's one where I'm violating the Brown rule but
there are no payments let's say that your interest rate is five point two five percent compounded
quarterly and you're going to put you're going to get in 15 years let's say twenty thousand
dollars there's the present value of it that's a lump sum so that goes back to what I was
saying technically a lump sum is an annuity of one payment but instead let's suppose that
we are over the next seven years with compounding twice a year and an APR of three point nine
nine percent you'll put in two hundred dollars there won't be any kicker at the end there's
how much you'll have at the end of seven years that's a classic ordinary present value of
an ordinary annuity that fast you don't have to master the formula you have to master how
to translate the word problem that I give you into an Excel problem into an Excel routine
all you have to do is put the numbers in this is classic when I taught algebra I taught
the students you get a word problem get rid of the words find the numbers find what they
where they go in the formula now find the numbers and figure out where they go in the
Excel sheet it's the same routine and this gives you the effective rate essentially an
effective rate an effective rate is the rate that would be annual compounding that would
be the same as the rate with the more than annual compounding like for example notice
here suppose that if compounding were four times a year you have a little you there'd
be a little more money to it but you also notice the effective rate would go up the
annual rate of four point zero five percent compounded annually is equivalent to the APR
of three point nine nine percent compounded quarterly do you follow it it's not hard just
and like I said that one I was kind of cheating for a lump sum you will have to put in the
FV but if it's a normal if it's a typical annuity there's no kicker at the end take
it over here to a future value let's do that one 18 years and let's say that the compounding
on it is oh you're going to put in payments every year so the compounding would be once
a year and an APR of let's say four point two five percent and you're going to put in
$2,000 every year that's how much you and notice down here it's one because it's an
annuity do and notice that the effective rate is identical to the APR because it was only
one compounding per year so you'll have a fifty two thousand four hundred eighty six
dollar check to give the kid on his or her 18th birthday putting in two thousand dollars
a year I can't emphasize that when we did these actually with the formula or even with
the tables for God's sake it took a lot longer than it does now to do this well let's talk
about this suppose that instead you put in a thousand dollars but you do it twice a year
you notice now the effective rate diverges and you've got a little more not much more
but you got a little more now payments on a loan this is a classic present value of
an annuity because but you know the present value right away in this case let's say that
you are going to buy a home on a 25 year mortgage normally for loans it's twelve periods per
year and your APR is let's say six point three nine percent right now and there's no kicker
and it's an ordinary annuity so there will be what I put in my present value let's say
that you buy an ugly house for negative you have to put in a negative here I hate that
but five thousand let's say hundred fifty thousand dollars those are your payments you
follow with me play with it for God's sake don't ever save what you've done because then
you'll have wiped out the core that's a big no-no I can even make this so that it's read
only or something like that you can't save it but I don't but if you do see an improvement
or something that you'd like to put in there by all means upgrade it but I mean this is
actually this takes care right here of almost everything that would be required in chapter
is that you see in chapter five there are a few tailing things like balance find the
balance on a loan after a certain number of years and I can let you figure that out to
give you a little bit of exercise with this kind of a model but this is actually very
simple doesn't use V lookups and H lookups and all that kind of nonsense it just goes
through and it makes it pretty easy for you to choose the numbers and put them in and
then just get your answers out now I've done this for a number of the quantitative parts
of the remainder of the course and the reason is that you will use Excel to do this in your
work environment no one's going to say I want you to use this formula to calculate the present
value they're going to expect you to know how to use Excel to do that to do things like
that if it isn't already somewhere in your system already as a template and that's the
reason I do it the way I do it here is so that you are getting the practice that you
need to become proficient in Excel so that the rest of the courses that you do you don't
have that geez so this is going to take forever you know how to create models in Excel and
they don't have to be too complicated how many of you have started that on Wall Street
prep course yet have any of you few of you have you run into the hard part the V lookups
and the H lookups hate hate hate those and truth be told Wall Street prep is a little
behind the curve now but it's still very much worth your while to have it done to get it
done so or something that is roughly equivalent yeah that's the cow that's the calculated
amount that's not the right color blue is it anyway yeah are you getting a different
number did you put these in the same 6.39 that's weird did you get six point five eight
out at the bottom that's a foreign yeah and that's an input wow that's bad I gotta fix
it let me fix that right now that's you're right that should be a white my bad and this
one should be a nice light blue nope little lighter blue there we go my apologies huh
and interesting I'll upload that and fix that yeah that's an input yeah that's your input
right there okay let me save that and upload it before the class is over but that is the
extent of what you should be able to do now like I said there's a balance there's a couple
problems where you figure out the balance on a loan and I leave that one out so that
you can create in here if you want or in a copy of this a routine that will calculate
the balance on a loan equals it's not balanced what is it I can't even remember right now
it's not balanced I can't remember what it is I my mind is drawing a blank right now
but it's something you can even Google to get it but you can do that one too but the
core of your problems and what my expectations would be that you could do these on a quiz
and or an exam just these so if you've mastered it you've got this all done any questions
okay then I'm done with you get one of those early days out of here