Question

This worksheet will compute the monthly value of an amortized loan for 36 months. This will be a worksheet in which the loan value or principal, interest rate, and monthly payment can be changed and the worksheet will automatically update. Within the worksheet there will be some absolute cell referencing that is needed and some relative cell referencing that is needed. You will need to determine which is appropriate. Title the worksheet "Amortization of Car Loan" in cell A1. Leave a blank row under the title. Enter Today's Date in cell G1 so that it updates each time the file is updated in the spreadsheet.

1. In row five of this worksheet create cells labeled "Amount of Loan," "Interest Rate," and "Monthly Payment," with two columns between each label. Invent reasonable values to put in the three cells below each title as a starting place. These values are temporary values to help you see if your worksheet is working as you create it. For example, you may want to enter $5000 as the loan amount, .065 as the interest rate, and $150 as the monthly payment.

2. Leave two empty rows below the cells created in the prior step, then label four columns as "Month," "Current Principal," "Interest Due," and "New Principal."

   1. In cells under the "Month" title place the numbers from 1 to 36. Put the number 1 in the first cell and then use cell referencing and cell copying to create the rest of the column by using a formula that adds one to the cell above.
   2. In the first cell under "Current Principal" title reference the value in the "Amount of Loan" cell.
   3. In the first cell under the "Interest Due" column use a formula to calculate the interest that is due on the first value in the "Current Principal" column. You will need to multiply the "Current Principal" by the "Interest Rate," divided by 12. (For the rate, reference the rate value entered in step 2. The rate is a yearly rate and the time is 1/12 of a year.) Use cell referencing in this formula. You will need to use an absolute reference to refer to the "Interest Rate" value. (The formula is I = p*r*t.)
   4. In the "New Principal" column use a formula to compute the new amount due by taking the "Current Principal" and adding the "Interest Due" and subtracting the "Monthly Payment." Again, you must use absolute cell referencing for this.
   5. Make the next "Current Principal" equal to the "New Principal" from the month before.
   6. Complete the second row of the data by copying the rest of the formulas from the cells in the row above. (Check that your references are correct.)
   7. Complete the worksheet by copying all three of the formulas down for the remaining 36 months.
   8. At this point you should be able to determine amounts due after 36 months by changing only the values of the "Amount of Loan," "Interest Rate," and "Monthly Payment." Test your worksheet by entering $15000 into the "Amount of Loan," 0.08 into the "Interest Rate," and $470 as the "Monthly Payment." If you have completed everything correctly the amount due after 36 months is $1.84! The interest due for the first month should be $100.

3. Place the letters "a," "b," "c," and "d" in a column to the right of the amortization work. Use your worksheet to determine the following and put the answer next to the letter:

Answer 1

Following is the amortization table along with formulae:

Amortization of Car Loan
						25-03-2020
Amount of loan			Interest Rate			Monthly Payment
$15,000			0.08			$470
Month	Current Principal	Interest Due	New Principal
1	$15,000.00	$100.00	$14,630.00	a
2	$14,630.00	$97.53	$14,257.53	b
3	$14,257.53	$95.05	$13,882.58	c
4	$13,882.58	$92.55	$13,505.13	d
5	$13,505.13	$90.03	$13,125.17
6	$13,125.17	$87.50	$12,742.67
7	$12,742.67	$84.95	$12,357.62
8	$12,357.62	$82.38	$11,970.00
9	$11,970.00	$79.80	$11,579.80
10	$11,579.80	$77.20	$11,187.00
11	$11,187.00	$74.58	$10,791.58
12	$10,791.58	$71.94	$10,393.53
13	$10,393.53	$69.29	$9,992.82
14	$9,992.82	$66.62	$9,589.44
15	$9,589.44	$63.93	$9,183.37
16	$9,183.37	$61.22	$8,774.59
17	$8,774.59	$58.50	$8,363.09
18	$8,363.09	$55.75	$7,948.84
19	$7,948.84	$52.99	$7,531.83
20	$7,531.83	$50.21	$7,112.04
21	$7,112.04	$47.41	$6,689.46
22	$6,689.46	$44.60	$6,264.05
23	$6,264.05	$41.76	$5,835.81
24	$5,835.81	$38.91	$5,404.72
25	$5,404.72	$36.03	$4,970.75
26	$4,970.75	$33.14	$4,533.89
27	$4,533.89	$30.23	$4,094.12
28	$4,094.12	$27.29	$3,651.41
29	$3,651.41	$24.34	$3,205.75
30	$3,205.75	$21.37	$2,757.12
31	$2,757.12	$18.38	$2,305.50
32	$2,305.50	$15.37	$1,850.87
33	$1,850.87	$12.34	$1,393.21
34	$1,393.21	$9.29	$932.50
35	$932.50	$6.22	$468.72
36	$468.72	$3.12	$1.84

Amortization of Car Loan
						=TODAY()
Amount of loan			Interest Rate			Monthly Payment
15000			0.08			470
Month	Current Principal	Interest Due	New Principal
1	=A6	=B10*$D$6/12	=SUM(B10:C10)-$G$6	a
=A10+1	=D10	=B11*$D$6/12	=SUM(B11:C11)-$G$6	b
=A11+1	=D11	=B12*$D$6/12	=SUM(B12:C12)-$G$6	c
=A12+1	=D12	=B13*$D$6/12	=SUM(B13:C13)-$G$6	d
=A13+1	=D13	=B14*$D$6/12	=SUM(B14:C14)-$G$6
=A14+1	=D14	=B15*$D$6/12	=SUM(B15:C15)-$G$6
=A15+1	=D15	=B16*$D$6/12	=SUM(B16:C16)-$G$6
=A16+1	=D16	=B17*$D$6/12	=SUM(B17:C17)-$G$6
=A17+1	=D17	=B18*$D$6/12	=SUM(B18:C18)-$G$6
=A18+1	=D18	=B19*$D$6/12	=SUM(B19:C19)-$G$6
=A19+1	=D19	=B20*$D$6/12	=SUM(B20:C20)-$G$6
=A20+1	=D20	=B21*$D$6/12	=SUM(B21:C21)-$G$6
=A21+1	=D21	=B22*$D$6/12	=SUM(B22:C22)-$G$6
=A22+1	=D22	=B23*$D$6/12	=SUM(B23:C23)-$G$6
=A23+1	=D23	=B24*$D$6/12	=SUM(B24:C24)-$G$6
=A24+1	=D24	=B25*$D$6/12	=SUM(B25:C25)-$G$6
=A25+1	=D25	=B26*$D$6/12	=SUM(B26:C26)-$G$6
=A26+1	=D26	=B27*$D$6/12	=SUM(B27:C27)-$G$6
=A27+1	=D27	=B28*$D$6/12	=SUM(B28:C28)-$G$6
=A28+1	=D28	=B29*$D$6/12	=SUM(B29:C29)-$G$6
=A29+1	=D29	=B30*$D$6/12	=SUM(B30:C30)-$G$6
=A30+1	=D30	=B31*$D$6/12	=SUM(B31:C31)-$G$6
=A31+1	=D31	=B32*$D$6/12	=SUM(B32:C32)-$G$6
=A32+1	=D32	=B33*$D$6/12	=SUM(B33:C33)-$G$6
=A33+1	=D33	=B34*$D$6/12	=SUM(B34:C34)-$G$6
=A34+1	=D34	=B35*$D$6/12	=SUM(B35:C35)-$G$6
=A35+1	=D35	=B36*$D$6/12	=SUM(B36:C36)-$G$6
=A36+1	=D36	=B37*$D$6/12	=SUM(B37:C37)-$G$6
=A37+1	=D37	=B38*$D$6/12	=SUM(B38:C38)-$G$6
=A38+1	=D38	=B39*$D$6/12	=SUM(B39:C39)-$G$6
=A39+1	=D39	=B40*$D$6/12	=SUM(B40:C40)-$G$6
=A40+1	=D40	=B41*$D$6/12	=SUM(B41:C41)-$G$6
=A41+1	=D41	=B42*$D$6/12	=SUM(B42:C42)-$G$6
=A42+1	=D42	=B43*$D$6/12	=SUM(B43:C43)-$G$6
=A43+1	=D43	=B44*$D$6/12	=SUM(B44:C44)-$G$6
=A44+1	=D44	=B45*$D$6/12	=SUM(B45:C45)-$G$6

Happy Learning!