+1(978)310-4246 credencewriters@gmail.com
  

Description

Complete the exercises that are in attached file below

Each part is a tab on the spreadsheet. You can use the tables provided, a financial calculator, or (preferably) Excel functions

ACCT 211
Spring, 2021
Individual Learning project for Chapter 13
Each part is a tab on the spreadsheet. You can use the tables provided, a financial
calculator, or (preferably) Excel functions
PV and PVA: Each question counts 1 point for a total of five points. Provide your answer in
the yellow spaces provided on the first tab of the Excel workbook.
1. How much must be invested today at 8% so as to have $5,000 in four years?
2. Today you have $10,000 in an account that pays 10%. At the end of each of the next
five years you intend to withdraw an equal amount so that the account is closed at
the end of the five years. What is the amount of each withdrawal?
3. I borrow $300,000 today at 11% and make payments of $96,697.77 at the end of
each year. How many years will it take to repay the loan?
4. If I borrow $250,000 today and agree to make payments of $40,259 per year for
each of the next 8 years, what rate of interest am I paying?
5. Nine years ago, I invested $2,500 in an account. Without making any deposits or
withdraws, today the account balance is $4,997.50. What rate of interest was
earned?
Part 2 – 10 point
On the second tab, Satchmo’s Restaurant, post the cash flows, negative and positive, in the
appropriate yellow cells below the time line. Note that each year’s cashflow will be totaled
and a summary is given below year 1. Additionally, the internal rate of return and the net
present value will be calculated below that summary. Answer the questions on the tab in
the blue boxes
Satchmo’s Restaurant is considering the purchase of a new equipment. The equipment will
cost $85,000 to purchase and will generate $15,000 of additional cash inflows per year for
the next 7 years. The machine will have a salvage value of $25,000 at the end of the 7 years.
1. Assuming a hurdle rate of 9%, calculate the net present value of the machine.
2. What is the Internal Rate of return?
3. Assume that in year 5 the machine is expected to need a major overhaul at a cost of
$5,000. Additionally, cash flows in year five will be reduced to $8,000.
a. What is the net present value of the investment at a hurdle rate of 9%?
b. What is the internal rate of return?
Part 3 – 5 points (just leave some evidence that you did some of this)
In this part I have provided you with a loan amortization schedule for a 30-year fixed rate
mortgage. The loan amortization schedule is based on your inputs to the blue boxes in the
upper left-hand corner
In the upper left hand corner of the third tab, insert a price for a new house under price of
home (you choose the price). Provide a current annual interest rate for a 30-year fixed rate
mortgage (use Google!). Provide a % down payment, at least 20%. The spreadsheet will
then give you the amount financed and a monthly payment.
1. Copy the monthly payment into the budgeted payment. Scroll down the 360
payments in the table and notice that the loan will be paid off after 360 months
(there may be a slight rounding error of a dollar or two).
2. Round the budgeted monthly payment up to the nearest $1,000 (even if the
payment is say, $1,100, rounding up will be $2,000). In how many months will the
mortgage be paid off?
3. Again, a budgeted payment rounding up from the minimum payment, but suppose
the interest rates have increased alarmingly to 10%. Again, using a payoff strategy
of rounding up to the nearest $1,000, how many months until the loan is paid off?
Keep this spreadsheet. You may want to revise it to your liking and have it on your laptop
when you’re buying a house!
Present value of $1
1
(1 + i )n
5%
0.95238
0.90703
0.86384
0.82270
0.78353
0.74622
0.71068
0.67684
0.64461
0.61391
6%
0.94340
0.89000
0.83962
0.79209
0.74726
0.70496
0.66506
0.62741
0.59190
0.55839
1
2
3
4
5
6
7
8
9
10
7%
0.93458
0.87344
0.81630
0.76290
0.71299
0.66634
0.62275
0.58201
0.54393
0.50835
8%
0.92593
0.85734
0.79383
0.73503
0.68058
0.63017
0.58349
0.54027
0.50025
0.46319
1Present Value of an annuity of $1
1
2
3
4
5
6
7
8
9
10
5%
0.95238
1.85941
2.72325
3.54595
4.32948
5.07569
5.78637
6.46321
7.10782
7.72173
6%
0.94340
1.83339
2.67301
3.46511
4.21236
4.91732
5.58238
6.20979
6.80169
7.36009
7%
0.93458
1.80802
2.62432
3.38721
4.10020
4.76654
5.38929
5.97130
6.51523
7.02358
9%
0.91743
0.84168
0.77218
0.70843
0.64993
0.59627
0.54703
0.50187
0.46043
0.42241
10%
0.90909
0.82645
0.75131
0.68301
0.62092
0.56447
0.51316
0.46651
0.42410
0.38554
11%
0.90090
0.81162
0.73119
0.65873
0.59345
0.53464
0.48166
0.43393
0.39092
0.35218
12%
0.89286
0.79719
0.71178
0.63552
0.56743
0.50663
0.45235
0.40388
0.36061
0.32197
9%
0.91743
1.75911
2.53129
3.23972
3.88965
4.48592
5.03295
5.53482
5.99525
6.41766
10%
0.90909
1.73554
2.48685
3.16987
3.79079
4.35526
4.86842
5.33493
5.75902
6.14457
11%
0.90090
1.71252
2.44371
3.10245
3.69590
4.23054
4.71220
5.14612
5.53705
5.88923
12%
0.89286
1.69005
2.40183
3.03735
3.60478
4.11141
4.56376
4.96764
5.32825
5.65022
1
(1 + i )n
i
8%
0.92593
1.78326
2.57710
3.31213
3.99271
4.62288
5.20637
5.74664
6.24689
6.71008
Question 1:
How much must be invested today at 8% so as to have $5,000 in
four years?
Question 2:
Today you have $10,000 in an account that pays 10%. At the
end of each of the next five years you intend to withdraw an
equal amount so that the account is closed at the end of the five
years. What is the amount of each withdrawal?
Question 3:
I borrow $300,000 today at 11% and make payments of
$96,697.77 at the end of each year. How many years will it take
to repay the loan?
Question 4:
If I borrow $250,000 today and agree to make payments of
$40,259 per year for each of the next 8 years, what rate of
interest am I paying?
Question 5:
Nine years ago, I invested $2,500 in an account. Without
making any deposits or withdraws, today the account balance is
$4,997.50. What rate of interest was earned?
Year 0
Cashflow 1 – Initial Investment
Cashflow 2 – Additional Cashflow/year
Cashflow3 – Salvage Value
Cashflow 4 – other
Cashflow 5 – other
Total Cashflow $
Initial Investment
Net cashflow – Year 1
Net cashflow – Year 2
Net cashflow – Year 3
Net cashflow – Year 4
Net cashflow – Year 5
Net cashflow – Year 6
Net cashflow – Year 7
Internal Rate of Return:
Net Present Value
Year 1

$
$
$
$
$
$
$
$
#NUM!
$

$
Year 2

$

Question 1: What is the net present va
at a hurdle rate of 9%?
Question 2 : What is the internal rate o
return?
Question 3a: Assuming the shutdown
maintenance in year 5, what is the net
present value at a hurdle rate of 9%?
Question 3b: Assuming the shutdown
maintenance in year 5, what is the inter
rate of return?
Year 3
$
Year 4

$
Year 5

stion 1: What is the net present value
hurdle rate of 9%?
stion 2 : What is the internal rate of
rn?
stion 3a: Assuming the shutdown for
ntenance in year 5, what is the net
ent value at a hurdle rate of 9%?
stion 3b: Assuming the shutdown for
ntenance in year 5, what is the internal
of return?
$
Year 6

$
Year 7

$

30-year fixed rate
Price of home
Annual Interest rate
% downpayment
Amount Financed
Monthly Payment
Budgeted Payment
Year 1
$
$,00/month
year 2
year 3
Year 4
Payment 1
Payment 2
payment 3
Payment 4
Payment 5
Payment 6
Payment 7
Payment 8
Payment 9
Payment 10
Payment 11
Payment 12
Payment 13
Payment 14
Payment 15
Payment 16
Payment 17
Payment 18
Payment 19
Payment 20
Payment 21
Payment 22
Payment 23
Payment 24
Payment 25
Payment 26
Payment 27
Payment 28
Payment 29
Payment 30
Payment 31
Payment 32
Payment 33
Payment 34
Payment 35
Payment 36
Payment 37
Payment 38
Payment 39
Payment 40
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Beginning
Principal

Year 5
Year 6
Year 7
Payment 41
Payment 42
Payment 43
Payment 44
Payment 45
Payment 46
Payment 47
Payment 48
Payment 49
Payment 50
Payment 51
Payment 52
Payment 53
Payment 54
Payment 55
Payment 56
Payment 57
Payment 58
Payment 59
Payment 60
Payment 61
Payment 62
Payment 63
Payment 64
Payment 65
Payment 66
Payment 67
Payment 68
Payment 69
Payment 70
Payment 71
Payment 72
Payment 73
Payment 74
Payment 75
Payment 76
Payment 77
Payment 78
Payment 79
Payment 80
Payment 81
Payment 82
Payment 83
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Year 8
Year 9
Year 10
Year 11
Payment 84
Payment 85
Payment 86
Payment 87
Payment 88
Payment 89
Payment 90
Payment 91
Payment 92
Payment 93
Payment 94
Payment 95
Payment 96
Payment 97
Payment 98
Payment 99
Payment 100
Payment 101
Payment 102
Payment 103
Payment 104
Payment 105
Payment 106
Payment 107
Payment 108
Payment 109
Payment 110
Payment 111
Payment 112
Payment 113
Payment 114
Payment 115
Payment 116
Payment 117
Payment 118
Payment 119
Payment 120
Payment 121
Payment 122
Payment 123
Payment 124
Payment 125
Payment 126
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Year 12
Year 13
Year 14
Year 15
Payment 127
Payment 128
Payment 129
Payment 130
Payment 131
Payment 132
Payment 133
Payment 134
Payment 135
Payment 136
Payment 137
Payment 138
Payment 139
Payment 140
Payment 141
Payment 142
Payment 143
Payment 144
Payment 145
Payment 146
Payment 147
Payment 148
Payment 149
Payment 150
Payment 151
Payment 152
Payment 153
Payment 154
Payment 155
Payment 156
Payment 157
Payment 158
Payment 159
Payment 160
Payment 161
Payment 162
Payment 163
Payment 164
Payment 165
Payment 166
Payment 167
Payment 168
Payment 169
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Year 16
Year 17
Year 18
Payment 170
Payment 171
Payment 172
Payment 173
Payment 174
Payment 175
Payment 176
Payment 177
Payment 178
Payment 179
Payment 180
Payment 181
Payment 182
Payment 183
Payment 184
Payment 185
Payment 186
Payment 187
Payment 188
Payment 189
Payment 190
Payment 191
Payment 192
Payment 193
Payment 194
Payment 195
Payment 196
Payment 197
Payment 198
Payment 199
Payment 200
Payment 201
Payment 202
Payment 203
Payment 204
Payment 205
Payment 206
Payment 207
Payment 208
Payment 209
Payment 210
Payment 211
Payment 212
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Year 19
Year 20
Year 21
Year 22
Payment 213
Payment 214
Payment 215
Payment 216
Payment 217
Payment 218
Payment 219
Payment 220
Payment 221
Payment 222
Payment 223
Payment 224
Payment 225
Payment 226
Payment 227
Payment 228
Payment 229
Payment 230
Payment 231
Payment 232
Payment 233
Payment 234
Payment 235
Payment 236
Payment 237
Payment 238
Payment 239
Payment 240
Payment 241
Payment 242
Payment 243
Payment 244
Payment 245
Payment 246
Payment 247
Payment 248
Payment 249
Payment 250
Payment 251
Payment 252
Payment 253
Payment 254
Payment 255
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Year 23
Year 24
Year 25
Payment 256
Payment 257
Payment 258
Payment 259
Payment 260
Payment 261
Payment 262
Payment 263
Payment 264
Payment 265
Payment 266
Payment 267
Payment 268
Payment 269
Payment 270
Payment 271
Payment 272
Payment 273
Payment 274
Payment 275
Payment 276
Payment 277
Payment 278
Payment 279
Payment 280
Payment 281
Payment 282
Payment 283
Payment 284
Payment 285
Payment 286
Payment 287
Payment 288
Payment 289
Payment 290
Payment 291
Payment 292
Payment 293
Payment 294
Payment 295
Payment 296
Payment 297
Payment 298
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Year 26
Year 27
Year 28
Year 29
Payment 299
Payment 300
Payment 301
Payment 302
Payment 303
Payment 304
Payment 305
Payment 306
Payment 307
Payment 308
Payment 309
Payment 310
Payment 311
Payment 312
Payment 313
Payment 314
Payment 315
Payment 316
Payment 317
Payment 318
Payment 319
Payment 320
Payment 321
Payment 322
Payment 323
Payment 324
Payment 325
Payment 326
Payment 327
Payment 328
Payment 329
Payment 330
Payment 331
Payment 332
Payment 333
Payment 334
Payment 335
Payment 336
Payment 337
Payment 338
Payment 339
Payment 340
Payment 341
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Year 30
Payment 342
Payment 343
Payment 344
Payment 345
Payment 346
Payment 347
Payment 348
Payment 349
Payment 350
Payment 351
Payment 352
Payment 353
Payment 354
Payment 355
Payment 356
Payment 357
Payment 358
Payment 359
Payment 360
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

30-year fixed rate mortgage
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Payment

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Payment of
Principal

Ending Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Purchase answer to see full
attachment

  
error: Content is protected !!