Amortization Tables

 

One of the most useful applications of a worksheet is to produce an amortization table.  Amortization is a method for computing equal periodic payments for a loan.  Installment loans are repaid in a series of periodic payments and are often computed using this method.  Car loans and mortgages are examples of this type of loan.  Each installment, or payment, is the same and consists of two parts: a portion which goes to reducing the principal and the remainder to pay interest due on the principal for that period.  Principal is the amount of money owed.  Therefore, the principal decreases as a loan is repaid.

 

An amortization table displays how much interest and principal make up each payment of an installment loan.  Interest is computed as a percentage of the current principal.  The principal portion of the payment goes toward reducing the amount owed.  For example, the payment made each month on a 30 year loan of $100,000 borrowed at an interest rate of 12% is $1,028.61.  On the first payment made, $1,000.00 goes toward interest and $28.61 to reduce principal (i.e., the amount owed).  Let’s really look at this - in the first payment of $1028.61, you have only paid off $28.61 from your loan!  $1000 of that $1028.61 is the bank’s to keep JUST for borrowing you the money (and that is only the amount they keep on ONE payment!).  On the 60th payment, $977.15 pays interest and $51.47 reduces principal.  The final payment of this loan is $10.18 interest and $1,018.43 principal (FINALLY).

 

The PMT function is used to calculate the periodic payment for an installment loan.  The interest rate, the number of payments to be made, and the amount of the loan (principal) are needed by the PMT function.  A formula using the PMT function takes on the form

 

=PMT(Rate, Nper/term, PV/Present value)

 

where Present Value is the amount to be paid back, Rate is the interest rate per period, and Term is the number of payments to be made.  As an example, if you borrow $100,000 to purchase a house at an interest rate of 12% for 30 years, the formula would be:

 

=PMT(12/12, 360, 100000)

 

Since the payments are monthly, the interest rate must also be monthly.  This is computed by dividing the annual rate of interest, 12%, by 12.  The number of payments is 360, 30 years * 12 months.  When entered into a cell, the formula displays a value of 1028.6126.  This means that the monthly payment for this loan is $1,028.61.

 

Amortization Practice:

 

1.  Open Loan from the shared drive (Data Files Folder).  Save it to your OWN folder as Loan.

 

The loan information is stored in cells that can be referenced in formulas.  By using cells to store the data, it is easy to answer What If? questions.  For example, what if a $20,000 loan at 10% interest rate to be repaid over 5 years was obtained?  To answer this, 10% (or .10) is entered into the cell storing the interest rate, 20000 is the principal, and 60 is the number of payments (5 Years * 12 Months).  The worksheet automatically recalculates any formulas referencing these cells and displays the new values, including the payment amount.

 

2.  Enter a Loan’s Information

a.  In cell C3, enter the principal: 100000

b.  In cell C4, enter the yearly interest rate: 12% (Which should be written as .12)

c.   In cell C5, enter the number of payments: 360 (30 years * 12 monthly payments)

 

3.  Calculate the Monthly Payment

a.  In cell C7, use the insert function dialog box to enter the formula for PMT.  Enter the rate, principal and number of payments in the appropriate locations (Remember the YEARLY rate always needs to be divided by 12 when doing monthly payments).  Notice that the PMT function produces a negative value (parenthesis around it).  We would like to know the payment amount which is positive, therefore, after entering the PMT function correctly, write the ABS( ) function (Absolute Value) around the entire PMT function.

 

Example: ABS(PMT(C4/12, C5, C3))

 

4.  Calculate Total Paid and Total Interest

a.  In cell C9, enter the formula: =ROUND(C5*C7,2).  This formula computes the total paid for the loan, including principal and interest (Yes this amount is CORRECT – This is how much you will pay for a $100000 loan over 30 years at 12% interest).

b.  In cell C10, enter the formula: =C9-C3.  The total interest paid over 30 years is calculated (And yes this amount is CORRECT also – This is the amount of INTEREST that the bank will make on this loan).

 

5.  Enter the First Payment Data:

a.  In cell A13, enter: 1

b.  In cell B13, enter: =C3

c.   In cell C13, enter =B13*($C$4/12) to calculate one month’s interest on the loan.  The cell reference C4 contains dollar signs because the interest rate will be the same for each payment (This is a GREAT example of an ABSOLUTE reference being needed).  The number displayed, $1,000.00 is 1% or (12%/12) of the principal.

d.  In cell D13, enter the formula =IF(C13<0.01, 0, $C$7-C13) to calculate the amount of the payment which is applied to the principal, $28.61.  If the value in cell C13 is less than 0.01, then 0 is displayed.  This comparison must be made because it is not possible to pay less than a penny.

e.  In cell E13, enter the formula =B13-D13 to calculate the new principal owed.

 

6.  Enter Formulas for the Second Payment:

a.  In cell A14, enter the formula =A13+1

b.  To display the new principal, enter =E13 in cell B14

c.   Highlight cells C13 to E14

d.  Use Fill Down to copy the formulas in cell C13 through E13 into cells C14 through E14.  This completes the data for the second payment and the principal owed, $99,942.49 is displayed in cell E14.

 

7.  Complete the table using Fill Down

a.  Highlight cells A14 to E372

b.  Use Fill Down to copy cells A14 through E14 into rows 15 through 372.  Because of the large number of cells and formulas involved, it will take a moment for the computer to recalculate the worksheet.  Note the value in cell E372.  The principal owed is $0.00 which indicates the loan has been paid in full.

c.   Scroll to find out at what payment is the amount paid in interest approximately equal to the amount paid in principal?

 

Experiment by changing the principal and interest rate values in cells C3 and C4 to see the effects on the monthly payment and the total amount of interest paid

 

8.  Save this document again in your own folder

 

9.  Create an auto Loan Model:

 

The present values in Loan represent a house loan.  By changing the interest rate and the number of payments, payment amounts can be compared for a car.

 

a.  In cell C3, enter the new principal: 10000

b.  In cell C4, enter the new yearly interest rate: 10% (or .10)

c.   In cell C5, enter the new number of payments (The car loan is a 5 year loan; therefore, the number of monthly payments will be 5*12 which is 60)

d.  Note how the worksheet has been recalculated.  Scroll down to row 72 which contains the last payment.  The worksheet can easily model loans with less than 360 payments.

 

10.                   Enter your own Values into the worksheet:

 

a.  Experiment by changing the principal and interest of the loan to any values you like.  Change the number of payments to see how that affects the interest paid for the loan.

b.  Close the document without saving it with your experimental values.