Question

Part A

You are thinking of purchasing a house. The house costs $350,000. You have $50,000 in cash that you can use as a down payment on the house, but you need to borrow rest of the purchase price. The bank is offering a 20-year mortgage that requires monthly payments and has an annual interest rate of 5% per year. Determine your monthly payments if you sign up for this mortgage. Draw the amortization schedule, monthly, 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. Identify the period of the break-even point, where the principal and interest payment amounts are equal.

Part B

Suppose you can only afford a monthly mortgage payment of $3,000 per month, would you purchase this house, if the interest rate increases to 7% per year and the length of repayment decreases to 15 years? Explain. Draw the new amortization schedule in a separate Excel sheet. Calculate the total amount of interest paid throughout the life of the loan. Even though the interest rate is higher, can the total amount of interest paid for the life of the loan be less than the total interest paid in the first amortization schedule? If so, by how much less? Explain.

Answer 1

		Cost of house	$350,000
		Down Payment	$50,000
	Pv	Mortgage Loan	$300,000
	Rate	Monthly interest =(5/12)%	0.4167%
	Nper	Number of months of repayment	240	(20*12)
	PMT	Amount to be repaid every month	$1,979.87	(using PMT function of excel with Rate =0.4167%, Nper=240,Pv=-300000)
		AMORTIZATION SCHEDULE
		Total Interest paid during life of loan	$175,168	(1979.87*240)-300000)
		Principal and interest equal in month	75	A	B	C=A*0.4167%	D=B-C	E=A-D
			Month	Beginning	Monthly	Interest	Principal	Ending
				Balance	Repayment	Payment	Payment	Loan Balance
			1	$300,000	$1,979.87	$1,250.00	$729.87	$299,270.13
			2	$299,270.13	$1,979.87	$1,246.96	$732.91	$298,537.22
			3	$298,537.22	$1,979.87	$1,243.91	$735.96	$297,801.26
			4	$297,801.26	$1,979.87	$1,240.84	$739.03	$297,062.23
			5	$297,062.23	$1,979.87	$1,237.76	$742.11	$296,320.13
			6	$296,320.13	$1,979.87	$1,234.67	$745.20	$295,574.93
			7	$295,574.93	$1,979.87	$1,231.56	$748.31	$294,826.62
			8	$294,826.62	$1,979.87	$1,228.44	$751.42	$294,075.20
			9	$294,075.20	$1,979.87	$1,225.31	$754.55	$293,320.64
			10	$293,320.64	$1,979.87	$1,222.17	$757.70	$292,562.95
			11	$292,562.95	$1,979.87	$1,219.01	$760.85	$291,802.09
			12	$291,802.09	$1,979.87	$1,215.84	$764.03	$291,038.07
			13	$291,038.07	$1,979.87	$1,212.66	$767.21	$290,270.86
			14	$290,270.86	$1,979.87	$1,209.46	$770.41	$289,500.45
			15	$289,500.45	$1,979.87	$1,206.25	$773.62	$288,726.84
			16	$288,726.84	$1,979.87	$1,203.03	$776.84	$287,950.00
			17	$287,950.00	$1,979.87	$1,199.79	$780.08	$287,169.92
			18	$287,169.92	$1,979.87	$1,196.54	$783.33	$286,386.60
			19	$286,386.60	$1,979.87	$1,193.28	$786.59	$285,600.01
			20	$285,600.01	$1,979.87	$1,190.00	$789.87	$284,810.14
			21	$284,810.14	$1,979.87	$1,186.71	$793.16	$284,016.98
			22	$284,016.98	$1,979.87	$1,183.40	$796.46	$283,220.52
			23	$283,220.52	$1,979.87	$1,180.09	$799.78	$282,420.74
			24	$282,420.74	$1,979.87	$1,176.75	$803.11	$281,617.62