Loan Calculation – Building An Amortization Table in Excel
Knowing how to build an amortization table will give you a good handle on your monthly payment for a loan and how much you will pay in interest over the course of your loan. I use amortization tables a lot in both business and in my personal life. For business, I usually use it to determine a monthly payment or determine the actual interest rate of a loan. Often, a loan will include a monthly processing fee or a service fee upfront – really just another form of interest, but if you are comparing two loans, you need to know what your true cost of capital is. In personal use, I use an amortization table to determine what my mortgage interest is for the purposes of calculating my estimated taxes. I also use it for determining what the payment will be on a car loan based on different loan terms, for instance.
Information you need:
- Loan amount (example: $200,000)
- Interest rate (example: 6%)
- Loan term in months (for this example, we are saying 36 months)
- Open Excel, in cell A-1, type ‘Interest.’
- In cell B-1, type your annual interest rate.
- In cell A-2, type ‘Term’, in B-2, type ‘Payment’, in C-2, type ‘Interest’, in D-2, type ‘Principal’, in E-2, type ‘Outstanding’.
- In cell E-3, type your total loan amount.
- In cell A-4, type ‘1’.
- In cell A-5, type ‘=A4+1’.
- Copy and paste into cells in the A column below A-5 until you get A-39 (or so that the number in the last cell equals the number of months of your loan).
- In cell B-4, type a reasonable number for your payment, 1,000 for every $100,000 in borrowed money will work fine.
- In cell C-4, type “=E3*$B$1/12”.
- In cell D-4, type “=B4-C4”.
- In cell E-4, type “=E3-D4”.
- In cell B-5, type “=B4”.
- Copy cells C-4 through E-4 into cells C-5 through E-5.
- Copy cells B-5 through E-5, and paste them in every row from row 6 to the row 39.
- Select cell E-39.
- Select ‘Goal Seek…” from the Tools menu.
- In ‘Set cell:’, it should say ‘E39’.
- In ‘To value:’, type in ‘0’.
- In ‘By changing cell:’, type ‘B4’.
- Hit OK.
This will give you the exact payment and monthly interest and principal payments for your loan. Remember if you change the term of your loan, the places where I have put ‘E39’ will have to be changed to the row where your last term month is.