| When a person takes a loan for a house, a car or any | | | | Date: 1/1/2008 |
| other major purchase he/she is making a large | | | | Beginning Balance: $300,000.00 |
| commitment that may involve monthly payments for | | | | Payment: $2,149.29 (the value we calculated above) |
| up to 30 years. That means a total of 360 payments, | | | | Interest: $1,500.00 |
| a large commitment over a long period of time. A loan | | | | Repayment of Principle: $649.29 |
| is something that needs to be taken seriously. The | | | | Ending Balance: $299,350.71 |
| borrower is at a huge advantage when entering an | | | | For period 2 we would have: |
| obligation like this is he is aware of four items before | | | | Date: 1/1/2008 |
| even talking to a loan officer. | | | | Beginning Balance: $299,350.71 (the same as th ending |
| 1. The expected payment. | | | | balance from the previous period) |
| 2. The impact the interest rate has on the loan. | | | | Payment: $2,149.29 (with a fixed interest rate this value |
| 3. Where the borrower will stand with respect to the | | | | stays the same) |
| loan at any given time in the future. | | | | Interest: $1,496.75 |
| 4. The impact on a change in the interest rate if a | | | | Repayment of Principle: $652.54 |
| variable interest loan is entered into. | | | | Ending Balance: $298.698.17 |
| The answers to all of these questions are provided | | | | Of course, you would put this all in an excel table and |
| with an amortization table. In this article I want to show | | | | here are the formulas you would use. |
| you how to quickly and easily build and amortization | | | | - Uner the Number column we will enter 1-360, the |
| table. In so doing we will find the answers to the four | | | | number of the payment. |
| points mentioned above. | | | | - Beginning Balance will for the first month be $300,000 |
| Loan Payment | | | | and for each subsequent month it will be the previous |
| If you have not taken out the loan yet, the first thing | | | | month minus the Repayment of Principle. For example, |
| that you want to do is to calculate what your | | | | for period 1, the beginning balance is $300,000 and the |
| expected loan payment will be. If the loan has already | | | | repayment of principle is $649.29 so the beginning |
| been entered into you already know the answer to | | | | balance in month 2 is $299,350.71. |
| this question. For those who are pro-actively looking | | | | - The Payment will be the same every month as long |
| into a major purchase I will quickly show how to | | | | as the interest rate does not change. We calculated |
| calculate what the loan payment will be. This can be | | | | the payment to be $2,149.29 per month. |
| done very easily in Microsoft Excel. Many hand | | | | - The Interest will be the Beginning balance for the |
| calculators provide this calculation too. I will | | | | month multiplied by .06 and divided by 12. For the first |
| demonstrate with the use of Excel. | | | | month take $300,000 x .06 /12 = $1,500.00. |
| The structure of the "Payment" formula looks like this: | | | | - The Repayment of Principle will be the Payment |
| =PMT(annual interest rate/12, number of periods | | | | minus the Interest. For the first month repayment of |
| (months), present value). | | | | principle will be $2,149.29 - $1,500 = $649.29. |
| For example: If you are going to buy a $300,000 house | | | | - The Ending Balance is calculated by Subtracting the |
| and pay for it over 30 years (360 months) and you | | | | Repayment of Principle from the Beginning Balance. |
| have negotiated a fixed interest rate of 6%, here are | | | | $300,000 - $649.29 = $299,350.71. |
| the exact entries you would make in Excel: | | | | The values for the last two months of data are as |
| =PMT(.06/12, 360, 300000) | | | | follows: |
| And our answer would be $1,798.65. Our monthly | | | | Number: 239 |
| payment will be $1,798.65. | | | | Date: 11/1/2027 |
| Before we proceed with building the table you may | | | | Beginning Balance: $4,628.01 |
| want to look at the total amount paid. $1,798.65 per | | | | Payment: $2,149.29 |
| month x 360 (months) = $647,514 dollars. More than | | | | Interest: $21.34 |
| double your initial purchase cost. Now let re-calculate | | | | Repayment of Principle: $2,127.95 |
| the same house loan for 20 years and see what the | | | | Ending Balance: $2,140.06 |
| differences are. | | | | Number: 240 |
| =PMT(.06/12,240,300000) | | | | Date: 12/1/2027 |
| The monthly payment will be $2,149.29. | | | | Beginning Balance: $2,140.29 |
| $2,149.29 x 240 = $515,830. If you paid for the house in | | | | Payment: $2,149.29 |
| 20 years you would be paying $350.64 more per | | | | Interest: $10.70 |
| month but you would pay a total of $131,684 less. A | | | | Repayment of Principle: $2,138.59 |
| serious consideration to make before entering into a | | | | Ending Balance: $1.47 |
| loan is how long you are going to have the loan for. Of | | | | Note that on your last payment (240 in this case) the |
| course, with most home loans you can make additional | | | | ending balance should be very close to $0. In this case |
| payments but when a loan is taken out for a certain | | | | it is $1.47 which represents a number as close to $0 |
| dollar amount, that amount usually goes into your | | | | as could be arrived at without paying a percentage of |
| budget and it is hard to keep up consistent payments | | | | a cent every month. |
| above it. | | | | Benefits of the Amortization Table There are several |
| Let us assume that our borrower looked over the | | | | benefits of having an amortization table before you |
| facts and decided that a 20 year loan was better for | | | | enter a loan as well as after the loan has been agreed |
| him. He liked the idea of saving $131,000 over the life of | | | | upon and signed. |
| the loan. | | | | 1. You will be able to see, on paper, what your loan will |
| So far we have agreed on a price for the house | | | | look like for the entire period. This will cause a person |
| ($300,000) and decided on the term (20 years). | | | | to stop and consider, is this the best method of |
| Interest is another thing that is sometimes slightly | | | | obtaining whatever am I buying? Should I pay over a |
| negotiable. In fact, with a shorter loan you may be able | | | | shorter (or longer) period? Am I over-extending |
| to get a discount on the interest charged. And | | | | myself? Having facts and figures down on paper, for |
| remember, one tenth of a percentage point makes a | | | | you to look at, helps in the decision process. |
| difference over 30 years. In our example we can | | | | 2. If you want to know exactly where you are or |
| assume that 6% is the best possible interest rate. | | | | where you will be at a certain time in the process of |
| Building the Amortization Table | | | | paying off the loan you have the figures right in front |
| When I do an amortization table I put three data points | | | | of you. |
| right at the very top on the left hand side, like this: | | | | 3. If the interest rate changes you can easily adjust the |
| Initial Value: $300,000 | | | | interest column and see what the impact will be. |
| Interest Rate: .06% | | | | 4. It will act as a check and balance against the |
| Term: 240 months | | | | agency you take the loan from. If there are any |
| Then we need to add the following columns: | | | | "hidden" charges in the loan that you are not being told |
| Number | | | | about you will be able to spot them quickly. If their |
| Date | | | | payment amount does not match up to yours either |
| Beginning Balance | | | | exactly or within a couple of pennies, something is |
| Payment | | | | amiss. |
| Interest | | | | 5. It will help greatly in the bargaining process. It is much |
| Repayment of Principle | | | | easier to bargain with a sales or finance person if they |
| Ending Balance | | | | see that you are intelligent about the subject and know |
| For period one the data would be: | | | | they cannot sell you a bag of hot air. |