One of my favorite uses for Microsoft Excel is to make a financially tracking spreadsheet. With this I track all my expenses by hand. Yes there are services out there like Mint that will do this for you, and I really like Mint, but Mint doesn’t have everything I want or need. One of them is a loan payoff calculator. We have shown you a really cool iPad app that will show you a nice graphical and table format for your loan payoff. If you don’t have an iPad but have a computer with Excel, here is a way to calculate loan payoff in Excel 2010. You can also use Excel 2007 as well.
Calculate Loan Payoff in Excel
First thing you need to do is open Excel. You can do this by going to the Start Search and typing in Excel, or going to the Programs folder under Microsoft Office. After you have it open Enter the headings in Loan Amount, Interest Rate, Term of Loans Months and Payment in B2, C2, D2, and E2. In the cell below each one but the correct values except for the E2, which is the Payment value, unless you already know what it is. Then enter Payment Date, Starting Balance, Interest Paid, Principal Paid, and Ending Balance in B4, C4, D4, and E4. After A4 enter in the frequencies of the payments. For me I’m going to do this for months since this is typically by months so I will be skipping by ever month on the 16th. Your spreadsheet should look this:
If you didn’t enter in E2 we can use the payment function to calculate this. Since we are using months in this spreadsheet we will be dividing the interest rate by 12 to calculate the monthly interest and multiply the term of the loan by 12 as well to correctly calculate the total number of payments. We will also add a minus sign to the Loan Amount in order to return a positive result rather than a negative liability figure which is the default. The formula will look like this: =PMT(C2/12,D2*12,-B2). Enter this into E2 like below:
The next step is filling about row 5. Since this is the first row we will need to make sure to set this one up special. First in B5 enter the formula =B2 just like below.
Next in C5 enter in the formula =B5*$C$2/12. This formula will calculate the interest paid for the first month. We are still dividing the interest rate by 12 since the rate is an annual interest rate and we need it for just one month. This is how it should look like:
Next up is the actual Principal Paid, or D5. All we have to do for this is subtract the interest paid from the payment. That is down with this formula =$E$2-C5. It will look like this:
Last field in this row the Ending Balance, or E5. All we need to do is subtract the Starting Balance minus the Principal Paid or =B5-DB. It will look like this.
That’s it for the first row. Your spreadsheet should look like this:
Now for the other rows. Starting in B6 we will want to enter in the previous Ending Balance or E5. Enter in =E5 in B6 just like the picture below:
Then select C5 through E5 and click and drag the little box in the bottom right hand corner to row 6 just like the picture below:
The formulas will drag and auto calculate the new values in row 6. Highlight B6 through E6 and click and drag the little box to the right to the end of your spreadsheet to let autocomplete fill in the blanks.
Your spreadsheet will look like this now. Make sure to use autocomplete function, or the little box in the bottom of cell, to complete the entire term of the loan.
The best part about this spreadsheet is that you can quickly change the Loan Amount, Interest Rate, Term of Loan Months and the Payments at the top, row 2, without having to redo all the formulas in the entire spreadsheet.
Give it a try! If you have any question or comments please share them below or post them in our excel how to forum
Comments are closed.