A B C D E F G

1 05problem 9/15/2021 1:34 1/29/2021

2
3 Chapter 5. Solution to End-of-Chapter Comprehensive/Spreadsheet Problem
4 Problem 5-41
5
6 a. Find the FV of $1,000 invested to earn 10% after 5 years. Answer this question by using a
7 math formula and also by using the Excel function wizard.
8
9 Inputs: PV = $1,000
10 I = 10%
11 N = 5
12 Formula: FV = PV(1+I)^N = $1,610.51
13 Wizard (FV): $1,610.51
14
15 Note: When you use the wizard and fill in the menu items, the result is the formula you see on the
16 formula line if you put the pointer on cell E13. Put the pointer on E13 and then click the function
17 wizard (fx) to see the completed menu. Finally, it is generally easiest to fill in the wizard menus by
18 clicking on one of the menu slots to activate the cursor in that slot and then clicking on the input cell
19 where the item is given. Then, hit the tab key to move down to the next menu slot.
20
21 Experiment by changing the input values to see how quickly the output values change.
22
23 b. Now create a table that shows the FV at 0%, 5%, and 20% for 0, 1, 2, 3, 4, and 5 years. Then
24 create a graph with years on the horizontal axis and FV on the vertical axis to display your results.
25
26 Begin by typing in the row and column labels as shown below. We could fill in the table by inserting
27 formulas in all the cells, but a better way is to use an Excel data table as described in 05model. We
28 used the data table procedure. Note that the Row Input Cell is D10 and the Column Input Cell is D11,
29 and we set Cell B33 equal to Cell E12. Then, we selected (highlighted) the range B33:E39, then
30 select Data tab > What-If Analysis > Data Table, and filled in the menu items to complete the table.
31
32 Years (D11) Interest Rate (D10)
33 $ 1,610.51 0% 5% 20%
34 0
35 1
36 2
37 3
38 4
39 5
40
41 To create the graph, first select the range C34:E39. Then click Insert tab > Scatter. Then follow the
42 menu. It is easy to make a chart, but a lot of detailed steps are involved to format it so that it's
43 "pretty." Pretty charts are generally not necessary to get the picture, though.
44 We put the chart right on the spreadsheet so we could see how changes
45 in the data lead to changes in the graph.
46
47
A B C D E F G
48 Note that the inputs to the data table, hence to the graph, are now in the row and column heads. Change
49 the 20% in Cell E33 to 0.3 (or 30%), then to 0.4, then to 0.5, etc., to see how the table and the chart
50 change.
51
52 Future Value FV as Function of Time and Rate
53 $12
54 $10
55
$8 0%
56
5%
57 $6
20%
58 $4
59
$2
60
61 $0
62 0 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5
Years
63
64
65 c. Find the PV of $1,000 due in 5 years if the discount rate is 10%. Again, work the problem with
66 a formula and also by using the function wizard.
67 Inputs: FV = $1,000
68 I = 10%
69 N = 5
70 Formula: PV = FV/(1+I)^N = $ 620.92
71 Wizard (PV): $ 620.92
72
73 Note: In the wizard's menu, use zero for Pmt because there are no periodic payments. Also, set the FV
74 with a negative sign so that the PV will appear as a positive number.
75
76 d. A security has a cost of $1,000 and will return $2,000 after 5 years. What rate of return does the
77 security provide?
78
79 Inputs: PV = -$1,000
80 FV = $2,000
81 I = ?
82 N = 5
83 Wizard (Rate): 14.87%
84
85 Note: Use zero for Pmt since there are no periodic payments. Note that the PV is given a negative sign
86 because it is an outflow (cost to buy the security). Also, note that you must scroll down the menu to
87 complete the inputs.
88
89 e. Suppose California’s population is 40 million people, and its population is expected to grow by 2%
90 annually. How long will it take for the population to double?
91
92 Inputs: PV = -40
93 FV = 80
94 I = growth rate 2%
95 N = ?
96 Wizard (NPER): 35.00 = Years to double.
A B C D E F G
97
98 f. Find the PV of an ordinary annuity that pays $1,000 each of the next 5 years if the interest rate
99 is 15%. Then find the FV of that same annuity.
100
101 Inputs: PMT $ (1,000)
102 N 5
103 I 15%
104
105 PV: Use function wizard (PV) PV = $3,352.16
106
107 FV: Use function wizard (FV) FV = $6,742.38
108
109 g. How will the PV and FV of the annuity change if it is an annuity due rather than an ordinary
110 annuity?
111
112 For the PV, each payment would be received one period sooner, hence would be discounted back one
113 less year. This would make the PV larger. We can find the PV of the annuity due by finding the PV of
114 an ordinary annuity and then multiplying it by (1 + I).
115
116 PV annuity due = $3,352.16 × 1.15 = $3,854.98
117
118 Exactly the same adjustment is made to find the FV of the annuity due.
119
120 FV annuity due = $6,742.38 × 1.15 = $7,753.74
121
122 h. What will the FV and the PV for problems a and c be if the interest rate is 10% with
123 semiannual compounding rather than 10% with annual compounding?
124
125 Part a. FV with semiannual compounding: Orig. Inputs: New Inputs:
126 Inputs: PV = $1,000 $1,000
127 I = 10% 5%
128 N = 5 10
129 Formula: FV = PV(1+I)^N = $ 1,610.51 $ 1,628.89
130 Wizard (FV): $ 1,610.51 $ 1,628.89
131
132 Part c. PV with semiannual compounding: Orig. Inputs: New Inputs:
133 Inputs: FV = $1,000 $1,000
134 I = 10% 5%
135 N = 5 10
136 Formula: PV = FV/(1+I)^N = $ 620.92 $ 613.91
137 Wizard (PV): $ 620.92 $ 613.91
138
A B C D E F G
139 i. Find the annual payments for an ordinary annuity and an annuity due for 10 years with a PV of $1,000
140 and an interest rate of 8%.
141
142 Inputs: N 10
143 I 8%
144 PV -$1,000
145 PMT: Use function wizard (PMT) PMT = $149.03
146
147 PMT (Due): Use function wizard (PMT) PMT = $137.99
148
149 j. Find the PV and the FV of an investment that makes the following end-of-year payments. The
150 interest rate is 8%.
151
152 Year Payment
153 1 100
154 2 200
155 3 400
156
157 Rate = 8%
158
159 To find the PV, use the NPV function: PV = $581.59
160
161 Excel does not have a function for the sum of the future values for a set of uneven payments. Therefore,
162 we must find this FV by some other method. Probably the easiest procedure is to simply compound each
163 payment, then sum them, as is done below. Note that since the payments are received at the end of each
164 year, the first payment is compounded for 2 years, the second for 1 year, and the third for 0 years.
165
166 Year Payment x (1 + I )^(N – t) = FV
167 1 100 1.1664 116.64
168 2 200 1.0800 216.00
169 3 400 1.0000 400.00
170
171 Sum of FV's = 732.64
172
An alternative procedure for finding the FV would be to find the PV of the series using the
173
NPV function, then compound that amount for 3 years at 8%, as is done below:
174
175 PV = $581.59
176 FV of PV = $732.64
177
A B C D E F G
178 k. 5 banks offer nominal rates of 6%, but differ in their compounding frequency.
179 A = annually; B = semiannually; C = quarterly; D = monthly; and E = daily.
180
181 I NOM 6%
182 Deposit $5,000
183 (1) A B C D
184 (i) EAR 6.00% 6.09% 6.14% 6.17%
185 (ii) Deposit $5,000. What is FV1? $5,300 $5,305 $5,307 $5,308
186 (iii) Deposit $5,000. What is FV2? $5,618 $5,628 $5,632 $5,636
187
188 (2) Would they be equally able to attract funds? No. People would prefer more compounding to less.
189 (i) What nominal rate would cause all banks to provide same EAR as Bank A?
190 A B C D
191 I NOM 6.00% 5.91% 5.87% 5.84%
192
193 Each of these nominal rates based on the frequency of compounding will provide an EAR of 6%.
194
195 (3) You need $5,000 at the end of the year. How much do you need to deposit annually for A,
196 semiannually, for B, etc. beginning today, to have $5,000 at the end of the year?
197 A B C D
198 PMT $4,716.98 $2,391.31 $1,204.16 $403.32
199
200 (4) Even if the banks provided the same EAR, would a rational investor be indifferent between the
201 banks? Probably not. An investor would probably prefer the bank that compounded more
202 frequently.
203
204 l. Suppose you borrow $15,000. The interest rate is 8%, and it requires 4 equal
205 end-of-year payments. Set up an amortization schedule that shows the annual
206 payments, interest payments, principal repayments, and beginning and ending
207 loan balances.
208
A B C D E F G
209 Original amount of mortgage: $15,000
210 Term to maturity: 4
211 Interest rate: 8%
212
213 Annual payment (use PMT function): ($4,528.81)
214
215 Beginning Ending
216 Year Balance Payment Interest Principal Balance
217 1 $ 15,000.00 $ 4,528.81 $ 1,200.00 $3,328.81 $ 11,671.19
218 2 $ 11,671.19 $ 4,528.81 $ 933.70 $3,595.12 $ 8,076.07
219 3 $ 8,076.07 $ 4,528.81 $ 646.09 $3,882.73 $ 4,193.34
220 4 $ 4,193.34 $ 4,528.81 $ 335.47 $4,193.34 $ -
221
222 Extension: (i) Create a graph that shows how the payments are divided between interest and
223 principal repayment over time.
224
225 Breakdown of payments
226
227
228 $5,000.00
229 $4,000.00
230
$3,000.00
231
232 $2,000.00 Principal

233 Interest
$1,000.00
234
235 $-
236 1 2 3 4
237 Years
238
239
240 Go back to Cells D210 and D211, and change the term to maturity and the interest
241 rate to see how the payments would change.
H
1 1/29/2021
2
dsheet Problem
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
isplay your results.
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
H
olumn heads. Change
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
nts. Also, set the
73FV
74
75
76
77
78
79
80
81
82
83
84
iven a negative 85
sign
ll down the menu86 to
87
88
89
90
91
92
93
94
95
96
H
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
by finding the PV
113of
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
H
ars with a PV of
139$1,000
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
ayments. Therefore,
161
mply compound 162each
ed at the end of163
each
164
165
166
167
168
169
170
171
172

173

174
175
176
177
H
178
179
180
181
182
183 E
184 6.18%
185 $5,309
186 $5,637
187
188
189
190 E
191 5.83%
192
193
194
195
196
197 E
198 $13.29
199
200
201
202
203
204
205
206
207
208