4

Subtask 1b (5 Marks)

  1. Click on the Loans sheet. This has some basic data for five people applying for loans: name, monthly repayment, and loan term in years. Column G contains a shorter term that the borrower is wondering about in order to be free of debt sooner.
    Row 8 contains the fixed annual interest rate that they will pay.

  2. First, neaten up the first row of headings - bold, columns wider, and a background of Orange, Accent 6, Lighter 80%.

  3. We need to compute the amount the amount (the principal) that each borrower could pay off in the specified number of years with monthly payments at the specified interest Rate. The formula is

    Principal = Repayment*(1–(1+Rate)(–nPeriods))/Rate

    where nPeriods = 12 * Term (years).

  4. Each borrower may have a different term, so a different value for nPeriods. Insert an extra column to the left of the Principal column, and use it to compute the number of Periods. The column header should be Periods. To insert a column into a sheet already populated with data, click in the column to the right of the place where you want the new column, go to the Cells group of the Home tab, click on the Insert arrow, move down to "Insert sheet columns", and click. Enter the new column header and fix the formatting. Now enter the appropriate formula into D2 to compute the number of payment periods (there are 12 a year and the number of years is recorded for each borrower). Hand-check the calculation for the first borrower (i.e. cell D2). Copy the calculation down column D. Save the workbook.

  5. For convenience, type "interest rate p.m." per month in row 9, and compute the value (as =B8/12).

  6. Click in cell E2, and enter the formula for the Principal (given above) into this cell. Press enter, and check the result. Always check the result! It should be 380493 (plus a fractional part). For this and other monetary amounts, format the amount as Currency with 0 decimal places and use commas to divide the numbers into 3s. To do this, go to the Number group of the Home tab. Experiment with it until you have achieved your goal, and in particular the Principal amount for E2 should be $380,493 with no decimal places to follow.

  7. Copy the Principal calculation down the column.

  8. How much will the borrower pay over the term of the loan? Compute this and put it in column F. The answer for F2 should be $900,000. Copy down column F. Save the workbook.

  9. If the borrowers pay off the debt over a shorter term, they will pay more per month but of course for less months. Compute for them how much in total they will pay if they pay off their loan over the shorter term specified in column G. To do this, you need to calculate their new repayment. You can do this using Excel's built-in PMT function. Please have a look on how the PMT function is used in slides 34 and 35 in the lecture notes week- 1c-Spreadsheets.pdf. Select it rather as you did with VLOOKUP in subtask 3a, but this time type PMT in the search window in the Insert function dialogue box. Double-click on PMT in the resulting list. You will get a Function Arguments dialogue box, with slots for Rate (monthly interest rate), NPer (number of payment periods, this time from column G), and Pv (Principal value) plus two optional parameters that we ignore. The principal value is the one that you calculated in column E.

  10. In column I, compute the new total paid (i.e. new repayment * new number of periods). For borrower Smith, this should be $778,093.

  11. The borrowers save money by having a shorter term (because they pay less interest). Compute the difference between the Total Paid (column F) and the New Total Paid (column I). This goes in column J: the column header should be Saving. It's a new column, but because column J is blank when you start this calculation, you don't need to insert a column as you did for the "Periods" calculation. For Smith, the difference should be $121,907.

  12. Make sure columns H, I and J are formatted as Currency, like columns B, E and F, but this time format them with 2 decimal places. Make column J the same background colour as H and I. Save the workbook.

  13. That's it for subtask 1b, and for the week 1 lab. Don't forget to show your work to your demonstrator for marking before you go, otherwise you won't get the marks!