1
2
3
4
5
6
7
8
9
10
11
12
13
14
18
24
Time period 0 1 2
Time period 0 1 2
26
27
28
29
30
31
32
33
34
35
36
37
39
40
ways the problem could have used Excel’s “Wizard Function”.
48
49
50
51
52
53
54
55
56
A B C D E F G H I J K L M N O P Q R S
11/20/2018
Situation
Uneven cash flow stream.
I%
Time period 0 1 2 3
FV at year end -50 100 75 50
Interest rate 0.1 These are the basic inputs, in blue.
Cash flow 100
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%
annual interest?
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.
1 of 11
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
94
95
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
136
137
138
139
A B C D E F G H I J K L M N O P Q R S
FV = $133.10
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:
After selecting the “FV” function from the “Financial” category, we will be using the following dialog
box to input our data.
After selecting the category for Financial functions, scroll down until you can selet the FV function, as
2 of 11
140
141
142
143
144
145
146
147
148
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
202
203
204
A B C D E F G H I J K L M N O P Q R S
PRESENT VALUE (PV)
PROBLEM
Interest rate 10%
Cash flow 100
Number of Years Discounted Back
Time period 0 1 2 3
PV $75.13 82.64 90.91 100.00
PV = $75.13
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).
b. (2) What is the present value of $100 to be received in 3 years if the appropriate interest rate is
10%?
$4.00
$5.00
Relationships among Future Value, Growth, Interest
Rate, and Time
3 of 11
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
240
241
A B C D E F G H I J K L M N O P Q R S
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.
will it take sales to double?
Finding N, the number of
periods
4 of 11
242
243
244
245
246
247
248
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
286
287
288
289
FV 2
294
A B C D E F G H I J K L M N O P Q R S
SOLVING FOR I
PROBLEM
N3
PV -1
FV 2
I = 25.99%
d. If you want an investment to double in three years, what interest rate must it earn?
5 of 11
295
296
297
298
299
300
301
302
303
304
305
306
307
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
344
347
349
A B C D E F G H I J K L M N O P Q R S
FUTURE VALUE OF AN ANNUITY
N3
I0.1
PMT 100
Time period 0 1 2 3
CFt0100 100 100 Annuity’s FV:
FV30121 110 100 Σ= $331.00
FV = $331.00
PRESENT VALUE OF AN ANNUITY
f. (1.) What is the future value of a 3-year ordinary annuity of $100 if the appropriate interest rate is
10%?
As explained below, one way to solve this problem is to find the future value of each of the annuity
6 of 11
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
384
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
417
418
FV = $364.10
A B C D E F G H I J K L M N O P Q R S
PV = $248.69
Time period 0 1 2 3
CFt100 100 100 0 Annuity FV
FV3133.1 121 110 0 = $364.10
Additionally, using the function wizard for this problem is exactly like above, but we enter a “1” instead of a “0” into
the “Type” field.
Or, you could use the function wizard for this ordinary annuity.
7 of 11
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
455
456
PV = $273.55
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
514
515
516
517
518
519
520
521
522
523
524
525
526
A B C D E F G H I J K L M N O P Q R S
N3
I0.1
PMT 100
Time period 0 1 2 3
CFt100 100 100 0 Annuity PV
PV3100.00 90.91 82.64 0.00 = $273.55
NPV = = Σ of PVs = $530.09
I0.1
N
CFNPV0
000.00
1100 90.91
2300 247.93
3300 225.39
4-50 -34.15
$530.09
PV = $530.09
Inputs
INOM (quarterly) 0.1 This is the rate stated in contracts.
m=periods/yr
2This is the number of periods per year, m.
The periodic is associated with the number of compounding periods per year. M = 4 quarterly, 12 for monthly, and
360 or 365 for annual compounding.
This problem could also be set up in a column format; it is a matter of personal preference as to which
As we show above, the first way to solve for the present value of this uneven cash flow stream is to
use the time line to find the present value of each of the cash flows in the periods in which they occur,
then sum all the present values. This procedure will yield the correct present value.
To find the present value of the annuity due, this problem is solved just like the previous problem,
except that the payments occur in periods 0 through 2.
PV of CF stream =
h. (1.) Identify (a) the stated, or quoted, or nominal rate (iNom) and (b) the periodic rate (iPER).
Using the function wizard, we follow the same procedure as above, except remember to enter a “1” to
tell Excel that in this problem the payments occur at the beginning of the periods.
8 of 11
527
528
529
530
531
532
542
545
Larger, because interest is earned on interest.
compounding periods.
h. (2.) Will the future value be larger or smaller if we compound an initial amount more often than annually, for
example, every 6 months (semiannually ), holding the stated interest rate constant? Why?
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
573
574
575
576
N (years x 4) 12
I (I per year/4) 0.03 FV = $142.58
PV 100
N (years x 12) 36
I (I per year/12) 0.01 FV = $143.08
PV 100
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
624
625
626
627
628
629
630
631
632
A B C D E F G H I J K L M N O P Q R S
IPER = inom/m
IPER = 10% / 2
IPER = 5%
EFF% = 10.25%
SEMIANNUAL AND OTHER COMPOUNDING PERIODS
h. (3.) What is the future value of $100 after 5 years under 12% annual compounding?
N 3
I0.12 FV = $140.49
PV 100
What is the FV with semiannual compounding?
N (years x 2) 6
I (I per year/2) 0.06 FV = $141.85
PV 100
What is the FV with quarterly compounding?
What is the FV with daily compounding?
N (years x 365) 1095
I (I per year/12) 0.00032877 FV = $143.32
PV 100
SETUP FOR A 30 YEAR MORTGAGE. GRAPH BELOW. THE LONGER THE
MATURITY, THE SMALLER THE INITIAL PRINCIPAL PAYMENT.
N3PMT = $402.11 Total pmts Tot. int. paid Tot. prin. pd N30 PMT = $106.08
I0.1 $1,206 $206 $1,000 I 0.1
PV 1000 PV 1000
NBeg. Amt. Payment Interest Principal End. Amt.
1 $1,000.00 $106.08 $100.00 $6.08 $993.92
2 $993.92 $106.08 $99.39 $6.69 $987.23
3 $987.23 $106.08 $98.72 $7.36 $979.88
NBeg. Amt. Payment Interest Principal End. Amt. 4 $979.88 $106.08 $97.99 $8.09 $971.79
1 $1,000.00 $402.11 $100.00 $302.11 $697.89 5 $971.79 $106.08 $97.18 $8.90 $962.89
2 $697.89 $402.11 $69.79 $332.33 $365.56 6 $962.89 $106.08 $96.29 $9.79 $953.09
3 $365.56 $402.11 $36.56 $365.56 $0.00 7 $953.09 $106.08 $95.31 $10.77 $942.33
8 $942.33 $106.08 $94.23 $11.85 $930.48
9 $930.48 $106.08 $93.05 $13.03 $917.45
10 $917.45 $106.08 $91.74 $14.33 $903.11
Note: See Columns M 11 $903.11 $106.08 $90.31 $15.77 $887.34
through R for a 30 year 12 $887.34 $106.08 $88.73 $17.34 $870.00
mortgage example. 13 $870.00 $106.08 $87.00 $19.08 $850.92
27 $336.26 $106.08 $33.63 $72.45 $263.80
28 $263.80 $106.08 $26.38 $79.70 $184.10
29 $184.10 $106.08 $18.41 $87.67 $96.44
30 $96.44 $106.08 $9.64 $96.44 $0.00
$3,182.38 $2,182.38 $1,000.00
0 1 2 3 4 5273
100
The periodic is associated with the number of compounding periods per year. M = 4 quarterly, 12 for monthly, and
360 or 365 for annual compounding.
k. On January 1, you deposit $100 in an account that pays a nominal (or quoted) interest rate of 11.33463%, with
interest added (compounded) daily. How much will you have in your account on October 1, or 9 months later? (273
days)
j. (1.) What would the required payment be on a $1,000 loan that is to be repaid in three equal installments at the
end of each of the next three years if the interest rate is 10%?
I. Will the effective annual rate ever be equal to the nominal (quoted) rate? Only if the compounding period is equal
to 1 year.
j. (2.) What is the annual interest expense for the borrower, and the annual interest income for the lender, during
Year 2?
Now, construct an amortization table for the loan described above.
$350.00
$400.00
$450.00
Payment
Payment Distribution
$125
9 of 11
633
634
635
636
637
638
639
640
641
642
643
648
655
657
658
663
664
665
A B C D E F G H I J K L M N O P Q R S
I0.00031054
N273
FV $108.85
Annual rate = 10%
l. (1.) What is the value at the end of Year 3 of the following cash flow stream if the quoted interest rate is 10%,
compounded semiannually?
$75
$100
Principal
Interest
10 of 11
666
667
668
669
670
671
672
673
674
675
676
677
678
687
688
689
690
691
692
693
694
695
696
697
698
699
700
l. (3.) Is the stream an annuity? No, because we don’t have a payment for each compounding period.
701
702
703
704
705
706
707
708
709
710
711
712
713
715
716
717
724
725
726
727
728
729
N456
PV of the note: PV $918.95 > $859 cost, so buy the note.
N456
See which has the higher effective rate of return, EFF%
733
A B C D E F G H I J K L M N O P Q R S
Periods 0 1 2 3.0 4 5.0 6
PV of CF $90.70 $82.27 $74.62
Total FV = $247.59
In the second approach, we use the annual effective rate to find the present value of a 3-year annuity.
PV = $247.59
See which provides the greater future wealth
0 1 2 3 4 5456
850
I0.00018538
N456
Bank account: FV $924.97 < $1,000, so buy the note.
0 1 2 3 4 5456
1000
l. (2.) What is the PV of the same stream?
See which has the greater present value
m. Suppose someone offered to sell you a note calling for the payment of $1,000 in 15 months (or 456 days). They
offer to sell it to you for $850. You have $850 in a bank time deposit that pays a 6.76649% nominal rate with daily
compounding, which is a 7% effective annual interest rate, and you plan to leave the money in the bank unless you
buy the note. The note is not risky–you are sure it will be paid on schedule. Should you buy the note? Check the
decision in three ways: (1) by comparing your future value if you buy the note versus leaving your money in the
bank, (2) by comparing the PV of the note with your current bank account, and (3) by comparing the EFF% on the
note versus that of the bank account.
Using the first approach, we find the present value of each individual cash flow using the periodic rate
and the number of periods.
7
11 of 11