Amortization Plan

When a person takes a loan for a house, a car or any other major purchase he/she is making a large commitment that may involve monthly payments for up to 30 years. That means a total of 360 payments, a large commitment over a long period of time. A loan is something that needs to be taken seriously. The borrower is at a huge advantage when entering an obligation like this is he is aware of four items before even talking to a loan officer.

1. The expected payment.

2. The impact the interest rate has on the loan.

3. Where the borrower will stand with respect to the loan at any given time in the future.

4. The impact on a change in the interest rate if a variable interest loan is entered into.

The answers to all of these questions are provided with an amortization table. In this article I want to show you how to quickly and easily build and amortization table. In so doing we will find the answers to the four points mentioned above.

Loan Payment

If you have not taken out the loan yet, the first thing that you want to do is to calculate what your expected loan payment will be. If the loan has already been entered into you already know the answer to this question. For those who are pro-actively looking into a major purchase I will quickly show how to calculate what the loan payment will be. This can be done very easily in Microsoft Excel. Many hand calculators provide this calculation too. I will demonstrate with the use of Excel.

The structure of the “Payment” formula looks like this: =PMT(annual interest rate/12, number of periods (months), present value).

For example: If you are going to buy a $300,000 house and pay for it over 30 years (360 months) and you have negotiated a fixed interest rate of 6%, here are the exact entries you would make in Excel:

=PMT(.06/12, 360, 300000)

And our answer would be $1,798.65. Our monthly payment will be $1,798.65.

Before we proceed with building the table you may want to look at the total amount paid. $1,798.65 per month x 360 (months) = $647,514 dollars. More than double your initial purchase cost. Now let re-calculate the same house loan for 20 years and see what the differences are.

=PMT(.06/12,240,300000)

The monthly payment will be $2,149.29.

$2,149.29 x 240 = $515,830. If you paid for the house in 20 years you would be paying $350.64 more per month but you would pay a total of $131,684 less. A serious consideration to make before entering into a loan is how long you are going to have the loan for. Of course, with most home loans you can make additional payments but when a loan is taken out for a certain dollar amount, that amount usually goes into your budget and it is hard to keep up consistent payments above it.

Let us assume that our borrower looked over the facts and decided that a 20 year loan was better for him. He liked the idea of saving $131,000 over the life of the loan.

So far we have agreed on a price for the house ($300,000) and decided on the term (20 years). Interest is another thing that is sometimes slightly negotiable. In fact, with a shorter loan you may be able to get a discount on the interest charged. And remember, one tenth of a percentage point makes a difference over 30 years. In our example we can assume that 6% is the best possible interest rate.

Building the Amortization Table

When I do an amortization table I put three data points right at the very top on the left hand side, like this:

Initial Value: $300,000

Interest Rate: .06%

Term: 240 months

Then we need to add the following columns:

Number

Date

Beginning Balance

Payment

Interest

Repayment of Principle

Ending Balance

For period one the data would be:

Date: 1/1/2008

Beginning Balance: $300,000.00

Payment: $2,149.29 (the value we calculated above)

Interest: $1,500.00

Repayment of Principle: $649.29

Ending Balance: $299,350.71

For period 2 we would have:

Date: 1/1/2008

Beginning Balance: $299,350.71 (the same as th ending balance from the previous period)

Payment: $2,149.29 (with a fixed interest rate this value stays the same)

Interest: $1,496.75

Repayment of Principle: $652.54

Ending Balance: $298.698.17

Of course, you would put this all in an excel table and here are the formulas you would use.

Uner the Number column we will enter 1-360, the number of the payment. Beginning Balance will for the first month be $300,000 and for each subsequent month it will be the previous month minus the Repayment of Principle. For example, for period 1, the beginning balance is $300,000 and the repayment of principle is $649.29 so the beginning balance in month 2 is $299,350.71. The Payment will be the same every month as long as the interest rate does not change. We calculated the payment to be $2,149.29 per month. The Interest will be the Beginning balance for the month multiplied by .06 and divided by 12. For the first month take $300,000 x .06 /12 = $1,500.00. The Repayment of Principle will be the Payment minus the Interest. For the first month repayment of principle will be $2,149.29 – $1,500 = $649.29. The Ending Balance is calculated by Subtracting the Repayment of Principle from the Beginning Balance. $300,000 – $649.29 = $299,350.71.

The values for the last two months of data are as follows:

Number: 239

Date: 11/1/2027

Beginning Balance: $4,628.01

Payment: $2,149.29

Interest: $21.34

Repayment of Principle: $2,127.95

Ending Balance: $2,140.06

Number: 240

Date: 12/1/2027

Beginning Balance: $2,140.29

Payment: $2,149.29

Interest: $10.70

Repayment of Principle: $2,138.59

Ending Balance: $1.47

Note that on your last payment (240 in this case) the ending balance should be very close to $0. In this case it is $1.47 which represents a number as close to $0 as could be arrived at without paying a percentage of a cent every month.

Benefits of the Amortization Table There are several benefits of having an amortization table before you enter a loan as well as after the loan has been agreed upon and signed.

1. You will be able to see, on paper, what your loan will look like for the entire period. This will cause a person to stop and consider, is this the best method of obtaining whatever am I buying? Should I pay over a shorter (or longer) period? Am I over-extending myself? Having facts and figures down on paper, for you to look at, helps in the decision process.

2. If you want to know exactly where you are or where you will be at a certain time in the process of paying off the loan you have the figures right in front of you.

3. If the interest rate changes you can easily adjust the interest column and see what the impact will be.

4. It will act as a check and balance against the agency you take the loan from. If there are any “hidden” charges in the loan that you are not being told about you will be able to spot them quickly. If their payment amount does not match up to yours either exactly or within a couple of pennies, something is amiss.

5. It will help greatly in the bargaining process. It is much easier to bargain with a sales or finance person if they see that you are intelligent about the subject and know they cannot sell you a bag of hot air.

Conclusion I highly recommend building an amortization table before a major purchase is made. It will help you make the decision as to how long of a loan you should enter into, what the impact of the interest rate is and the impact of a change in interest rates. It will also help in the bargaining process when making a large purchase. If you follow the instructions here, it is really a very simple process and well worth the time and effort.

By: Tom Thorne

Tags: Amortization Table, Four Points, Interest Loan, Interest Rate, Loan Officer, Microsoft, Microsoft Excel, Obligation
Posted in Finance · January 8th, 2008 · Comments (0)