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. 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+i)" 5% 6% 7% 8% 9% 10% 11% 12% 1 0.95238 0.94340 0.93458 0.92593 0.91743 0.90909 0.90090 0.89286 2 0.90703 0.89000 0.87344 0.85734 0.84168 0.82645 0.81162 0.79719 30.86384 0.83962 0.81630 0.79383 0.77218 0.75131 0.73119 0.71178 4 0.82270 0.79209 0.76290 0.73503 0.70843 0.68301 0.65873 0.63552 5 0.78353 0.74726 0.71299 0.68058 0.64993 0.62092 0.59345 0.56743 6 0.74622 0.70496 0.66634 0.63017 0.59627 0.56447 0.53464 0.50663 7 0.71068 0.66506 0.62275 0.58349 0.54703 0.51316 0.48166 0.45235 8 0.67684 0.62741 0.58201 0.54027 0.50187 0.46651 0.43393 0.40388 9 0.64461 0.59190 0.54393 0.50025 0.46043 0.42410 0.39092 0.36061 10 0.61391 0.55839 0.50835 0.46319 0.42241 0.38554 0.35218 0.32197 1 1- Present Value of an annuity of $1 5% 6% 7% 8% 9% 10% 11% 12% 1 0.95238 0.94340 0.93458 0.92593 0.91743 0.90909 0.90090 0.89286 2 1.85941 1.83339 1.80802 1.78326 1.75911 1.73554 1.71252 1.69005 3 2.72325 2.67301 2.62432 2.57710 2.53129 2.48685 2.44371 2.40183 4 3.54595 3.46511 3.38721 3.31213 3.23972 3.16987 3.10245 3.03735 5 4.32948 4.21236 4.10020 3.99271 3.88965 3.79079 3.69590 3.60478 6 5.07569 4.91732 4.76654 4.62288 4.48592 4.35526 4.23054 4.11141 7 5.78637 5.58238 5.38929 5.20637 5.03295 4.86842 4.71220 4.56376 8 6.46321 6.20979 5.97130 5.74664 5.53482 5.33493 5.14612 4.96764 9 7.10782 6.80169 6.51523 6.24689 5.99525 5.75902 5.53705 5.32825 10 7.72173 7.36009 7.02358 6.71008 6.41766 6.14457 5.88923 5.65022 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. 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+i)" 5% 6% 7% 8% 9% 10% 11% 12% 1 0.95238 0.94340 0.93458 0.92593 0.91743 0.90909 0.90090 0.89286 2 0.90703 0.89000 0.87344 0.85734 0.84168 0.82645 0.81162 0.79719 30.86384 0.83962 0.81630 0.79383 0.77218 0.75131 0.73119 0.71178 4 0.82270 0.79209 0.76290 0.73503 0.70843 0.68301 0.65873 0.63552 5 0.78353 0.74726 0.71299 0.68058 0.64993 0.62092 0.59345 0.56743 6 0.74622 0.70496 0.66634 0.63017 0.59627 0.56447 0.53464 0.50663 7 0.71068 0.66506 0.62275 0.58349 0.54703 0.51316 0.48166 0.45235 8 0.67684 0.62741 0.58201 0.54027 0.50187 0.46651 0.43393 0.40388 9 0.64461 0.59190 0.54393 0.50025 0.46043 0.42410 0.39092 0.36061 10 0.61391 0.55839 0.50835 0.46319 0.42241 0.38554 0.35218 0.32197 1 1- Present Value of an annuity of $1 5% 6% 7% 8% 9% 10% 11% 12% 1 0.95238 0.94340 0.93458 0.92593 0.91743 0.90909 0.90090 0.89286 2 1.85941 1.83339 1.80802 1.78326 1.75911 1.73554 1.71252 1.69005 3 2.72325 2.67301 2.62432 2.57710 2.53129 2.48685 2.44371 2.40183 4 3.54595 3.46511 3.38721 3.31213 3.23972 3.16987 3.10245 3.03735 5 4.32948 4.21236 4.10020 3.99271 3.88965 3.79079 3.69590 3.60478 6 5.07569 4.91732 4.76654 4.62288 4.48592 4.35526 4.23054 4.11141 7 5.78637 5.58238 5.38929 5.20637 5.03295 4.86842 4.71220 4.56376 8 6.46321 6.20979 5.97130 5.74664 5.53482 5.33493 5.14612 4.96764 9 7.10782 6.80169 6.51523 6.24689 5.99525 5.75902 5.53705 5.32825 10 7.72173 7.36009 7.02358 6.71008 6.41766 6.14457 5.88923 5.65022