Microsoft Excel is a widely used spreadsheet program that can use formulas to compute and display values. When your small business is taking out a loan, you need to know how much of each loan payment goes toward paying off the principal and how much goes toward interest. In addition, computing the amortization of the loan lets you know how much of the loan has been paid off at any point in time. When you use Excel, you can make changes to the amount borrowed, the interest rate and the payment period and immediately see how the loan would change.
Enter "Original Balance" in cell A1, "Interest Rate (as a percentage)" in cell A2, "Term (in years)" in cell A3 and "Monthly Payment" in cell A4.
Enter the corresponding values in cells B1 through B3. In cell B4, enter the formula "=-PMT(B2/1200,B3*12,B1)" to have Excel automatically calculate the monthly payment.
For example, if you had a $25,000 loan at 6.5 percent annual interest for 10 years, the monthly payment would be $283.87.
Enter "Payment" in cell A6, "Interest Portion" in cell B6, "Principal Portion" in cell C6 and "New Balance" in cell D6.
Enter "=$B$4" in cell A7 to have Excel automatically transfer the monthly payment calculated in cell B4. The "$" makes sure that when the cell is copied and pasted for future payment periods in the following steps, Excel will continue to use the amount in cell B4 as the monthly payment.
Enter "=ROUND(B1*B$2/1200,2)" in cell B7 to calculate the interest portion of the first month's payment. In this example, Excel would display $135.42.
Enter "=A7-B7" in cell C7 to calculate the principal portion of the payment. In this example, Excel would display $148.45.
Enter "=B1-C7" in cell D7 to calculate the new balance of the loan. Continuing the example, Excel would display $24,851.55.
Copy and paste the values from A7 and C7 to A8 and C8. In cell B8, enter "=ROUND(D7*B$2/1200,2)" and in cell D8 enter "=D7-C8" to calculate the values for the second payment period.
In this example, row eight would read $283.87 in A8, $134.61 in B8, $149.26 in C8 and $24,702.29 in D8.
Copy and paste row eight into row nine to calculate the values for the third payment period. Repeat this step for each payment period until the loan is paid off.
- computer image by Hao Wang from Fotolia.com