Home | Membership | Archives | Training | Mission |

## Creating an Excel Loan Payoff SheetIn previous articles we explored the Excel payment function (pmt) and the use of absolute references in formulas. Here is a useful spreadsheet that uses both. Let's assume that you purchased a new house in January of 1998 with the first payment on February 1. You borrowed $250,000 at 5.9% for 30 years and have been making payments on the first of the month for the last 10 years. How much do you owe the bank on February 1 of this year? This spreadsheet will give you the loan payoff balance for any month, for any loan of any amount and any interest rate. It assumes monthly payments but you can adjust for different periods. First, here is the basic setup of the spreadsheet: The payment function is =pmt(c2/12,d2*12,-b2). Note that the interest rate had to be divided by 12 to calculate monthly interest and that the term of the loan had to be multiplied by 12 to correctly calculate the total number of payments. The minus sign in front of the loan amount (principal) makes Excel return a positive result rather than the negative liability figure that is the default. Our task is to write the formulas necessary to initialize the first payment and then to complete the necessary formulas for each successive row. ## The First RowThe first row is different from all the others because it gets the initial balance from cell B2. After this row, every successive row will get its starting balance from the ending balance on the previous row. The formula for cell B5 is simply =B2. That puts the beginning loan balance into cell B5. By doing it this way, when we change the loan amount in cell B5, the entire spreadsheet will recalculate with the new amount. Now it gets just a bit more complex. To calculate the interest paid for the first month, we need to multiply the starting balance by the monthly interest rate. Here's the formula: =b5*c2/12. Note that we had to divide the interest rate by 12. The rate is written as the annual interest rate. We needed the interest rate for one month. Here is a picture of what we have so far: To calculate the principal paid, we can simply subtract the interest paid from the payment. That formula will be: =E2-C5. The Ending balance then can be calculated by subtracting the principal paid from the starting balance for this payment: =B5-D5. Here is the complete row of formulas and the results.
It appears that we have finished the first row, but we have not. Only the starting balance formula is different from the formulas on successive rows. Using absolute references, we can adjust the interest paid and principal paid formulas to work on all rows. The ending balance formula is ready for the rest of the spreadsheet. Notice that we need the interest rate for every item in the interest paid column. Since the interest rate is always at cell C2, we can adjust the row 5 interest paid formula to read =B5*$C$2/12. Note that when we copy this formula down the column that the B5 will become B6, B7, B8, etc. It will refer to the starting balance for each payment. The C2 will continue to be C2 because it is locked by the dollar signs. By doing that, the formula will always refer to the interest rate at cell C2. That allows us to change C2 and automatically adjust the entire sheet to a new interest rate. Now, we will do the same thing with the principal paid formula. The payment is always at cell E2. By putting dollar signs before the E and the 2, the formula will always refer to that cell to find the payment amount. Here's what we have so far and the results. Note that the amounts didn't change when we used the absolute references. ## Subsequent RowsAll that remains is to complete row 6 and copy it down as far as we want. The starting balance for the second payment is the ending balance for the first payment. The formua is simply: =E5. When we copy that down, the row number will adjust appropriately for each successive row. Since we adjusted the other three formulas, you may copy them down to row 6 now. Here are the resulting formulas and numbers: You may now copy the row 6 formulas down the spreadsheet using the fill handle. Our original quest was to find the balance on February 1 of 2008. Let's see what we find. Our balance before the February 1 payment is $208,652.81. After the payment it is $208,195.85. One more note. To create the dates in column A you can use the autofill function. All you have to do is type the first two dates in A5 and A6. Then select both dates and use the fill handle to increment down the column for as many rows as you like. But... Autofill is a different topic for a different time. ## Written by Steven Tuttle - sdt@unh.edu |