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 value and future value using Excel.
Now the plan of attack on this is to show you how you can do the problems that I would ask you
or that you would get in Cengage or in your practical work, how you do those using Excel spreadsheets.
This is a direct brute force attack on the problems that would be how you would do it
if you ever had to do it for yourself personally or in a professional work environment.
So this is quite a bit different from how most courses are being done still
and it was certainly different from how any class would have been even a couple of years ago.
And my purpose here is to prepare you for the work environment of the 21st century
instead of getting you ready for a good job in the previous century.
Now in order to do this, oh by the way, really quickly let me look at the numbers
except that there's something odd going on here.
The problem is that the numbers on the stock markets are dead because it is a national holiday, President's Day.
So there is nothing going on. Those numbers you see there are as of last week
and the markets will wake up tomorrow and start to show life again.
However, just because we are having a holiday doesn't mean that the world is having a holiday.
And hence as a result of that you would see for example, oh the oil market is moving along
just like every other day and as you can see crude has now crossed that 79 on the top side
of the band that it was in from 72 to 79. Is it going to stay there?
If you noticed in that spark chart, you see how it's hit about 79 and then it suddenly starts getting a little bit woozy about going any higher.
It's now realized that well the traders have realized well we're in new territory.
Is this justified by the market itself?
We do have some concerns going on right now in the world markets.
Most mainly the result of concerns about what's going on in the Middle East.
There are rumblings that something really nasty is kind of going on and it's sort of rattling the crude markets,
the crude oil markets. I don't know how serious it is.
These markets react quite sensitive sometimes especially if there's credible evidence that there may be some supply disruptions
as a result of what's going on. It's not in any of the new services yet,
but there's scuttlebutt that I'm hearing about something or other being a big worry right about now.
It may be a major expansion of the combat zone of activity. I don't know.
But anyway, as you can see gold is above $200 an ounce, it topped out and it's fallen,
but now just in the last hour or so it's gotten its wind again.
Silver isn't really interested in this at all and the currency markets are just sort of sitting there looking stupid.
Bond market is dead because it's a U.S. market, so there's not going to be any activity there.
But we get over here and we see that the Nikkei was dropped down most of the day.
It tried to crawl back up, but it finished pretty much where it started for the day.
And London seems to be on a little bit of a rise, although if you look at the spark chart it looks like a really big bull day.
But if you look at the percentage, it was up less than a quarter of a percent,
and that means it was almost not worth even talking about.
So always look at the percentage and not a chart or not the absolute numbers to see what the real story is that's going on.
And just one company, let me do one right now because it's right on the threshold.
And again, do not ever take what I show you here as investment advice for heaven's sake.
Rivian, and the reason I'm picking on them aside from them, the company being local, is that now of course it's not trading today,
but if you look down here, they have earnings being reported in two days.
So you would expect that tomorrow will probably be an active day on rumors.
The rumors I heard were that it was, see they're projecting that they'll have losses of about $1.33 a share,
which would be a closing because their last earnings were down a lot more than that.
So this might be a good sign.
They always seem to be reporting what they get, actually they report, is oftentimes better than what they estimated.
You can see that these open circles are their estimates, and the green dots are what actually came about.
So there's some expectation that they'll come in better than a negative $1.33 a share.
How much more seems to be some belief that they may come in as good as about a negative $1.15 or so.
So the rumors are in favor of it, so tomorrow may very well be an up day.
Let me see the, that's a one day chart.
That was last, that was yesterday, that was on Friday.
See how it was just lying really quiet yesterday on Friday?
With earnings coming close, that would say that there are a lot of investors waiting just before the earnings call, the earnings report.
And so that might mean a big day up or maybe a big day down, depending upon which way the rumors flow.
So this is one of those where if you're a risk taking speculator, which I am, I would go with this.
And very, very, very good chance that I would get my ass kicked.
But who knows? I can see that in the post market Friday, the rumors were swinging in Rivian's favor.
So we'll see.
Let me go over here, and we're going to begin our happy time with Excel.
Give me a second while I get into my account.
File 240, and I encourage you to get this with me.
And we'll go to the student view, and now you can see what I'm seeing here.
You go over to files, and I'm repeating what I've said before.
In your files, you're going to see a nice little packet of different spreadsheets in here.
Well, let's go to the sheets and grab, where are my spreadsheets?
Really hard to, oh, here we go.
In your files, go to your spreadsheets, and you can see that there is a nice little package of different Excel spreadsheets.
These are more or less templates for you to use.
There's one I'm going to fix a little bit here, what I'm going to use today.
You have to know how they work in order for them to be of benefit.
But if you know how they work, you can use these.
I'm not saying you may.
I'm telling you, I expect you to use these for quizzes and exams from this point forward.
Two years ago, I said, I expect you to use a financial calculator for your problems.
Now I'm telling you, I expect you, you don't have to, but if you know how to use Excel,
and you can follow what I'm saying, then you will be in a position that you can use the tool that is used in corporate life nowadays.
In a few years, I will probably shift it over to using chat GPTs in combination with Excel
to try to keep you at the current level of expectations of corporate America.
Right now, they're not really getting how powerful chat GPTs are, but they will soon enough,
and soon enough I will adjust so that you will query, you will create chat GPTs,
and then you will ask them for answers.
But all that requires is that you know what's going on behind the scenes.
For the purposes of this one, where present and future values.
Now I'm going to have to do a little quick fix in this, and then I'll re-upload it once I've brought it up.
Enable editing.
Okay, so there's something that I need to repair in here right away, just to fix it, and then I'll re-upload it.
I noticed it in the last class that needed to be fixed.
Okay, nope, that wasn't what I wanted. Let's try it again.
Right, good.
Okay, good.
Now, before I do this, this has to do with present values and future values.
Now, this isn't all of the work, what I'm doing in the Excel.
You'll probably want to put in a little bit of an enhancement or two,
but remember, whenever you do an enhancement to a core sheet, you save it as a copy.
You don't overwrite the original in case you made a mistake or something like that.
You really don't want to.
But the impetus of what I'm doing here is on annuities.
Now, annuities come in different flavors, and some of this you learned in your accounting class, I'm sure,
but you didn't learn it from an incredible professor like me, so I'm going to go back through it again.
Let's start with annuities. There are level and non-level annuities.
Now, first of all, an annuity.
An annuity is just a cash flow, a periodic amount of money coming in or going out.
That's all it is.
So payments on a loan are an annuity because it's an amount of money that you are paying out on a periodic, usually monthly basis.
Nothing big about that at all.
Oh, as far as level annuities go, there is a formula you can use that will calculate them.
A calculator like the TI-83 Plus I showed you last week, it can do it too.
Excel can do it as well.
Excel just happens to have a format so that you have a hard copy and you can change parameters within it.
And so a level annuity.
There, the formula isn't that hard, but we can do it much faster.
Even with tables, it's a little faster.
A non-level annuity is one where those cash flows kind of bobble around, up and down.
They're not the same every time.
There is one point I want to make about a level annuity.
I showed you lumped sum present value, lumped sum future value last week.
Technically, those are annuities with one cash flow.
So we should be able to take an annuity formula in Excel and trick it into doing just a one, a lumped sum.
And we'll do that in a little bit.
But level annuity, payments on a loan.
Money you put in the bank every month if it's the same amount.
Those are level annuities.
Non-level annuities, we don't really need to worry about those right now.
They come into play a little later in the course.
They're more of a pain in the butt.
Because in level annuity, there's a formula you can take and just say,
this is how much I'm putting in every month, every quarter or whatever.
And the formula will spit back out the present value or the future value.
Non-level annuities, they are a pain in the butt because you have to take,
like if you're finding the present value of a non-level annuity,
you have to take the present value of each one of the components,
bring it back and then add them all up.
After about three or four payments in a non-level annuity, it gets to be really tedious.
You wouldn't even want to do it that way in Excel.
Well, you can do it in Excel.
It's pretty easy.
But anyway, non-level annuities.
Now, we see those later in the course.
For example, if I were to find, looking to find the present value of the future projected cash flows of a corporation,
well, the problem is those future projected cash flows aren't going to be all the same.
They're going to vary from one year to the next as you're clocking your way out.
And then you've got to telescope those back,
and that would give you an intrinsic value for the company.
So non-level annuities can be a little realistic, but they're also kind of a pain.
Also, if you were to consider the value of a stock, the intrinsic value of a stock,
as the present value of its future expected dividends, that would be a non-level annuity.
And that could be, so you'd have to find the present value of each one of the dividends
for enough years to get a good approximation in present value
and add all those present values of those dividends up.
Non-level annuities can be a pain.
That's where programs like Excel, forgive the no pun intended,
Excel excels at things like that.
It'll be happy to crank those out in a split second.
But anyway, level and non-level annuities.
Now, we have present values of annuities, and we have future values of annuities.
One thing that we need to get out on the table right away.
There are also, you can categorize annuities as ordinary annuities,
and there are also annuity due.
An ordinary annuity is an annuity where the payments occur at the end of each period.
End of each period.
An annuity due, the payments occur at the beginning of each period.
So you get a loan on a car.
Well, probably your first payment is going to be due,
you're going to have to pay it in a month from the day you take out the loan.
And those, that annuity, payments on a loan, are generally ordinary annuities.
Generally ordinary annuities.
Annuity due.
At first it might be a little bit difficult.
Well, when would you start making payments right away instead of a month out?
Well, those happen.
For example, you sir.
You have a significant other.
Now your significant other comes to you one evening and says three words to you that change your life.
No, it's not I love you, it's I am pregnant.
It happened to me.
I tried to kill myself.
Didn't work.
Damn cheap razor blades.
Well, the day of birth happens.
It's exhausting.
I've been at five in my life and I'm tired.
I mean, it wears me out.
But man, there's that rush afterwards.
You leave the hospital and you say, by golly, I'm going to make it good for this kid.
I'm going to put money in the bank on this kid's birthday every year.
So you go right to the bank and you deposit $2,000.
And every year on the kid's birthday you're going to put in $2,000.
And one last $2,000 when the kid has his 17th birthday.
And then you'll let it ride.
And then on the 18th birthday you'll take the money out and give it to the kid as a birthday gift.
That's an annuity due.
Because they occur at the beginning of each period and your last payment rides with the other ones and all the interest one more period.
Now, there are other examples of annuity due.
As a matter of fact, just in my last class I was giving examples of annuity due.
Someone brought up rent.
When you sign a lease, you usually have to pay your first month's rent along with a deposit right up front.
So that is an annuity due because payments occur at the beginning of each payment cycle.
Another example is wealth management, financial planning.
When you sell a package, you go to an upper middle class family and they explain to you,
okay, well, we'd like to have a new car every three years and we'd love to have a vacation, a really nice vacation every four years.
And oh, yeah, we'd like to have a house down payment, a really good down payment on a house in seven years when we have a larger family.
Okay.
What you would do is you would have out here the future value of each of these components.
Then you'd figure out a stream of payments that would achieve each of those goals at the time it happened.
That's financial planning.
That's how you do it.
And then you'd go to the family, here's the plan.
Now, please write me a check for the sum of those payments.
That would be an annuity due because each of those would accumulate and then one month or one quarter or one year before each of them,
the goals was realized, that would be the last payment.
So that would be just a cluster of annuity due, annuities due.
Same concept.
So annuities due are actually out there and they're pretty normal.
We just don't think about a lot of things being annuities due that really are.
So we have here, suppose that you were going to take on, you're going to, let's say, get a paycheck at the end of every,
let's say, every quarter, you're going for the next 10 years, you're going to get a payment every quarter in an account,
let's say, with an APR of 3.59% and each of those payments that you get is going to be $250.
Now, there wouldn't be any future value kicker in this at all.
But there's your present value.
That's what that is worth to you now.
In other words, you would be indifferent at a discount rate of 3.59% between getting those quarterly payments of $250 for the next 10 years
and just getting $8,371 right now.
That's what it's telling you.
You would see no difference between those two.
Now, the effective rate.
You've got a formula in the book, but right here it's calculated for you.
An effective rate is the rate that would be the same as that 3.59 compounded quarterly.
3.64% would be the same rate compounded annually.
That's what an effective rate does.
It turns it into an annual.
It's kind of a way so that you can see interest rates comparatively the same, even if the cash flows occur at different periodic rates.
It says it brings them all down to an annualized rate.
That's what an effective rate does.
Pretty much.
Okay, now let's go over here to that future value.
You, sir, we're going to put in 18 payments.
Be careful of that because even though the kid was 17, you put in one on the 0th birthday, so there's a total of 18.
I even trip up on that sometimes.
I start counting on my fingers, and you'll see that again in another thing.
Now, you're going to do this once a year, so that will automatically create the discount rate.
If you're putting them in on a periodic basis, the period will drive the compoundings per year.
Let's say that you are in an account that has an APR 4.09%, and you're going to put in $2,000 every time.
Now, I'm going to show you something.
You see that 0 over there?
That's an ordinary annuity.
The 1 is an annuity due.
Don't forget that.
Usually you won't have to change these, but here maybe.
Okay, so the kid's going to accumulate $51,718.72.
That's what will be in there on the kid's 18th birthday.
You follow?
Once you get the hang of it, just practice.
I used to actually, last year I even had everyone do the formulas and the calculator approach just so they could appreciate how
blindingly fast and easy it is in Excel once you get the hang of the Excel formulas.
Now, one thing I want you to notice, see the effective rate in the future value problem was the same as the APR?
That's because there was only one compounding per year.
So the annualized rate was the same because there was one compounding per year.
Now, if I had done, let's say, twice a year with $1,000, you can see that the effective rate does begin to pull away a little bit.
And notice how you've got a little more.
Because you're putting them in faster, some money is earning interest a little faster.
But that's future value and present value.
Now, let's take it to loan payments, which are basically annuities.
Okay, you, madam, decide you're going to buy a car.
It's an ugly car, but it's your car.
And so what we're going to do is we're going to figure out, let's say a six-year loan, and you've negotiated out the door that you will
have to get a loan for $25,000.
Now, here's one where I have to give you a caution with it.
With annuity, with everything in finance, the calculators excel.
They like present values as negatives.
Now, I can fix some things, but here you have to put in the present value.
So you have to put it in as a negative.
So I put in negative $25,000.
Now, up here, we're going to make payments for, yeah, let's do five years.
And you'll make 12 payments a year, monthly payments.
That's normally how any loan works, private, personal loans.
And let's say your APR is 6.39%.
There's no kicker.
You usually hardly, that's why that one's in orange.
Orange means don't touch it.
Leave it alone.
And so there's your payments every month.
It just cranks it out.
That doesn't look like a very nice blue.
Let me get that blue.
It's supposed to, blues are supposed to be what calculates.
Yeah, there we go.
I'll re-upload this with that fix.
Okay, there you go.
Your payments are $478.87 a month.
That's all there was to it.
All you have to do, and this is the same principle I taught for 40 years in math,
in arithmetic and algebra classes, take the numbers, get the word problem,
and I'll give you word problems.
Get the numbers out and throw away the words.
Put the numbers in the boxes where they want.
If it's a formula or in Excel, just put the numbers where they belong.
There are no words.
And the answer will come out.
And then you can ask yourself, well, does this answer make sense?
Yeah, that's about what a car payment on a new car would run these days.
Yeah.
I was looking at a new car, and they said, we could get you a deal for $39,000.
And I'm using, it was 6.39% APR, five-year loan.
Well, they can kiss my butt. That's too much.
You see, this will tell you the cold number that you will be paying out the door.
If that's what you borrowed, that's what your payment's going to be to the loan company.
Notice, by the way, see the 6.58%, that's your effective rate.
Do you notice that the effective rate has nothing to do with the amount of money you borrow?
Notice it's the effective rate. I could make this a six-year loan.
My payments come down a little. See how the effective rate, all that matters is the number of compoundings per year
and the interest, the APR. That's all that matters.
That's all. So that's something that I sometimes will ask as a multiple choice question,
which of the following changes the effective rate?
The amount of the loan, the number of payments per year, the number of years, or the original APR?
Well, the number of payments a year affects it, and the amount of money, rather, the APR affects it.
But none of the others do.
That is the extent of doing, that's most of Chapter 5.
Oh, by the way, let me show you. Remember last time I did lump sum problems, like...
You will get $250,000 in 18 years.
If you discount cash flow at, let's say, 5.25% compounded semi-annually,
what is that worth now?
That's a lump sum problem. It's just one pile of money.
Well, this is for annuities. But remember I told you that a lump sum is nothing but an annuity
with just one payment, one amount. So here's how you do it.
Go to the present value, and we're going to just pull a trick here.
Years, 18.
Periods per year, it's semi-annual, so that'd be two. Your APR is 5.25. I couldn't see it. Wow.
Now, there are no payments. So this is one where the payments are zero, but there is a future value,
and here's where you violate the orange block. $250,000.
There you go.
Because you've got just a future amount of money, so we're just going to put that in as the FV,
and everything else, no payments. You're not getting payments along the way. You're just getting a plop of money at the end.
And so there is your present value of the lump sum.
And you can do that with a future value too. I put in an amount of money. Let's say that, oh, I don't know.
I'm going to say, Madam, find $10,000. Find.
And so you say, okay, what if I put that in the bank and I just leave it there?
What if I put that in the bank now and just leave it there for 10 years?
And it's the same, it's compounded, let's say, the account compound semi-annually, just two.
And you can put it into an account that will pay you, let's say, 4.89% compound semi-annually.
Now, there are no payments in this one, zero, but you have a present value in this one of
negative $10,000. Remember, you always have to put in present values as negatives.
What the hell happened there?
Why is APR 1010?
Why is that giving me a wrong answer there? I must have accidentally deleted something. Yeah?
No, I don't think that one. Oh, no, boy, that really helped.
So don't do that. I'll figure out what's wrong here. I must have accidentally changed the formula.
I started fiddling around with this. Years, 10, periods, APR, payments are zero.
The future value is, we don't know what the future value is, but we do know that the present value is
$10,000, type one. I don't get what, I must have accidentally touched a formula.
It'll work though. I'll fix it. Now, the last thing though is that you might want to ask about a balance.
How much is in this payments here? How much do you still owe after a certain number of years?
That's a balance question. Now, I leave that one for you to work out. It's not too difficult to do it,
but there is an Excel function that will give you the balance on these, you still use these numbers,
except that you tell it how many months are left in the loan. So I leave that to you to figure that one out
and see if you can get the answer, the result. I will show it to you on Wednesday, but the balance,
and you can even find the rate. If you know the payments, the amount, and how long the loan is,
you could back in and figure out what the APR on the loan was. All of this is doable with this Excel template right here.
Now, the one thing is I've got to fix this here and I've got to re-upload it, but this should be able to get you through
almost all problems in your homework, plus it'll get you through anything that I could possibly ask you.
Now, the typical problem that I have here, let me delete, get this out here. I'm going to, don't save this,
I'm going to pull up the original one that I downloaded a few minutes ago, downloads. Okay, there you go.
Now, if I ask you a question about this material, I would ask you for a present value of a lump sum or the future value of a lump sum
and the present value of an annuity or the future value of an annuity, and I could ask you for the payments on a loan
and the effective rate. That's what I would ask you on a quiz and I would definitely ask this on the midterm.
I would frame it so that you should be able to put the numbers right in to this Excel sheet, but you have to know how the sheet works.
Now, the last thing that I want to point out to you, I did an annuity here. The first thing, and I have to show you this.
First of all, ABS just means absolute, because Excel always gives present values as negatives.
That's just what Excel does. So I make it absolute so it is a number that looks right to you.
Now, I want you to notice, first of all, Excel has a nastiness in it. PV is actually the present value of an annuity.
It's not just a plain present value. It's the present value of an annuity. So that's just a warning to you.
It's been something that we've fussed at Microsoft a long time ago. They wouldn't change it to PVA,
which would make it a little more logical. But anyway, now the other thing. You see the formula in Excel.
You don't ever want to have numbers in a formula. You see how everything there is a call from a reference cell.
That way you can change the reference cells and the formula will immediately give you the right new answer.
Again, notice that I do that across the board with these calculations. Those are all, even the zero is B6, or rather B8 there.
It always wants Excel thrives on references. So for example, with this loan, I can change, well you saw me do it.
Let me do it with this one. Suppose that you had the present value of, you were going to put money into the bank for five years.
Let's say to build up a down payment. And every six months on a bank account, let's say it's paying you 3.55%.
You are going to put, let's say, $100 in. So after five years of putting in $100 every six months, you would have $2,741.
That's because the formula is being driven by these numbers up here.
That's why you don't ever want to put a number into a formula. Because it completely eliminates your ability to change it and say what if.
It's considered best practices, standard practices. So when you go into that fearsome workplace and they hand you a worksheet, you will see all of this in here.
Now it can be daunting. And later in the course I'll show you how you can actually, there's a function up here in the menus that will allow you to put your cursor on the complicated formula.
And then hit that audit and it will show you, it will light up all of, and even in arrows, show you where everything is that went into that formula.
This is useful to you when you go in and you're starting to try to figure out what these worksheets, these spreadsheets that companies have are doing.
It can get kind of daunting because often times they've been tweaking these every, all the time. So when you look up here, you'll see maybe a lot of different cell references.
That's because we don't put numbers in the actual formulas. It's the same thing with webpages. Back in the old days we put all the text in every table right in the web page.
Now we don't do that. We just call little pockets, little pocket text files to bring in and put the news of the day into the actual web page you see.
This is common all across the business world now. That is pretty much the whole story.
Like I said, I've left one here for you just to practice and I'll show you on Wednesday very quickly.
If you can't figure out how to calculate a balance on a loan after a certain number of months, I'll show you how to do it.
But I'm leaving that for you to give a stab at after I've saved this sheet for you again.
I saved this sheet for you. But as far as anything else goes, yeah, this is the one where I say that's all I have for you today. I thank you.