CCE 170 - Final Exam KEY

Fall Semester 2021

29. (excel) It is a widely known fact that making even a small extra monthly payment on a loan can dramatically reduce the time required to pay off the loan. The following spreadsheet is used to analyze how long it takes to pay off a mortgage by paying an extra amount each month. The annual interest rate is shown in cell B4. The total time required to pay off the loan assuming no extra payment is made (the loan term) is shown in cell B6. The loan amount is shown in cell B8.

The objective of this exercise is to first compute the required monthly payment in cell B9. This will then be referenced as the Base Pmt in column A. A set of values representing an extra payment added to the base monthly payment are listed in column B. The total payment will be computed in column C and the time required to pay off the loan using the total payment will be computed in column D and converted to a yearly value in column E. We will then plot the payoff time vs. extra payment.

Click here to download a copy of this spreadsheet.

Click here to download a copy of the solution.

Note: Do NOT name any of the cells in this spreadsheet. Also, please note that this code does not require any VB code. You should solve the problem entirely using Excel formulas. You should NOT change the suffix of the file from *.xlsx to *.xlsm. There is no need to do so and it messes up the grading system.

(a). (1 pt) Enter a formula in cell B5 to convert the yearly rate to a monthly rate by dividing by 12.

(b). (1 pt) Enter a formula in cell B7 to find the number of months in the term by multiplying by 12.

(c). (2 pts) Enter a forumula in cell B9 that uses the PMT function in Excel to compute the required monthly payment. Note that the interest rate and number of periods passed to the function should be monthly values. Put a minus sign in front of the function so that it returns the payment as a positive value.

(d). (1 pt) Enter a formula in cells A12:A32 that references the payment value in cell B9. Note that all cells in the range will show the same payment. This represents the base monthly payment.

(e). (1 pt) Enter a formula in cells C12:C32 that computes the total monthly payment as the sum of the base payment and the extra payment.

(f). (3.5 pts) Enter a formula in cells D12:D32 that computes the number of months to pay off the loan using the total payment in column C using the NPER function in Excel. Put a minus sign in front of the payment value you pass in as the second argument to the function since the function expects a negative number. You don't need to pass the last two arguments to the NPER function. As is the case with the PMT function, the pv argument represents the loan amount.

(g). (1 pt) Enter a formula in cells E12:E32 that converts the number of months in column D to years.

(h). (3 pts) Create a chart that plots the number of years (vertical axis) vs. the extra payment amount (horizontal axis). Use the XY Scatter type. Remove the legend and put in a set of titles for the plot and the X and Y axes. Put the plot just to the right of your table.

(i). (3 pts) Copy the contents of row 32 down to row 35 using the Copy and Paste commands. Use the Goal Seek tool on this row to find the extra payment (cell B35) required to pay the loan off in exactly 20 years (cell E35). Do not modify row 32!

(Upload instructions and links went here)