Calculating a loan's payoff date is sometimes challenging depending on the method you use, but knowing how to calculate the payoff date of your loan under various payment scenarios gives you the ability to adjust your payments and your budget more effectively. For example, you can budget for slightly more than your minimum payment each month and know how soon the loan will end. Alternatively, you can evaluate how one-time payments affect the term of your loan.
Calculating Loan Payoff Dates Using Online Resources
Refer to your loan paperwork to determine the existing principal amount, the current interest rate, and your current monthly payment amount. You can also call your loan provider to get exact numbers.
Use an online calculator (see Resources) to determine your loan payoff date. Enter your current balance and interest rate. You will also enter when you want to pay off your loan. The calculator will return the amount you will need to pay monthly to do so. You can manipulate this by putting in the amount you wish to pay each month and then finding the new payoff date.
Download the loan amortization spreadsheet (see Resources) if you would like a more detailed tool to calculate loan payoff dates. This spreadsheet has you first input your current loan information, including the balance and the interest rate, and then has cells where you can input either additional monthly payments or one-time payments. It will then adjust your payoff date accordingly.
Creating Your Own Loan Payoff Spreadsheet
Open a new spreadsheet and enter the following values in the given cells: in cell B1, "Loan Amount"; in cell C1, "Interest Rate"; in cell D1, "Term of Loan"; in cell E1, "Payment." Note that you are inputting the words, not the numeric values of your loan. These are your headings. Underneath these headings, place the numeric values that are accurate for your loan. For example, if your loan is for $100,000, you would put that number in cell B2. Your interest rate goes in cell C2, your loan term in D2, and your current payment amount in cell E2.
Create your spreadsheet format by placing the following values in the given cells: in cell A4, "Payment Date"; in cell B4, "Starting Balance"; in cell C4, "Interest Paid"; in cell D4, "Principal Paid"; in cell E4, "Ending Balance." These are again the words, not the numeric values. In cell A5, put the first month and year of your payment. For example, you might put, "January 2013" if your first payment is in January 2013.
Set the formulas for the first row of calculations. The first row is not the same as future rows and needs setting before setting the rest of the spreadsheet. The starting balance for the first month is the same as the amount of the loan so the formula for cell B5 is "=B2." The interest paid is a monthly interest rate, so we calculate the amount of the yearly interest we had in cell C2 divided by 12 months, and multiply that by the initial monthly payment. Therefore, the formula for C5 is "=E2*C2/12." To calculate the principal paid, we subtract the amount of interest paid from the total payment amount. The formula for D5 is "=E2-C5." Finally, we calculate the new ending balance of our loan by subtracting the principal paid from the previous balance. The formula for E5 is "B5-D5."
Set the formulas for the rest of the spreadsheet. The formulas for columns C and D need to be changed to absolute values. Creating absolute values in a spreadsheet allows the spreadsheet to adjust the amounts to work in successive rows. To make these columns absolute values, change them to the following values. For C5, change the formula to "=B5*$C$2/12." For D5, change the formula to "=$E$2-C5."
Change the formula for B6 to "E5" as opposed to "B2" because the new starting balance is not the initial loan amount. Copy and paste the remaining formulas into row 6. Copy and paste this final row 6 formula for as many months as you have payments. The values of each cell are adjustable, allowing you to calculate the effect of a higher monthly payment.
- The easiest way to calculate your loan payoff date is using an online calculator.
- No home calculation is likely to be exactly as the loan company calculates, due to daily interest calculations. For an exact number, contact your loan provider directly.
- Stockbyte/Stockbyte/Getty Images