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