Home > 05 Chapter model

# 05 Chapter model

A B C D E F G H I J K L M N O
1 05 Chapter model       12-10-08
2
Chapter 5.  The Time Value of Money
4
This model is STRICTLY OPTIONAL.  Neither students nor instructors need to go through it.  However, if someone wants to practice with Excel, then the model can be useful.  Also, on the tabs we show solutions for the within-chapter self-test questions.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ••The "Main Model" performs the calculations required for Chapter 5, and was used to create many of the chapter exhibits (Tables and Figures).  We pasted in a few dialog boxes for specific Excel functions and features and show then off to the right of where they apply.  However, in general we encourage students who want to know more about Excel to use the Excel Tutorial and refer to it as necessary.  We also let students know that Excel models can be used to create tables and graphs that can then be copied into Word documents, which is the way we prepared the text manuscript for submission to the publisher.  That procedure is used often in business to prepare reports.
6 Although answers to the Self-Test questions within the chapter are generally quite easy and were found with a calculator, we also solved some of them with Excel as a check and to provide additional information on the solutions in case students have questions. The tabs at the lower part of this screen take you to these solutions.  Even if students are not familiar with Excel, they should still be able to see the solution setup and then work out the answer with a calculator.  Although we did not create the model specifically for use in lectures, it could be used as a lecture vehicle in a classroom where a projector is attached to a computer.  The instructor could scroll through the model and lecture on points as they come up.  This would be more useful if students have some familiarity with Excel, but that is not really necessary because everything the model does can also be done with a financial calculator.
7 Finally, if we do something in Excel that you don't understand, you might look at our Excel Tutorial, which explains a lot of points related to Excel.  For example, it discusses in some detail how to enter functions to do various math operations, how to make graphs, and lots of other things we commonly do with Excel in finance.
8 FUTURE VALUES (Section 5-2)
Note on Excel: You can do calculations in Excel in several different ways. Always start with the pointer on the cell where you want the answer displayed.  Then do one of the following:  (1) Enter an equal sign on the formula bar (which is right after the fx), then write out an equation, like =(1.05)^3*100 to get 1.05 to the 3rd power times 100. Hit Enter after typing the equation and you get the answer, 115.76.  (2) Use a dialog box as shown below and to the right. Click on fx , then Financial, then FV, then OK to get the box.  Then fill in the rate, number of periods, periodic payments, and present value. Hit OK to get the answer, 115.76.  (3) Put the pointer just to the right of the fx and type =  . Since you know you want FV, type FV(  , i.e., FV and a left parenthesis. As soon as you enter the left paren, an equation will appear below the line to prompt you about what to enter next, and you enter on the formula line the first item, which is the same as the first entry in the dialog box. Then type a comma, and you will be prompted to enter the next variable.  Continue, and after the last variable type a right parenthesis.
10 Then, when you hit the enter key, you get the answer, 115.76.
11 Question:  You plan to deposit \$100 in a bank that pays a guaranteed 5% interest each year.  How much would you have at the end of Year 3?
12
13  Table 5-1.  Summary of Future Value Calculations
14  Investment       = CF0 = PV =   -\$100.00
15  Interest rate     =    I  =   5.00%
16  No. of periods  =    N  =   3
17
18      Periods: 0 1 2 3
19        | | | |
20      Cash Flow Time Line: -\$100     FV = ? Select a cell
21                to see the
22  Step-by-Step Approach:     \$100 \$105.00 \$110.25 \$115.76 formula in it.
23
24  Formula Approach: FVN = PV(1+I)N      FVN = \$100(1.05)3 = \$115.76
25
26      3 5 -\$100.00 \$0
27  Calculator Approach:   N I/YR PV PMT FV
28              \$115.76
29
30  Excel Approach:                     Fixed inputs: FVN =      =FV(0.05,3,0,-100)     =   \$115.76
31                   Cell references: FVN =      =FV(C15,C16,0,C14)  =   \$115.76
32
33  In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no intermediate cash flows, and then the PV.  The data can be entered as fixed numbers or as cell references.
34
35  The Compounding Process: A Graphic View
36  Figure 5-1 (shown below) shows how a \$1 investment grows over time at different interest rates.  The curves were created by solving for FV at different values for N and I.  This allows you to simultaneously see the effects of varying time and the interest rate.
37
38  The data table used to create this figure is shown to the right.  For instruction on data tables and graphs, refer to the Excel Tutorial.
39
40  Periods Future Value
of \$1

41                Data for Figure 5-1.
42                  Interest Factors, FVIFi,n
43               Periods (n) Interest Rate (i)
44               1.1576 0% 5% 10% 20%
45                0 1.0000 1.0000 1.0000 1.0000
46                1 1.0000 1.0500 1.1000 1.2000
47                2 1.0000 1.1025 1.2100 1.4400
48                3 1.0000 1.1576 1.3310 1.7280
49          4 1.0000 1.2155 1.4641 2.0736
50               5 1.0000 1.2763 1.6105 2.4883
51                6 1.0000 1.3401 1.7716 2.9860
52                7 1.0000 1.4071 1.9487 3.5832
53                8 1.0000 1.4775 2.1436 4.2998
54                9 1.0000 1.5513 2.3579 5.1598
55                10 1.0000 1.6289 2.5937 6.1917
56
57
58  PRESENT VALUES (Section 5-3)
59  The present value is the opposite of the future value.  Instead of compounding a value forward, you discount it back.  If you know the PV, you can compound to find the FV, while if you know the FV, you can discount to find the PV.
60
61  You must male a payment of \$115.76 in 3 years.  If a bank pays a guaranteed 5% interest rate each year, how much would you need to deposit now to have \$115.76 in 3 years?
62 You can work this problem in the same ways that we discussed above.
63 Table 5-2.  Summary of Present Value Calculations
64  Future payment = CFN = FV =   \$115.76
65  Interest rate       =    I    =   5.00%
66  No. of periods    =    N   =   3
67      Periods: 0 1 2 3
68        | | | |
69      Cash Flow Time Line: PV = ?     \$115.76
70
71  Step-by-Step Approach:     \$100.00 \$105.00 \$110.25 \$115.76
72
73  Formula Approach: PV = FVN / (1 + I)N     PV = \$115.76/(1.05)3 = \$100.00
74
75      3 5   \$0 \$115.76
76  Calculator Approach:   N I/YR PV PMT FV
77          -\$100.00
78  Excel Approach:                  PV Function: PV =      =PV(I,N,0,FV)
79                        Fixed inputs: PV =      =PV(0.05,3,0,115.76)   =   -\$100.00
80      Cell references: PV =      =PV(C65,C66,0,C64)    =   -\$100.00
81
82  In the Excel formula, 0 indicates that there are no intermediate cash flows.
83
84
85  The Discounting Process: A Graphic View
86  Figure 5-2 shows how a \$1 payment in the future has a lower and lower present value as the interest rate and time until receipt increase.  The data table to the right provides the data used to draw the figure.
87
88
89  Periods Present Value
of \$1
Data for Figure 5-2.
90                  Interest Factors, PVIFi,n
91               Periods (n) Interest Rate (i)
92               0.8638 0% 5% 10% 20%
93                0 1.0000 1.0000 1.0000 1.0000
94                5 1.0000 0.7835 0.6209 0.4019
95                10 1.0000 0.6139 0.3855 0.1615
96                15 1.0000 0.4810 0.2394 0.0649
97          20 1.0000 0.3769 0.1486 0.0261
98               25 1.0000 0.2953 0.0923 0.0105
99                30 1.0000 0.2314 0.0573 0.0042
100                35 1.0000 0.1813 0.0356 0.0017
101                40 1.0000 0.1420 0.0221 0.0007
102                45 1.0000 0.1113 0.0137 0.0003
103                50 1.0000 0.0872 0.0085 0.0001
104
105
106  FINDING THE INTEREST RATE (Section 5-4)
107  Previously, we solved equations to find FV and PV.  However, we could just as easily solve for I or N.  For example, suppose we know that a given bond has a cost of \$100 and that it will return \$150 after 10 years.  It makes no annual payments, so PMT = 0. Thus, we know PV, FV, and N, and we want to find the rate of return we would earn if we bought the bond.
108
109  Present value (PV)     -\$100.00
110  Future value (FV)     \$150.00
111  No. of years (N)     10
112  Interest rate function     = RATE(N,0,PV,FV)
113  Interest rate (I)   4.14% =RATE(C111,0,C109,C110)
114
115
116  FINDING THE NUMBER OF YEARS (Section 5-5)
117  Sometimes we need to know how long it will take to accumulate a given sum of money, given our beginning funds and the rate we will earn on those funds.  For example, suppose we believe that we could retire comfortably if we had \$1 million, and we want to find how long it will take us to reach that goal, assuming that we now have \$500,000 invested at 4.5%.
118
119
120  Present value (PV)     -\$500,000                 This calculation assumes that the 1st interest payment will come in 1 year.  If not true, use Excel's Yield function.
121  Future value (FV)     \$1,000,000
122  Interest rate (I)     4.50%
123  No. of years (N)     =NPER(I,0,PV,FV)
124  No. of years (N)     15.7473 =NPER(C122,0,C120,C121)
125
126  FUTURE VALUE OF AN ORDINARY ANNUITY (Section 5-7)
127  An ordinary annuity has regular, periodic payments that occur at the end of each period.  Methods for solving the future value of an ordinary annuity are shown below.
128
129  Table 5-3.  Summary:  Future Value of an Ordinary Annuity
130
131  Payment amount     = PMT =   \$100.00
132  Interest rate             =    I    =   5.00%
133  Number of periods   =   N    =   3
134
135    Periods: 0 1 2 3
136      | | | |
137    Cash Flow Time Line:   -\$100 -\$100 -\$100
138
139  Step-By-Step Approach.         -\$100.00
140  Multiply each payment by         -\$105.00
141  (1+I)N-t and sum these FVs to         -\$110.25
142  find FVAN:         -\$315.25
143
144  Formula Approach:
145
146    FVAN       =     = \$315.25
147
148
149      3 5 \$0 -\$100.00
150  Calculator Approach:   N I/YR PV PMT FV
151              \$315.25
152  Excel Function Approach:                  FV Function: FVAN =   =FV(I,N,PMT,PV)
153                        Fixed inputs: FVAN =   =FV(0.05,3,-100,0)              =   \$315.25
154                   Cell references: FVAN =   =FV(C132,C133,-C131,0)  =   \$315.25
155  In the Excel formula, the 0 at the end of the formula indicates that cash flows occur at the end of each period. A 1 would indicate beginning of period payments, i.e., an annuity due.
156
157  FUTURE VALUE OF AN ANNUITY DUE (Section 5-8)
158  An annuity due also has regular, periodic payments, but unlike an ordinary annuity, the payments occur at the beginning of each period.
159
160  Summary:  Future Value of an Annuity Due (Diagram Not in Text)
161
162  Payment amount     = PMT =   \$100.00
163  Interest rate             =    I    =   5.00%
164  Number of periods   =   N    =   3
165
166    Periods: 0 1 2 3
167      | | | |
168    Cash Flow Time Line: -\$100 -\$100 -\$100
169
170  Step-By-Step Approach.         -\$105.00
171  Multiply each payment by         -\$110.25
172  (1+I)N-t and sum these FVs to         -\$115.76
173  find FVAN:         -\$331.01
174
175  Formula Approach:
176
177    FVAN(due)  =     = \$331.01
178
179
180    BEG MODE 3 5 0 -100
181  Calculator Approach:   N I PV PMT FV
182              331.01
183  Excel Function Approach:                  FV Function: FVAN =   =FV(I,N,PMT,PV,Type)
184                        Fixed inputs: FVAN =   =FV(0.05,3,-100,0,1)              =   331.01
185                   Cell references: FVAN =   =FV(C163,C164,-C162,0,1)  =   331.01
186        Excel entries correspond with these calculator keys:            I      N   PMT PV TYPE   FV
187
188  In the Excel formula, the 1 at the end of the formula indicates that cash flows occur at the beginning of each period. A 0 or no entry (blank) would indicate end of period payments, i.e., an ordinary annuity.
189
190
191  PRESENT VALUE OF AN ORDINARY ANNUITY (Section 5-9)
192  The present value of an ordinary annuity is the sum of the PVs of the individual cash flows.  Methods for solving the present value of an ordinary annuity are shown below.
193
194  Summary:  Present Value of an Ordinary Annuity
195
196  Payment amount     = PMT =   \$100.00
197  Interest rate             =    I    =   5.00%
198  Number of periods   =   N    =   3
199
200    Periods: 0 1 2 3
201      | | | |
202    Cash Flow Time Line:   -\$100 -\$100 -\$100
203
204  Step-By-Step Approach.   \$95.24
205  Divide each payment by   \$90.70
206  (1+I)t and sum these PVs to   \$86.38
207  find PVAN:   \$272.32
208
209  Formula Approach:
210
211    PVAN       =     = \$272.32
212
213
214      3 5   -100 0
215  Calculator Approach:   N I PV PMT FV
216          272.32
217  Excel Function Approach:                  PV Function: PVAN =   =PV(I,N,PMT,FV)
218                        Fixed inputs: PVAN =   =PV(0.05,3,-100,0)              =   272.32
219                   Cell references: PVAN =   =PV(C197,C198,-C196,0)  =   272.32
220        Excel entries correspond with these calculator keys:              I       N   PMT FV   PV
221
222
223  PRESENT VALUE OF AN ANNUITY DUE (Not in Text)
224  The difference between the present value of an ordinary annuity and an annuity due is that payments are received earlier in an annuity due.
225
226  Summary:  Present Value of an Annuity Due (Not in text; this is a "bonus")
227
228  Payment amount     = PMT =   \$100.00
229  Interest rate             =    I    =   5.00%
230  Number of periods   =   N    =   3
231
232    Periods: 0 1 2 3
233      | | | |
234    Cash Flow Time Line: -\$100 -\$100 -\$100
235
236  Step-By-Step Approach.   -\$100.00
237  Divide each payment by   -\$95.24
238  (1+I)t and sum these PVs to   -\$90.70
239  find PVAN:   -\$285.94
240
241  Formula Approach:
242
243    PVAN       =     = \$285.94
244
245
246    BEG MODE 3 5   -100 0
247  Calculator Approach:   N I PV PMT FV
248          285.94
249  Excel Function Approach:                  PV Function: PVAN =   =PV(I,N,PMT,FV,Type)
250                        Fixed inputs: PVAN =   =PV(0.05,3,-100,0,1)              =   285.94
251                   Cell references: PVAN =   =PV(C229,C230,-C228,0,1)  =   285.94
252        Excel entries correspond with these calculator keys:              I       N   PMT FV   PV
253  Question: The PV of the annuity due is larger than the PV of the ordinary annuity.  Why is that?  Answer:  Because each of the annuity due's payments is discounted back one less year.
254
255  FINDING ANNUITY PAYMENTS, PERIODS, AND INTEREST RATES (Section 5-10)
256  Fundamentally, this section is no different than previous TVM exercises.  When solving for PMT, N, or I, you must be given values for the other variables, and then you solve the problem.
257
258  FINDING PMT
259  Suppose we need to accumulate \$10,000 and have it available 5 years from now.  Suppose further that we can earn a return of 6% on our savings, which are currently zero.
260
261  No. of years (N)   5
262  Interest rate (I)   6%
263  Present value (PV)   \$0
264  Future value (FV)   \$10,000
265
266  END MODE =PMT(I,N,PV,FV)   BEGIN MODE =PMT(I,N,PV,FV,Type=1)
267 Payment (PMT) -\$1,773.96 =PMT(C262,C261,C263,C264)     Payment (PMT) -\$1,673.55
268
269  Trick question: Notice the problem setup didn't specify when payments would be made.  Therefore, consider both possibilities (END and BEGIN mode).
270
271  FINDING N
272  For the preceding question, suppose you decide to make end-of-year deposits, but you can only save \$1,200 per year.  Again assuming that you would earn 6%, how long would it take you to reach your \$10,000 goal?
273
274  Interest rate (I)   6%
275  Present value (PV)   \$0
276  Payment (PMT)   -\$1,200
277  Future value (FV)   \$10,000
278        =NPER(I,PMT,PV,FV)
279  No. of years (N)   6.96 =NPER(C274,C276,C275,C277)
280
281  FINDING I
282  Now suppose you can only save \$1,200 annually, but you still to have the \$10,000 inonly 5 years.  What rate of return would you have to earn to achieve your goal?
283
284  No. of years (N)   5
285  Present value (PV)   \$0
286  Payment (PMT)   -\$1,200
287  Future value (FV)   \$10,000
288        =RATE(N,PMT,PV,FV)
289  Interest rate (I)   25.78% =RATE(C284,C286,C285,C287)        Would be tough to do!
290
291
292  PERPETUITIES (Section 5-11)
293  Perpetuities are securities that promise to make payments forever.  The present value of a perpetuity can be found with a simple formula: Value = I / r .  Note that we do not attempt to calculate the future value of a perpetuity.  Because the payments go on forever, the future value would be infinitely large and thus meaningless.
294
295  Consider a British consol that pays a \$25 annual payment.  If interest rates are currently 5.2%, what is the value of the consol?
296
297  Payment (PMT)   \$25
298  Interest rate (I)   4.3%
299
300  Present Value (PV)   \$581.40 = PMT/ I =C297/C298
301
302  An annuity makes constant payments, those payments are discounted, and the longer before a payment is received, the smaller its PV.  Therefore, adding more payments to the security adds less value for each additional payment.  This helps explain why perpetuities' present values are finite, while future value are infinite.  To see this better, consider Figure 2-3 from the text (reproduced below).  The data used to construct the graph are shown to the right in columns I through L.  One hundred payments are analyzed and their present values, the total value of an annuity of N number of years, and the contribution of the Nth payment are all shown in the table off to the right.
303                 Annuity PMT 100
304  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 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 Years PV of Each \$100 Payment; Addition to Annuity's Value Figure 5-3  Contribution of Each \$100 Payment to the Value of a \$100 Annuity at a 10% Rate 12/31/1899 00:00:001/1/1900 00:00:001/2/1900 00:00:001/3/1900 00:00:001/4/1900 00:00:001/5/1900 00:00:001/6/1900 00:00:001/7/1900 00:00:001/8/1900 00:00:001/9/1900 00:00:001/10/1900 00:00:001/11/1900 00:00:001/12/1900 00:00:001/13/1900 00:00:001/14/1900 00:00:001/15/1900 00:00:001/16/1900 00:00:001/17/1900 00:00:001/18/1900 00:00:001/19/1900 00:00:001/20/1900 00:00:001/21/1900 00:00:001/22/1900 00:00:001/23/1900 00:00:001/24/1900 00:00:001/25/1900 00:00:001/26/1900 00:00:001/27/1900 00:00:001/28/1900 00:00:001/29/1900 00:00:001/30/1900 00:00:001/31/1900 00:00:002/1/1900 00:00:002/2/1900 00:00:002/3/1900 00:00:002/4/1900 00:00:002/5/1900 00:00:002/6/1900 00:00:002/7/1900 00:00:002/8/1900 00:00:002/9/1900 00:00:002/10/1900 00:00:002/11/1900 00:00:002/12/1900 00:00:002/13/1900 00:00:002/14/1900 00:00:002/15/1900 00:00:002/16/1900 00:00:002/17/1900 00:00:002/18/1900 00:00:002/19/1900 00:00:002/20/1900 00:00:002/21/1900 00:00:002/22/1900 00:00:002/23/1900 00:00:002/24/1900 00:00:002/25/1900 00:00:002/26/1900 00:00:002/27/1900 00:00:002/28/1900 00:00:002/28/1900 00:00:003/1/1900 00:00:003/2/1900 00:00:003/3/1900 00:00:003/4/1900 00:00:003/5/1900 00:00:003/6/1900 00:00:003/7/1900 00:00:003/8/1900 00:00:003/9/1900 00:00:003/10/1900 00:00:003/11/1900 00:00:003/12/1900 00:00:003/13/1900 00:00:003/14/1900 00:00:003/15/1900 00:00:003/16/1900 00:00:003/17/1900 00:00:003/18/1900 00:00:003/19/1900 00:00:003/20/1900 00:00:003/21/1900 00:00:003/22/1900 00:00:003/23/1900 00:00:003/24/1900 00:00:003/25/1900 00:00:003/26/1900 00:00:003/27/1900 00:00:003/28/1900 00:00:003/29/1900 00:00:003/30/1900 00:00:003/31/1900 00:00:004/1/1900 00:00:004/2/1900 00:00:004/3/1900 00:00:004/4/1900 00:00:004/5/1900 00:00:004/6/1900 00:00:004/7/1900 00:00:004/8/1900 00:00:004/9/1900 00:00:001 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 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               Interest Rate 10%
305                        Contribution to 100yr Ann Value
306                 Pmt # PV this PMT Ann Value
307                 0 100.00
308   Value of 25-Year Annuity:     \$907.70
Value of 50-Year Annuity:     \$991.48
Value of 100-Year Annuity:   \$999.93
Value of Perpetuity:           \$1,000.00
1 90.91 90.91 908.86
309                 2 82.64 173.55 826.22
310                 3 75.13 248.69 751.09
311                 4 68.30 316.99 682.79
312                 5 62.09 379.08 620.69
313                 6 56.45 435.53 564.25
314                 7 51.32 486.84 512.93
315                 8 46.65 533.49 466.28
316     Bars indicate PV of each payment. Sum of PVs from 0 to N = Value of the Annuity           9 42.41 575.90 423.87
317                 10 38.55 614.46 385.32
318                 11 35.05 649.51 350.27
319                 12 31.86 681.37 318.40
320                 13 28.97 710.34 289.44
321                 14 26.33 736.67 263.10
322                 15 23.94 760.61 239.16
323                 16 21.76 782.37 217.40
324                 17 19.78 802.16 197.62
325                 18 17.99 820.14 179.63
326                 19 16.35 836.49 163.28
327                 20 14.86 851.36 148.42
328 UNEVEN CASH FLOWS (Section 5-12)               21 13.51 864.87 134.90
329 An annuity has constant payments.  Although many financial decisions do involve annuities, many others involve uneven, or nonconstant, cash flows.  With a spreadsheet, the present value of a series of uneven cash flows (called the net present value) can be calculated easily.  The calculations can also be done easily with a calculator, but with a spreadsheet it's much easier to see what's happening, to check the inputs, and thus avoid errors.   22 12.28 877.15 122.62
330                 23 11.17 888.32 111.45
331  First, consider a security that pays \$100 at the end of the year for 5 years and a lump sum of \$1,000 at the end of 5 years when the interest rate is 12%.   24 10.15 898.47 101.30
332                 25 9.23 907.70 92.07
333  Summary of Uneven Cash Flow Present Value Calculations (Annuity plus Lump Sum).               26 8.39 916.09 83.68
334  This is the cash flow pattern for a typical bond.               27 7.63 923.72 76.05
335  Interest rate (I)       =   12%           28 6.93 930.66 69.12
336                  29 6.30 936.96 62.81
337  Periods: 0 1 2 3 4 5   30 5.73 942.69 57.08
338    | | | | | |   31 5.21 947.90 51.87
339  Annuity CFs: \$0 \$100 \$100 \$100 \$100 \$100   32 4.74 952.64 47.13
340  Lump sum CFs:           \$1,000   33 4.31 956.94 42.83
341  Total CFs: \$0 \$100 \$100 \$100 \$100 \$1,100   34 3.91 960.86 38.91
342    PV of CFs             35 3.56 964.42 35.36
343    \$89.29             36 3.23 967.65 32.12
344    79.72             37 2.94 970.59 29.18
345    71.18             38 2.67 973.27 26.51
346    63.55             39 2.43 975.70 24.08
347    624.17             40 2.21 977.91 21.87
348    \$927.90 = PV of cash flow stream = value of the asset           41 2.01 979.91 19.86
349  The time line shows what's happening with a step-by-step solution. To actually work the problem with a calculator, use the cash flow register. With Excel, use the NPV function. Could use the NPV dialog box.  Alternatively, type, just to the right of the fx,  =NPV(    see the formula, and make the cell entries by clicking on them.  This also works for the PV or any other function.   42 1.83 981.74 18.03
350 Excel Function Approach:                     Fixed inputs: PV = =PV(0.12,5,-100,-1000)   \$927.90   43 1.66 983.40 16.37
351  (Use PV or NPV functions)   Cell references PV = =PV(C335,G337,C339,G340,0)   \$927.90   44 1.51 984.91 14.86
352                        Fixed inputs: NPV = =NPV(0.12,100,100,100,100,1100)   \$927.90   45 1.37 986.28 13.49
353                   Cell references: NPV = =NPV(B335,C341:G341)   \$927.90   46 1.247 987.53 12.24
354  Now consider an irregular cash flow stream (where CFs can take on any value).   47 1.134 988.66 11.11
355                 48 1.031 989.69 10.08
356  Figure 5-4.  PV of an Uneven Cash Flow Stream               49 0.937 990.63 9.14
357                  50 0.852 991.48 8.29
358  Interest rate (I)        =   12%           51 0.774 992.26 7.52
359                  52 0.704 992.96 6.81
360  Periods: 0 1 2 3 4 5   53 0.640 993.60 6.17
361    | | | | | |   54 0.582 994.18 5.59
362  CF Time Line: \$0 \$100 \$300 \$300 \$300 \$500   55 0.529 994.71 5.06
363    PV of CFs             56 0.481 995.19 4.58
364    \$89.29             57 0.437 995.63 4.14
365    239.16             58 0.397 996.03 3.75
366    213.53             59 0.361 996.39 3.38
367    190.66             60 0.328 996.72 3.06
368    283.71             61 0.299 997.01 2.76
369    \$1,016.35 = PV of cash flow stream = value of the asset           62 0.271 997.29 2.49
370                  63 0.247 997.53 2.24
371                  64 0.224 997.76 2.02
372  Excel Function Approach:                     Fixed inputs: NPV = =NPV(0.12,100,300,300,300,500)   \$1,016.35   65 0.204 997.96 1.81
373                   Cell references: NPV = =NPV(B358,C362:G362)   \$1,016.35   66 0.185 998.15 1.63
374                  67 0.169 998.31 1.46
375  The NPV Excel formula ignores the initial cash flow (in Year 0).  When entering a cash flow range, Excel assumes that the first value entered occurs at the end of the first year.  If there is an initial cash flow, as we will see later, that cash flow must be separately added to the NPV formula result.  Notice too that you can enter cash flows one-by-one, or if the cash flows appear in consecutive cells, you can enter the cell range by highlighting it.   68 0.153 998.47 1.30
376                 69 0.139 998.61 1.165
377                  70 0.127 998.73 1.038
378  FUTURE VALUE OF AN UNEVEN CASH FLOW STREAM (Section 5-13)               71 0.115 998.85 0.923
379  We find the future value of uneven cash flow streams by compounding rather than discounting.  The step-by-step approach works the same as for PVs, but unfortunately, Excel does not have a net future value (NFV) function.  One way around this is to solve for the NPV and then find the FV of this amount by compounding it to the end of the cash flow stream.   72 0.105 998.95 0.819
380                 73 0.095 999.05 0.724
381  Figure 5-5.  FV of an Uneven Cash Flow Stream               74 0.086 999.14 0.637
382                  75 0.079 999.21 0.558
383  Interest rate (I)        =   12%           76 0.071 999.29 0.487
384                  77 0.065 999.35 0.422
385  Periods: 0 1 2 3 4 5   78 0.059 999.41 0.363
386    | | | | | |   79 0.054 999.46 0.309
387  CF Time Line: \$0 \$100 \$300 \$300 \$300 \$500   80 0.049 999.51 0.260
388              \$500.00   81 0.044 999.56 0.216
389              336.00   82 0.040 999.60 0.176
390              376.32   83 0.037 999.63 0.139
391              421.48   84 0.033 999.67 0.106
392              157.35   85 0.030 999.70 0.075
393              0.00   86 0.028 999.72 0.048
394      = PV of cash flow stream = value of the asset       \$1,791.15   87 0.025 999.75 0.023
395                  88 0.023 999.77 0.000
396  Excel Function Approach: (Some versions of Excel do not have a NFV function, requiring the procedure shown here.   89 0.021 999.79 -0.021
397          First find NPV: NPV = =NPV(B383,C387:G387)   \$1,016.35   90 0.019 999.81 -0.040
398      Then compound NPV for 5 years:   FV = =FV(B383,G385,,G397)   -\$1,791.15   91 0.017 999.83 -0.057
399                  92 0.016 999.84 -0.072
400  The FV result using the Excel formulas is a negative number.  This is because we used Excel's FV function and entered the NPV as a positive value as the PV. Put a minus sign in just after the equal sign to make the answer positive.   93 0.014 999.86 -0.086
401                 94 0.013 999.87 -0.099
402                  95 0.012 999.88 -0.111
403  SOLVING FOR I WITH UNEVEN CASH FLOWS (Section 5-14)               96 0.011 999.89 -0.121
404  Assume that an investment with the following positive cash flows has a cost of \$927.90.  Find the rate of return on this investment.   97 0.010 999.90 -0.131
405                 98 0.009 999.91 -0.140
406  Finding the Interest Rate, Annuity Plus Lump Sum (A Typical Bond)               99 0.008 999.92 -0.148
407                  100 0.007 999.93 -0.155
408  Annuity pmts \$100
409  Future lump sum \$1,000
410
411  Periods: 0 1 2 3 4 5
412    | | | | | |
413  CF Time Line: -\$927.90 \$100 \$100 \$100 \$100 \$1,100
414
415  Excel Function Approach:   Rate function RATE = =RATE(G411,B408,B413,B409)   12.00%
416  Excel Function Approach:   IRR function IRR =   =IRR(B413:G413)   12.00%
417  To use the Rate function, we need an annuity or an annuity with a lump sum.  The IRR function can be used for any set of uneven cash flows, provided the first one is negative (you need an investment to find the rate of return on the investment.) See the next example, where the IRR approach is required.
418
419  Finding the Interest Rate of an Uneven Cash Flow Stream with a Cost of \$1,000
420
421  Periods: 0 1 2 3 4 5
422    | | | | | |
423  CF Time Line: -\$1,000 \$100 \$300 \$300 \$300 \$500
424
425  Excel Function Approach:                Cell references: IRR =   =IRR(B423:G423)   12.55%
426
427  SEMIANNUAL AND OTHER COMPOUNDING PERIODS (Section 5-15)
428
429  Assume that you deposit \$100 in an account that pays 5% and leave it there for 10 years.  Here's what would happen under annual and semiannual compounding.  Notice that with semiannual compounding, there are twice as many periods, but the periodic interest rate is half the nominal rate.
430
431  ANNUAL COMPOUNDING   SEMIANNUAL COMPOUNDING
432 Periods/Year 1   2
433  N 10   20 = 2 �� 10
434  I 5%   2.5% = 5%/2
435  PV -\$100   -\$100
436  PMT 0   0
437  FV \$162.89   \$163.86
438
439    Additional interest = \$0.97
440
441  The future value under semiannual compounding, \$163.86, exceeds the FV under annual compounding, \$162.89, because interest starts accruing sooner and thus produces more interest on interest.
442
443  This can be further illustrated by considering the example of quarterly, monthly, and daily compounding.
444
445  QUARTERLY MONTHLY DAILY
446 N 40 N 120 N 3650
447  I 1.25% I 0.417% I 0.0137%
448  PV -\$100 PV -\$100 PV -\$100
449  PMT 0 PMT 0 PMT 0
450
451  FV \$164.36 FV \$164.70 FV \$164.87
452
453  Just as more frequent compounding increases the future value of a lump sum, it also decreases the present value of a lump sum.  Consider the example of \$100 to be received 10 years from now, with annual versus semiannual compounding and a 10% interest rate.
454
455  ANNUAL   SEMIANNUAL
456 N 10   N 20
457  I 5.0%   I 2.5%
458  PMT 0   PMT 0
459  FV \$100   FV \$100
460
461  PV -\$61.39   PV -\$61.03
462
463
464  COMPARING INTEREST RATES (Section 5-16)
465  Different compounding periods are used for different types of investments.  If we compare investments or loans with different compounding periods, we need to put them on a common basis.  Excel has a function that solves for the effective annual rate by using the following formula:
466
467
468
469
470  Using the Excel function, if the nominal rate is 10% with semiannual compounding, the effective annual rate is:
471
472  Nominal rate   10%
473  Periods per year   2
474
475  Effective rate: 10.25% =EFFECT(C472,C473)
476  Using formula: 10.25% =(1+C472/C473)^C473-1
477
478  FRACTIONAL TIME PERIODS (Section 5-17)
479  Bank loans are often for fractional periods.  Say you borrow \$100 on January 2, 2009 and plan to repay the loan on October 3, 2009.  The loan has a rate of 10% and is based on a 365-day year.  Interest is earned (charged) on a daily basis, and is compounded. How much will you have to pay when the loan matures?  Assume (a) simple interest and (b) compound interest.
480 Amount borrowed   \$100
481  Days in year   365
482  Nominal interest rate   10.00%
483  Rate per day. Fraction, not %   0.00027 =C482/C481
484  Number of days:   274
485  Simple Interest
486  Simple interest, cost per day:   \$0.02740 =C480*C483
487  Total interest:   \$7.51 =C486*C484
488  Ending amount:   \$107.51 =C480+C487
489
490  Compound Interest
491  Rate per day:   0.000273973 =C483
492  Ending amount   \$107.79 =C480*(1+C483)^C484
493
494  AMORTIZED LOANS (Section 5-18)
495  If a loan is to be repaid in equal amounts on a monthly, quarterly, or annual basis it is said to be an amortized loan.
496
497  Table 5-4 (shown below) illustrates the amortization process.  A homeowner borrows \$100,000 on a mortgage loan, and the loan is to be repaid in 5 equal payments at the end of each of the next 5 years.   The lender charges 6% on the balance at the beginning of each year.
498
499  First, we solve for the required payment, then we construct an amortization table.
500
501  N 5
502  I 6%
503  PV \$100,000
504  FV \$0
505  PMT =PMT(rate,nper,pv,[FV],[type]
506  PMT -\$23,739.64 =PMT(B502,B501,B503,B504)
507
508  Table 5-4.  Loan Amortization Schedule, \$100,000 at 6% for 5 Years
509  Amount borrowed:   \$100,000
510  Years:   5
511  Rate:   6%
512  PMT:   -\$23,739.64
513
514    Beginning     Repayment of Principal (4) Ending Balance (5)
515    Amount Payment Interesta Principalb Balance
516  Year (1) (2) (3) (4) (5)
517  1 \$100,000.00 \$23,739.64 \$6,000.00 \$17,739.64 \$82,260.36
518  2 \$82,260.36 \$23,739.64 \$4,935.62 \$18,804.02 \$63,456.34
519  3 \$63,456.34 \$23,739.64 \$3,807.38 \$19,932.26 \$43,524.08
520  4 \$43,524.08 \$23,739.64 \$2,611.44 \$21,128.20 \$22,395.89
521  5 \$22,395.89 \$23,739.64 \$1,343.75 \$22,395.89 \$0.00
522
523  a Interest in each period is calculated by multiplying the loan balance at the beginning of the year by the interest rate.  Therefore, interest in Year 1 is \$100,000(0.06) = \$6,000; in Year 2 it is \$4,935.62; and so on.
524 b Repayment of principal is equal to the payment of \$23,739.64 minus the interest charge for the year.
525
526          1/1/1900 00:00:001/1/1900 00:00:001/1/1900 00:00:001/1/1900 00:00:001 Periods Present Value r = 5% r = 10% r = 20% FV of Annuity 12/31/1899 00:00:0012/31/1899 00:00:0012/31/1899 00:00:00     1/1/1900 00:00:001/1/1900 00:00:001/1/1900 00:00:001/1/1900 00:00:001/1/1900 00:00:001 Years Future Value 15% 5% 0% -10% Effect of Actual Rate Earned on Future Values 12/31/1899 00:00:0012/31/1899 00:00:0012/31/1899 00:00:0012/31/1899 00:00:00   1/1/1900 00:00:001/1/1900 00:00:001/1/1900 00:00:001/1/1900 00:00:001 Years PV of \$1 0% Rate 5% Rate 20% rate Figure 2-2. Effects of Interest Rates and Time on the PV of \$1 12/31/1899 00:00:0012/31/1899 00:00:0012/31/1899 00:00:00

# 5-2

A B C D E F G H
1 SECTION 5-2
SOLUTIONS TO SELF-TEST QUESTIONS

2a.  What would the future value of \$100 be after 5 years at 10% compound interest?
5
N 5
I 10%
PV \$100
PMT \$0   FV  = \$161.05 =FV(B7,B6,B9,-B8)
10
11  2b.  At 10% simple interest?
12
13  N 5   Annual interest \$10.00
14  I 10%   Total interest \$50.00   = 5*10
15  PV \$100
16  PMT \$0   FV  = \$150.00
17
18  3a.  Suppose you currently have \$2,000 and plan to purchase a 3-year certificate of deposit (CD) that pays 4% interest compounded annually.  How much will you have when the CD matures?
19
20  N 3
21  I 4%
22  PV \$2,000
23  PMT \$0   FV  = \$2,249.73
24
25  3b.  How would your answer change if the interest rate were 5%, or 6%, or 20%?
26
27  Interest rate \$2,249.73
28  5% \$2,315.25
29  6% \$2,382.03
30  20% \$3,456.00
31
32  4.   A company��s sales in 2008 were \$100 million.  If sales grow at 8%, what will they be 10 years later, in 2018?
33
34  N 10
35  I 8%
36  PV (\$M) \$100
37  PMT \$0   FV (\$M) = \$215.89
38
39  5a.  How much would \$1, growing at 5% per year, be worth after 100 years?
40
41  N 100
42  I 5%
43  PV \$1
44  PMT \$0   FV  = \$131.50
45
46  5b.  What would FV be if the growth rate were 10%?
47
48  N 100
49  I 10%
50  PV \$1
51  PMT \$0   FV  = \$13,780.61

# 5-3

SECTION 5-3
SOLUTIONS TO SELF-TEST QUESTIONS

3a.  Suppose a U.S. government bond promises to pay \$2,249.73 three years from now.  If the going interest rate on 3-year government bonds is 4%, how much is the bond worth today?
5
N 3
I 4%
PMT \$0
FV \$2,250   PV  = \$2,000.00 =PV(B7,B6,B8,-B9)
10
11  3b.  How would your answer change if the bond matured in 5 rather than 3 years?
12
13  N 5
14  I 4%
15  PMT \$0
16  FV \$2,250   PV  = \$1,849.11 =PV(B14,B13,B15,-B16)
17
18  3c.  What if the interest rate on the 5-year bond was 6% rather than 4%?
19
20  N 5
21  I 6%
22  PMT \$0
23  FV \$2,250   PV  = \$1,681.13 =PV(B21,B20,B22,-B23)
24
25  4a.  How much would \$1,000,000 due in 100 years be worth today if the discount rate was 5%?
26
27  N 100
28  I 5%
29  PMT \$0
30  FV \$1,000,000   PV  = \$7,604.49 =PV(B28,B27,B29,-B30)
31
32  4b.  If the discount rate was 20%?
33
34  N 100
35  I 20%
36  PMT \$0
37  FV \$1,000,000   PV  = \$0.0121 =PV(B35,B34,B36,-B37)

# 5-4

SECTION 5.4
SOLUTIONS TO SELF-TEST QUESTIONS

1a.  The U.S. Treasury offers to sell you a bond for \$585.43.  No payments will be made until the bond matures 10 years from now, at which time it will be redeemed for \$1,000.  What interest rate would you earn if you bought this bond for \$585.43?
5
N 10
PMT \$0
PV \$585.43
FV \$1,000   I  = 5.50%
10
11  1b.  What rate would you earn if you could buy the bond for \$550?
12
13  N 10
14  PMT \$0
15  PV \$550.00
16  FV \$1,000   I  = 6.16%
17
18  1c.  For \$600?
19
20  N 10
21  PMT \$0
22  PV \$600.00
23  FV \$1,000   I  = 5.24%
24
25  2a.  Microsoft earned \$0.33 per share in 1997.  Ten years later, in 2007, it earned \$1.42.  What was the growth rate in Microsoft��s earnings per share (EPS) over the 10-year period?
26
27  N 10
28  PMT \$0
29  PV \$0.33
30  FV \$1.42   I  = 15.71%
31
32  2b.  If EPS in 2007 had been \$0.90 rather than \$1.42, what would the growth rate have been?
33
34  N 10
35  PMT \$0
36  PV \$0.33
37  FV \$0.90   I  = 10.55%

# 5-5

SECTION 5-5
SOLUTIONS TO SELF-TEST QUESTIONS

1a.  How long would it take \$1,000 to double if it were invested in a bank that pays 6% per year?
5
I 6%
PMT \$0
PV \$1,000
FV \$2,000   N = 11.90 =NPER(B6,B7,-B8,B9)
10
11  1b.  How long would it take if the rate was 10%?
12
13  I 10%
14  PMT \$0
15  PV \$1,000
16  FV \$2,000   N = 7.27 =NPER(B13,B14,-B15,B16)
17
18  2a.  Microsoft��s 2007 earnings per share were \$1.42, and its growth rate during the prior 10 years was 15.71% per year.  If that growth rate were maintained, how long would it take for Microsoft��s EPS to double?
19
20  I 15.71%
21  PMT \$0
22  PV \$1.42
23  FV \$2.84   N = 4.75 =NPER(B20,B21,-B22,B23)

# 5-7

SECTION 5-7
SOLUTIONS TO SELF-TEST QUESTIONS

1a.  For an ordinary annuity with 5 annual payments of \$100 and a 10% interest rate, how many years will the 1st payment earn interest, and what will this payment��s value be at the end?
5
N 5
I 10%   Years of interest   4
PMT -\$100
PV \$0   Val of Pmt \$146.41 =FV(B7,E7,,B8)
10
11  1b.  Answer this same question for the 5th payment.
12
13  N 5
14  I 10%   Years of interest   0
15  PMT -\$100
16  PV \$0   Val of Pmt \$100.00 =FV(B14,E14,,B15)
17
18  2a.  Assume that you plan to buy a condo 5 years from now, and you estimate that you can save \$2,500 per year.  You plan to deposit the money in a bank that pays 4% interest, and you will make the first deposit at the end of the year.  How much will you have after 5 years?
19
20  N 5
21  I 4%
22  PMT -\$2,500
23  PV \$0   FV \$13,540.81 =FV(B21,B20,B22,-B23)
24
25  2b.  How will your answer change if the interest rate were increased to 6%, or lowered to 3%?
26
27  N 5
28  I 6%
29  PMT -\$2,500
30  PV \$0   FV \$14,092.73 =FV(B28,B27,B29,-B30)
31
32  N 5
33  I 3%
34  PMT -\$2,500
35  PV \$0   FV \$13,272.84 =FV(B33,B32,B34,-B35)

# 5-8

SECTION 5-8
SOLUTIONS TO SELF-TEST QUESTIONS

3a.  Assume that you plan to buy a condo 5 years from now, and you need to save for a down payment.  You plan to save \$2,500 per year (with the first deposit made immediately) and you will deposit the funds in a bank account that pays 4% interest.  How much will you have after 5 years?
5
N 5
I 4%
PV \$0
PMT -\$2,500   FV \$14,082.44 =FV(B7,B6,B9,-B8,1)
10
11  2b  How much will you have if you made the deposits at the end of each year?
12
13  N 5
14  I 4%
15  PV \$0
16  PMT -\$2,500   FV \$13,540.81 =FV(B14,B13,B16,-B15)

# 5-9

SECTION 5-9
SOLUTIONS TO SELF-TEST QUESTIONS

3a.  What is the PVA of an ordinary annuity with 10 payments of \$100 if the appropriate interest rate is 10%?
5
N 10
I 10%
PMT -\$100
FV \$0   PV  = \$614.46 =PV(B7,B6,B8,-B9)
10
11  3b.  What would PVA be if the interest rate was 4%?
12
13  N 10
14  I 4%
15  PMT -\$100
16  FV \$0   PV  = \$811.09 =PV(B14,B13,B15,-B16)
17
18  3c.  What if the interest rate was 0%?
19
20  N 10
21  I 0%
22  PMT -\$100
23  FV \$0   PV  = \$1,000.00
24
25  3d.  How would the PVA values differ if we were dealing with annuities due?
26
27  Part a   Part b   Part c
28 N 10   N 10   N 10
29 I 10%   I 4%   I 0%
30 PMT -\$100   PMT -\$100   PMT -\$100
31 FV \$0   FV \$0   FV \$0
32 PV \$675.90   PV  = \$843.53   PV \$1,000.00
33   =PV(B29,B28,B30,-B31,1)     =PV(E29,E28,E30,-E31,1)     =PV(H29,H28,H30,-H31,1)
34 4a.  Assume that you are offered an annuity that pays \$100 at the end of each year for 10 years.  You could earn 8% on your money in other investments with equal risk.  What is the most you should pay for the annuity?
35
36  N 10
37  I 8%
38  PMT -\$100
39  FV \$0   PV  = \$671.01 =PV(B37,B36,B38,-B39)
40
41  4b.  If the payments began immediately, how much would the annuity be worth?
42
43  N 10
44  I 8%
45  PMT -\$100
46  FV \$0   PV  = \$724.69 =PV(B44,B43,B45,-B46,1)

# 5-10

SECTION 5-10
SOLUTIONS TO SELF-TEST QUESTIONS

1a.  Suppose you inherited \$100,000 and invested it at 7% per year.  How much could you withdraw at the end of each of the next 10 years?
5
N 10
I 7%
PV \$100,000
FV \$0   PMT  = \$14,237.75 =PMT(B7,B6,B8,B9)
10
11  1b.  How would your answer change if you made withdrawals at the beginning of each year?
12
13  N 10
14  I 7%
15  PV \$100,000
16  FV \$0   PMT  = \$13,306.31 =PMT(B14,B13,B15,B16,1)
17
18  2a.  If you had \$100,000 that was invested at 7% and you wanted to withdraw \$10,000 at the end of each year, how long would your funds last?
19
20  I 7.0%
21  PV \$100,000
22  PMT -\$10,000
23  FV \$0   N  = 17.8 =NPER(B20,B22,B21,B23)
24
25  2b.  How long would they last if you earned 0%?
26
27  I 0.0%
28  PV \$100,000
29  PMT -\$10,000
30  FV \$0   N  = 10.0 =NPER(B27,B29,B28,B30)
31
32  2c.  How long would they last if you earned the 7% but limited your withdrawals to \$7,000 per year?
33
34  I 7.0%
35  PV \$100,000       * This result means that with \$7,000 withdrawals, you would never exhaust the funds.
36 PMT -\$7,000
37 FV \$0   N  = #NUM!
38
39 3.    Your rich uncle named you as the beneficiary of his life insurance policy.  The insurance company gives you a choice of \$100,000 today or a 12-year annuity of \$12,000 at the end of each year.  What rate of return is the insurance company offering?
40
41  N 12
42  PMT \$12,000
43  PV \$100,000
44  FV \$0   I  = 6.11% =RATE(B41,B42,-B43,B44)
45
46  4a.  Assume that you just inherited an annuity that will pay you \$10,000 per year for 10 years, with the first payment being made today.  A friend of your mother offers to give you \$60,000 for the annuity.  If you sell it, what rate of return would your mother��s friend earn on his investment?
47
48  N 10
49  PMT -\$10,000
50  PV \$60,000
51  FV \$0   I  = 13.70% =RATE(B48,B49,B50,B51,1)
52
53  4b.  If you think a ��fair�� return would be 6%, how much should you ask for the annuity?
54
55  N 10
56  I 6%
57  PMT -\$10,000
58  FV \$0   PV \$78,016.92 =PV(B56,B55,B57,-B58,1)

# 5-11

SECTION 5-11
SOLUTIONS TO SELF-TEST QUESTIONS

1a.  What��s the present value of a perpetuity that pays \$1,000 per year, beginning one year from now, if the appropriate interest rate is 5%?
5
PMT \$1,000
I 5%   PV \$20,000 =B6/B7

1b.  What would the value be if payments on the annuity began immediately?
10
11  PMT \$1,000       **The perpetuity value formula values payments 1 through infinity.  If a payment is received immediately, it must be added to the formula result.
12 I 5%   PV \$21,000
13         =B11/B12+B11
14
15
16
17 2.  Would distant payments contribute more to the value of an annuity if interest rates were high or low?
18
19  PMT \$100
20  I 5%
21  I 20%
22
23  Value of 25th payment at 5%:     \$29.53
24  Value of 25th payment at 20%:     \$1.05
25
26  If we drew graphs like Figure 5-3, the one for 20% would show a much steeper decline and hit the zero line (just visually, not really because the PV would approach zero but not actually hit it, ever) much sooner.

# 5-12

A B C D E F G H I J K L
1 SECTION 5-12
SOLUTIONS TO SELF-TEST QUESTIONS

2a.  What��s the present value of a 5-year ordinary annuity of \$100 plus an additional \$500 at the end of Year 5 if the interest rate is 6%?
5
Interest rate 6%

Year 0 1 2 3 4 5
Ann Pmt \$0 \$100 \$100 \$100 \$100 \$100
10  Lump Sum           \$500
11  Total CFs \$0 \$100 \$100 \$100 \$100 \$600
12
13  NPV \$794.87 =NPV(B6,C11:G11)
14
15  2b.  What is the PV if the \$100 payments occur in Years 1 through 10 and the \$500 comes at the end of Year 10?
16
17  Interest rate 6%
18
19  Year 0 1 2 3 4 5 6 7 8 9 10
20 Ann Pmt \$0 \$100 \$100 \$100 \$100 \$100 \$100 \$100 \$100 \$100 \$100
21 Lump Sum                     \$500
22 Total CFs \$0 \$100 \$100 \$100 \$100 \$100 \$100 \$100 \$100 \$100 \$600
23
24  NPV \$1,015.21 =NPV(B17,C22:L22)
25
26  3a.  What��s the present value of the following uneven cash flow stream:  \$0 at Time 0, \$100 in Year 1 (or at Time 1), \$200 in Year 2, \$0 in Year 3, and \$400 in Year 4 if the interest rate is 8%?
27
28  Interest rate 8%
29
30  Year 0 1 2 3 4
31  CFs \$0 \$100 \$200 \$0 \$400
32
33  NPV \$558.07 =NPV(B28,C31:F31)

# 5-13

SECTION 5-13
SOLUTIONS TO SELF-TEST QUESTIONS

2.  What is the future value of this cash flow stream: \$100 at the end of 1 year, \$150 due after 2 years, and \$300 due after 3 years if the appropriate interest rate is 15%?
5
Interest rate 15%

Year 0 1 2 3
9 CFs \$0 \$100 \$150 \$300
10
11  FV of CFs \$0.00 \$132.25 \$172.50 \$300.00
12   =B9*(1+\$B\$6)^(\$E\$8-B8) =C9*(1+\$B\$6)^(\$E\$8-C8) etc. etc.
13
14
15  NFV \$604.75 =SUM(B11:E11)
16

# 5-14

A B C D E F G H
1 SECTION 5-14
SOLUTIONS TO SELF-TEST QUESTIONS

1.  An investment costs \$465 and is expected to produce cash flows of \$100 at the end of each of the next 4 years, then an extra lump sum payment of \$200 at the end of the 4th year.  What is the expected rate of return on this investment?
5
Interest rate 6%

Year 0 1 2 3 4
Ann Pmt -\$465 \$100 \$100 \$100 \$100
10  Lump Sum         \$200
11  Total CFs -\$465 \$100 \$100 \$100 \$300
12
13  NPV 9.05% =IRR(B11:G11)
14
15  2.  An investment costs \$465 and is expected to produce cash flows of \$100 at the end Year 1, \$200 at the end or Year 2, and \$300 at the end of Year 3.  What is the expected rate of return on this investment?
16
17  Year 0 1 2 3
18  CFs -\$465 \$100 \$200 \$300
19
20  IRR 11.71% =IRR(B18:E18)

# 5-15

SECTION 5-15
SOLUTIONS TO SELF-TEST QUESTIONS

2a.  What��s the future value of \$100 after 3 years if the appropriate interest rate is 8%, compounded annually?
5
N 3
I 8%
PV -\$100
PMT \$0   FV  = \$125.97   =FV(B7,B6,B9,B8)
10
11  2b.  Compounded monthly?
12
13  N 36
14  I 0.67%
15  PV -\$100
16  PMT \$0   FV  = \$127.02   =FV(B14,B13,B16,B15)
17
18  3a.  What��s the present value of \$100 due in 3 years if the appropriate interest rate is 8%, compounded annually?
19
20  N 3
21  I 8%
22  PMT \$0
23  FV \$100   PV = \$79.38   =PV(B21,B20,B22,-B23)
24
25  3b.  Compounded monthly?
26
27  N 36
28  I 1%
29  PMT \$0
30  FV \$100   PV = \$78.73   =PV(B28,B27,B29,-B30)

# 5-16

SECTION 5-16
SOLUTIONS TO SELF-TEST QUESTIONS

3.   By law, credit card issuers must print their annual percentage rate (APR) on their monthly statements.  A common APR is 18% with interest paid monthly.  What is the EFF% on such a loan?
5
Nominal rate 18%
Comp/year 12

Effective rate 19.56%   =EFFECT(B6,B7)
10    19.56%   =(1+B6/B7)^B7-1

# 5-17

SECTION 5-17
SOLUTIONS TO SELF-TEST QUESTIONS

1a.  Suppose a company borrowed \$1 million at a rate of 9%, simple interest, with interest paid at the end of each month.  The bank uses a 360-day year.  How much interest would the firm have to pay in a 30-day month?
5
Loan \$1,000,000
Interest rate 9%
Days/year 360
Interest pd (days) 30
10
11  Interest paid \$7,500   =B6*B7*(B9/B8)
12
13  1b. What would the interest be if the bank used a 365-day year?
14
15  Loan \$1,000,000
16  Interest rate 9%
17  Days/year 365
18  Interest pd (days) 30
19
20  Interest paid \$7,397.26   =B15*B16*(B18/B17)
21
22  2a.  Suppose you deposited \$1,000 in a credit union that pays 7% with daily compounding and a 365-day year.  What is the EFF%, and how much could you withdraw after 7 months, assuming this is 7/12 of a year?
23
24  Loan \$1,000
25  Interest rate 7%
26  Comp/year 365   Time period (months)   7
27
28  Effective rate 7.250098% Account value   \$1,041.67
29   7.25%     =B24*(1+B28)^(F26/12)
30    =(1+B25/B26)^B26-1

# 5-18

SECTION 5-18
SOLUTIONS TO SELF-TEST QUESTIONS

1.   Suppose you borrowed \$30,000 on a student loan at a rate of 8% and must repay it in 3 equal installments at the end of each of the next 3 years.  How large would your payments be, how much of the first payment would represent interest, how much would be principal, and what would your ending balance be after the first year?
5
N 3
I 8%
PV \$30,000
FV \$0
10
11  PMT -\$11,641.01   =PMT(B7,B6,B8,B9)
12
13  Loan Amortization Schedule, \$30,000 at 8% for 3 Years
14  Amount borrowed:   \$30,000
15  Years:   3
16  Rate:   8%
17  PMT:   -\$11,641.01
18
19    Beginning Amount (1) Payment      (2) Interest        (3) Repayment of Principal (4) Ending Balance (5)
20 Year
21 1 \$30,000.00 \$11,641.01 \$2,400.00 \$9,241.01 \$20,758.99
22 2 \$20,758.99 \$11,641.01 \$1,660.72 \$9,980.29 \$10,778.71
23 3 \$10,778.71 \$11,641.01 \$862.30 \$10,778.71 \$0.00
24
25 Rather than only present data for Year 1, it was just as easy to construct a full amortization schedule so you can see what it looks like and how easy it is to make.

Search more related documents:05 Chapter model