1
00:00:00,000 --> 00:00:08,000
Alan Cring Productions in association with the Emergent Light Studio presents

2
00:00:08,000 --> 00:00:17,000
the Illinois State Collegiate Compendium, Academic Lectures in Business and Economics.

3
00:00:17,000 --> 00:00:24,000
This is Business Finance, FIL 240 for spring semester 2024.

4
00:00:24,000 --> 00:00:34,000
Today, present value and future value using Excel.

5
00:00:34,000 --> 00:00:44,000
Now the plan of attack on this is to show you how you can do the problems that I would ask you

6
00:00:44,000 --> 00:00:53,000
or that you would get in Cengage or in your practical work, how you do those using Excel spreadsheets.

7
00:00:53,000 --> 00:01:01,000
This is a direct brute force attack on the problems that would be how you would do it

8
00:01:01,000 --> 00:01:08,000
if you ever had to do it for yourself personally or in a professional work environment.

9
00:01:08,000 --> 00:01:16,000
So this is quite a bit different from how most courses are being done still

10
00:01:16,000 --> 00:01:23,000
and it was certainly different from how any class would have been even a couple of years ago.

11
00:01:23,000 --> 00:01:30,000
And my purpose here is to prepare you for the work environment of the 21st century

12
00:01:30,000 --> 00:01:35,000
instead of getting you ready for a good job in the previous century.

13
00:01:35,000 --> 00:01:40,000
Now in order to do this, oh by the way, really quickly let me look at the numbers

14
00:01:40,000 --> 00:01:43,000
except that there's something odd going on here.

15
00:01:43,000 --> 00:01:52,000
The problem is that the numbers on the stock markets are dead because it is a national holiday, President's Day.

16
00:01:52,000 --> 00:01:58,000
So there is nothing going on. Those numbers you see there are as of last week

17
00:01:58,000 --> 00:02:06,000
and the markets will wake up tomorrow and start to show life again.

18
00:02:06,000 --> 00:02:13,000
However, just because we are having a holiday doesn't mean that the world is having a holiday.

19
00:02:13,000 --> 00:02:21,000
And hence as a result of that you would see for example, oh the oil market is moving along

20
00:02:21,000 --> 00:02:32,000
just like every other day and as you can see crude has now crossed that 79 on the top side

21
00:02:32,000 --> 00:02:41,000
of the band that it was in from 72 to 79. Is it going to stay there?

22
00:02:41,000 --> 00:02:52,000
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.

23
00:02:52,000 --> 00:02:58,000
It's now realized that well the traders have realized well we're in new territory.

24
00:02:58,000 --> 00:03:03,000
Is this justified by the market itself?

25
00:03:03,000 --> 00:03:10,000
We do have some concerns going on right now in the world markets.

26
00:03:10,000 --> 00:03:17,000
Most mainly the result of concerns about what's going on in the Middle East.

27
00:03:17,000 --> 00:03:26,000
There are rumblings that something really nasty is kind of going on and it's sort of rattling the crude markets,

28
00:03:26,000 --> 00:03:30,000
the crude oil markets. I don't know how serious it is.

29
00:03:30,000 --> 00:03:40,000
These markets react quite sensitive sometimes especially if there's credible evidence that there may be some supply disruptions

30
00:03:40,000 --> 00:03:45,000
as a result of what's going on. It's not in any of the new services yet,

31
00:03:45,000 --> 00:03:52,000
but there's scuttlebutt that I'm hearing about something or other being a big worry right about now.

32
00:03:52,000 --> 00:03:59,000
It may be a major expansion of the combat zone of activity. I don't know.

33
00:03:59,000 --> 00:04:07,000
But anyway, as you can see gold is above $200 an ounce, it topped out and it's fallen,

34
00:04:07,000 --> 00:04:12,000
but now just in the last hour or so it's gotten its wind again.

35
00:04:12,000 --> 00:04:20,000
Silver isn't really interested in this at all and the currency markets are just sort of sitting there looking stupid.

36
00:04:20,000 --> 00:04:26,000
Bond market is dead because it's a U.S. market, so there's not going to be any activity there.

37
00:04:26,000 --> 00:04:33,000
But we get over here and we see that the Nikkei was dropped down most of the day.

38
00:04:33,000 --> 00:04:40,000
It tried to crawl back up, but it finished pretty much where it started for the day.

39
00:04:40,000 --> 00:04:48,000
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.

40
00:04:48,000 --> 00:04:53,000
But if you look at the percentage, it was up less than a quarter of a percent,

41
00:04:53,000 --> 00:04:56,000
and that means it was almost not worth even talking about.

42
00:04:56,000 --> 00:05:05,000
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.

43
00:05:05,000 --> 00:05:13,000
And just one company, let me do one right now because it's right on the threshold.

44
00:05:13,000 --> 00:05:19,000
And again, do not ever take what I show you here as investment advice for heaven's sake.

45
00:05:19,000 --> 00:05:28,000
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,

46
00:05:28,000 --> 00:05:37,000
but if you look down here, they have earnings being reported in two days.

47
00:05:37,000 --> 00:05:44,000
So you would expect that tomorrow will probably be an active day on rumors.

48
00:05:44,000 --> 00:05:54,000
The rumors I heard were that it was, see they're projecting that they'll have losses of about $1.33 a share,

49
00:05:54,000 --> 00:06:01,000
which would be a closing because their last earnings were down a lot more than that.

50
00:06:01,000 --> 00:06:04,000
So this might be a good sign.

51
00:06:04,000 --> 00:06:13,000
They always seem to be reporting what they get, actually they report, is oftentimes better than what they estimated.

52
00:06:13,000 --> 00:06:21,000
You can see that these open circles are their estimates, and the green dots are what actually came about.

53
00:06:21,000 --> 00:06:29,000
So there's some expectation that they'll come in better than a negative $1.33 a share.

54
00:06:29,000 --> 00:06:37,000
How much more seems to be some belief that they may come in as good as about a negative $1.15 or so.

55
00:06:37,000 --> 00:06:44,000
So the rumors are in favor of it, so tomorrow may very well be an up day.

56
00:06:44,000 --> 00:06:47,000
Let me see the, that's a one day chart.

57
00:06:47,000 --> 00:06:52,000
That was last, that was yesterday, that was on Friday.

58
00:06:52,000 --> 00:06:57,000
See how it was just lying really quiet yesterday on Friday?

59
00:06:57,000 --> 00:07:08,000
With earnings coming close, that would say that there are a lot of investors waiting just before the earnings call, the earnings report.

60
00:07:08,000 --> 00:07:17,000
And so that might mean a big day up or maybe a big day down, depending upon which way the rumors flow.

61
00:07:17,000 --> 00:07:25,000
So this is one of those where if you're a risk taking speculator, which I am, I would go with this.

62
00:07:25,000 --> 00:07:31,000
And very, very, very good chance that I would get my ass kicked.

63
00:07:31,000 --> 00:07:40,000
But who knows? I can see that in the post market Friday, the rumors were swinging in Rivian's favor.

64
00:07:40,000 --> 00:07:44,000
So we'll see.

65
00:07:44,000 --> 00:07:50,000
Let me go over here, and we're going to begin our happy time with Excel.

66
00:07:50,000 --> 00:07:59,000
Give me a second while I get into my account.

67
00:07:59,000 --> 00:08:04,000
File 240, and I encourage you to get this with me.

68
00:08:04,000 --> 00:08:11,000
And we'll go to the student view, and now you can see what I'm seeing here.

69
00:08:11,000 --> 00:08:16,000
You go over to files, and I'm repeating what I've said before.

70
00:08:16,000 --> 00:08:25,000
In your files, you're going to see a nice little packet of different spreadsheets in here.

71
00:08:25,000 --> 00:08:31,000
Well, let's go to the sheets and grab, where are my spreadsheets?

72
00:08:31,000 --> 00:08:34,000
Really hard to, oh, here we go.

73
00:08:34,000 --> 00:08:42,000
In your files, go to your spreadsheets, and you can see that there is a nice little package of different Excel spreadsheets.

74
00:08:42,000 --> 00:08:47,000
These are more or less templates for you to use.

75
00:08:47,000 --> 00:08:51,000
There's one I'm going to fix a little bit here, what I'm going to use today.

76
00:08:51,000 --> 00:08:56,000
You have to know how they work in order for them to be of benefit.

77
00:08:56,000 --> 00:09:00,000
But if you know how they work, you can use these.

78
00:09:00,000 --> 00:09:03,000
I'm not saying you may.

79
00:09:03,000 --> 00:09:11,000
I'm telling you, I expect you to use these for quizzes and exams from this point forward.

80
00:09:11,000 --> 00:09:21,000
Two years ago, I said, I expect you to use a financial calculator for your problems.

81
00:09:21,000 --> 00:09:27,000
Now I'm telling you, I expect you, you don't have to, but if you know how to use Excel,

82
00:09:27,000 --> 00:09:39,000
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.

83
00:09:39,000 --> 00:09:47,000
In a few years, I will probably shift it over to using chat GPTs in combination with Excel

84
00:09:47,000 --> 00:09:52,000
to try to keep you at the current level of expectations of corporate America.

85
00:09:52,000 --> 00:10:01,000
Right now, they're not really getting how powerful chat GPTs are, but they will soon enough,

86
00:10:01,000 --> 00:10:07,000
and soon enough I will adjust so that you will query, you will create chat GPTs,

87
00:10:07,000 --> 00:10:10,000
and then you will ask them for answers.

88
00:10:10,000 --> 00:10:13,000
But all that requires is that you know what's going on behind the scenes.

89
00:10:13,000 --> 00:10:20,000
For the purposes of this one, where present and future values.

90
00:10:20,000 --> 00:10:32,000
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.

91
00:10:32,000 --> 00:10:34,000
Enable editing.

92
00:10:34,000 --> 00:10:43,000
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.

93
00:10:43,000 --> 00:10:47,000
I noticed it in the last class that needed to be fixed.

94
00:10:47,000 --> 00:10:52,000
Okay, nope, that wasn't what I wanted. Let's try it again.

95
00:10:52,000 --> 00:10:54,000
Right, good.

96
00:10:54,000 --> 00:10:55,000
Okay, good.

97
00:10:55,000 --> 00:11:02,000
Now, before I do this, this has to do with present values and future values.

98
00:11:02,000 --> 00:11:07,000
Now, this isn't all of the work, what I'm doing in the Excel.

99
00:11:07,000 --> 00:11:11,000
You'll probably want to put in a little bit of an enhancement or two,

100
00:11:11,000 --> 00:11:17,000
but remember, whenever you do an enhancement to a core sheet, you save it as a copy.

101
00:11:17,000 --> 00:11:24,000
You don't overwrite the original in case you made a mistake or something like that.

102
00:11:24,000 --> 00:11:26,000
You really don't want to.

103
00:11:26,000 --> 00:11:38,000
But the impetus of what I'm doing here is on annuities.

104
00:11:38,000 --> 00:11:44,000
Now, annuities come in different flavors, and some of this you learned in your accounting class, I'm sure,

105
00:11:44,000 --> 00:11:50,000
but you didn't learn it from an incredible professor like me, so I'm going to go back through it again.

106
00:11:50,000 --> 00:12:01,000
Let's start with annuities. There are level and non-level annuities.

107
00:12:01,000 --> 00:12:03,000
Now, first of all, an annuity.

108
00:12:03,000 --> 00:12:12,000
An annuity is just a cash flow, a periodic amount of money coming in or going out.

109
00:12:12,000 --> 00:12:14,000
That's all it is.

110
00:12:14,000 --> 00:12:25,000
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.

111
00:12:25,000 --> 00:12:31,000
Nothing big about that at all.

112
00:12:31,000 --> 00:12:40,000
Oh, as far as level annuities go, there is a formula you can use that will calculate them.

113
00:12:40,000 --> 00:12:46,000
A calculator like the TI-83 Plus I showed you last week, it can do it too.

114
00:12:46,000 --> 00:12:48,000
Excel can do it as well.

115
00:12:48,000 --> 00:12:57,000
Excel just happens to have a format so that you have a hard copy and you can change parameters within it.

116
00:12:57,000 --> 00:13:00,000
And so a level annuity.

117
00:13:00,000 --> 00:13:06,000
There, the formula isn't that hard, but we can do it much faster.

118
00:13:06,000 --> 00:13:09,000
Even with tables, it's a little faster.

119
00:13:09,000 --> 00:13:15,000
A non-level annuity is one where those cash flows kind of bobble around, up and down.

120
00:13:15,000 --> 00:13:21,000
They're not the same every time.

121
00:13:21,000 --> 00:13:24,000
There is one point I want to make about a level annuity.

122
00:13:24,000 --> 00:13:29,000
I showed you lumped sum present value, lumped sum future value last week.

123
00:13:29,000 --> 00:13:34,000
Technically, those are annuities with one cash flow.

124
00:13:34,000 --> 00:13:47,000
So we should be able to take an annuity formula in Excel and trick it into doing just a one, a lumped sum.

125
00:13:47,000 --> 00:13:49,000
And we'll do that in a little bit.

126
00:13:49,000 --> 00:13:52,000
But level annuity, payments on a loan.

127
00:13:52,000 --> 00:13:56,000
Money you put in the bank every month if it's the same amount.

128
00:13:56,000 --> 00:13:57,000
Those are level annuities.

129
00:13:57,000 --> 00:14:02,000
Non-level annuities, we don't really need to worry about those right now.

130
00:14:02,000 --> 00:14:05,000
They come into play a little later in the course.

131
00:14:05,000 --> 00:14:07,000
They're more of a pain in the butt.

132
00:14:07,000 --> 00:14:13,000
Because in level annuity, there's a formula you can take and just say,

133
00:14:13,000 --> 00:14:19,000
this is how much I'm putting in every month, every quarter or whatever.

134
00:14:19,000 --> 00:14:24,000
And the formula will spit back out the present value or the future value.

135
00:14:24,000 --> 00:14:28,000
Non-level annuities, they are a pain in the butt because you have to take,

136
00:14:28,000 --> 00:14:31,000
like if you're finding the present value of a non-level annuity,

137
00:14:31,000 --> 00:14:35,000
you have to take the present value of each one of the components,

138
00:14:35,000 --> 00:14:38,000
bring it back and then add them all up.

139
00:14:38,000 --> 00:14:44,000
After about three or four payments in a non-level annuity, it gets to be really tedious.

140
00:14:44,000 --> 00:14:46,000
You wouldn't even want to do it that way in Excel.

141
00:14:46,000 --> 00:14:48,000
Well, you can do it in Excel.

142
00:14:48,000 --> 00:14:51,000
It's pretty easy.

143
00:14:51,000 --> 00:14:53,000
But anyway, non-level annuities.

144
00:14:53,000 --> 00:14:56,000
Now, we see those later in the course.

145
00:14:56,000 --> 00:15:07,000
For example, if I were to find, looking to find the present value of the future projected cash flows of a corporation,

146
00:15:07,000 --> 00:15:12,000
well, the problem is those future projected cash flows aren't going to be all the same.

147
00:15:12,000 --> 00:15:16,000
They're going to vary from one year to the next as you're clocking your way out.

148
00:15:16,000 --> 00:15:19,000
And then you've got to telescope those back,

149
00:15:19,000 --> 00:15:22,000
and that would give you an intrinsic value for the company.

150
00:15:22,000 --> 00:15:28,000
So non-level annuities can be a little realistic, but they're also kind of a pain.

151
00:15:28,000 --> 00:15:33,000
Also, if you were to consider the value of a stock, the intrinsic value of a stock,

152
00:15:33,000 --> 00:15:40,000
as the present value of its future expected dividends, that would be a non-level annuity.

153
00:15:40,000 --> 00:15:45,000
And that could be, so you'd have to find the present value of each one of the dividends

154
00:15:45,000 --> 00:15:51,000
for enough years to get a good approximation in present value

155
00:15:51,000 --> 00:15:55,000
and add all those present values of those dividends up.

156
00:15:55,000 --> 00:15:58,000
Non-level annuities can be a pain.

157
00:15:58,000 --> 00:16:04,000
That's where programs like Excel, forgive the no pun intended,

158
00:16:04,000 --> 00:16:07,000
Excel excels at things like that.

159
00:16:07,000 --> 00:16:11,000
It'll be happy to crank those out in a split second.

160
00:16:11,000 --> 00:16:14,000
But anyway, level and non-level annuities.

161
00:16:14,000 --> 00:16:22,000
Now, we have present values of annuities, and we have future values of annuities.

162
00:16:22,000 --> 00:16:29,000
One thing that we need to get out on the table right away.

163
00:16:29,000 --> 00:16:38,000
There are also, you can categorize annuities as ordinary annuities,

164
00:16:38,000 --> 00:16:48,000
and there are also annuity due.

165
00:16:48,000 --> 00:16:55,000
An ordinary annuity is an annuity where the payments occur at the end of each period.

166
00:16:55,000 --> 00:17:04,000
End of each period.

167
00:17:04,000 --> 00:17:14,000
An annuity due, the payments occur at the beginning of each period.

168
00:17:14,000 --> 00:17:18,000
So you get a loan on a car.

169
00:17:18,000 --> 00:17:23,000
Well, probably your first payment is going to be due,

170
00:17:23,000 --> 00:17:28,000
you're going to have to pay it in a month from the day you take out the loan.

171
00:17:28,000 --> 00:17:35,000
And those, that annuity, payments on a loan, are generally ordinary annuities.

172
00:17:35,000 --> 00:17:42,000
Generally ordinary annuities.

173
00:17:42,000 --> 00:17:45,000
Annuity due.

174
00:17:45,000 --> 00:17:47,000
At first it might be a little bit difficult.

175
00:17:47,000 --> 00:17:53,000
Well, when would you start making payments right away instead of a month out?

176
00:17:53,000 --> 00:17:55,000
Well, those happen.

177
00:17:55,000 --> 00:18:04,000
For example, you sir.

178
00:18:04,000 --> 00:18:08,000
You have a significant other.

179
00:18:08,000 --> 00:18:19,000
Now your significant other comes to you one evening and says three words to you that change your life.

180
00:18:19,000 --> 00:18:25,000
No, it's not I love you, it's I am pregnant.

181
00:18:25,000 --> 00:18:28,000
It happened to me.

182
00:18:28,000 --> 00:18:31,000
I tried to kill myself.

183
00:18:31,000 --> 00:18:33,000
Didn't work.

184
00:18:33,000 --> 00:18:38,000
Damn cheap razor blades.

185
00:18:38,000 --> 00:18:41,000
Well, the day of birth happens.

186
00:18:41,000 --> 00:18:42,000
It's exhausting.

187
00:18:42,000 --> 00:18:45,000
I've been at five in my life and I'm tired.

188
00:18:45,000 --> 00:18:48,000
I mean, it wears me out.

189
00:18:48,000 --> 00:18:51,000
But man, there's that rush afterwards.

190
00:18:51,000 --> 00:18:57,000
You leave the hospital and you say, by golly, I'm going to make it good for this kid.

191
00:18:57,000 --> 00:19:03,000
I'm going to put money in the bank on this kid's birthday every year.

192
00:19:03,000 --> 00:19:09,000
So you go right to the bank and you deposit $2,000.

193
00:19:09,000 --> 00:19:15,000
And every year on the kid's birthday you're going to put in $2,000.

194
00:19:15,000 --> 00:19:22,000
And one last $2,000 when the kid has his 17th birthday.

195
00:19:22,000 --> 00:19:24,000
And then you'll let it ride.

196
00:19:24,000 --> 00:19:34,000
And then on the 18th birthday you'll take the money out and give it to the kid as a birthday gift.

197
00:19:34,000 --> 00:19:37,000
That's an annuity due.

198
00:19:37,000 --> 00:19:40,000
That's an annuity due.

199
00:19:40,000 --> 00:19:52,000
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.

200
00:19:52,000 --> 00:19:54,000
Now, there are other examples of annuity due.

201
00:19:54,000 --> 00:20:00,000
As a matter of fact, just in my last class I was giving examples of annuity due.

202
00:20:00,000 --> 00:20:02,000
Someone brought up rent.

203
00:20:02,000 --> 00:20:09,000
When you sign a lease, you usually have to pay your first month's rent along with a deposit right up front.

204
00:20:09,000 --> 00:20:19,000
So that is an annuity due because payments occur at the beginning of each payment cycle.

205
00:20:19,000 --> 00:20:24,000
Another example is wealth management, financial planning.

206
00:20:24,000 --> 00:20:33,000
When you sell a package, you go to an upper middle class family and they explain to you,

207
00:20:33,000 --> 00:20:43,000
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.

208
00:20:43,000 --> 00:20:55,000
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.

209
00:20:55,000 --> 00:20:56,000
Okay.

210
00:20:56,000 --> 00:21:02,000
What you would do is you would have out here the future value of each of these components.

211
00:21:02,000 --> 00:21:11,000
Then you'd figure out a stream of payments that would achieve each of those goals at the time it happened.

212
00:21:11,000 --> 00:21:13,000
That's financial planning.

213
00:21:13,000 --> 00:21:14,000
That's how you do it.

214
00:21:14,000 --> 00:21:17,000
And then you'd go to the family, here's the plan.

215
00:21:17,000 --> 00:21:22,000
Now, please write me a check for the sum of those payments.

216
00:21:22,000 --> 00:21:31,000
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,

217
00:21:31,000 --> 00:21:35,000
the goals was realized, that would be the last payment.

218
00:21:35,000 --> 00:21:42,000
So that would be just a cluster of annuity due, annuities due.

219
00:21:42,000 --> 00:21:44,000
Same concept.

220
00:21:44,000 --> 00:21:49,000
So annuities due are actually out there and they're pretty normal.

221
00:21:49,000 --> 00:21:54,000
We just don't think about a lot of things being annuities due that really are.

222
00:21:54,000 --> 00:22:13,000
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,

223
00:22:13,000 --> 00:22:27,000
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,

224
00:22:27,000 --> 00:22:40,000
let's say, with an APR of 3.59% and each of those payments that you get is going to be $250.

225
00:22:40,000 --> 00:22:47,000
Now, there wouldn't be any future value kicker in this at all.

226
00:22:47,000 --> 00:22:50,000
But there's your present value.

227
00:22:50,000 --> 00:22:52,000
That's what that is worth to you now.

228
00:22:52,000 --> 00:23:04,000
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

229
00:23:04,000 --> 00:23:09,000
and just getting $8,371 right now.

230
00:23:09,000 --> 00:23:11,000
That's what it's telling you.

231
00:23:11,000 --> 00:23:19,000
You would see no difference between those two.

232
00:23:19,000 --> 00:23:22,000
Now, the effective rate.

233
00:23:22,000 --> 00:23:26,000
You've got a formula in the book, but right here it's calculated for you.

234
00:23:26,000 --> 00:23:36,000
An effective rate is the rate that would be the same as that 3.59 compounded quarterly.

235
00:23:36,000 --> 00:23:43,000
3.64% would be the same rate compounded annually.

236
00:23:43,000 --> 00:23:44,000
That's what an effective rate does.

237
00:23:44,000 --> 00:23:46,000
It turns it into an annual.

238
00:23:46,000 --> 00:23:57,000
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.

239
00:23:57,000 --> 00:24:01,000
It says it brings them all down to an annualized rate.

240
00:24:01,000 --> 00:24:04,000
That's what an effective rate does.

241
00:24:04,000 --> 00:24:06,000
Pretty much.

242
00:24:06,000 --> 00:24:11,000
Okay, now let's go over here to that future value.

243
00:24:11,000 --> 00:24:14,000
You, sir, we're going to put in 18 payments.

244
00:24:14,000 --> 00:24:23,000
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.

245
00:24:23,000 --> 00:24:25,000
I even trip up on that sometimes.

246
00:24:25,000 --> 00:24:29,000
I start counting on my fingers, and you'll see that again in another thing.

247
00:24:29,000 --> 00:24:36,000
Now, you're going to do this once a year, so that will automatically create the discount rate.

248
00:24:36,000 --> 00:24:43,000
If you're putting them in on a periodic basis, the period will drive the compoundings per year.

249
00:24:43,000 --> 00:24:55,000
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.

250
00:24:55,000 --> 00:24:56,000
Now, I'm going to show you something.

251
00:24:56,000 --> 00:24:59,000
You see that 0 over there?

252
00:24:59,000 --> 00:25:01,000
That's an ordinary annuity.

253
00:25:01,000 --> 00:25:04,000
The 1 is an annuity due.

254
00:25:04,000 --> 00:25:06,000
Don't forget that.

255
00:25:06,000 --> 00:25:11,000
Usually you won't have to change these, but here maybe.

256
00:25:11,000 --> 00:25:20,000
Okay, so the kid's going to accumulate $51,718.72.

257
00:25:20,000 --> 00:25:27,000
That's what will be in there on the kid's 18th birthday.

258
00:25:27,000 --> 00:25:30,000
You follow?

259
00:25:30,000 --> 00:25:34,000
Once you get the hang of it, just practice.

260
00:25:34,000 --> 00:25:42,000
I used to actually, last year I even had everyone do the formulas and the calculator approach just so they could appreciate how

261
00:25:42,000 --> 00:25:50,000
blindingly fast and easy it is in Excel once you get the hang of the Excel formulas.

262
00:25:50,000 --> 00:25:58,000
Now, one thing I want you to notice, see the effective rate in the future value problem was the same as the APR?

263
00:25:58,000 --> 00:26:02,000
That's because there was only one compounding per year.

264
00:26:02,000 --> 00:26:07,000
So the annualized rate was the same because there was one compounding per year.

265
00:26:07,000 --> 00:26:22,000
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.

266
00:26:22,000 --> 00:26:25,000
And notice how you've got a little more.

267
00:26:25,000 --> 00:26:35,000
Because you're putting them in faster, some money is earning interest a little faster.

268
00:26:35,000 --> 00:26:41,000
But that's future value and present value.

269
00:26:41,000 --> 00:26:47,000
Now, let's take it to loan payments, which are basically annuities.

270
00:26:47,000 --> 00:26:51,000
Okay, you, madam, decide you're going to buy a car.

271
00:26:51,000 --> 00:26:54,000
It's an ugly car, but it's your car.

272
00:26:54,000 --> 00:27:03,000
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

273
00:27:03,000 --> 00:27:08,000
have to get a loan for $25,000.

274
00:27:08,000 --> 00:27:15,000
Now, here's one where I have to give you a caution with it.

275
00:27:15,000 --> 00:27:22,000
With annuity, with everything in finance, the calculators excel.

276
00:27:22,000 --> 00:27:26,000
They like present values as negatives.

277
00:27:26,000 --> 00:27:33,000
Now, I can fix some things, but here you have to put in the present value.

278
00:27:33,000 --> 00:27:35,000
So you have to put it in as a negative.

279
00:27:35,000 --> 00:27:40,000
So I put in negative $25,000.

280
00:27:40,000 --> 00:27:48,000
Now, up here, we're going to make payments for, yeah, let's do five years.

281
00:27:48,000 --> 00:27:52,000
And you'll make 12 payments a year, monthly payments.

282
00:27:52,000 --> 00:27:58,000
That's normally how any loan works, private, personal loans.

283
00:27:58,000 --> 00:28:04,000
And let's say your APR is 6.39%.

284
00:28:04,000 --> 00:28:06,000
There's no kicker.

285
00:28:06,000 --> 00:28:09,000
You usually hardly, that's why that one's in orange.

286
00:28:09,000 --> 00:28:11,000
Orange means don't touch it.

287
00:28:11,000 --> 00:28:14,000
Leave it alone.

288
00:28:14,000 --> 00:28:17,000
And so there's your payments every month.

289
00:28:17,000 --> 00:28:20,000
It just cranks it out.

290
00:28:20,000 --> 00:28:22,000
That doesn't look like a very nice blue.

291
00:28:22,000 --> 00:28:24,000
Let me get that blue.

292
00:28:24,000 --> 00:28:28,000
It's supposed to, blues are supposed to be what calculates.

293
00:28:28,000 --> 00:28:30,000
Yeah, there we go.

294
00:28:30,000 --> 00:28:34,000
I'll re-upload this with that fix.

295
00:28:34,000 --> 00:28:37,000
Okay, there you go.

296
00:28:37,000 --> 00:28:42,000
Your payments are $478.87 a month.

297
00:28:42,000 --> 00:28:45,000
That's all there was to it.

298
00:28:45,000 --> 00:28:50,000
All you have to do, and this is the same principle I taught for 40 years in math,

299
00:28:50,000 --> 00:28:57,000
in arithmetic and algebra classes, take the numbers, get the word problem,

300
00:28:57,000 --> 00:28:59,000
and I'll give you word problems.

301
00:28:59,000 --> 00:29:02,000
Get the numbers out and throw away the words.

302
00:29:02,000 --> 00:29:05,000
Put the numbers in the boxes where they want.

303
00:29:05,000 --> 00:29:10,000
If it's a formula or in Excel, just put the numbers where they belong.

304
00:29:10,000 --> 00:29:12,000
There are no words.

305
00:29:12,000 --> 00:29:14,000
And the answer will come out.

306
00:29:14,000 --> 00:29:17,000
And then you can ask yourself, well, does this answer make sense?

307
00:29:17,000 --> 00:29:24,000
Yeah, that's about what a car payment on a new car would run these days.

308
00:29:24,000 --> 00:29:26,000
Yeah.

309
00:29:26,000 --> 00:29:35,000
I was looking at a new car, and they said, we could get you a deal for $39,000.

310
00:29:35,000 --> 00:29:41,000
And I'm using, it was 6.39% APR, five-year loan.

311
00:29:41,000 --> 00:29:45,000
Well, they can kiss my butt. That's too much.

312
00:29:45,000 --> 00:29:49,000
You see, this will tell you the cold number that you will be paying out the door.

313
00:29:49,000 --> 00:29:55,000
If that's what you borrowed, that's what your payment's going to be to the loan company.

314
00:29:55,000 --> 00:30:02,000
Notice, by the way, see the 6.58%, that's your effective rate.

315
00:30:02,000 --> 00:30:10,000
Do you notice that the effective rate has nothing to do with the amount of money you borrow?

316
00:30:10,000 --> 00:30:15,000
Notice it's the effective rate. I could make this a six-year loan.

317
00:30:15,000 --> 00:30:23,000
My payments come down a little. See how the effective rate, all that matters is the number of compoundings per year

318
00:30:23,000 --> 00:30:27,000
and the interest, the APR. That's all that matters.

319
00:30:27,000 --> 00:30:33,000
That's all. So that's something that I sometimes will ask as a multiple choice question,

320
00:30:33,000 --> 00:30:39,000
which of the following changes the effective rate?

321
00:30:39,000 --> 00:30:49,000
The amount of the loan, the number of payments per year, the number of years, or the original APR?

322
00:30:49,000 --> 00:30:59,000
Well, the number of payments a year affects it, and the amount of money, rather, the APR affects it.

323
00:30:59,000 --> 00:31:02,000
But none of the others do.

324
00:31:02,000 --> 00:31:09,000
That is the extent of doing, that's most of Chapter 5.

325
00:31:09,000 --> 00:31:18,000
Oh, by the way, let me show you. Remember last time I did lump sum problems, like...

326
00:31:18,000 --> 00:31:36,000
You will get $250,000 in 18 years.

327
00:31:36,000 --> 00:32:05,000
If you discount cash flow at, let's say, 5.25% compounded semi-annually,

328
00:32:05,000 --> 00:32:22,000
what is that worth now?

329
00:32:22,000 --> 00:32:26,000
That's a lump sum problem. It's just one pile of money.

330
00:32:26,000 --> 00:32:34,000
Well, this is for annuities. But remember I told you that a lump sum is nothing but an annuity

331
00:32:34,000 --> 00:32:40,000
with just one payment, one amount. So here's how you do it.

332
00:32:40,000 --> 00:32:45,000
Go to the present value, and we're going to just pull a trick here.

333
00:32:45,000 --> 00:32:49,000
Years, 18.

334
00:32:49,000 --> 00:33:09,000
Periods per year, it's semi-annual, so that'd be two. Your APR is 5.25. I couldn't see it. Wow.

335
00:33:09,000 --> 00:33:18,000
Now, there are no payments. So this is one where the payments are zero, but there is a future value,

336
00:33:18,000 --> 00:33:29,000
and here's where you violate the orange block. $250,000.

337
00:33:29,000 --> 00:33:35,000
There you go.

338
00:33:35,000 --> 00:33:43,000
Because you've got just a future amount of money, so we're just going to put that in as the FV,

339
00:33:43,000 --> 00:33:50,000
and everything else, no payments. You're not getting payments along the way. You're just getting a plop of money at the end.

340
00:33:50,000 --> 00:33:55,000
And so there is your present value of the lump sum.

341
00:33:55,000 --> 00:34:05,000
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.

342
00:34:05,000 --> 00:34:14,000
I'm going to say, Madam, find $10,000. Find.

343
00:34:14,000 --> 00:34:20,000
And so you say, okay, what if I put that in the bank and I just leave it there?

344
00:34:20,000 --> 00:34:30,000
What if I put that in the bank now and just leave it there for 10 years?

345
00:34:30,000 --> 00:34:37,000
And it's the same, it's compounded, let's say, the account compound semi-annually, just two.

346
00:34:37,000 --> 00:34:46,000
And you can put it into an account that will pay you, let's say, 4.89% compound semi-annually.

347
00:34:46,000 --> 00:34:59,000
Now, there are no payments in this one, zero, but you have a present value in this one of

348
00:34:59,000 --> 00:35:10,000
negative $10,000. Remember, you always have to put in present values as negatives.

349
00:35:10,000 --> 00:35:14,000
What the hell happened there?

350
00:35:14,000 --> 00:35:20,000
Why is APR 1010?

351
00:35:20,000 --> 00:35:27,000
Why is that giving me a wrong answer there? I must have accidentally deleted something. Yeah?

352
00:35:27,000 --> 00:35:36,000
No, I don't think that one. Oh, no, boy, that really helped.

353
00:35:36,000 --> 00:35:41,000
So don't do that. I'll figure out what's wrong here. I must have accidentally changed the formula.

354
00:35:41,000 --> 00:35:48,000
I started fiddling around with this. Years, 10, periods, APR, payments are zero.

355
00:35:48,000 --> 00:35:56,000
The future value is, we don't know what the future value is, but we do know that the present value is

356
00:35:56,000 --> 00:36:06,000
$10,000, type one. I don't get what, I must have accidentally touched a formula.

357
00:36:06,000 --> 00:36:17,000
It'll work though. I'll fix it. Now, the last thing though is that you might want to ask about a balance.

358
00:36:17,000 --> 00:36:27,000
How much is in this payments here? How much do you still owe after a certain number of years?

359
00:36:27,000 --> 00:36:35,000
That's a balance question. Now, I leave that one for you to work out. It's not too difficult to do it,

360
00:36:35,000 --> 00:36:46,000
but there is an Excel function that will give you the balance on these, you still use these numbers,

361
00:36:46,000 --> 00:36:56,000
except that you tell it how many months are left in the loan. So I leave that to you to figure that one out

362
00:36:56,000 --> 00:37:03,000
and see if you can get the answer, the result. I will show it to you on Wednesday, but the balance,

363
00:37:03,000 --> 00:37:11,000
and you can even find the rate. If you know the payments, the amount, and how long the loan is,

364
00:37:11,000 --> 00:37:22,000
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.

365
00:37:22,000 --> 00:37:29,000
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

366
00:37:29,000 --> 00:37:37,000
almost all problems in your homework, plus it'll get you through anything that I could possibly ask you.

367
00:37:37,000 --> 00:37:46,000
Now, the typical problem that I have here, let me delete, get this out here. I'm going to, don't save this,

368
00:37:46,000 --> 00:37:56,000
I'm going to pull up the original one that I downloaded a few minutes ago, downloads. Okay, there you go.

369
00:37:56,000 --> 00:38:09,000
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

370
00:38:09,000 --> 00:38:21,000
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

371
00:38:21,000 --> 00:38:31,000
and the effective rate. That's what I would ask you on a quiz and I would definitely ask this on the midterm.

372
00:38:31,000 --> 00:38:41,000
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.

373
00:38:41,000 --> 00:38:51,000
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.

374
00:38:51,000 --> 00:38:59,000
First of all, ABS just means absolute, because Excel always gives present values as negatives.

375
00:38:59,000 --> 00:39:07,000
That's just what Excel does. So I make it absolute so it is a number that looks right to you.

376
00:39:07,000 --> 00:39:18,000
Now, I want you to notice, first of all, Excel has a nastiness in it. PV is actually the present value of an annuity.

377
00:39:18,000 --> 00:39:27,000
It's not just a plain present value. It's the present value of an annuity. So that's just a warning to you.

378
00:39:27,000 --> 00:39:35,000
It's been something that we've fussed at Microsoft a long time ago. They wouldn't change it to PVA,

379
00:39:35,000 --> 00:39:45,000
which would make it a little more logical. But anyway, now the other thing. You see the formula in Excel.

380
00:39:45,000 --> 00:39:56,000
You don't ever want to have numbers in a formula. You see how everything there is a call from a reference cell.

381
00:39:56,000 --> 00:40:06,000
That way you can change the reference cells and the formula will immediately give you the right new answer.

382
00:40:06,000 --> 00:40:18,000
Again, notice that I do that across the board with these calculations. Those are all, even the zero is B6, or rather B8 there.

383
00:40:18,000 --> 00:40:28,000
It always wants Excel thrives on references. So for example, with this loan, I can change, well you saw me do it.

384
00:40:28,000 --> 00:40:37,000
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.

385
00:40:37,000 --> 00:40:54,000
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%.

386
00:40:54,000 --> 00:41:17,000
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.

387
00:41:17,000 --> 00:41:27,000
That's because the formula is being driven by these numbers up here.

388
00:41:27,000 --> 00:41:39,000
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.

389
00:41:39,000 --> 00:41:52,000
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.

390
00:41:52,000 --> 00:42:07,000
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.

391
00:42:07,000 --> 00:42:19,000
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.

392
00:42:19,000 --> 00:42:29,000
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.

393
00:42:29,000 --> 00:42:42,000
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.

394
00:42:42,000 --> 00:42:55,000
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.

395
00:42:55,000 --> 00:43:10,000
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.

396
00:43:10,000 --> 00:43:19,000
This is common all across the business world now. That is pretty much the whole story.

397
00:43:19,000 --> 00:43:26,000
Like I said, I've left one here for you just to practice and I'll show you on Wednesday very quickly.

398
00:43:26,000 --> 00:43:33,000
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.

399
00:43:33,000 --> 00:43:40,000
But I'm leaving that for you to give a stab at after I've saved this sheet for you again.

400
00:43:40,000 --> 00:43:50,000
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.