Question

You have asked a friend for a loan of $ 50,000. You both agree on an interest rate of 10 %. You promise to pay him/her in installments at the end of every month for next 2 years. Create a model in EXCEL to show a payment schedule by you. Assume today is 1/1/19. You can use PMT function, IPMT function and PPMT function. Each month end should also say how much of the principal is remaining.

Use the below mentioned format

Group member | Ashley Group member Tom Group member Max jones Smith Willow 3 Loan amount:50000 Annual Tax 10% Rate Years Principal payment Remaining Principal part Payment Interest payment part 1/31/2019 2/28/2019

***please include all steps and formulas**

Answer 1

Given information -

Loan Amount = 50000

years to amortize = 2 Year or 24 months

rate of interest = 10%

Calculation of loan installment -

Loan Installment = Loan Amount / PVIFA (10%/12, 24)

= 50000 / 21.67085

= 2307.246

Calculation of Loan Amortization Table -

Date	payment	Interest payment part (Remaining principal*0.008333)	Principal Payment part	Remaning Principal (principal -prin. Payment part)
1/1/2019				50000
1/31/2019	2307.25	416.67	1890.58	48109.42
2/28/2019	2307.25	400.91	1906.33	46203.09
"	2307.25	385.03	1922.22	44280.87
"	2307.25	369.01	1938.24	42342.63
"	2307.25	352.86	1954.39	40388.24
"	2307.25	336.57	1970.68	38417.56
"	2307.25	320.15	1987.10	36430.46
"	2307.25	303.59	2003.66	34426.80
"	2307.25	286.89	2020.36	32406.44
"	2307.25	270.05	2037.19	30369.25
"	2307.25	253.08	2054.17	28315.08
"	2307.25	235.96	2071.29	26243.79
"	2307.25	218.70	2088.55	24155.24
"	2307.25	201.29	2105.95	22049.29
"	2307.25	183.74	2123.50	19925.79
"	2307.25	166.05	2141.20	17784.59
"	2307.25	148.20	2159.04	15625.55
"	2307.25	130.21	2177.03	13448.52
"	2307.25	112.07	2195.18	11253.34
"	2307.25	93.78	2213.47	9039.87
"	2307.25	75.33	2231.91	6807.96
"	2307.25	56.73	2250.51	4557.45
"	2307.25	37.98	2269.27	2288.18
"	2307.25	19.07	2288.18	0.00

Please check with your answer and let me know.