Question

Please complete on Excel and show any computations

Thank you

1. Shop online for your dream car and get its price. Attach documentation on the price (screen print). 2. Shop around (online) for financing and select the best financing rate available for a 4-year (48 months) loan. Attach documentation on financing rates. (Do not apply for actual loan – this is simply an exercise) 3. Pay 10% down. How much are you financing today? 4. Using MS Excel features, compute the monthly payment (PMT) on the loan. Show computations. 5. Prepare an amortization chart for this loan for 48 months. 6. How much interest charge will you pay in total? 7. Re-do #5 above using 72 months as the loan period. 8. How much interest charge would you be paying in total with the 72-month loan? 9. Compare the interest cost for the 48-month loan with that for the 72-month loan. Which loan is more costly? NOTES: 1. Use MS Excel for all computations; calculations should be viewable once the cursor is placed on the cell

Answer 1

Q1-5 Following the instructions,amortization chart prepared in excel:

Price of the car	100,000
Best interest rate available	5%
Downpayment	10%
Financing required	90000
Tenure (Months)	48
Monthly Payment (PMT)	$2,073.00
Months	EMI	Interest	Principal	Balance
1	$2,073.00	375	$1,698.00	$88,302.00
2	$2,073.00	$367.93	$1,705.08	$86,596.93
3	$2,073.00	$360.82	$1,712.18	$84,884.75
4	$2,073.00	$353.69	$1,719.31	$83,165.43
5	$2,073.00	$346.52	$1,726.48	$81,438.95
6	$2,073.00	$339.33	$1,733.67	$79,705.28
7	$2,073.00	$332.11	$1,740.89	$77,964.39
8	$2,073.00	$324.85	$1,748.15	$76,216.24
9	$2,073.00	$317.57	$1,755.43	$74,460.81
10	$2,073.00	$310.25	$1,762.75	$72,698.06
11	$2,073.00	$302.91	$1,770.09	$70,927.97
12	$2,073.00	$295.53	$1,777.47	$69,150.50
13	$2,073.00	$288.13	$1,784.87	$67,365.63
14	$2,073.00	$280.69	$1,792.31	$65,573.32
15	$2,073.00	$273.22	$1,799.78	$63,773.54
16	$2,073.00	$265.72	$1,807.28	$61,966.27
17	$2,073.00	$258.19	$1,814.81	$60,151.46
18	$2,073.00	$250.63	$1,822.37	$58,329.09
19	$2,073.00	$243.04	$1,829.96	$56,499.13
20	$2,073.00	$235.41	$1,837.59	$54,661.54
21	$2,073.00	$227.76	$1,845.24	$52,816.30
22	$2,073.00	$220.07	$1,852.93	$50,963.36
23	$2,073.00	$212.35	$1,860.65	$49,102.71
24	$2,073.00	$204.59	$1,868.41	$47,234.31
25	$2,073.00	$196.81	$1,876.19	$45,358.12
26	$2,073.00	$188.99	$1,884.01	$43,474.11
27	$2,073.00	$181.14	$1,891.86	$41,582.25
28	$2,073.00	$173.26	$1,899.74	$39,682.51
29	$2,073.00	$165.34	$1,907.66	$37,774.85
30	$2,073.00	$157.40	$1,915.60	$35,859.25
31	$2,073.00	$149.41	$1,923.59	$33,935.66
32	$2,073.00	$141.40	$1,931.60	$32,004.06
33	$2,073.00	$133.35	$1,939.65	$30,064.41
34	$2,073.00	$125.27	$1,947.73	$28,116.68
35	$2,073.00	$117.15	$1,955.85	$26,160.83
36	$2,073.00	$109.00	$1,964.00	$24,196.84
37	$2,073.00	$100.82	$1,972.18	$22,224.66
38	$2,073.00	$92.60	$1,980.40	$20,244.26
39	$2,073.00	$84.35	$1,988.65	$18,255.61
40	$2,073.00	$76.07	$1,996.93	$16,258.68
41	$2,073.00	$67.74	$2,005.26	$14,253.42
42	$2,073.00	$59.39	$2,013.61	$12,239.81
43	$2,073.00	$51.00	$2,022.00	$10,217.81
44	$2,073.00	$42.57	$2,030.43	$8,187.38
45	$2,073.00	$34.11	$2,038.89	$6,148.50
46	$2,073.00	$25.62	$2,047.38	$4,101.12
47	$2,073.00	$17.09	$2,055.91	$2,045.20
48	$2,073.00	$8.52	$2,064.48	($19.28)

With Formulas:

Monthly payment calculated using calculator = $2073;

Interest for each month = Principal outstanding * Interest rate/12

Principal amount paid = Monthly Instalment - Interest paid

Balance outstanding = Previous/Beginning period balance - principal amount paid in the month

6. Total Interest paid with 48 months tenure = $9485

7. Changing period to 72 months:

Price of the car	100,000
Best interest rate available	5%
Downpayment	10%
Financing required	90000
Tenure (Months)	72
Monthly Payment (PMT)	$1,449.44
Months	EMI	Interest	Principal	Balance
1	$1,449.44	375	$1,074.44	$88,925.56
2	$1,449.44	$370.52	$1,078.92	$87,846.64
3	$1,449.44	$366.03	$1,083.41	$86,763.23
4	$1,449.44	$361.51	$1,087.93	$85,675.30
5	$1,449.44	$356.98	$1,092.46	$84,582.84
6	$1,449.44	$352.43	$1,097.01	$83,485.83
7	$1,449.44	$347.86	$1,101.58	$82,384.25
8	$1,449.44	$343.27	$1,106.17	$81,278.08
9	$1,449.44	$338.66	$1,110.78	$80,167.30
10	$1,449.44	$334.03	$1,115.41	$79,051.89
11	$1,449.44	$329.38	$1,120.06	$77,931.83
12	$1,449.44	$324.72	$1,124.72	$76,807.11
13	$1,449.44	$320.03	$1,129.41	$75,677.70
14	$1,449.44	$315.32	$1,134.12	$74,543.58
15	$1,449.44	$310.60	$1,138.84	$73,404.74
16	$1,449.44	$305.85	$1,143.59	$72,261.15
17	$1,449.44	$301.09	$1,148.35	$71,112.80
18	$1,449.44	$296.30	$1,153.14	$69,959.66
19	$1,449.44	$291.50	$1,157.94	$68,801.72
20	$1,449.44	$286.67	$1,162.77	$67,638.96
21	$1,449.44	$281.83	$1,167.61	$66,471.34
22	$1,449.44	$276.96	$1,172.48	$65,298.87
23	$1,449.44	$272.08	$1,177.36	$64,121.51
24	$1,449.44	$267.17	$1,182.27	$62,939.24
25	$1,449.44	$262.25	$1,187.19	$61,752.05
26	$1,449.44	$257.30	$1,192.14	$60,559.91
27	$1,449.44	$252.33	$1,197.11	$59,362.80
28	$1,449.44	$247.34	$1,202.10	$58,160.70
29	$1,449.44	$242.34	$1,207.10	$56,953.60
30	$1,449.44	$237.31	$1,212.13	$55,741.47
31	$1,449.44	$232.26	$1,217.18	$54,524.28
32	$1,449.44	$227.18	$1,222.26	$53,302.03
33	$1,449.44	$222.09	$1,227.35	$52,074.68
34	$1,449.44	$216.98	$1,232.46	$50,842.22
35	$1,449.44	$211.84	$1,237.60	$49,604.62
36	$1,449.44	$206.69	$1,242.75	$48,361.87
37	$1,449.44	$201.51	$1,247.93	$47,113.93
38	$1,449.44	$196.31	$1,253.13	$45,860.80
39	$1,449.44	$191.09	$1,258.35	$44,602.45
40	$1,449.44	$185.84	$1,263.60	$43,338.85
41	$1,449.44	$180.58	$1,268.86	$42,069.99
42	$1,449.44	$175.29	$1,274.15	$40,795.84
43	$1,449.44	$169.98	$1,279.46	$39,516.39
44	$1,449.44	$164.65	$1,284.79	$38,231.60
45	$1,449.44	$159.30	$1,290.14	$36,941.46
46	$1,449.44	$153.92	$1,295.52	$35,645.94
47	$1,449.44	$148.52	$1,300.92	$34,345.02
48	$1,449.44	$143.10	$1,306.34	$33,038.69
49	$1,449.44	$137.66	$1,311.78	$31,726.91
50	$1,449.44	$132.20	$1,317.24	$30,409.66
51	$1,449.44	$126.71	$1,322.73	$29,086.93
52	$1,449.44	$121.20	$1,328.24	$27,758.69
53	$1,449.44	$115.66	$1,333.78	$26,424.91
54	$1,449.44	$110.10	$1,339.34	$25,085.57
55	$1,449.44	$104.52	$1,344.92	$23,740.65
56	$1,449.44	$98.92	$1,350.52	$22,390.13
57	$1,449.44	$93.29	$1,356.15	$21,033.99
58	$1,449.44	$87.64	$1,361.80	$19,672.19
59	$1,449.44	$81.97	$1,367.47	$18,304.71
60	$1,449.44	$76.27	$1,373.17	$16,931.54
61	$1,449.44	$70.55	$1,378.89	$15,552.65
62	$1,449.44	$64.80	$1,384.64	$14,168.02
63	$1,449.44	$59.03	$1,390.41	$12,777.61
64	$1,449.44	$53.24	$1,396.20	$11,381.41
65	$1,449.44	$47.42	$1,402.02	$9,979.39
66	$1,449.44	$41.58	$1,407.86	$8,571.53
67	$1,449.44	$35.71	$1,413.73	$7,157.81
68	$1,449.44	$29.82	$1,419.62	$5,738.19
69	$1,449.44	$23.91	$1,425.53	$4,312.66
70	$1,449.44	$17.97	$1,431.47	$2,881.19
71	$1,449.44	$12.00	$1,437.44	$1,443.75
72	$1,449.44	$6.02	$1,443.42	$0.33

WITH FORMULAS:

Monthly payment calculated using calculator = $1449.44;

Interest for each month = Principal outstanding * Interest rate/12

Principal amount paid = Monthly Instalment - Interest paid

Balance outstanding = Previous/Beginning period balance - principal amount paid in the month

8. Interest charged in 72 months period = $14,360

9. Interest charged in 48 month period loan is $9485 while that charged in 72 month period is $14,360. Hence we can conclude 72 month period loan is more costly