The basic formula for calculating an outstanding balance is to take the original balance and subtract payments made. Interest charges complicate the equation for mortgages and other loans, though. Because some of your loan payments are applied toward interest rather than principal, you must create an amortization table to calculate the outstanding balance on a loan.
Calculate Outstanding Balance
With traditional loans, some of your payment is applied to interest charges and the rest goes to principal repayment. Interest comprises a larger portion of your monthly payment at the beginning of the loan than at the end. That's because, on these loans, the interest payment is equal to your interest rate multiplied by the outstanding loan balance.
An amortization table allows you to calculate how much of each payment is applied toward the principal rather than interest payments. To create an amortization table and calculate your outstanding balance, follow these steps:
Set up the Loan Data
On a paper or in a spreadsheet program, list pertinent details for your loan, such as monthly interest rate, payment amount and original loan balance. For example, your list might read:
- Original loan balance = $600,000
- Monthly payment amount = $500
- Interest rate each month = 0.4 percent
To calculate your monthly interest rate, divide your annual interest rate by the number of payments you make each year. For example, if your loan has a 5 percent interest rate and you make payments once a month, your interest rate is 5 percent divided by 12, or 0.4 percent.
Create the Amortization Table
- Create five columns for your amortization table. Label them Payment number, Payment amount, Interest payment, Principal payment and Outstanding balance.
- Directly below Payment number, write the number 0 in the first row
- Directly below Outstanding balance, write the original loan balance in the first row. In this example, it would be $600,000.
Record the First Payment
- In the Payment number column, write the number 1 in the row below Payment 0.
- In the same row of the Payment amount column, write your monthly payment amount. In this example, it would be $500.
- In the same row of the Interest payment column, multiply the interest rate by the outstanding balance before this payment to determine the interest portion of the payment. In this example, it would be the previous balance of $600,000 multiplied by 0.0004, or $240.
- Subtract the interest payment amount from the total payment amount to find the Principal payment for this row. In this example, it's $500 minus $240, or $260.
- In the same row of the Outstanding balance column, subtract the principal repayment from the previous balance to calculate the new outstanding balance. In this example, the new outstanding balance would be $600,000 minus $260, or $599,740.
Record Subsequent Payments and Find Outstanding Balance on the Loan
- In the Payment number column, continue labeling payment numbers for as many payments as you've made. For example, if you're two years into your loan and you make payments once a month, you've made 24 payments.
- Repeat the process you performed for the first payment for each subsequent payment you've made. The figure listed in the Outstanding balance column in the row of your most recent payment is the current outstanding balance on the loan.