BUSA 133 Loan Amortization

ACCT 133 - SPREADSHEET ACCOUNTING

INSTRUCTOR PROBLEM NO. 3 (I103)

AMORTIZATION OF A LOAN

Purpose: The purpose of this handout is to develop an amortization schedule for a loan.

In Instructor Problem No. 2, you calculated the monthly payment on a $12,000 auto loan for four years with an annual interest rate of $12%. You used the =PMT function to calculate the monthly payment of $316.01. In this problem, you will develop an amortization table which will calculate the amount of interest and the amount of principal reduction for each payment as well as the remaining balance after each payment.

1. To begin, let’s establish a data section. This section should include the information needed to calculate the monthly payment on the loan described above (see attached sheet; use the same cells as shown in the data section). Develop the formula needed to determine the monthly payment (cell D10).

      Line 7           Loan                  $12,000
Line 8           Interest Rate             12%
Line 9           Term (months)           48
Line 10          Payment              You calculate using =PMT

2. Once you have created the data table, you can create the answer section for the amortization schedule. Start by creating the following headings: in cell A13 type Answer Section; in cell A15 type PAYMENT; in A16 type NUMBER; in C16 type PAYMENT; in D16 type INTEREST; in E16 type PRINCIPAL; and in G16 type BALANCE.

3. Next you need the number of payments in the Payment Number column (for this problem we need 48). Using the AutoFill command do the following: in A17 insert a 0 and in A18 insert a 1. Next, highlight these two cells and using the AutoFill command, drag down to row 65. When you lift your finger from the left mouse button, you should see the number 48 in cell A65.

4. In G17, cell reference the principal (in this problem it is =D7).

5. In cell C18, cell reference the payment (in this problem it is =D$10. You will want this cell to stay absolute so be sure to insert a $ in front of the 10).

6. In cell D18 you will need to calculate the interest on each payment. To do this, use the following formula =(G17*D$8/12).

7. In cell E18 you will need to calculate the amount of principal that is being amortized for each payment. This is the payment (C16) minus the interest (D16). Insert the following formula to accomplish this: =C18-D18.

8. The last formula needed is to calculate the remaining principal balance. This is the previous balance (G17) minus the principal that is amortized (E16). Insert the following formula to accomplish this: =G17-E18.

9. Now you need to copy your formulas into the remaining rows. To do this, highlight the following cells: C18 to G18. Use Copy command, and Paste the formulas to rows C19 to G65.

10. At this point, your amortization schedule should be complete. Notice that the ending balance is 0.

12. Print your amortization schedule. Be sure to use the print setup command and print to one page. The file name is AMORTIZE. Save the file as AMORTIZE.

13. Insert two new columns and total the interest paid and principal amortized for each of the four years (Hint: payments 1-12 is the first year; 13-24 is the second year, etc.). Also determine the total amount of interest paid for the four years and the amount of principal paid for the four years (which should be $12,000) and total amount paid for the loan.

If you could afford to pay $400 each month, what would be the resulting cost savings in interest?__________________________________

If you could afford to pay $500 each month, what would be the resulting cost savings in interest?__________________________________

How much sooner could you pay the loan off if your payments were $400 ______________________

How much sooner could you pay the loan off if your payments were $500____________________

(Hint: Insert the new payment amount and review the balance of the loan. At the point where it goes negative, the loan is paid off. You may have to adjust the last payment and reduce the amount of principal on it so that the loan balance is 0. All the other payments -- beyond the payoff point -- should be deleted. Then you can recalculate the amount of interest paid, and compare it to the original interest to derive the cost savings.)

14. Change cell A2 to Interest and print. Use the Save As command and save as INTEREST.

15. Use the Ctrl + ~ command and print the formulas for your amortization schedule.

16. Now assume that you want to borrow $10,000 at 9% for 3 years. Open the file AMORTIZE and modify as needed. Change cell A2 to New Loan and print. Use the Save As command and save as NEW LOAN. Make sure you calculate the total interest, principal and payments made.

Please staple this handout in front of the following printouts (arrange in correct order)”