Amortization Project Assignment: 1. Develop an Excel spreadsheet that shows the amortization schedule to pay off a mortgage loan of $350,000 over 20 years at an interest rate of 8.5% annual rate, payable monthly. Include the principal portion repaid monthly, the interest portion paid monthly, the total monthly payment and the remaining loan balance after each payment. See table 64 on page 258 in the Lasher text ( 8s edition) for an example. 2. Calculate the periodic, effective, and nominal interest rates on this loan. Include this information (including the formulas) near the top of the spreadsheet. 3. What is the unpaid balance after 10 years (halfway through the loan)? 4. What is the total amount of interest paid over the entire life (20 years) of the loan? 5. What is the total amount of money (in nominal dollars) needed to repay the loan? Scenario Analyses For each of the following scenarios, begin with the base case described above, then change only the items mentioned in that scenario. (These questions can be answered with just the number of months or years and months; there is no need to write a single sentence.) a) If the interest rate were 5.5%, but the monthly payment remained the same, how long would it take to pay off the loan? What would be the difference in the total amount of interest paid? b) If the loan period were 10 years instead of 20 years, what would be the difference in the monthly payment? What would be the difference in the total amount of interest paid? c) How soon would the loan be paid off in the borrower increased the monthly mortgage payment by $200 every month? What would be the difference in the total amount of interest paid? This is a group project. You are to turn in the Excel file containing your spreadsheet(s). Turn in one spreadsheet file per group. Include everyone's name on the email and in the excel file. Identify your answers; do not expect me to look for them or guess at them. Sketch a rough graph for question #1 of dollars vs. time. Include a line for the loan balance, payments, principal payments, and interest payments over the life of the loan. This is 10% of the grade on this project. What happens to the proportion of principal and interest in each payment over the course of the loan? Set-up Hints 1. Construct a data box at the top of your spreadsheet so that the variables are easy to change. Put your variables in blue making them easier to identify. Once your spreadsheet is running, just copy that spreadsheet over into another tab in the file and run your next scenario. Only slight modifications will then be required. 2. Adjust the final payment to fully pay off the loan and bring the balance to zero (not a negative number) at the end of your loan periods. 3. Your spreadsheet should be laid out in an easy-to-read format. FORMAT vour spreadsheet. Dollars should be dollars. Percentages should be percentages. Months should be whole numbers. Information should be properly labeled and rounded to the appropriate decimal place. 4. Number or date the time periods