Question

Du Feb.28 Assignment#1 Loan Amortization (Submission due at the beginning of class on Wednesday, October 23, 2019. Late submissions will not be accepted. A. You are thinking of purchasing a house that costs $225,000. You have $35,000 in cash that you can use as a down payment, but you need to borrow the rest of the purchase price. The bank is offering a 30-year mortgage that requires monthly payments and has an annual interest rate of 4.43% per year. • What will your monthly payments be if you sign up for this mortgage? • Present the amortization schedule (Beginning balance, Monthly payment, Principal payment, Interest payment, Ending balance) on a monthly basis using Excel. Calculate the total amount of interest paid throughout the life of the loan. Create a graph depicting the changes in the portions of interest and principal for each monthly payment throughout the life of the loan. Pu- 190k Frio N=36 wuths B. Suppose the interest rate decreases to 4.07% per year and the length of repayment decreases to 15 years. - What will the new monthly payment be? Present a new amortization schedule (Beginning balance, Monthly payment, Principal payment, Interest payment, Ending balance) in a separate Excel sheet. - Calculate the total amount of interest paid throughout the life of the loan. Create a graph depicting the changes in the portions of interest and principal for each monthly payment throughout the life of the loan. N= 180 month C. How much do you save if you go with the 15-year mortgage versus the 30-year mortgage example above? Compute the difference between the total interest paid in parts A and B. Notes: 1. It might help to use the landscape orientation instead of portrait) so that your amortization tables remain within margins when you print them. II. When you submit the order should be as follows: 1. Part A (including graph) 2. Part B (including graph) 3. Part C III. You may want to apply borders (Go to Home tab - Font tab - Borders) to the amortization schedule/table on excel so that the rows are distinct when you print the schedules. When printing out your assignment, please print only the first 10 rows and last 10 rows of the amortization schedule. To do this, highlight the middle rows, right click on the highlighted area and select hide. This will avoid printing out an unnecessary number of pages. V. Please staple the pages of your submission before you submit them to me.

Answer 1

Answering the first four question including the sub part as per policy :

Loan amount (PV) =225000-35000= 190,000

Interest rate(rate) = 4.3% ( annual) =0.3691%(monthly)

Loan Period(nper) = 12*30=360 (months)

FV=0

a.1 ) Monthly repayment : PMT(rate, nper, pv, [fv],)=PMT(0.3692%,360,-190000) =$ 954.82

\a. 2)

Repayment Number	Opening Balance	Loan Repayment	Interest Charged	Capital Repaid	Closing Balance
1	1,90,000.00	954.82	701.42	253.40	1,89,746.60
2	1,89,746.60	954.82	700.48	254.33	1,89,492.27
3	1,89,492.27	954.82	699.54	255.27	1,89,236.99
4	1,89,236.99	954.82	698.60	256.22	1,88,980.78
253	84,915.67	954.82	313.48	641.34	84,274.34
254	84,274.34	954.82	311.11	643.70	83,630.64
255	83,630.64	954.82	308.74	646.08	82,984.56
299	52,825.67	954.82	195.01	759.80	52,065.87
300	52,065.87	954.82	192.21	762.61	51,303.27
301	51,303.27	954.82	189.39	765.42	50,537.85
302	50,537.85	954.82	186.57	768.25	49,769.60
303	49,769.60	954.82	183.73	771.08	48,998.52
304	48,998.52	954.82	180.89	773.93	48,224.59
305	48,224.59	954.82	178.03	776.79	47,447.80
306	47,447.80	954.82	175.16	779.65	46,668.15
307	46,668.15	954.82	172.28	782.53	45,885.61
308	45,885.61	954.82	169.39	785.42	45,100.19
309	45,100.19	954.82	166.49	788.32	44,311.87
310	44,311.87	954.82	163.58	791.23	43,520.64
311	43,520.64	954.82	160.66	794.15	42,726.49
312	42,726.49	954.82	157.73	797.08	41,929.41
313	41,929.41	954.82	154.79	800.03	41,129.38
314	41,129.38	954.82	151.84	802.98	40,326.40
315	40,326.40	954.82	148.87	805.94	39,520.46
316	39,520.46	954.82	145.90	808.92	38,711.54
317	38,711.54	954.82	142.91	811.91	37,899.63
318	37,899.63	954.82	139.91	814.90	37,084.73
319	37,084.73	954.82	136.90	817.91	36,266.82
320	36,266.82	954.82	133.88	820.93	35,445.89
321	35,445.89	954.82	130.85	823.96	34,621.92
322	34,621.92	954.82	127.81	827.00	33,794.92
323	33,794.92	954.82	124.76	830.06	32,964.86
324	32,964.86	954.82	121.70	833.12	32,131.74
325	32,131.74	954.82	118.62	836.20	31,295.55
326	31,295.55	954.82	115.53	839.28	30,456.27
327	30,456.27	954.82	112.43	842.38	29,613.88
328	29,613.88	954.82	109.32	845.49	28,768.39
329	28,768.39	954.82	106.20	848.61	27,919.78
330	27,919.78	954.82	103.07	851.75	27,068.04
331	27,068.04	954.82	99.93	854.89	26,213.15
332	26,213.15	954.82	96.77	858.05	25,355.10
333	25,355.10	954.82	93.60	861.21	24,493.89
334	24,493.89	954.82	90.42	864.39	23,629.49
335	23,629.49	954.82	87.23	867.58	22,761.91
336	22,761.91	954.82	84.03	870.79	21,891.13
337	21,891.13	954.82	80.81	874.00	21,017.12
338	21,017.12	954.82	77.59	877.23	20,139.90
339	20,139.90	954.82	74.35	880.47	19,259.43
340	19,259.43	954.82	71.10	883.72	18,375.71
341	18,375.71	954.82	67.84	886.98	17,488.74
342	17,488.74	954.82	64.56	890.25	16,598.48
343	16,598.48	954.82	61.28	893.54	15,704.94
344	15,704.94	954.82	57.98	896.84	14,808.10
345	14,808.10	954.82	54.67	900.15	13,907.96
346	13,907.96	954.82	51.34	903.47	13,004.48
347	13,004.48	954.82	48.01	906.81	12,097.68
348	12,097.68	954.82	44.66	910.16	11,187.52
349	11,187.52	954.82	41.30	913.52	10,274.01
356	4,721.66	954.82	17.43	937.38	3,784.27
357	3,784.27	954.82	13.97	940.85	2,843.43
358	2,843.43	954.82	10.50	944.32	1,899.11
359	1,899.11	954.82	7.01	947.80	951.30
360	951.30	954.82	3.51	951.30	-
		3,43,733.65	1,53,733.65	1,90,000.00

a. 3) Total amount of interest is $ 1,53,733.65.

Total loan paid - Principle = $ 954.82*360 -190000= $ 1,53,733.65

a 4)

Interst and capital repayment 1,000.00 900.00 800.00 700.00 600.00 500.00 400.00 300.00 200.00 100.00 337 Interest Charged - Capital Repaid