1. finance
2. personal finance
3. financial planning
4. retirement and pension

This is a sample of the instructor resources for Understanding Healthcare Financial
Management, Fifth Edition, by Louis Gapenski. This sample contains the chapter models,
end-of-chapter problems, and end-of-chapter solutions, as well as PowerPoint slides for
Chapter 3.
The complete instructor resources consist of 39 pages of instructor notes; chapter models,
end-of chapter problems, end-of-chapter solutions; and 744 PowerPoint slides. If you
adopt this text you will be given access to complete materials. To obtain access, e-mail
your request to [email protected] and include the following information in your message:
•
•
•
•
•
•
Book title
Your name and institution name
Title of the course for which the book was adopted and season course is taught
Course level (graduate, undergraduate, or continuing education) and expected
enrollment
The use of the text (primary, supplemental, or recommended reading)
A contact name and phone number/e-mail address we can use to verify your
employment as an instructor
You will receive an e-mail containing access information after we have verified your
instructor status. Thank you for your interest in this text and the accompanying instructor
resources.
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
B
C
Chapter 3
D
E
F
G
H
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT, 5ed
Time Value Analysis
This spreadsheet model performs many of the calculations contained in Chapter 3. We recommend that you use the model in the
following manner:
1. First, recognize that you do not have to use this model at all to understand time value analysis concepts. However, using the
model will increase your understanding of the relevant concepts, and it will surely help when you use spreadsheet models
for other purposes, especially any problem sets or cases assigned for this course.
2. Start by reading the chapter in its entirety.
3. Now place the text alongside your computer with this model on the screen. When you come to an explanation of a calculation
in the text, see if the model has a matching calculation.
4. We assume that you know the basics of Excel, but you have not encountered some of its features or you may need a
refresher or two. So we have built in explanations of how to do some of the functions in the model. As a result, you will learn
more about Excel at the same time you learn about time value analysis.
5. Throughout this model, page numbers of the matching text calculations are provided in pink. Input data are in red on a
yellow background, and output data are in green on a beige background. You are encouraged to change the input data to learn
more about the calculations in the model.
FUTURE VALUE OF A LUMP SUM (COMPOUNDING) (PAGE 81)
Find the future value (FV) of $100 after five years at an interest rate of 5%.
Interest rate
Lump sum
Time period
FV at year end
5.0%
100
0
100
Note that input values are always
in red on a yellow background.
1
105.00
2
110.25
Make some changes to the input values
to see the effect on future value.
3
115.76
4
121.55
5
127.63
Note that an
in green on
N
This problem was solved using the formula, FV = PV x (1+I) . However, there are a number of ways this problem could have
been solved. One of the most valuable features in Excel is the "Function Wizard."
To begin, first move the cursor to the cell where the answer is to appear. In this case, H90.
Next, select the Function Wizard icon found in the toolbar at the top of the screen. It looks like this
This button allows you to enter the Function Wizard. Upon clicking on it, you will see a dialog box entitled "Paste Function."
On the left side of the dialog box is a menu entitled "Function Category," and on the right is a menu called "Function Name."
A
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
B
C
D
E
F
G
H
We will be selecting the "FV" function from the "Financial" category, and we will be using the following dialog box to input our data
Notice that we entered a cell reference as the input for the problem instead of the actual value. We do this so that our spreadsheet
can automatically reflect any changes to the input data. This is one of the features that make the spreadsheet such a valuable tool.
Also note that the FV function can use up to five inputs. To find the future value of a lump sum only three are required, so the
resulting formula has double commas, which means that the third input is zero. The final input (Type) is not required.
The wizard enters =FV(B35,G38,,-B36) in Cell G90.
FV =
$127.63
GRAPHIC VIEW OF THE COMPOUNDING (GROWTH) PROCESS (PAGE 85)
With a spreadsheet, calculating FVs is a simple operation, and we can use them to graph the relationship between future value,
growth, interest rates, and time. A similar graph can be found in the textbook in Figure 3.1.
Period (n)
0
2
4
6
8
10
0%
1.0000
1.0000
1.0000
1.0000
1.0000
1.0000
5%
1.0000
1.1025
1.2155
1.3401
1.4775
1.6289
10%
1.0000
1.2100
1.4641
1.7716
2.1436
2.5937
15%
1.0000
1.3225
1.7490
2.3131
3.0590
4.0456
A
B
C
D
E
F
G
H
Relationships Among Future Value, Growth, Interest
Rate, and Time
$5.00
Future Value of $1
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
$4.00
$3.00
$2.00
$1.00
$0.00
0
2
4
6
8
10
12
Periods
To create a graph in Excel, you first must access the "Chart Wizard" found in the toolbar near the top of
the screen denoted by
. Upon selecting the Chart Wizard, the first input dialog box will appear,
and it will ask for the "Chart Type." In this case, we want a line graph, so we select "Line," and then we
click one of the subtypes, in this case the first one.
After clicking "Next," we are presented with the "Source Data" box. We first enter the "Data Range" for the chart. These are the
data that will comprise the lines in our line graph. For our example, this information can be found in the above table. To select the
data range, use the cursor to highlight the cells range from B99 to E104. Note that the data are contained in columns.
A
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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
B
C
D
E
F
G
H
Now we must select the "Series" for our graph. Essentially, this is the set of data that will comprise each line in the graph. In this
graph, you can name each individual series by selecting it in the Series section and typing a label into the Name box. When an
individual series is highlighted, the Values section will let you know what data from the data range make up that particular line. To
place appropriate labels on the X axis, you must go to the Category (X) axis labels box and highlight from A99 to A104.
A
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
B
C
D
E
F
G
H
At this point, all of the necessary data for the chart have been inserted. From here, the chart just needs to be formatted according to
your preferences (e.g., show or hide gridlines, change the numbers on the axes).
PRESENT VALUE OF A LUMP SUM (DISCOUNTING) (PAGE 87)
Find the present value (PV) of $127.63 discounted back five years at an interest rate of 5%.
Interest rate
Lump sum
5.0%
127.63
Time period
PV at year end
0
100.00
Make some changes to the input values
to see the effect on present value.
1
105.00
2
110.25
3
115.76
4
121.55
5
127.63
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, in this case
Cell C264. 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, $100.
PV =
$100.00
SOLVING FOR INTEREST RATE (I) (PAGE 92)
What is the interest rate of a security priced at $78.35 that pays $100 after five years?
N
PV
FV
5
78.35
100
Make some changes to the input values
to see how the variables affect I (Cell C298).
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 activate the menu slot and then click on
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
A
the appropriate cell.
B
C
I =
D
E
F
G
H
5.00%
th
We noted above the difficulty of solving this problem mathematically. This is because it involves taking the n root of a value (an
operation that generally requires either a calculator or a computer). However, if you would like to know how to solve the problem
1/N
mathematically, the formula is: (FVN / PV) - 1, which is derived from the FV formula.
N
PV
FV
5
78.35
100
I =
5.00%
SOLVING FOR TIME (N) (PAGE 93)
A security yields 5%, costs $78.35 today, and will return $100 at some future date. What is the security's term to maturity?
I
PV
FV
5.0%
78.35
100
Make some changes to see the impact
on N (Cell C338).
The Function Wizard can be used to solve for time, or N. This operation can be performed by selecting the "Nper" option from the li
of financial functions, and then entering the input data into the dialog box.
A
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
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
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
B
C
N =
D
E
F
G
H
5.00
Solving for N mathematically is very complicated. The formula for finding N involves using natural logarithms, which is a complex
operation. For this reason, we highly suggest the use of the Function Wizard or financial calculator to solve this type of problem.
However, here is the formula needed to solve for N: N = (ln (FVN / PV ) / (ln (1+ I)). This formula is applied in Cell C345.
N =
5.00
ORDINARY ANNUITIES (PAGE 94) (Future Value Calculation Only, See Row 427 for Present Value)
If the interest rate is 5%, what is the future value of an ordinary annuity that pays $100 at the end of each of the next three years?
As explained below, one way to solve this problem is to find the future value of each of the annuity payments. However, this is
somewhat tedious, especially if a lot of years are involved. In the following example, we use the input data of the interest rate and
time to calculate the future value in time period 3 of each individual cash flow. Lastly, we take the sum of all the future values, which
gives us the future value of the entire annuity.
N
I
PMT
Time period
Annuity pmt
FV
3
5.0%
100
0
0
0
Change the I and PMT inputs to see their
effect on FV. Note that N cannot be changed.
1
100
110.25
2
100
105
3
100
100
=
Annuity FV
$315.25
An easier procedure is to use the Function Wizard to solve for the future value of an annuity. This procedure is similar to
that of a lump sum future value. Whereas before we left the "Pmt" field blank, 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"
Type input box. Previously, we left this box alone. A "0"
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.
FV =
$315.25
ANNUITIES DUE (PAGE 96) (Future Value Calculation Only, See Row 463 for Present Value)
If the interest rate is 5%, what is the future value of an annuity due that pays $100 at the beginning of each of the next three years?
The procedure for solving this problem follows the previous example with one notable exception. Because the payments occur at the
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
A
B
C
D
E
F
G
beginning of each year, the first annuity payment occurs in time period 0, and the last occurs in time period 2.
N
I
PMT
Time period
Annuity pmt
FV
3
5.0%
100
H
Change the I and PMT inputs to see their
effect on FV. Note that N cannot be changed.
0
100
115.7625
1
100
110.25
2
100
105
3
0
0
=
Annuity FV
$331.01
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.
FV =
$331.01
ORDINARY ANNUITIES (PAGE 94)
(Present Value)
If you were given the option of receiving a lump sum of money today or an annuity that pays $100 at the end of each of the next three
years, at what price should you be indifferent to the two options, if the interest rate is 5%?
The way to solve this problem is to find the PV of the annuity and then compare it with the lump sum. First, we consider each
payment separately .
N
I
PMT
Time period
Annuity pmt
PV
3
5.0%
100
0
0
0
Change the I and PMT inputs to see their
effect on PV. Note that N cannot be changed.
1
100
95.24
2
100
90.70
Or, you could use the Function Wizard for this ordinary annuity.
3
100
86.38
=
Annuity PV
$272.32
A
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
B
C
PV =
D
E
F
G
H
$272.32
ANNUITIES DUE (PRESENT VALUE) (PAGE 96)
(Present Value)
What if the payments occurred at the beginning of each year?
This problem is solved just like the previous problem, except that the payments occur in periods 0 through 2.
N
I
PMT
3
5.0%
100
Time period
Annuity pmt
PV
Change the I and PMT inputs to see their
effect on PV. Note that N cannot be changed.
0
100
100.00
1
100
95.24
2
100
90.70
3
0
0.00
=
Annuity PV
$285.94
Using the Function Wizard, we follow the same procedure as above, except remember to enter a "1" to tell Excel that this problem ha
payments occurring at the beginning of the periods.
PV =
$285.94
PERPETUITIES (PAGE 99)
What is a perpetuity worth if it pays $100 every year and the discount rate is 5%?
PMT
I
100
5.0%
PV =
Change the payment and discount rate
to see their impact on present value.
$2,000
UNEVEN CASH FLOW STREAMS
A
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
B
C
D
E
F
G
H
Present Value (Page 100)
Calculate the present value of the following cash flow stream, discounted at 10%.
I
10.0%
Change the discount rate (I) to
examine its effect on PV.
0
1
2
3
4
5
0
100
120
150
180
250
90.91
99.17
112.70
122.94
155.23
Change the cash flows to
examine their impact on PV
PV of Stream
0.00
NPV =
$580.95
As we show above, the first way to solve for the present value of this uneven cash flow stream is to use
the timeline 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.
This problem could also be set up in a column format; it is a matter of personal preference as to which format is easier to
interpret and use. Once we have placed the data into columns, we can solve for the present value of each of the
cash flows (like we did previously) and add all of the present values together to get the final answer.
I
10.0%
N
0
1
2
3
4
5
PV0
CF
0
100
120
150
180
250
0
90.91
99.17
112.70
122.94
155.23
NPV =
Try entering a $500 outflow at Year 0.
$580.95
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 separately. In this particular example, the initial cash flow is zero.
Data from either
the timeline or
the columns
could be entered
here.
A
569
570
571
572
573
574
575
576
577
578
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
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
B
C
NPV =
D
E
F
G
H
$580.95
Both methods will yield the same result, so use the one that you are most comfortable with. In the
event that you have a problem consisting of a cash flow at time period zero, you will have to manually add this
value to the NPV of the remaining cash flows. Also note that when using the Function Wizard, we used
the cash flow data from columns. However, we could have just as easily used the timeline data that we
initially presented.
Future Value (Page 101)
Calculate the future value of the cash flow stream illustrated above in the previous question.
First, we will solve this problem by adding the future values of all the cash flows in time period 7.
I
10.0%
N
0
1
2
3
4
5
# compoundings
5
4
3
2
1
0
Change the discount rate (I) to
examine its effect on FV.
FV5
CF
0
100
120
150
180
250
0
146.41
159.72
181.50
198.00
250.00
FV =
$935.63
Excel does not have a net future value function, but the above procedure works for this type of problem.
An alternative is to calculate the NPV and then compound that value out to the end of the cash flow
stream, but that procedure is not as easy conceptually as that used above.
USING TIME VALUE ANALYSIS TO MEASURE FINANCIAL RETURNS
Dollar Return (Page 103)
Calculate the net present value (NPV) of the following cash flow stream, discounted at 10%.
I
10.0%
Change the discount rate (I) to
examine its effect on PV.
0
1
2
3
4
5
-500
100
120
150
180
250
90.91
99.17
112.70
122.94
155.23
Change the cash flows to
examine their impact on PV
PV of Stream
-500.00
NPV =
$80.95
As we show above, the first way to solve for the NPV of this investment is to use
the timeline 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.
This problem could also be set up in a column format; it is a matter of personal preference as to which format is easier to
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
A
B
C
D
E
F
G
interpret and use. Once we have placed the data into columns, we can solve for the present value of each of the
cash flows (like we did previously) and add all of the present values together to get the final answer.
I
H
10.0%
N
0
1
2
3
4
5
PV0
CF
-500
100
120
150
180
250
-500
90.91
99.17
112.70
122.94
155.23
NPV =
$80.95
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 separately. In this particular example, the initial cash flow is -500.
NPV =
$80.95
Rate of Return (Page 104)
Calculate the internal rate of return (IRR) of the cash flow stream illustrated above in the previous question.
Here we use the IRR function:
IRR =
15.3%
Note that the IRR function has this format: IRR(range, starting guess). The starting guess is required to "begin" the iterative
calculation procedure used by Excel. We used the discount rate as the guess, but any reasonable value could have been entered.
SEMIANNUAL AND OTHER COMPOUNDING PERIODS (PAGE 105)
If $100 is invested in an account at an interest rate of 6%, annual compounding, for three years, what is the FV?
N
I
PV
3
6.0%
100
FV =
$119.10
$0.30
is the difference.
What is the FV with semiannual compounding?
N (years x 2)
I (I per year/2)
PV
6
3.0%
100
FV =
$119.41
What is the PV of an ordinary annuity of $100 per year for three years when the interest rate is 8%, compounded annually?
N
I
3
8.0%
PV =
$257.71
A
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
PMT
B
C
D
E
F
G
H
100
What is the PV of an ordinary annuity of $100 per year for three years when the interest rate is 8%, compounded semiannually?
N
I
PMT
6
4.0%
50
FV =
$262.11
Remember that in cases of non-annual compounding, all input variables (N, I, PMT) must reflect the number of compounding period
AMORTIZED LOANS (PAGE 108)
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 6%?
N
I
PV
3
6.0%
1000000
PMT =
Change the inputs to see the impact
on the payment amount and the
amortization table.
$374,110
Now, construct an amortization table for the loan described above.
N
1
2
3
Totals
Loan amount
$1,000,000
685,890
352,934
Payment
$374,110
374,110
374,110
Interest
$60,000
41,153
21,176
Principal
$314,110
332,956
352,934
$1,122,329
$122,329
$1,000,000
Balance
$685,890
352,934
0
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT, 5ed
Chapter 3 -- Time Value Analysis
PROBLEM 1
Find the following values for a lump sum assuming annual compounding
a. The future value of $500 invested at 8 percent for one year
b. The future value of $500 invested at 8 percent for five years
c. The present value of $500 to be received in one year when the opportunity cost rate is 8 percent
d. The present value of $500 to be received in five years when the opportunity cost rate is 8 percent
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
B
C
D
E
F
G
H
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT, 5ed
Chapter 3 -- Time Value Analysis
PROBLEM 1
Find the following values for a lump sum assuming annual compounding:
a. The future value of $500 invested at 8 percent for one year
b. The future value of $500 invested at 8 percent for five years
c. The present value of $500 to be received in one year when the opportunity cost rate is 8 percent
d. The present value of $500 to be received in five years when the opportunity cost rate is 8 percent
ANSWER
a.
FV = $500 x (1.08)1 = $540.00
Spreadsheet solution:
($540.00) =FV(0.08,1,,500)
b.
FV = $500 x (1.08)5 = $734.66
($734.66) =FV(0.08,5,,500)
c.
PV = $500 / (1.08)1 = $462.96
($462.96) =PV(0.08,1,,500)
d.
PV = $500 / (1.08)5 = $340.29
($340.29) =PV(0.08,5,,500)
I
3-1
CHAPTER 3
Time Value Analysis
„Future
F t
and
d presentt values
l
g for I and N
„Solving
„Investment returns
„Opportunity cost
„Amortization
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
8/22/06
3-2
Ti
Time
Value
V l off Money
M
„Time value analysis is necessary
because money has time value.
zA do
dollar
a in hand
a d today is
s worth
o t more
o e tthan
a
a dollar to be received in the future. Why?
zBecause of time value, the values of future
dollars must be adjusted before they can
be compared to current dollars.
„Time value analysis constitutes the
techniques that are used to account for
the time value of money.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3-3
Time Lines
0
1
2
3
CF1
CF2
CF3
I%
CF0
Tick marks designate ends of periods.
g point
p
(the
(
beginning
g
g
Time 0 is the starting
of Period 1); Time 1 is the end of Period 1
(the beginning of Period 2); and so on.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3-4
Whatt is
Wh
i the
th FV after
ft three
th
years off
a $100 lump sum invested at 10%?
0
1
2
3
10%
-$100
$100
FV = ?
„Finding
g future values (moving
(
g to the right
g
along the time line) is called compounding.
„For now,
now assume interest is paid annually.
annually
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3-5
After 1 year:
FV1 = PV + INT1 = PV + (PV x I)
= PV x (1 + I)
= $100 x 1.10 = $110.00
After 2 years:
y
FV2 = FV1 + INT2
= FV1 + (FV1 x I) = FV1 x (1 + I)
= PV x (1 + I) x (1 + I) = PV x (1 + I)2
= $100 x (1
(1.10)
10)2 = $121.00
$121 00
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3-6
After 3 years:
FV3 = FV2 + I3
= PV x (1 + I)3
= 100 x (1
(1.10)
10)3
= $133.10
In general,
FVN = PV x (1 + I)N
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3-7
Th
Three
Primary
Pi
Methods
M th d to
t Find
Fi d FVs
FV
„Solve
„S
l the
th FV equation
ti using
i a regular
l
calculator.
„Use a financial calculator—that is, one
with financial functions.
„Use a computer with a spreadsheet
program such as Microsoft Excel®.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3-8
R
Regular
l Calculator
C l l t Solution
S l ti
0
1
2
3
10%
-$100
$100
$110.00
$121.00
$133.10
$100 x 1.10 x 1.10 x 1.10 = $133.10
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3-9
S
Spreadsheet
d h t Solution
S l ti
Function = FV(rate,nper,pmt,pv,type)
FV(rate nper pmt pv type)
Cell formula = FV(0.1,3,,100)
Cell display = (133.10)
(133 10)
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 10
S
Spreadsheet
d h t Solution
S l ti (Cont.)
(C t )
rate = interest rate per period
nper = total number of payment periods in
an annuity
it
pmt = payment made each period; cannot
change over the life of the annuity
pv = present value or lump sum amount
that a series of future p
payments
y
is
worth now; if omitted, pv=0
type = payment at beginning of period = 1;
paymentt att end
d off period
i d = 0 or omitted
itt d
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 11
Whatt is
Wh
i the
th PV off $100 due
d
in three years if I = 10%?
0
1
2
3
10%
PV = ?
$
$100
Finding present values (moving to the left
along the time line) is called discounting.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 12
Solve FVN = PV x (1 + I )N for PV
PV = FVN / (1 + I )N
PV = $100 / (1.10)3
= $100(0.7513) = $75.13
? If I offer you $75.13 today or $100 three
years from now,
now which would you prefer?
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 13
S
Spreadsheet
d h t Solution
S l ti
Function = PV(rate,nper,pmt,fv,type)
PV(rate nper pmt fv type)
Cell formula = PV(0.1,3,,100)
Cell display = (75
(75.13)
13)
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 14
S l i for
Solving
f I
Assume that a bank offers an account
that will pay $200 after five years on
each $75 invested.
invested What is the implied
interest rate?
Function = RATE(nper,pmt,pv,fv,type)
Cell formula
Ce
o u a = RATE(5,0,-75,200)
(5,0, 5, 00)
Cell display = 22%
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 15
S l i for
Solving
f N
Assume an investment earns 20 percent
per year. How long will it take for the
investment to double?
Function = NPER(rate,pmt,pv,fv,type)
NPER(rate pmt pv fv type)
Cell formula = NPER(0.2,0,-1,2)
Cell display = 3
3.801784
801784
? What is the Rule of 72?
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 16
Types of Annuities
Three Year Ordinary Annuity
0
I%
1
2
3
PMT
PMT
PMT
1
2
3
PMT
PMT
Three Year Annuity Due
0
I%
PMT
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 17
Whatt is
Wh
i the
th FV off a three-year
th
ordinary
di
annuity of $100 invested at 10%?
0
1
2
$100
$100
3
10%
$100
110
121
FV = $331
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 18
S
Spreadsheet
d h t Solution
S l ti
Function= FV(rate,nper,pmt,pv,type)
FV(rate nper pmt pv type)
Cell formula = FV(0.1,3,100)
Cell display = (331.00)
(331 00)
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 19
Wh t is
What
i the
th PV off the
th annuity?
it ?
0
1
2
3
$100
$100
$100
10%
$90.91
82.65
75.13
$248 69 = PV
$248.69
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 20
S
Spreadsheet
d h t Solution
S l ti
Function = PV(rate,nper,pmt,fv,type)
PV(rate nper pmt fv type)
Cell formula = PV(0.1,3,100)
Cell display = (248.69)
(248 69)
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 21
Whatt is
Wh
i the
th FV and
d PV if the
th
annuity were an annuity due?
0
1
2
3
10%
$100
?
$100
$100
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
?
3 - 22
What is the FV of the annuity due?
0
1
2
$100
$100
3
10%
$100
110
121
133
FV = $364
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 23
Wh t is
What
i the
th PV off the
th annuity
it due?
d ?
0
1
2
$100
$100
10%
$100.00
$90.91
82.64
$273.55 = PV
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3
3 - 24
P
Perpetuities
t iti
„A perpetuity is an annuity that lasts
forever.
„Whatt iis the
„Wh
th presentt value
l off a
perpetuity?
PV (Perpetuity) =
PMT
I
? What is the future value of a perpetuity?
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 25
U
Uneven
C
Cash
h Fl
Flow Streams:
St
Setup
S t
0
10%
1
$100
2
$300
3
4
$300
-$50
$ 90.91
247.93
225 40
225.40
-34.15
$530 09 = PV
$530.09
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 26
S
Spreadsheet
d h t Solution
S l ti
Function = NPV(rate
NPV(rate,value1,value2,…)
value1 value2 )
Cell formula =NPV(0.1,100,300,300,-50)
Cell display = 530
530.09
09
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 27
R t
Return
on IInvestment
t
t (ROI)
„The financial performance of an
investment is measured by its return on
investment.
zTime value analysis is used to calculate
investment returns.
zReturns can be measured either in dollar
terms or in rate of return terms.
„Assume that a hospital is evaluating a
new MRI. The project’s expected cash
flows are given on the next slide.
slide
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 28
MRI IInvestment
t
t Expected
E
t d Cash
C h Flows
Fl
(thousands)
0
-$1 500
-$1,500
1
$310
2
$400
3
4
$500
$750
? Where
Wh
do
d these
th
numbers
b
come from?
f
?
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 29
Si
Simple
l D
Dollar
ll Return
R t
0
1
2
3
-$1,500
$ ,
$310 $400
$500
310
400
500
750
$ 460 = Simple dollar return
?
4
$750
I this
Is
thi a good
d measure?
?
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 30
Di
Discounted
t d Cash
C h Flow
Fl
(DCF) Dollar
D ll Return
R t
0
8%
1
2
3
-$1,500
$ ,
$310 $400
$500
287
343
397
551
$ 78 = net present value (NPV)
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
4
$750
3 - 31
S
Spreadsheet
d h t Solution
S l ti
A
1
2
3 $
4
5
6
7
8
9
10 $
B
8.0%
(1,500)
310
400
500
750
C
D
Interest rate
Year 0 CF
Year 1 CF
Year 2 CF
Year 3 CF
Year 4 CF
78 =NPV(A2,A4:A7)+A3 (entered into Cell A10)
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 32
O
Opportunity
t it Cost
C t Rate
R t
„To find an investment’s dollar return
(NPV), we need to apply a discount
rate. Where does it come from?
zThe discount rate is the opportunity
cost rate.
zIt is the rate that could be earned on
alternative investments of similar risk.
p
on the source of the
zIt does not depend
investment funds.
„We will apply
pp y this concept
p over and
over in this course.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 33
O
Opportunity
t it Cost
C t Rate
R t (C
(Cont.)
t)
„ The opportunity cost rate is found (at least
in theory) as follows.
z Assess the riskiness of the cash flow(s) to be
discounted.
z Identify alternative investments (usually
securities) that have the same risk.
z Estimate
E ti t th
the expected
t d return
t
on the
th similar-risk
i il
i k
alternative investment.
„ When applied, the resulting PV provides a
return equal to the opportunity cost rate.
„ In most
ost ttime
e value
a ue s
situations,
tuat o s, be
benchmark
c a
opportunity cost rates are known.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 34
O
Opportunity
t it Cost
C t Rate
R t (C
(Cont.)
t)
„When calc
calculating
lating NPV,
NPV the disco
discounting
nting
process automatically recognizes the
opportunity cost of capital
capital. Thus,
Thus
zA positive NPV means that the investment
is expected to create value for the
investor.
zA negative NPV means that the
investment is expected to lose value for
the investor.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 35
R t off (P
Rate
(Percentage)
t
)R
Return
t
0
10%
1
2
3
-$1,500
$1 500
$310 $400
$500
282
331
376
511
$ 0.00 = NPV, so E(R) = 10.0%.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
4
$750
3 - 36
S
Spreadsheet
d h t Solution
S l ti
A
1
2
3 $
4
5
6
7
8
9
10
B
8.0%
(1,500)
310
400
500
750
C
D
Interest rate guess
Year 0 CF
Year 1 CF
Year 2 CF
Year 3 CF
Year 4 CF
10.0% =IRR(A3:A7,A2) (entered into Cell A10)
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 37
R t off R
Rate
Return
t
(C
(Cont.)
t)
„In capital investment analyses
analyses, the rate
of return often is called internal rate of
return (IRR).
„In essence, it is the percentage return
expected
p
on the investment.
„To interpret the rate of return, it must be
compared
co
pa ed to the
t e opportunity
oppo tu ty cost o
of
capital. In this case, 10 percent versus 8
percent.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 38
I t Y
Intra-Year
Compounding
C
di
„Thus far
far, all examples have assumed
annual compounding.
„When compounding
„Wh
di occurs intra-year,
i t
the following occurs:
zInterest
zI
t
t is
i earned
d on interest
i t
t during
d i the
th
year (more frequently).
The future value of an investment is larger
zThe
than under annual compounding.
zThe present value of an investment is
smaller
ll than
th under
d annuall compounding.
di
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 39
0
1
2
3
10%
-100
133.10
Annual: FV3 = 100 x (1
(1.10)
10)3 = 133.10
133 10
0
0
1
1
2
3
2
4
5
3
6
5%
-100
134.01
Semiannual: FV6 = 100 x (1.05)6 = 134.01
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 40
Eff ti Annual
Effective
A
l Rate
R t (EAR)
„ EAR is the annual rate that causes the
PV to grow to the same FV as under
intra-year
intra
year compounding.
compounding
„ What is the EAR for 10 percent,
semiannual compounding?
zConsider the FV of $1 invested for one
year. FV = $
y
$1 x ((1.05))2 = $
$1.1025.
zEAR = 10.25%, because this rate would
produce the same ending amount
($1 1025) under
($1.1025)
d annuall compounding.
di
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 41
Th EAR Formula
The
F
l
EAR = 1 +
IStated
- 1.0
M
0.10
= 1+
M
2
2
- 1.0
= (1.05)2 - 1.0 = 0.1025 = 10.25%
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 42
EAR off 10% att V
Various
i
C
Compounding
di
EARAnnual
= 10%
EARQ
= (1 + 0.10/4)4 - 1.0
= 10.38%
EARM
= (1 + 0.10/12)12 - 1.0
= 10.47%
EARD(360) = (1 + 0.10/360)365 - 1.0 = 10.52%
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 43
S
Spreadsheet
d h t Solution
S l ti
Function
F
nction = EFFECT(nominal
EFFECT(nominal_rate,npery)
rate nper )
Cell formula = EFFECT(0.10,4)
Cell display = 0.103813
0 103813
Function = EFFECT(nominal_rate,npery)
C ll formula
Cell
f
l = EFFECT(0.10,12)
EFFECT(0 10 12)
Cell display = 0.104713
Function = EFFECT(nominal_rate,npery)
Cell formula = EFFECT(0.10,365)
Cell display = 0.105156
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 44
S
Spreadsheet
d h t Solution
S l ti
nominal_rate = nominal interest rate
npery = number of compounding
periods
i d per year
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 45
U i the
Using
th EAR
1
0
2
3
4
6
5
5%
$100
$100
$
6-month
periods
$100
$
Here, payments occur annually, but
compounding occurs semiannually,
semiannually so we can
not use normal annuity valuation techniques.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 46
Fi t M
First
Method:
th d Compound
C
d Each
E h CF
0
5%
1
2
$100
3
4
$100
5
6
$100.00
110.25
121.55
$331.80
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 47
Second Method: Treat as an Annuity
„Find the EAR for the stated rate:
Function = EFFECT(nominal_rate,npery)
C ll formula
Cell
f
l = EFFECT(0.10,2)
EFFECT(0 10 2)
Cell display = 0.1025
„Then, use standard annuity techniques:
Function = FV(rate,nper,pmt,pv,type)
FV(rate nper pmt pv type)
Cell formula = FV(0.1025,3,100)
Cell display
p y = ((331.80))
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 48
A
Amortization
ti ti
Construct an amortization schedule for a
$1 000 10% annual rate loan with three
$1,000,
equal payments.
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 49
Step 1: Find the required payments
0
1
2
3
PMT
PMT
PMT
10%
-$1,000
Function = PMT(rate,nper,pv,fv,type)
(
, p ,p , , yp )
Cell formula =PMT(0.1,3,1000)
Cell display
p y = ((402.11))
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 50
St 2
Step
2: Fi
Find
d interest
i t
t charge
h
for
f Year
Y
1.
1
INTt = Beginning balance x I.
I
INT1 = $1,000 x 0.10 = $100
Step 3: Find repayment of principal in
Year 1.
Repmt = PMT - INT
= $402.11 - $100
= $302.11
$302 11
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 51
Step 4: Find ending balance at end of
Year 1.
End bal = Beg balance - Repayment
= $1,000 - $302.11 = $697.89
Repeat these steps for Years 2 and 3
to complete the amortization table
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 52
YR
1
2
3
TOTAL
BEG
BAL
$1,000
698
366
PMT
INT
$402
402
402
$1,206
$100
70
36
$206
PRIN
PMT
END
BAL
$302 $698
332
366
366
0
$1,000
Note that annual interest declines over time
while the principal payment increases.
increases
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 53
402.11
$
Interest
302.11
Principal Payments
0
1
2
3
Level payments. Interest declines because
outstanding balance declines. Lender earns
10% on lloan outstanding,
t t di
which
hi h is
i falling.
f lli
Copyright © 2007 by the Foundation of the American College of Healthcare Executives
3 - 54
C
Conclusion
l i
„This concludes
„Thi
l d our discussion
di
i off
Chapter 3 (Time Value Analysis).
„Although not all concepts were
discussed in class, you are
responsible for all of the material in
the text.
? Do
D you h
have any questions?
ti
?
Copyright © 2007 by the Foundation of the American College of Healthcare Executives