A B D E F G 1 2 Problem 4-37 3 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. 4 5 uc 7 $ $ S 350,000 50,000 300,000 8 House price Down payment Loan amount Number of periods Interest rate Annual payment 9 30 7% 10 11 S 23,500 12 13 14 PV of payments Loan - PV of payments Balloon payment 15 16 17 Requirements 1 In cell D13, by using cell references, calculate 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.). 2 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.)