1. Consider a 15-year mortgage loan for $160,000 with a fixed annual interest rate of 6.50% and monthly payments. Using MS Excel, A) Construct the amortization table for this loan for its entire term of 15 years, month by month. Your table must have the following columns: (1) month, (2) beginning balance, (3) payment, (4) principal reduction, (5) interest paid. (6) ending balance. B) Plot a chart of the evolution of the loan's remaining mortgage balance over its 15-year term. C) Plot a chart showing the evolution of the mortgage payment, interest paid and principal reduction, month by month, over the loan's 15-year term. 2. Consider a 15-year mortgage loan for $160,000 with a fixed annual interest rate of 6.50% and monthly payments (the same loan as above). Using MS Excel, A) Construct the amortization table for this loan for its entire term pf 15 years, year by year. Your table must have the following columns: (1) year, (2) beginning balance, (3) annual spending on mortgage, (4) principal reduction, (5) interest paid, (6) ending balance. B) Plot a chart of the evolution of the loan's remaining mortgage balance over its 15-year term. C) Plot a chart showing the evolution of the mortgage payment, interest paid and principal reduction, year by year, over the loan's 15-year term. For the first part of the assignment, the first task is to create and to fill out the table in the following format. Beginning Balance 160,000 Remaining Balance Payment PiRed Int Month 1 2 3 4 5 Then make two plots: 1) loan's remaining mortgage balance measured on the vertical axis relative to time measured in months on the horizontal axis, and 2) mortgage payment, interest paid and principal reduction together on the same chart measured on the vertical axis relative to time measured in months on the horizontal axis. For the first part of the assignment, the first task is to create and to fill out the table in the following format. 3 Beginning Remaining Year Balance Spending PrRed Int Balance 1 160,000 2 3 4 5 Note that the only difference in the second part of the assignment relative to its first part is that the first column in the table reflects cash flows on annual basis instead of monthly one, as in the first part of the assignment. Everything else stays the same: same loan, same interest rate and even same monthly payments, as in the first part of the assignment. Then make two plots: 1) loan's remaining mortgage balance measured on the vertical axis relative to time measured in years on the horizontal axis, and 2) mortgage payment interest paid and principal reduction together on the same chart measured on the vertical axis relative to time measured in years on the horizontal axis. 1. Consider a 15-year mortgage loan for $160,000 with a fixed annual interest rate of 6.50% and monthly payments. Using MS Excel, A) Construct the amortization table for this loan for its entire term of 15 years, month by month. Your table must have the following columns: (1) month, (2) beginning balance, (3) payment, (4) principal reduction, (5) interest paid. (6) ending balance. B) Plot a chart of the evolution of the loan's remaining mortgage balance over its 15-year term. C) Plot a chart showing the evolution of the mortgage payment, interest paid and principal reduction, month by month, over the loan's 15-year term. 2. Consider a 15-year mortgage loan for $160,000 with a fixed annual interest rate of 6.50% and monthly payments (the same loan as above). Using MS Excel, A) Construct the amortization table for this loan for its entire term pf 15 years, year by year. Your table must have the following columns: (1) year, (2) beginning balance, (3) annual spending on mortgage, (4) principal reduction, (5) interest paid, (6) ending balance. B) Plot a chart of the evolution of the loan's remaining mortgage balance over its 15-year term. C) Plot a chart showing the evolution of the mortgage payment, interest paid and principal reduction, year by year, over the loan's 15-year term. For the first part of the assignment, the first task is to create and to fill out the table in the following format. Beginning Balance 160,000 Remaining Balance Payment PiRed Int Month 1 2 3 4 5 Then make two plots: 1) loan's remaining mortgage balance measured on the vertical axis relative to time measured in months on the horizontal axis, and 2) mortgage payment, interest paid and principal reduction together on the same chart measured on the vertical axis relative to time measured in months on the horizontal axis. For the first part of the assignment, the first task is to create and to fill out the table in the following format. 3 Beginning Remaining Year Balance Spending PrRed Int Balance 1 160,000 2 3 4 5 Note that the only difference in the second part of the assignment relative to its first part is that the first column in the table reflects cash flows on annual basis instead of monthly one, as in the first part of the assignment. Everything else stays the same: same loan, same interest rate and even same monthly payments, as in the first part of the assignment. Then make two plots: 1) loan's remaining mortgage balance measured on the vertical axis relative to time measured in years on the horizontal axis, and 2) mortgage payment interest paid and principal reduction together on the same chart measured on the vertical axis relative to time measured in years on the horizontal axis
