1
2
3
4
5
6
7
8
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
A B C D E F G H I J K L M N O P Q R
12/9/2012
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
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%
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.
1 of 13
18
24
Time period 0 1 2
Time period 0 1 2
57
58
59
60
70
71
72
73
74
75
76
77
78
79
93
94
95
96
97
98
99
100
101
102
117
118
119
120
121
122
123
124
125
A B C D E F G H I J K L M N O P Q R
FV = $133.10
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
show below. Alternatively, select the menu Formulas, then then select Financial, then pick FV.
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.
Using the function wizard yields the following result:
2 of 13
126
127
128
129
130
131
132
133
134
A B C D E F G H I J K L M N O P Q R
Period (N) 0% 5% 10% 15%
01.0000 1.0000 1.0000 1.0000
21.0000 1.1025 1.2100 1.3225
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
3 of 13
174
Cash flow 100
140
141
142
143
144
145
146
147
148
149
161
162
163
164
165
166
167
168
169
170
171
176
177
178
179
180
181
182
183
184
185
186
187
188
202
203
204
A B C D E F G H I J K L M N O P Q R
PRESENT VALUE (PV)
PROBLEM
Interest rate 10%
PV = $75.13
b. (2) What is the present value of $100 to be received in 3 years if the appropriate interest rate is 10%?
Simply put, the present value (PV) is the value today of some future cash flow (or series of cash flows).
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
$4.00
$5.00
Relationships among Future Value, Growth, Interest
Rate, and Time
4 of 13
205
206
207
208
209
210
211
212
216
217
218
219
220
221
222
223
224
225
226
A B C D E F G H I J K L M N O P Q R
Finding Time to Double
I = 0.2
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?
5 of 13
214
242
243
244
245
246
247
248
249
250
256
257
258
259
260
261
262
263
277
278
279
280
281
282
283
284
285
286
287
288
289
FV 2
A B C D E F G H I J K L M N O P Q R
SOLVING FOR I
PROBLEM
N3
PV -1
FV 2
I = 25.99%
N3
We noted above the difficulty of solving this problem mathematically. This is because it involves taking
the Nth root of a value (an operation which generally requires either a calculator or a computer).
However, if you would like to know how to solve the problem mathematically, the formula is (FVn/PV)(1/N) –
1, which is derived from the FV formula.
Once again, Excel has a special function for this calculation. We suggest using either a financial
d. If you want an investment to double in three years, what interest rate must it earn?
6 of 13
295
296
297
298
299
300
301
302
303
304
305
306
316
317
318
319
320
321
322
323
324
337
338
339
340
341
342
343
344
347
349
A B C D E F G H I J K L M N O P Q R
FUTURE VALUE OF AN ANNUITY
N3
I0.1
PMT 100
Time period 0 1 2 3
CFt0100 100 100 Annuity’s FV:
FV = $331.00
PRESENT VALUE OF AN ANNUITY
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%?
f. (2.) What is the present value of the annuity?
7 of 13
353
354
355
356
357
358
359
360
361
377
378
379
380
381
382
383
384
385
386
387
389
392
393
394
395
396
397
398
399
400
401
402
417
418
419
A B C D E F G H I J K L M N O P Q R
N3
I0.1
PMT 100
FV = $364.10
f. (3.) What would the future and present values be if the annuity were an annuity due?
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.
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.
8 of 13
375
376
420
421
422
423
424
425
426
427
430
431
432
433
434
435
436
437
438
439
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
476
477
478
484
485
486
A B C D E F G H I J K L M N O P Q R
N3
I0.1
PMT 100
Time period 0 1 2 3
PV = $273.55
I = 10%
Time period
0 1 2 3 4
0100 300 300 -50
Cash Flows
PV of Cash Flows
0 90.91 247.93 225.39 -34.15
NPV = = Σ of PVs = $530.09
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.
g. What is the present value of the following uneven cash flow stream? The appropriate interest rate is 10%,
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.
6
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
514
515
516
517
518
519
520
521
522
523
524
529
525
526
527
533
534
535
536
542
545
546
537
538
539
540
548
549
550
551
552
553
554
555
558
A B C D E F G H I J K L M N O P Q R
Or
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.
SEMIANNUAL AND OTHER COMPOUNDING PERIODS
h. (3.) What is the future value of $100 after 5 years under 12% annual compounding?
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.
The effective annual rate is the annual rate that causes the PV to grow to the same FV as under multiple
compounding periods.
With, the financial calculator, we could enter each of these cash flows and the discount rate, and simply
press NPV for the present value of the cash flow stream. In Excel, we can perform a similar calculation
by using the “NPV” function. While this function is very similar, there is a key distinction. In the cash
flow register of your calculator, the first entry you make would be the cash flow to occur in time period
zero. However, the “NPV” function interprets the first data entry as being the cash flow in time period
one. Therefore, the initial cash flow must be added seperately. In this particular example, the initial
cash flow is zero.
Larger, because interest is earned on interest.
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?
h. (1.) Identify (a) the stated, or quoted, or nominal rate (iNom) and (b) the periodic rate (iPER).
559
560
561
562
563
564
565
566
578
579
580
581
582
583
584
585
586
587
588
590
592
593
594
595
596
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%?
597
598
599
600
601
602
603
604
627
605
606
607
608
609
610
A B C D E F G H I J K L M N O P Q R
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
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
j. (2.) What is the annual interest expense for the borrower, and the annual interest income for the lender, during
Year 2?
I. Will the effective annual rate ever be equal to the nominal (quoted) rate? Only if the compounding period is equal
to 1 year.
$350.00
$400.00
$450.00
Payment
Payment Distribution
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
628
629
630
631
632
633
634
635
636
637
638
639
646
647
648
655
657
658
663
664
Periods 0 1.0 2 3.0 4 5.0 6
FV of CF $121.55 $110.25 $100.00
Annual effective rate = 10.25%
649
650
651
652
653
A B C D E F G H I J K L M N O P Q R
$3,182.38 $2,182.38 $1,000.00
0 1 2 3 4 5273
100
I0.00031054
N273
FV $108.85
Years 0 0.5 1 1.5 2 2.5 3
Periods 0 1.0 2 3.0 4 5.0 6
Cash Flow 0 100 0100 0100
There are two approaches. First, you could simply find the future value of each cash flow using the
period rate and compounded for the appropriate number of periods, as shown below.
$0
$25
$100
$125
Principal
Interest
12 of 13
666
667
668
669
670
671
672
681
682
683
684
685
686
695
696
697
698
699
700
701
702
703
704
705
713
715
716
717
0 1 2 3 4 5456
N456
0 1 2 3 4 5456
See which has the higher effective rate of return, EFF%
See which has the greater present value
724
725
726
727
728
729
A B C D E F G H I J K L M N O P Q R
Periods 0 1 2 3.0 4 5.0 6
PV of CF $90.70 $82.27 $74.62
See which provides the greater future wealth
0 1 2 3 4 5456
850
I0.00018538
N456
N456
I 0.035646% per day
EAR 13.89% > 7% so buy the note.
l. (4.) An important rule is that you should never show a nominal rate on a time line or use it in calculations unless
what condition holds? (Hint: Think of annual compounding, when iNOM = EAR = iPER.) What would be wrong with
your answer to questions l(1) and l(2) if you used the nominal rate (10%) rather than the periodic rate (iNOM/2 = 10%/2
= 5%)? Use the nominal rate only for annual compounding.
l. (2.) What is the PV of the same stream?
Using the first approach, we find the present value of each individual cash flow using the periodic rate
and the number of periods.
13 of 13
673
674
677
678