1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
24
Ordinary annuity of $100 per year for three years.
Time period 0 1 2
27
28
29
30
31
33
Interest rate 0.1 These are the basic inputs, in blue.
Cash flow 100
38
39
40
41
42
45
A B C D E F G H I J K
10/28/2015
Situation
FUTURE VALUE
$100 lump sum at the end of year 2.
I%
Time period 0 1 2
FV at year end 100
Uneven cash flow stream.
I%
Time period 0 1 2 3
FV at year end
-50 100 75 50
Time period 0 1 2 3
FV at year end
100 110.00 121 133.10
Chapter 4. Mini Case
b. (1.) What’s the future value of an initial $100 after 3 years if it is invested in an account paying 10%
Assume that you are nearing graduation and have applied for a job with a local bank. As part of the
bank’s evaluation process, you have been asked to take an examination that covers several financial
analysis techniques. The first section of the test addresses discounted cash flow analysis. See how
you would do by answering the following questions.
a. Draw time lines for (1) a $100 lump sum cash flow at the end of Year 2, (2) an ordinary annuity of
$100 per year for 3 years, and (3) an uneven cash flow stream of -$50, $100, $75, and $50 at the end of
Years 0 through 3.
Note: This problem was solved using the formula, FVn = PV (1+I)N. However, there are a number of
First, you must select the Function wizard icon found in the toolbar at the top of the screen, which
57
58
59
60
61
62
63
64
69
70
71
72
73
80
81
82
83
84
91
92
93
94
101
102
103
104
105
106
113
114
115
116
117
A B C D E F G H I J K
After selecting the “FV” function from the “Financial” category, we will be using the following dialog
After selecting the category for Financial functions, scroll down until you can selet the FV function, as
show below. Alternatively, select the menu Formulas, then then select Financial, then pick FV.
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
A B C D E F G H I J K
FV = $133.10
Period (N) 0% 5% 10% 15%
01.0000 1.0000 1.0000 1.0000
21.0000 1.1025 1.2100 1.3225
41.0000 1.2155 1.4641 1.7490
61.0000 1.3401 1.7716 2.3131
81.0000 1.4775 2.1436 3.0590
10 1.0000 1.6289 2.5937 4.0456
Notice that we entered a value instead of a cell reference as the input for the problem for instructional
purposes. It’s really better to enter cell values so that your spreadsheet can automatically reflect any
changes to the input data. This is one of the features that makes the spreadsheet such a valuable tool.
With a spreadsheet, calculating FVIF’s is a simple operation, and we can use it to graph the
relationship between future value, growth, interest rates, and time. A similar table can be found in the
textbook, along with a corresponding graph.
Future Value Interest Factors
Using the function wizard yields the following result:
3 of 30
140
141
142
143
144
145
146
147
153
154
155
156
157
158
164
165
166
167
168
169
175
176
177
178
179
180
186
187
188
189
190
191
197
198
199
200
201
202
A B C D E F G H I J K
PRESENT VALUE (PV)
PROBLEM
PV $75.13 82.64 90.91 100.00
This problem can also be solved using the function wizard using a procedure similar to that for the FV
function. Begin by putting the pointer on the cell in which you want to display the result. Then, after
selecting the “PV” function from the “Paste Function” box, the input data for the problem must be
entered. Then click OK to get the result, $75.13.
Relationships among Future Value, Growth, Interest Rates, and Time
Simply put, the present value (PV) is the value today of some future cash flow (or series of cash flows).
$0.00
$1.00
$5.00
0246810 12
Future Value of $1
Relationships among Future Value, Growth, Interest
Rate, and Time
204
A B C D E F G H I J K
PV = $75.13
5 of 30
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
A B C D E F G H I J K
Finding Time to Double
I = 0.2
Time period 0 1 2 ?
Present Value $1.00 2.00
3.8 Use the function NPER, as shown below.
Finding N, the number of
periods
c. We sometimes need to find how long it will take a sum of money (or anything else) to grow to some
specified amount. For example, if a company’s sales are growing at a rate of 20% per year, how long
will it take sales to double?
6 of 30
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
279
280
286
287
288
289
FV 2
A B C D E F G H I J K
SOLVING FOR I
PROBLEM
N3
PV -1
FV 2
Once again, Excel has a special function for this calculation. We suggest using either a financial
calculator or the function wizard to solve this type of problem, because of its complexity. The
procedure can be carried out using the function wizard, by selecting the “Rate” function from the list of
financial functions in the “Paste Function” dialog box. Upon entering the time, present value, and
future value, the interest rate can be found. Note that you can either type the data in or else activate
the menu slot and then click on the appropriate cell.
d. If you want an investment to double in three years, what interest rate must it earn?
295
296
297
298
299
300
301
302
303
304
305
306
311
312
313
314
315
322
323
324
325
326
327
333
334
335
336
337
338
339
344
345
346
347
348
349
350
351
A B C D E F G H I J K
FUTURE VALUE OF AN ANNUITY
N3
I0.1
PMT 100
Time period 0 1 2 3
CFt0100 100 100 Annuity‘s FV:
N3
I0.1
PMT 100
CFt0100 100 100 Annuity PV
PV30 90.91 82.64 75.13 = $248.69
As explained below, one way to solve this problem is to find the future value of each of the annuity
f. (1.) What is the future value of a 3-year ordinary annuity of $100 if the appropriate interest rate is
10%?
now we insert the annuity payment ($100 in this case). First, we access the “FV” function box from the
list of financial functions. Then, we input our new data. A key thing to watch is the “Type” input box.
Previously, we left this box alone. An “0” or no entry in the box indicates an ordinary annuity, and a
“1” indicates an annuity due. Though we can leave the box blank, it is a good habit to enter a “0” in
the field.
8 of 30
353
354
355
356
357
358
359
360
366
367
368
369
370
371
375
376
377
378
379
380
381
382
383
384
387
388
389
390
391
392
394
396
397
399
400
401
402
403
404
410
411
412
413
414
415
A B C D E F G H I J K
Time period 0 1 2 3
CFt100 100 100 0 Annuity FV
FV3133.1 121 110 0 = $364.10
The procedure for solving this problems follows the previous example with one notable exception. Since, the
payments occur at the beginning of each year, the first annuity payment occurs in time period 0, and the last occurs
in time period 2.
Or, you could use the function wizard for this ordinary annuity.
f. (3.) What would the future and present values be if the annuity were an annuity due?
the “Type” field.
417
418
419
A B C D E F G H I J K
FV = $364.10
10 of 30