Question

Determine an asset you would like to purchase (car, home, boat, etc) by making payments. Find out the selling price, interest rate, and number of payments (annual or monthly payments, you decide and adjust accordingly).  Create an amortization table of the loan that shows the portion of interest and principal of each payment. What is the total amount of interest that you will have paid at the end of the loan term? Create a Stacked Column chart that shows both interest and principal on each column.  You can research online to find out how to do an amortization table. Add clear labels to show the data you are using.

Answer 1

Assuming a car loan of INR 10,00,000 at the interest rate of 10% for a period of five years. The payment frequency is monthly.

Therefore, the assumptions are as follows;

Loan Amount	10,00,000.00
Interest Rate	10.00%
Loan Period in Years	5
No. of Payment Per Year	12
Grace Period	-
Number of Installment	60
EMI	(21,247.04)

  The EMI is calculated using the PMT function of excel sheet. The syntax of PMT function is,

=PMT(RATE,NPER,PV,FV,TYPE)]

So the funtion will be written as,

=PMT(10%/12, 5*12, 10,00,000)

Therefore, EMI = 21,247.04

The interest charged every month will be calculated using the IPMT function of excel. The syntax for IPMT function is,

=IPMT( rate, per, nper, pv, [fv], [type] )

where per is the payment period of the interest.

The principal amount of every EMI will be calculated using the PPMT function. The syntax of PPMT function is,

=PPMT( rate, per, nper, pv, [fv], [type] )

The total amount of interest paid during the tenure of loan = INR 2,72,822.68

{the interest can be calculated by summing up the interest component in the excel sheet}

The amortisation table of the loan is as follows and is self-explainatory,

S.No	Beginning Balance	EMI	Principal	Interest	Outstanding Principal
1	10,00,000.00	21,247.04	12,913.71	8,333.33	9,87,086.29
2	9,87,086.29	21,247.04	13,021.33	8,225.72	9,74,064.96
3	9,74,064.96	21,247.04	13,129.84	8,117.21	9,60,935.13
4	9,60,935.13	21,247.04	13,239.25	8,007.79	9,47,695.87
5	9,47,695.87	21,247.04	13,349.58	7,897.47	9,34,346.30
6	9,34,346.30	21,247.04	13,460.83	7,786.22	9,20,885.47
7	9,20,885.47	21,247.04	13,573.00	7,674.05	9,07,312.47
8	9,07,312.47	21,247.04	13,686.11	7,560.94	8,93,626.36
9	8,93,626.36	21,247.04	13,800.16	7,446.89	8,79,826.20
10	8,79,826.20	21,247.04	13,915.16	7,331.89	8,65,911.05
11	8,65,911.05	21,247.04	14,031.12	7,215.93	8,51,879.93
12	8,51,879.93	21,247.04	14,148.05	7,099.00	8,37,731.88
13	8,37,731.88	21,247.04	14,265.95	6,981.10	8,23,465.93
14	8,23,465.93	21,247.04	14,384.83	6,862.22	8,09,081.11
15	8,09,081.11	21,247.04	14,504.70	6,742.34	7,94,576.40
16	7,94,576.40	21,247.04	14,625.57	6,621.47	7,79,950.83
17	7,79,950.83	21,247.04	14,747.45	6,499.59	7,65,203.37
18	7,65,203.37	21,247.04	14,870.35	6,376.69	7,50,333.02
19	7,50,333.02	21,247.04	14,994.27	6,252.78	7,35,338.76
20	7,35,338.76	21,247.04	15,119.22	6,127.82	7,20,219.53
21	7,20,219.53	21,247.04	15,245.22	6,001.83	7,04,974.32
22	7,04,974.32	21,247.04	15,372.26	5,874.79	6,89,602.06
23	6,89,602.06	21,247.04	15,500.36	5,746.68	6,74,101.70
24	6,74,101.70	21,247.04	15,629.53	5,617.51	6,58,472.17
25	6,58,472.17	21,247.04	15,759.78	5,487.27	6,42,712.39
26	6,42,712.39	21,247.04	15,891.11	5,355.94	6,26,821.28
27	6,26,821.28	21,247.04	16,023.53	5,223.51	6,10,797.75
28	6,10,797.75	21,247.04	16,157.06	5,089.98	5,94,640.69
29	5,94,640.69	21,247.04	16,291.71	4,955.34	5,78,348.98
30	5,78,348.98	21,247.04	16,427.47	4,819.57	5,61,921.51
31	5,61,921.51	21,247.04	16,564.37	4,682.68	5,45,357.14
32	5,45,357.14	21,247.04	16,702.40	4,544.64	5,28,654.74
33	5,28,654.74	21,247.04	16,841.59	4,405.46	5,11,813.15
34	5,11,813.15	21,247.04	16,981.94	4,265.11	4,94,831.22
35	4,94,831.22	21,247.04	17,123.45	4,123.59	4,77,707.77
36	4,77,707.77	21,247.04	17,266.15	3,980.90	4,60,441.62
37	4,60,441.62	21,247.04	17,410.03	3,837.01	4,43,031.59
38	4,43,031.59	21,247.04	17,555.11	3,691.93	4,25,476.48
39	4,25,476.48	21,247.04	17,701.41	3,545.64	4,07,775.07
40	4,07,775.07	21,247.04	17,848.92	3,398.13	3,89,926.15
41	3,89,926.15	21,247.04	17,997.66	3,249.38	3,71,928.49
42	3,71,928.49	21,247.04	18,147.64	3,099.40	3,53,780.85
43	3,53,780.85	21,247.04	18,298.87	2,948.17	3,35,481.98
44	3,35,481.98	21,247.04	18,451.36	2,795.68	3,17,030.62
45	3,17,030.62	21,247.04	18,605.12	2,641.92	2,98,425.49
46	2,98,425.49	21,247.04	18,760.17	2,486.88	2,79,665.33
47	2,79,665.33	21,247.04	18,916.50	2,330.54	2,60,748.83
48	2,60,748.83	21,247.04	19,074.14	2,172.91	2,41,674.69
49	2,41,674.69	21,247.04	19,233.09	2,013.96	2,22,441.60
50	2,22,441.60	21,247.04	19,393.36	1,853.68	2,03,048.24
51	2,03,048.24	21,247.04	19,554.98	1,692.07	1,83,493.26
52	1,83,493.26	21,247.04	19,717.93	1,529.11	1,63,775.33
53	1,63,775.33	21,247.04	19,882.25	1,364.79	1,43,893.07
54	1,43,893.07	21,247.04	20,047.94	1,199.11	1,23,845.14
55	1,23,845.14	21,247.04	20,215.00	1,032.04	1,03,630.14
56	1,03,630.14	21,247.04	20,383.46	863.58	83,246.68
57	83,246.68	21,247.04	20,553.32	693.72	62,693.35
58	62,693.35	21,247.04	20,724.60	522.44	41,968.75
59	41,968.75	21,247.04	20,897.31	349.74	21,071.45
60	21,071.45	21,247.04	21,071.45	175.60	(0.00)

The stacked column chart showing monthly principal and interest is below,