Problem 4-37 You are thinking of purchasing a house. The house costs $350,000. You have $50,000 in cash that you can use as a down payment on the house, but you need to borrow the rest of the purchase price. The bank is offering a 30-year mortgage that requires annual payments and has an interest rate of 7% per year. You can afford to pay only $23,500 per year. The bank agrees to allow you to pay this amount each year, yet still borrow $300,000. At the end of the mortgage (in 30 years), you must make a balloon payment; that is, you must repay the remaining balance on the mortgage. How much will this balloon payment be? Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. $ House price Down payment Loan amount Number of periods Interest rate Annual payment 350,000 50,000 300,000 30 $ 7% $ 23,500 PV of payments Loan - PV of payments Balloon payment 18 Requirements 1 In cell D13, by using cell references, calculate the present value of the 19 payments that you will make every year. Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 pt.). In cell D14, by using cell references, calculate the difference between the original loan and the present value of the payments that you will make every year. Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 pt.). 3 In cell D15, by using cell references, calculate the balloon payment to pay off the mortgage at the end of the loan. Note: The output of the expression or function you typed in this cell is expected as a positive number. (1 pt.). When you purchased your car, you took out a five-year annual payment loan with an interest rate of 6% per year. The annual payment on the car is $5.000 You have just made a payment and have now decided to pay of the loan by repaying the outstanding balance. What is the payot amount for the following scenarios? Complete the steps below using cell references to given data or previous calculations. In some cases a simple cell reference is all you need to copy paste a for CTOSS a row or down a color an t e el reference or a mixed cell reference may be preferred a specific Excel function is to be on the directions will specify the use of that function. Do not type in merical data into a cell or function. Instead, make a reference to the cell in which the data is found Make your competations on the blue cells highlighted below in alles unless otherwise directed, use the earliest appearance of the data in your formulas usually the Given Data section a. You have owned the car for one year (so there are four years left on the loan b. You have owned the car for four years (so there is one year left on the loan). $ 5.000 Annual payment Interest rate Number of periods a. You have owned the car for one year (so there are four years left on the loun)? Number of periods 4 Payale amount b. You have owned the car for four years (so there is one year left on the loan)? Number of periods 1 Payoff amount uirements 1 In cell D16, by using cell references, calculate the payoff amount under the first scenario (1 pr.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 2 In cell D22, by using cell references, calculate the payoff amount under the second scenario (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number