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. IFI 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 accountWithout making any deposits or withdraws, today the account balance is $4,997.50. What rate of interest was earned? Part 2 - 15 point On the second tab, Satchmo's Restaurant, post the cash flows below on 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 will be calculated below that summary. Answer the questions on the tab in the yellow boxes Satchmo's Restaurant is considering the purchase of a new equipment. The equipment will cost $85,000 to purchase and will generate $12,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. 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%? Part 3 - 5 points In this part I have provided you with a loan amortization schedule for a 30-year fixed rate mortgage. I'll ask you to provide some answers in the yellow boxes to the right just because I want you to at least look at a home 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 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? Provide your answers in the yellow boxes to the right 3. Again, a budgeted payment rounding up from the minimum payment, but suppose the interest rates have increased alarmingly to 10% (your budgeted payment may rise with this higher interest rate - in my case the budgeted payment was $1,000 for part 2, but the payment for 10% was greater than $1,000 so I had to use a $2,000 budgeted payment). How many months until the loan is paid off with the 10% rate of interest? Provide your answer in the yellow boxes to the right B D Year 1 E Year 3 Year 2 1 G Year 5 Year lear 3 Year 6 Year 2 Year 4 Year 4 Year 7 2 + + + + + + H 4 S. 6 Cashflow 1 Cashflow 2 Cashflows Cashflow 4 Total Cashflow 7 8 9 10 11 12 13 14 15 16 Question 1: What is the net present value at al hurdle rate of 9962 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Internal Rate of Retur: $ $ $ S $ S S Question 2: Assuming the shutdown for maintenance in year 5, what is the net present value at a hurdle rate of 9%? UNUMI 13 19 20 21 esthome WE Erre doupament Amout and 5 Soon Pidgebed Part Irrest Payment Pop $ $ 8 5 $ 3 5 The price of the home There were four downpayment Multed in the current norista payoff with budget S 3 S 5 5 5 5 5 Beginning 1 Peme! 5 Payment 2 5 Damer S Payment 5 mets 5 5 Pamat? Payment S Payment 5 met 105 Pamatis watt) warmers Payment 14 S Payment 155 P10 5 met $ Paymer 18 5 Part 2 3 5 5 1 5 5 $ $ 5 Amorth pool with buted payment and $ 5 5 $ 5 $ 5 5 S 5 3 5 5 9 5. 5 5 3 S Vand VAS
