Alan Cring Productions in association with the Emergent Light Studio presents
the Illinois State Collegiate Compendium, Academic Lectures in Business and Economics.
This is Business Finance, FIL 240 for spring semester 2024.
Today, present values and future values using Excel.
This is a relatively short lecture because I am going to show you how to make it easy to do the problems
that I would expect you to do on a quiz or an exam and it will be relatively straightforward
in doing problems in your homework as well.
The upshot of it is that you have to make sure that you know what my template in this case does.
Where you put numbers in, where you don't put numbers, and where you kind of break a rule once or twice.
But overall, this will make it a lot more efficient for you to do your work.
And one of the big reasons is that you will be in corporate America, almost all of you will be.
They will hand you Excel and often times you'll see they'll give you a spreadsheet that looks like some kind of ocean monster
and you'll know kind of what to do and how to look at the sheet and you'll also know how to do a few things for yourself.
Now later you'll build templates with me.
The first one, this one for today's lecture, I've built it pretty much most of the way for you
and so you can see the best practices as they come about in this.
If you will bear with me one moment here, I want to look at something very quickly.
Well, not too worried about that for the time being, but we'll get to that here in just a little bit.
But before we do that, of course, we always like to have a look at the numbers.
And as you can see, the numbers look kind of dim, but it's okay because the markets were closed today,
the stock market and the bond market, this being a national holiday, President's Day, nothing happened today.
However, the rest of the world that does not recognize our presidents as meriting a holiday in their countries,
they were rolling right along. So we will move over here and the crude oil market would be a market that's global.
And as you can see, it has punched a little ways through that upper line of that trading band that I've been talking about from 72 to 79.
It's a little on the top side of that, but you notice that in the last couple of hours,
the traders have chickened out and they're running back toward that upper bound there at 79.
And why was it going up like that? Well, near midday, well, in the morning, there were rumors rumbling around,
and I didn't get exactly who started them, but there were some concerns for something in the Middle East,
and I don't know what it was, although it would probably have been something that would have indicated a potential expansion of the combat region,
the active combat region. I don't know, though, but that seemed to spook the markets a while, but they're calming back down.
They're not seeing any more shrapnel than usual, so it probably will find its way back down below that upper level of 79 by the evening.
By this evening sometime, we hope. Gold seemed to get all kinds of excited, but then it lost its ambition.
These are global markets again. These are not U.S. markets, so gold was, the gold bugs around the world were excited,
but then they started selling off as they got the news that the rumors weren't so big after all.
So running over here, now the bond markets closed because that's U.S. treasuries and that would be a U.S. market,
but over here in Japan, they got spooked. They were up a little bit at the start last night, but then they got spooked,
but again, you see that as the rumors calmed down, they began to crawl back up until by the end,
they were virtually where they started, down just a measly 0.04%.
London, by the time we got to London, they were all chipper and they had a little bit of an up day, but still it wasn't much of an up day at all.
0.22% up from the start, nothing to really get excited about, so there's a lot of hold on, let's see what's coming next around the global markets.
Now, just as a little bit going on and focusing on a company that is relatively local, but it also has some interesting things going on.
Rivian, now remember, what you're going to see was Rivian at Friday. Nothing happened today, but there was an aftermarket.
Now, the reason I'm showing you Rivian is this. Go down here, Rivian is going to announce its earnings on the 21st,
so that would be that it's going to announce its earnings on Wednesday. Now, it's estimated it's going to lose money.
It's been losing money all along. It's lost a lot of money, but always what it said its earnings would be were worse than what they actually reported.
So the expectation is that right now they have projected that their earnings they will announce are going to be about negative $1.33 per share.
The general sentiment is that they're always conservative, so they'll be a little better than that.
But we see from the market, and it's hard to see anything, but if you'll notice it's a close on Friday.
They were down, but then in the aftermarket they began to rise on Friday evening.
That was rumors that it's going to be not as bad as they said they thought it would be.
Those rumors we'll see on Tuesday, but I would imagine that from what I'm hearing the rumors are still that it's going to be a better report than what they said they thought it would be.
So that will drive the rumors. The rumors will drive the pre-market tomorrow upward and probably through the day Rivian will find some wind in its sails and go up more.
And so we should expect that when they give their estimate it will probably, their green dot will be maybe about there.
But remember as I said, you buy on the rumors, you sell on the news.
So if you're going to think that Rivian is going to go up, most likely that will happen before the earnings report.
Because if the report is really better than what they estimate, it's already in the price because the rumors drove the price up.
And anything after that will just be disappointment or well, we priced it correctly before it happened.
So in the worst scenario, if they report earnings down here, then the stock is going to collapse in price.
So any positive movement would probably happen tomorrow and possibly in the pre-market on Wednesday.
But if they're like most companies, they announce their earnings early before the market opens.
I don't know about that though. I didn't check when their earnings report will come out.
But there you are. That gives you, now notice Rivian actually, if you look at it as an investment, it's extraordinarily risky at 2.08 on the beta.
Losing money hand over fist, negative $6.02 per share, that's terrible. It's so bad that there's, well, we don't report a price earnings ratio if the earnings are negative.
They don't pay a dividend. So if you are of the mind that this is going to be a play, you're taking a very big risk.
Even if you're just doing a one day swing on the stock, it's still a very serious risk.
So you have to decide, if you're the kind of investor who wants to take a very short risk, here's one you would take.
But you also have to appreciate that you could very well come out with a lot less money than you went in, even for a one day hit on it.
Now, notice something, one last thing here. Yahoo Finance, as well as other services, give one year projections where they think the stock is going to be in a year.
Are they right? Well, whatever. You can do it as well as they can.
Yahoo Finance is projecting that in a year, this company's stock is going to be $24.52 from where it is now at $16.37.
So the projection is that it is going to be a winner over the next year. Do you believe that?
Well, look at that beta and decide whether you want to take the risk of that or not.
The rumors are that Rivian is being circled by a large company.
Ford already has a 12% stake in Rivian right now and there are rumors of another car manufacturer, possibly European or Asian, circling Rivian too.
That would all be really good news for the stock price if that really happens.
But we'll see. That's how it works.
Now, going to our fun for the day.
And I did this last week and I'm going to bring it up again.
In your canvas, files.
Now in your files you want to find your spreadsheets.
Now, I have, there is a template in here for almost everything that you would need to do quantitative problems from here out in the course.
Almost everything.
Now the ones that are in there, you can certainly augment them with a few extra stupid pet tricks if you want to.
I caution you, if you modify a template, always save it as a copy, not as the original.
Well, in this case you could still download the original.
But you always want to do any extras, pretty them up or anything in somewhere else.
But in the case of today, this is the present and future values spreadsheet. And you download it.
And I encourage you to do this now.
And there you have the core.
It doesn't do you much good unless first you know what you're actually calculating.
And also it helps for you to know how Excel is doing the calculations.
Now I'm doing this one more pedantically, in other words, more step by step than I would the ones later than this.
So you can see how the architecture of a good spreadsheet is created.
And I'm not doing everything that I could do in this, macros and pivot tables and all that.
You don't really have to be a genius at Excel to use it very effectively in a class like this.
We, as I told you, many, many, many years ago we used formulas, tables, and then financial calculators.
Well, in the workplace that is out there now, Excel is absolutely imperative.
Most companies are living in Excel.
Now in a couple of years, I'm already starting this in my advanced classes, you'll be using chat GPTs to do them.
They will maybe work with Excel, but it will get to the point where even Excel will be somewhat obsolete.
You guys are living in a very fast changing technological environment.
I won't show you chat GPTs here, but if you want to know about them, I can show them to you in my office.
But let me take this off the board, everything I've got here, and we're going to start this over here with annuities.
An annuity is a stream of cash.
You get $10 today, $20 tomorrow, $30 next week.
It's just clumps of money.
Now the one I taught you last week was just a lump sum.
What's the present value of $15,000 that you'll get in 18 years at 5.25% compounded quarterly?
This is multiple in a periodic fashion.
Now technically a lump sum is an annuity with just one cash flow, one payment.
But that's kind of a trivial one.
And in fact, the spreadsheets that I've got for you, you can actually trick it into doing the lump sum calculations as well as more traditional annuities.
Now there are level annuities, it's the same amount periodically.
Like for example, your payment on a car loan, that is a level annuity.
It's the same amount every time.
You pay the same amount in each payment until you're done.
Or they took your car away in repo.
These are very common.
Anything that's a payment, most payment structures are level annuities.
Nothing big about that.
Non-level annuities, which we don't deal with now, we do deal with them later in the semester, those are where it's not the same amount every time.
Think about this.
Suppose I'm projecting the free cash flow for 10 years out for a company.
Now if I find the present value of those, I've got a pretty good estimate of the value of the company intrinsic value right now.
But those are certainly not going to be the same amount every time in the annuity stream.
That would be a non-level annuity.
Dividends that a company pays, oftentimes we will value a company by the present value of its future expected dividends.
Pull them back to the present, figure out what it is.
Those dividends are probably not going to be the same.
Especially in a young company, they might have a little dividend next year, a nicer dividend the year after that, maybe no dividend the year after that.
So those can be non-level annuities.
And that's where things like Excel come in to their excellence, because in Excel you can just give it each one of them, say find the present value of each of those, and add them up.
Very easy.
Okay, non-level.
Now I know that most of this was covered in your accounting classes, but now you're getting it from a great professor like me, so I'm repeating what you probably already know.
Ordinary annuity.
An ordinary annuity is where the payments occur at the end of every period.
You borrowed money for a car.
You'll make your first payment a month after you get the loan, probably.
So it will be at the end of each period that you'll get it.
Ordinary annuities are the basis for almost all loans.
You assume that the payment is at the end of each period, and a lot of loans are 12 payments a year every month, so your compounding period is 12.
Nothing really complicated about that one right there.
Ordinary annuities kind of are the rock-solid basis.
The other side of it, though, is the annuity due.
Those, the payments begin at the beginning of every period.
So your first payment is right away.
That's a little bit different.
Now those are actually not that unusual.
Like, for example, your rent is an annuity due because when you sign your lease agreement, you pay your deposit and your first month's rent.
So those payments begin at the start of every period.
Another example, and this comes from the world of financial planning, and plugging, we do have a financial planning degree here.
We have a financial planning institute, which is nationally ranked.
Here's how it would work.
You, madam, I'm a financial planner.
You come to me and say, I want to make all my dreams come true.
And of course, first thing I think is, well, I'm here, so there you are.
Oh, sorry.
But you say, okay, I should like to have a new car every three years.
I should like to have a really fabulous European vacation every five years.
And oh, by the way, I have two kids, one's six and one's eight.
The six-year-old is, he's got to go to college.
The eight-year-old, he's kind of stupid, but we're going to put both of them in college.
So I want to have the money to pay their tuition for four years of college when that happens.
Okay, so what's going on here is that you have a timeline,
and along this timeline are different amounts of money that you will need to achieve.
Some of them will have the same timeline repeating itself over and over.
Some of them will have timelines way out.
Some of them will have timelines that are closer in.
You want to buy a house and you want to have a nice down payment in seven years.
All those, that's what a financial planner will assess.
How much are each of those amounts and when will you have to have that money?
And so what we will do is we'll figure out the payment stream that would get you to this one.
To this one, to this one, and to this one.
And how much you have to put in the bank so that the future value of each of those
was achieved through the payments for that.
We add those up and I say write me a check for that total amount,
and every year or every six months or whatever you write me another check
so that I create so that the future value of this annuity due right here
will achieve each of these along the way.
That's what financial planning is at its heart.
You write me that one check and then I distribute the payments
so that they build, each one builds up so that it hits the goal.
So that one month or one quarter or one year,
before any of them is due, you've got the money for it.
That's a classic example of an annuity due.
I'll give you another good example.
I'm trying to think.
I better not bother you.
You look like you've had a hard day.
Yes, sir.
You have a significant other.
A significant other.
And this significant other comes to you one night and she says
those three words that change your life.
No, the three words aren't I love you.
They are I am pregnant.
It happened to me, bruh.
I didn't have a razor blade to kill myself.
Well, it turned out nine months later, well, there it was.
I've been at five of those in my life.
Let me tell you, it's exhausting.
I was so worn out after the birth.
No, seriously, it was rough.
She seemed to have a problem too,
but for me personally, I had things to do that day.
So anyway, you get through the birth and you got that thing
happens that the kid shows up and you say I'm Taddy.
Oh, God.
And so the first thing you do, you leave the hospital and you
go to Burger King and get yourself a Whopper and then you
think I got to take care of this kid.
I want to make sure this kid goes to college at Illinois
State University and maybe that old geezer finance professor
will still be there and corrupt his life too.
Anyway, so you go right to the bank on the kid's birthday and
you put in $2,000 and every year on the kid's birthday you put
in $2,000.
You put in your last payment when the kid is 17.
That would make a total of 18 payments, right?
Okay, 18 payments.
So you let the whole mess ride for one year,
you take it out and you give them a check.
Here it is.
I've been saving this all these years.
That is a classic annuity deal.
$2,000 a year, payments at the beginning of each period.
That's an annuity deal.
That's very similar to 529s, all these raw things.
You put your money in at the beginning and then that last
one along with all the others rides one period more and then
you pull it out.
That's how it works.
So the annuity due is actually, even though it seems a little
odd at first, payments at the beginning of every period,
it's actually the foundation of a lot of financial responsibility.
Like if you wanted, you finally realize I want to own a home.
I want my own house in five years.
Well, then you start putting payments, money in to an account
for a down payment so that you get a better interest rate and
all that.
Well, you're going to start those and then the last payment
you put in would be about a month before you pull it out to
make that down payment when you're getting the mortgage
loan.
That's how these work.
So there's the foundation of what we're doing.
Now let's go back through and let's let Excel do all the
heavy lifting because every one of these, present value,
payments on a loan, future value, these are just classic
annuities and they're all level annuities, which is all we
really care about at this point.
Now if you've got a non-level annuity, you can just take
present value of each of them and add them up, but that's a
pain in the butt.
But anyway, now let me bring up something here.
You've got, in the way I construct these sheets, and
these are not the super great awesome.
I don't use things like pivot tables and other things.
I don't want you to be completely confused by them.
But there are a couple of things that I want to point out.
First of all, white are the numbers you put in, in the
white cells.
And this is the way I taught lower level math for many,
many years.
I tell people, get, it's a word problem.
Get rid of the words, pull the numbers, those are what you're
going to need.
You just need to know what number goes where.
And that's what the words were there to tell you.
Once you've got the numbers and you know where they belong,
just stick the numbers in, in the white areas.
What will come out is the blue is your result, your answer.
What you will put on your Canvas exam, that's what you will
put out there.
Normally I round these to two decimal places, but my usual
for dollar amounts, I say to the nearest dollar.
So you can round it down if you want.
Now the orange, or whatever color that looks like up there
on that projector, the orange is thou shalt not touch these.
Although there's one exception, one place where you can pull a
little trick.
I'll show it to you, but it's not necessary for the time
being.
But, so for example, let's take a problem where you, madam, are
going to get $300 a month for the next ten years.
You'll get $300 every month for the next ten years.
If you discount, what the hell?
I lost my track.
Cash flow.
Wow.
I get confused when they ask if I want extra cheese on my
burger.
Cash flows at 3.49%.
What is the present value of this annuity?
You will get $300 every month for the next ten years if you
discount the cash flows at 3.49%.
What is the present value of this amount?
So this is a classic present value of an annuity.
And I will not throw at you something weird.
Present values are almost always ordinary annuities.
Future values are almost always annuity due.
And they will always, in both cases, be that way on anything
I would ask you.
Where do you put that?
You see this type in Excel, a zero in a financial formula
says ordinary annuity.
A one says annuity due.
Okay?
I put them in there.
Don't change them.
That's why it's in the orange zone.
So now we're going to go through, how many years is it?
Ten.
How many periods per year?
Well, I said every month, so that's 12.
What's the APR?
3.49.
You see how I'm putting it in?
I've already set it up so that as you type in 3.49, it's
automatically turning it into a percent.
And what are the payments?
Oh, $300.
And there it is.
There's your answer.
The present value is $30,352.
Yeah?
Are we able to do this in the next half of the first?
I want you to.
Sounds good.
Yeah, I know, right?
They call me Pizza Hut because I deliver.
That's because you're cheesy, Prof.
Where the hell was I?
Okay.
You see the effective rate?
There's a formula for it in the book.
What that does, the effective rate just says that 3.49%
compounded 12 times a year.
What equivalent rate would compound once every year?
We do that so that if you've got one thing that does
quarterly compounding, another does monthly, another does
weekly, the effective rate makes them all in the same
units, annual.
That's all effective rate does.
And you notice it's calculated for you.
You don't have to worry about it.
And so the only things that you ever have to worry about
are the years, how many times a year, what the APR is,
and how much you're putting in.
That's all you have to do, and it happens for you.
Now, let's do that one where we're looking to see how
much that check would be for the kid on his or her 18th
birthday.
So we said we're going to put in $2,000 every year.
Let's say the rate is 4.35%.
And we're going to do that for 18 years.
Now, be careful about counting years.
I sometimes have to do this on my fingers.
It's the zero, when the kid was born, that was year one.
And then you do 17 more to the kid getting to 17.
So that's a total of 18 there.
I don't want to complicate it too much, but there are a
total of 18.
And then that last one you stop and you just let all the
interest from all the payments accumulate, and then you
pull it out at the end of that last year.
So in this case, we're going to do future value annuity.
We are going to do 18 years, and it's going to be one
period per year.
You're doing it every year.
And our APR is 4.35%, and the payments are going to be $2,000.
So you will have $52,973.
That's it.
I guarantee you these problems, if you've done them
before with tables or with a formula, they're a lot longer.
Now, I'm going to help you out with a couple of other things.
But one thing, well, notice here.
Do you see how the effective rate was the same as the APR
because there was only one compounding per year?
So you didn't get that extra boost of recalculation of
interest.
Now, suppose, watch this.
Now, remember the amount, 52,973.
Now, suppose that I had done two a year, $1,000 each.
See how you would have had a little more because it was
gaining interest faster?
And notice now that the effective rate is a little
boosted above the APR.
I could even do it this.
Watch what happens.
If I do four payments a year, $500 each, see how you get
even a little more because you're recalculating interest
at a faster rate.
Every time you put in a payment, interest recalculates.
So in this case, you have a little more as the check at 18
years old and the effective rate is even still a little
better.
So there you go.
Now, let me take you back here.
I'm going to do this with this.
Now, first of all, this ABS.
As I told you on Wednesday of last week, financial
calculators in Excel have this thing about negatives on
present values.
If something has a, if you're calculating a present value,
it will give you a negative.
If you put in a present value, it will come out as a
negative.
You should put it in.
I'm sorry.
If you need to put in a present value, you should put
it in as a negative.
So that's what that absolute value is.
Now, going to the core function, though, if you look at
the core function, to calculate annuities, the Excel
calling function is PV.
Long ago, we gave up on trying to get Microsoft to say
that was PVA.
It wasn't just a present value.
It's a present value of an annuity, for God's sake.
Unfortunately, it's kind of a misnomer.
And also, their number, something we'll do in just a
few weeks is NPV, net present value, which they don't
do the way it's supposed to be done, but they rule the
universe, so I have to modify what I teach you to do it
in Excel.
But one way or the other, notice something in the
formula.
There are no numbers in the formula.
This is Excel best practices.
You don't ever put numbers into a formula.
You call those numbers from someplace else.
You call by reference.
This is best practices.
The reason, the big reason is that, did you see how I
was changing the numbers up here in the white zone and
it was automatically recalculating?
That's why.
You want to leave that core formula with just what you
tell it from other places.
This is going to be the case all through Excel.
You don't ever want to put numbers in a formula.
You want to call them from someplace else.
In fact, this isn't just Excel.
If any of you ever step into the world of web page design,
long, long ago, I wrote the web pages that were in the
very early days of the internet.
We would put a web page.
You'd have do this, make it pretty, make it bold, make it
italics, then you'd give it the text, and then you'd close
the text with div symbols and go on.
We don't do that ever anymore.
If you look at the index page, the page that you actually
call when you type in a URL or click on a link, that is
nothing but formulas.
It calls everything like text and even the colors from
files.
The actual web page itself is just a bunch of code.
You don't see anything there.
So I have web pages.
Okay, I want to put in a feature, a new pretty picture of mine.
I don't stick the picture in the web page.
I stick it in the file that the web page calls when someone
goes to the website.
In my news articles that I used to do, I didn't put news in
the page that people called.
I put it in a file so that when the news changed, I'd just
change the text in the file and call it in, make it pretty,
and all of that.
That's best practices in Excel.
It's what you have to learn to be able to use Excel
effectively in corporate.
That's why it's scary.
The first time you look at a corporate spreadsheet and you
try to look at the formulas, what the hell am I looking at?
Where are the numbers?
Well, they aren't there because those numbers are being
called from other files that are being pulled in or other
worksheets that are being called to this one.
This is something for you to know right now as you begin to
do your design.
Now we've got payments on a loan.
Who do I use?
You, madam.
You want to live the dream.
You want your own house trailer on the outskirts of Decatur,
double wide.
Your parents, they're kind of double wide, so they have to
visit, they don't want to be cramped.
Now, madam, you decide I should have to get a loan for this.
Let's say I'm going to go for the deluxe, $150,000, and you've
got a lender who will lend you that money on a 25-year loan
with an APR of 6.39%.
So remember the numbers, 150,000.
That is going to come now.
That is a present value.
You have to have that now to buy the house.
Okay?
So we're going to go to payments on the loan.
So we're going to, as I said, we'll have a 25-year loan.
There it is.
Now, the periods per year, 12.
Now, I didn't say 12 anywhere in that description, but I
implied it because you're going to make monthly payments.
The payment structure determines the compoundings.
If I had said you will make your payments every other month,
that would have been six.
I would not have had to say six compoundings per year.
If you're going to make your payments bi-weekly, that would
have been 20, bi-weekly, 26.
So now we go down.
The APR was 6.39%.
There's no future value.
Now, here's where you have to remember, that present values
you key in as negatives.
So it's going to be negative $150,000.
I tried to figure out a way to do that without getting into
too much complication of the code, and I just gave up.
Just remember that in payments on a loan, you have to put it
in a negative.
You'll know you didn't do that because your answer will look
really weird.
But now the payments, $1,002.52 a month.
That's all there was to it.
The effective rate on loan is 6.58%.
That fast.
And you can do all this.
This sheet is, I expect you to pull this up.
You can use tables if you want, like they do in accounting,
or you can use a financial calculator if you wish to.
Or you can actually use a calculator with the formula
that they give you in the book.
If you want to do it the really butch way.
But we are in the 21st century, and I can't prepare you
for jobs in the 20th century.
Matter of fact, I can't even prepare you for jobs that would
have been there five years ago.
So here we are, right here.
Now, I leave one little thing, just kind of leave it for you.
You can actually, there's a formula, and I'm going to let
you figure it out for yourself.
I'll show you on Wednesday.
But there's a way you can figure out how much you still owe
on the loan after a certain number of years.
There's an Excel calling function to find out the balance
on a loan after a certain number.
Now, remember, everything is actually in months here.
So in order to do it, you always would have to do, like after
20 years, you do 20 times 12, so that was in months.
Because it thinks you want everything in months.
So remember that in any kind of a calculation that you would do,
you would always want to tell us what you want in months.
Look here, let me show you something.
Number of periods.
Do you see how the number of periods there was, I had the
times, the 10 times 12, B2 times B3.
Why did I do a B4?
Oh, APR, okay.
And the number of periods, there it is, B3 times B2.
In other words, remember that I have to turn it into months,
years times months.
Whenever you do anything with a financial calculation, you'll
probably have that years times months cell references.
But other than that, give it a try yourself.
See if you can do the balance after a certain number of
periods.
And you can even figure out how much interest and all those
kinds of things, too, if you want to try that.
They're all in Excel.
Excel has so many functions that I can't remember even half of
them, and I'm neurodivergent.
It's just ridiculous.
But in terms of finance, the ones you see up here that I've
done, those are really core.
Those are really core.
But anyway, that's how you do Excel.
And that's on a quiz.
Now, I'm not saying I'll give you a quiz next Monday.
I'm not saying that at all.
So if I give you a quiz next Monday, which I didn't say I'm
going to do, I would anticipate that you can do this like a
boss.
You can just crank these out, and you can see in that kind of
a context the power of Excel and why it has become the atlas
carrying on its shoulders the weight of the financial world.
And then that will give you about two years of running room
before you'll have to learn how to do chat GPTs.
But anyway, for now, though, this is the main bulk of what
you need to know.
And that is all I have for you today.
I thank you.