BUSA
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.
You
have just purchased a new car for $14,000. After a down payment of $2,000, you
financed $12,000 over four years at a interest rate of 12%. Use the =PMT function to calculate the monthly payment.
(Check figure: $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.
First put your name in cell A1 and "Amortization" in cell A2. 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.
Use the following format: Principal Amount in D7, Interest Rate in D8; Number
of Payments in D9 and Payment Amount in D10
(Put the numbers in the cells indicated, the headings descriptions
should be in column B).
Develop the formula needed to determine the monthly payment (cell D10).
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.
11.
Change the format of your work sheet to be more attractive.
Be creative.
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).
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.
Please
staple this handout in front of the following printouts (arrange in correct
order)”
AMORTIZE
INTEREST
INTEREST
(formulas)
NEW
LOAN