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% and comment (briefly) on the impact of mortgage rates on home affordability.

Expert Solution

Due to space restrictions, posting the first 10 and last 10 payments

Loan Amount	Interest Rate	Term in Years	Monthly Payment
$170,000.00	3.75%	30	$787.30

Month	StartingBalance	Interest	Principal	EndingBalance	TotalInterest
1	$170,000.00	$531.25	$256.05	$169,743.95	$531.25
2	$169,743.95	$530.45	$256.85	$169,487.11	$1,061.70
3	$169,487.11	$529.65	$257.65	$169,229.46	$1,591.35
4	$169,229.46	$528.84	$258.45	$168,971.00	$2,120.19
5	$168,971.00	$528.03	$259.26	$168,711.74	$2,648.22
6	$168,711.74	$527.22	$260.07	$168,451.67	$3,175.45
7	$168,451.67	$526.41	$260.89	$168,190.78	$3,701.86
8	$168,190.78	$525.60	$261.70	$167,929.08	$4,227.46
9	$167,929.08	$524.78	$262.52	$167,666.57	$4,752.23
10	$167,666.57	$523.96	$263.34	$167,403.23	$5,276.19

351	$7,739.32	$24.19	$763.11	$6,976.21	$113,317.29
352	$6,976.21	$21.80	$765.50	$6,210.72	$113,339.09
353	$6,210.72	$19.41	$767.89	$5,442.83	$113,358.49
354	$5,442.83	$17.01	$770.29	$4,672.54	$113,375.50
355	$4,672.54	$14.60	$772.69	$3,899.85	$113,390.10
356	$3,899.85	$12.19	$775.11	$3,124.74	$113,402.29
357	$3,124.74	$9.76	$777.53	$2,347.20	$113,412.06
358	$2,347.20	$7.34	$779.96	$1,567.24	$113,419.39
359	$1,567.24	$4.90	$782.40	$784.84	$113,424.29
360	$784.84	$2.45	$784.84	$0.00	$113,426.74

WORKINGS

PART 2

Loan Amount	Interest Rate	Term in Years	Monthly Payment
$170,000.00	8.75%	30	$1,337.39

Month	StartingBalance	Interest	Principal	EndingBalance	TotalInterest
1	$170,000.00	$1,239.58	$97.81	$169,902.19	$1,239.58
2	$169,902.19	$1,238.87	$98.52	$169,803.67	$2,478.45
3	$169,803.67	$1,238.15	$99.24	$169,704.43	$3,716.61
4	$169,704.43	$1,237.43	$99.96	$169,604.47	$4,954.03
5	$169,604.47	$1,236.70	$100.69	$169,503.78	$6,190.73
6	$169,503.78	$1,235.97	$101.43	$169,402.35	$7,426.70
7	$169,402.35	$1,235.23	$102.17	$169,300.19	$8,661.92
8	$169,300.19	$1,234.48	$102.91	$169,197.28	$9,896.40
9	$169,197.28	$1,233.73	$103.66	$169,093.62	$11,130.13
10	$169,093.62	$1,232.97	$104.42	$168,989.20	$12,363.11

351	$12,852.84	$93.72	$1,243.67	$11,609.17	$311,033.30
352	$11,609.17	$84.65	$1,252.74	$10,356.43	$311,117.95
353	$10,356.43	$75.52	$1,261.88	$9,094.55	$311,193.46
354	$9,094.55	$66.31	$1,271.08	$7,823.47	$311,259.78
355	$7,823.47	$57.05	$1,280.34	$6,543.13	$311,316.82
356	$6,543.13	$47.71	$1,289.68	$5,253.45	$311,364.53
357	$5,253.45	$38.31	$1,299.08	$3,954.36	$311,402.84
358	$3,954.36	$28.83	$1,308.56	$2,645.81	$311,431.67
359	$2,645.81	$19.29	$1,318.10	$1,327.71	$311,450.97
360	$1,327.71	$9.68	$1,327.71	$0.00	$311,460.65

Interest payments are much higher resulting in higher payments.

jeff jeffy answered 5 months ago

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%...

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...

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on July 1, how much interest will be earned in the first year? How much interest will be earned in the second year?

In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on May 1, how much interest will be earned in the first year? How much interest will be earned in the second year? Upload your Excel file below.

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

Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers...

Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on May 1, how much interest will be earned in the first year? How much interest will be earned in the second year? Upload your Excel file below. I dont know how to create an ammortization in excel. It is...

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.

Question