Question

You will consider how to pay for a $2,000 consumer good and/or service someone might need to purchase in an emergency, such as a major repair to a car or home. You will analyze two loans for paying for the good and/or service. The first is for someone who has enough money set aside to cover some of the upfront costs (e.g. a down payment) and the second is for someone who does not. Additionally, the second person has less money available on a monthly basis and a significantly worse credit score.

1) Make a 20% down payment and finance the balance due using an installment loan paid monthly over a two-year term with an interest rate of 18%, and

2) Make no down payment and finance 100% using an interest rate of 36% for an installment loan paid monthly over a four-year term.

The higher rate and longer term are used in this case since we are simulating a situation where the person is limited in the amount that can be paid monthly, cannot afford a down payment, and has poor credit.

Installment Loan Assignment Tasks

• Model each installment loan showing each of the following in an amortization schedule table
  • Payment number starting with 0,
  • monthly payment amounts,
  • interest paid for the period,
  • principal paid for the period, and
  • the principal balance at the end of each period.

• It must have five columns labeled similarly.
  • You are not permitted to use an online amortization calculator
  • You must use either a Microsoft Excel, Google Sheets, or iWork Numbers spreadsheet to compute and create the amortization schedule
  • The spreadsheet formulas you use to create the table must be correct and yield the proper values. In other words, the values cannot simply be typed into a table.
  • Make sure to show the headings for each column on your tables when they are "printed" or converted to PDF form as they may take more than one page.
  • Clearly show the interest rate factor you found and used to compute the monthly payment. Show the computation of the monthly payment clearly above each table in a similar fashion as part (a) of example 2 shows in the text. Make sure your factor is shown to five decimal places.

Answer 1

1. Amortisation Table For the Person Who can pay down payment
Amount Payable	$                       2,000.00
Down Payment @20%	$                           400.00
Amount to be finance	$                       1,600.00
No. of Paytment	24
Monthly Interest rate	1.50%
Installment Amount =	Loan	=	1600
	1 + PVAF(1.5%,23)		20.3308
	Installment/Month =	$78.70
No. of Payment	Outstanding Amount	Installment	Principal	Interest	Closing Balance
1	$                       1,600.00	$         78.70	$78.70	$0.00	$            1,521.30
2	$                       1,521.30	$         78.70	$55.88	$22.82	$            1,465.42
3	$                       1,465.42	$         78.70	$56.72	$21.98	$            1,408.71
4	$                       1,408.71	$         78.70	$57.57	$21.13	$            1,351.14
5	$                       1,351.14	$         78.70	$58.43	$20.27	$            1,292.71
6	$                       1,292.71	$         78.70	$59.31	$19.39	$            1,233.40
7	$                       1,233.40	$         78.70	$60.20	$18.50	$            1,173.20
8	$                       1,173.20	$         78.70	$61.10	$17.60	$            1,112.10
9	$                       1,112.10	$         78.70	$62.02	$16.68	$            1,050.09
10	$                       1,050.09	$         78.70	$62.95	$15.75	$               987.14
11	$                           987.14	$         78.70	$63.89	$14.81	$               923.25
12	$                           923.25	$         78.70	$64.85	$13.85	$               858.40
13	$                           858.40	$         78.70	$65.82	$12.88	$               792.58
14	$                           792.58	$         78.70	$66.81	$11.89	$               725.77
15	$                           725.77	$         78.70	$67.81	$10.89	$               657.96
16	$                           657.96	$         78.70	$68.83	$9.87	$               589.13
17	$                           589.13	$         78.70	$69.86	$8.84	$               519.27
18	$                           519.27	$         78.70	$70.91	$7.79	$               448.36
19	$                           448.36	$         78.70	$71.97	$6.73	$               376.39
20	$                           376.39	$         78.70	$73.05	$5.65	$               303.33
21	$                           303.33	$         78.70	$74.15	$4.55	$               229.18
22	$                           229.18	$         78.70	$75.26	$3.44	$               153.92
23	$                           153.92	$         78.70	$76.39	$2.31	$                  77.54
24	$                             77.54	$         78.70	$77.54	$1.16	$                    0.00

2. Amortisation Table For the Person Who can not pay down payment
Amount Payable	$                    2,000.00
No. of Paytment	36
Amount to be finance	$                    2,000.00
Monthly Interest rate	3.00%
Installment Amount =	Loan	=	$ 2,000.00
	1 + PVAF(3 %,35)		22.4872
	Installment/Month =		$        88.94
No. of Payment	Outstanding Amount	Installment	Principal	Closing Balance
1	$                    2,000.00	$         88.94	$88.94	$            1,911.06
2	$                    1,911.06	$                -	$31.61	$            1,879.45
3	$                    1,879.45	$                -	$32.56	$            1,846.90
4	$                    1,846.90	Principal	$33.53	$            1,813.36
5	$                    1,813.36	$         88.94	$34.54	$            1,778.83
6	$                    1,778.83	$         31.61	$35.57	$            1,743.25
7	$                    1,743.25	$         32.56	$36.64	$            1,706.61
8	$                    1,706.61	$         33.53	$37.74	$            1,668.87
9	$                    1,668.87	$         34.54	$38.87	$            1,630.00
10	$                    1,630.00	$         35.57	$40.04	$            1,589.96
11	$                    1,589.96	$         36.64	$41.24	$            1,548.72
12	$                    1,548.72	$         37.74	$42.48	$            1,506.24
13	$                    1,506.24	$         38.87	$43.75	$            1,462.48
14	$                    1,462.48	$         40.04	$45.06	$            1,417.42
15	$                    1,417.42	$         41.24	$46.42	$            1,371.00
16	$                    1,371.00	$         42.48	$47.81	$            1,323.19
17	$                    1,323.19	$         43.75	$49.24	$            1,273.95
18	$                    1,273.95	$         45.06	$50.72	$            1,223.23
19	$                    1,223.23	$         46.42	$52.24	$            1,170.99
20	$                    1,170.99	$         47.81	$53.81	$            1,117.18
21	$                    1,117.18	$         49.24	$55.42	$            1,061.75
22	$                    1,061.75	$         50.72	$57.09	$            1,004.67
23	$                    1,004.67	$         52.24	$58.80	$               945.87
24	$                        945.87	$         53.81	$60.56	$               885.30
25	$                        885.30	$         55.42	$62.38	$               822.92
26	$                        822.92	$         57.09	$64.25	$               758.67
27	$                        758.67	$         58.80	$66.18	$               692.49
28	$                        692.49	$         60.56	$68.16	$               624.33
29	$                        624.33	$         62.38	$70.21	$               554.12
30	$                        554.12	$         64.25	$72.32	$               481.80
31	$                        481.80	$         66.18	$74.49	$               407.32
32	$                        407.32	$         68.16	$76.72	$               330.60

Answer is not taking more than 65000 characters

I