Problem $3$: As the new analytics hire at First Band & Trust, you have been asked to conduct an analysis

of the bank$$s mortgage loans. The data includes the customer$$s name, the loan date, loan amount, loan

duration $($in years$),$ Annual Percentage Rate $($APR$),$ number of completed payments by the customer $($all

loans are paid monthly$),$ and the date in which the loan will be fully paid. Note that if the date is in the

past, e$.$g$.,$ in a previous year, it means the loan has been paid off.

a$.$ For each loan, calculate the amount of interest paid to date. Place your answer in cell H$5.$

Auto$-$fill the column. You should not hard$-$code any values in your formula.

b$.$ For each loan, calculate the amount of principal paid to date. Place your answer in cell I$5.$

Auto$-$fill the column. You should not hard$-$code any values in your formula.

c$.$ Calculate the loan balance. Place your answer in cell J$5.$

The bank is considering making refinancing offers to customers with outstanding loans, e$.$g$.,$ loans that

have not been fully paid yet. The messages will focus on two themes:

$1.$ Offering a $1\%$ drop on the customer$$s Loan APR

$2.$ Suggesting that customers pay an additional $$150$ monthly

d$.$ Create a refinancing message that states $$We can offer you a new loan with a $1\%$ drop in

interest. Your new loan will have payments of: new loan payment over loan term years.$$

$$ Calculate the new loan payment.

If you do not know how to calculate the new loan payment, calculate the payment

based on the old loan, and display that in the parts below.

$$ Using Concatenation and the Text function construct the statement for each customer.

Note that the loan term is not the same for every customer, and that the bank is

assuming customers will refinance the new loan for the same duration as the original

loan.

$$ The message should only display for customers who have not finished paying off their

loans.

e$.$ Create a refinancing message that states $$If you pay an additional $$150$ each month, you will

reduce your repayment schedule by: reduction in repayment schedule. $$

$$ Calculate the reduction in repayment schedule.

If you do not know how to calculate reduction in payment schedule, calculate the

number of periods to repay the old loan with an additional $$150,$ and display that in the

parts below.

$$ Using Concatenation and the Text function construct the statement for each customer.

Note that the loan term is not the same for every customer, and that the bank is

assuming customers will refinance the new loan for the same duration as the original

loan.

$$ The message should only display for customers who have not finished paying off their

loans.