Home > 05 Chapter model

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
Download Document:05 Chapter model

Set Home | Add to Favorites

All Rights Reserved Powered by Free Document Search and Download

Copyright © 2011
This site does not host pdf,doc,ppt,xls,rtf,txt files all document are the property of their respective owners. complaint#nuokui.com
TOP