1) Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at...

1) Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. 2) Repeat the analysis, changing the interest rate to 8.75% and comment (briefly) on the impact of mortgage rates on home affordability. 3) In both of the previous cases, how much quicker do I pay off my loan if I pay an extra $100 per month on my mortgage payment?

Expert Solution


Pv	Amount of loan	$170,000
Nper	Number of moths of payment	360	(30*12)
Rate	Monthly interest rate=(3.75/12)%	0.3125%
PMT	Monthly mortgage payment	$787.30	(Using PMT function of excel with Rate=0.3125%,Nper=360,Pv=-170000)
Month	Year	Interest Paid	Principal Paid	Ending Balance
.1-12	1	$6,321.64	$3,125.92	$166,874.08
13-24	2	$6,202.38	$3,245.18	$163,628.90
25-36	3	$6,078.57	$3,368.99	$160,259.91
37-48	4	$5,950.04	$3,497.52	$156,762.39
49-60	5	$5,816.60	$3,630.95	$153,131.44
61-72	6	$5,678.08	$3,769.48	$149,361.96
73-84	7	$5,534.27	$3,913.29	$145,448.67
85-96	8	$5,384.97	$4,062.59	$141,386.08
97-108	9	$5,229.98	$4,217.58	$137,168.50
109-120	10	$5,069.07	$4,378.49	$132,790.02
121-132	11	$4,902.03	$4,545.53	$128,244.49
133-144	12	$4,728.61	$4,718.95	$123,525.54
145-156	13	$4,548.58	$4,898.98	$118,626.56
157-168	14	$4,361.67	$5,085.88	$113,540.67
169-180-	15	$4,167.64	$5,279.92	$108,260.75
181-192	16	$3,966.20	$5,481.35	$102,779.40
193-204	17	$3,757.08	$5,690.47	$97,088.93
205-216	18	$3,539.98	$5,907.57	$91,181.35
217-228	19	$3,314.60	$6,132.95	$85,048.40
229-240	20	$3,080.62	$6,366.94	$78,681.46
241-252	21	$2,837.72	$6,609.84	$72,071.62
253-264	22	$2,585.54	$6,862.02	$65,209.61
265-276	23	$2,323.75	$7,123.81	$58,085.80
277-288	24	$2,051.96	$7,395.59	$50,690.20
289-300	25	$1,769.81	$7,677.74	$43,012.46
301-312	26	$1,476.90	$7,970.66	$35,041.80
313-324	27	$1,172.81	$8,274.75	$26,767.05
325-336	28	$857.11	$8,590.44	$18,176.60
337-348	29	$529.38	$8,918.18	$9,258.42
349-360	30	$189.14	$9,258.42	$0.00

	MORTGAGE SCHEDULE:
			A	B	C=A*0.3125%	D=B-C	E=A-D
		Month	Beginning Loan Balance	Monthly Payment	Interest	Principal	Ending Loan Balance
		1	$170,000	$787.30	$531.25	$256.05	$169,743.95
		2	$169,743.95	$787.30	$530.45	$256.85	$169,487.11
		3	$169,487.11	$787.30	$529.65	$257.65	$169,229.46

jeff jeffy answered 1 year ago

Excel assignment Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000...

Excel assignment Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. Repeat the analysis, changing the interest rate to 8.75%...

Please explain, Excel assignment. Prepare an amortization table for a 30-year mortgage where the homeowner is...

Please explain, Excel assignment. Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. Repeat the analysis, changing the interest rate...

Your 30 year mortgage is $350,000 with an interest rate of 4%. Prepare the amortization schedule...

Your 30 year mortgage is $350,000 with an interest rate of 4%. Prepare the amortization schedule for repayment of the mortgage

On the third tab build the full amortization table for a 30 year Constant Payment Mortgage...

On the third tab build the full amortization table for a 30 year Constant Payment Mortgage (CPM) Loan with a 4.5% interest rate compounded monthly. The initial loan amount should be $2,500,000. in excel

Consider a 30-year, $170,000 mortgage with a rate of 5.70 percent. Seven years into the mortgage,...

Consider a 30-year, $170,000 mortgage with a rate of 5.70 percent. Seven years into the mortgage, rates have fallen to 5 percent. What would be the monthly saving to a homeowner from refinancing the outstanding mortgage balance at the lower rate?

A homeowner took out a 30-year, fixed-rate mortgage of $145,000. The mortgage was taken out 6...

A homeowner took out a 30-year, fixed-rate mortgage of $145,000. The mortgage was taken out 6 years ago at a rate of 8.4 percent. If the homeowner refinances, the charges will be $2,550. What is the highest interest rate at which it would be beneficial to refinance the mortgage? (Do not round intermediate calculations. Enter your answer as a percent rounded to 2 decimal places.)

A homeowner paid a fixed 3% on a 30-year mortgage of $200,000. 11a. Calculate the monthly...

A homeowner paid a fixed 3% on a 30-year mortgage of $200,000. 11a. Calculate the monthly mortgage payment. [10 points] 11b. Calculate the total interest payment over this 30-year period. [5 points] 11c. In the third monthly mortgage payment, calculate the interest payment. [5 points] 11d. In the fourth monthly mortgage payment, calculate the principal payment.

a homeowner can obtain 250,000, 30 year fixed mortgage at a rate of 6% with zero...

a homeowner can obtain 250,000, 30 year fixed mortgage at a rate of 6% with zero points or at a rate of 5.5% with 2.25 points. IF you will keep the mortgage for 30 years, what is the net present value of paying the points (to the nearest dollar)

A homeowner can obtain a 250,000, 30 year fixed rate mortgage at a rate of 6.0%...

A homeowner can obtain a 250,000, 30 year fixed rate mortgage at a rate of 6.0% with zero points or at a rate of 5.5% with 2.25 points. How long must the owner stay in the house to make it worthwhile to pay the points if the payment saving is not invested?

A homeowner can obtain a $250,000, 30-year fixed-rate mortgage at a rate of 6.0 percent with...

A homeowner can obtain a $250,000, 30-year fixed-rate mortgage at a rate of 6.0 percent with zero points or at a rate of 5.5 percent with 2.25 points. How long must the owner stay in the house to make it worthwhile to pay the points if the payment saving is NOT invested? please answer in excel format

Question