Question

On 1/1/20, you purchase a new car for $20,000 with a 12% loan (note) payable monthly. Payments of principal and interest are made at the end of each month for the next 30 months.

1. Create an effective interest amortization table in Excel for the entire life of the car loan payable using the following columns: payment number, payment date, beginning carrying value, effective interest rate, interest expense, cash (principal and interest paid), note payable (reduction of principal), and ending carrying value.

Answer 1

Solution:

Monthly interest rate = 12% / 12 = 1%

Nos of periods = 30

Monthly installment = $20,000 / Cumulative PV factor at 1% for 30 periods

= $20,000 / 25.80771 = $774.96

Payment No	Payment Date	Beginning carrying value	Effective interest rate	Interest expense	Cash (Principal and interest paid)	Note payable (Reduction of Principal)	Ending Carrying value
1	31-Jan-20	$20,000.00	1%	$200.00	$774.96	$574.96	$19,425.04
2	29-Feb-20	$19,425.04	1%	$194.25	$774.96	$580.71	$18,844.33
3	31-Mar-20	$18,844.33	1%	$188.44	$774.96	$586.52	$18,257.81
4	30-Apr-20	$18,257.81	1%	$182.58	$774.96	$592.38	$17,665.43
5	31-May-20	$17,665.43	1%	$176.65	$774.96	$598.31	$17,067.13
6	30-Jun-20	$17,067.13	1%	$170.67	$774.96	$604.29	$16,462.84
7	31-Jul-20	$16,462.84	1%	$164.63	$774.96	$610.33	$15,852.51
8	31-Aug-20	$15,852.51	1%	$158.53	$774.96	$616.43	$15,236.07
9	30-Sep-20	$15,236.07	1%	$152.36	$774.96	$622.60	$14,613.47
10	31-Oct-20	$14,613.47	1%	$146.13	$774.96	$628.83	$13,984.65
11	30-Nov-20	$13,984.65	1%	$139.85	$774.96	$635.11	$13,349.53
12	31-Dec-20	$13,349.53	1%	$133.50	$774.96	$641.46	$12,708.07
13	31-Jan-21	$12,708.07	1%	$127.08	$774.96	$647.88	$12,060.19
14	28-Feb-21	$12,060.19	1%	$120.60	$774.96	$654.36	$11,405.83
15	31-Mar-21	$11,405.83	1%	$114.06	$774.96	$660.90	$10,744.93
16	30-Apr-21	$10,744.93	1%	$107.45	$774.96	$667.51	$10,077.42
17	31-May-21	$10,077.42	1%	$100.77	$774.96	$674.19	$9,403.23
18	30-Jun-21	$9,403.23	1%	$94.03	$774.96	$680.93	$8,722.30
19	31-Jul-21	$8,722.30	1%	$87.22	$774.96	$687.74	$8,034.57
20	31-Aug-21	$8,034.57	1%	$80.35	$774.96	$694.61	$7,339.95
21	30-Sep-21	$7,339.95	1%	$73.40	$774.96	$701.56	$6,638.39
22	31-Oct-21	$6,638.39	1%	$66.38	$774.96	$708.58	$5,929.82
23	30-Nov-21	$5,929.82	1%	$59.30	$774.96	$715.66	$5,214.16
24	31-Dec-21	$5,214.16	1%	$52.14	$774.96	$722.82	$4,491.34
25	31-Jan-22	$4,491.34	1%	$44.91	$774.96	$730.05	$3,761.29
26	28-Feb-22	$3,761.29	1%	$37.61	$774.96	$737.35	$3,023.94
27	31-Mar-22	$3,023.94	1%	$30.24	$774.96	$744.72	$2,279.22
28	30-Apr-22	$2,279.22	1%	$22.79	$774.96	$752.17	$1,527.05
29	31-May-22	$1,527.05	1%	$15.27	$774.96	$759.69	$767.37
30	30-Jun-22	$767.37	1%	$7.59	$774.96	$767.37	$0.00