There are many websites and software products out there to help you track your loans, but they often come with a price tag and are overly complicated. If you are looking for a simpler solution with the software you already have on your computer then look no further than Excel. Microsoft's Excel has built-in formulas for financial calculations. All you have to do is input pertinent information and you are ready to go.
Step 1
Open a blank Excel spreadsheet file. Write "Loan Amount:" in cell A1 (omit the quotation marks here and throughout), "Interest Rate:" in cell A2, "# of Months:" in cell A3 and "Monthly Payment:" in cell A4. Highlight and bold the text to make them stand out.
Video of the Day
Step 2
Fill in the corresponding values in cells B1, B2 and B3. In B1 input the total loan amount, in B2 write the interest rate (omit the percentage sign) and in B3 write the number of months it will take you to pay off the loan. In cell B4, write "=PMT(B2/12/100, B3, B1)" in order to calculate your monthly payment.
Step 3
Select columns B, C, D and E and press "Ctrl-1" on your keyboard to access the "Format Cells" dialog box. Click on the "Accounting" category and make sure your decimal places are set to two and the symbol box is set to "None." This will format your cells properly for calculations. Click "OK" to save your changes.
Step 4
Write the following headings in row six (horizontally) one after the other: "Payment #," "Start Balance," "Interest for Month," "Payment" and "End balance."
Step 5
Fill in row seven with the beginning values. Write "1" in A7 for the first payment, "=B1" in B7 for the starting balance, "=B7*($B$2/12)/100" in C7 for the interest in the first month, "=$B$4" in D7 for your monthly payment and "=B7+C7+D7" in E7 to calculate the month's ending balance.
Step 6
Write the equations that recalculate your loan amounts each month in row eight. Write "=A7+1" in cell A8, "=E7" in B8, "=B8*($B$2/12)/100" in C8, "=$B$4" in D8 and "=B8+C8+D8" in E8.
Step 7
Highlight row eight from A8 to E8. Click on the fill handle (the little black box in the bottom right corner of your selection) and drag it down as many months as you have payments. For example, if you have a two-year loan, drag it down 24 rows to represent the 24 monthly payments.
Tip
When entering your specific number values, do not add a percent sign or a dollar sign. Additionally, Excel offers templates to help you analyze more complicated loan scenarios, such as if you want to make extra payments or your loan requires a balloon payment. Click "File," "New" then type "Loan Amortization" in the "Office.com Templates" search field to download templates.
Video of the Day