Excel Lab 3: Financial Mathematics Open a blank Excel sheet and put your name in cell A1 of each worksheet. Problem 1 (Sheet 1) In this problem we will investigate monthly payments on a loan by using the PMT formula. This is NOT an amortization table. We will see how the length of time taken to repay a loan will influence the monthly payment by generating a table showing the length of the loan (called the term) in years and the corresponding monthly payments. Please note that this is NOT showing the payment changing as you pay off a single loan but comparing 26 different loans for the same amount but lasting for different numbers of years. 1. Type the following: A3: Amount Borrowed B3: $100,000 A4: Rate B4: 8% A6: Time (yrs) B6: Monthly Payments A7:5 2. In cell B7 use the PMT function to calculate the monthly payment for a $100,000 loan for the time shown in cell A7. Click on B7. Type the PMT formula using cell references for APR, Y, and P and typing 12 for the n value. When you put in the reference for y, you want the reference to change when you drag the formula down, as it does by default; in the next row we will be looking at a six-year loan, not a five- year loan. But the APR and the loan amount don't change. We want the cell references to stay as B3 and B4 when we drag the formula. You can do this by typing the references as $B$3 and $B$4. The $ symbol tells Excel not to adjust the reference when you drag or copy the formula. This is called "anchoring." 3. In column A count from 5 to 30 starting with A7. Drag the formula from B7 to generate a table which shows what the monthly payments would be for all these different loan lengths. 4. Make a scatter plot of these data points. Be certain to title and label your graph! 5. Answer the following questions in your spreadsheet where your instructor can easily find them: a. Describe the curve from the graph. b. Explain what this graph tells us in the context of this problem. C. Calculate the total price of a house with a $100,000 mortgage at 8% for 15 years. Use Excel. d. Repeat part c, except with a 30-year mortgage. e. What is the difference in price