Scenario $1-$Vanilla

You are a loan officer and your next client is Sandra Patterson. Sandra wants to borrow X from the bank at r percent interest compounded monthly to be repaid over n years. Complete the following:

$$ Workings illustrating the payment to be paid monthly

$$ Amortization table for entire loan period

Scenario $2-$Pre$-$Payment

Sandra is advised to make additional monthly payments on the principal of P for the first four years. Complete the following:

$$ Workings illustrating the payment to be paid monthly

$$ Amortization table for entire loan period

Scenario $3-$Downpayment on Vanilla

Assume after explaining Scenario $1$ and $2$ to your boss, they recommend that Sandra make a down payment of D$,$ thereby reducing the loan to Y$.$ Complete the following:

$$ Workings illustrating the payment to be paid monthly

$$ Amortization table for entire loan period

Scenario $4-$Downpayment on Vanilla and prepayments

In addition to the down payment of D$,$ Sandra is advised to make additional monthly payments on the principal of P for the first four years. Complete the following:

$$ Workings illustrating the payment to be paid monthly

$$ Amortization table for entire loan period

Sandra loan amount $$2,380,000$

Fixed interest rate $8\mathrm{.}5\%$ annually

Duration of loan $43$years

Downpayment amount $$71,400$

Prepayment on principal $$4760$

Instruction $-$ Using the information above complete the loan amortization table for each of the senario mentioned above on a seperate excel sheet and then answer the questions below.

Scenario $1($Loan of X$)$

$($i$)$ Principal paid in the first month?

$($ii$)$ Balance at the end of the $3$rd month?

$($iii$)$ Amount of interest in the last installment?

$($iv$)$ Total interest paid?

Scenario $2($Loan of $x$ and prepayments of $P$ for first $4$ years$)$

$($i$)$ Total Principal paid in the first month?

$($ii$)$ Balance at the end of the $3$rd month?

$($iii$)$ In what month will Sandra make her final payment?

$($iv$)$ What is the amount of the final payment?

$($v$)$ What is the difference in interest paid between Scenario $2$ and $1?$

$($vi$)$ What is the number of months the loan has been reduced by$?$

Scenario $3($Deposit of D on a loan of X$)$

$($i$)$ Total Principal paid in the first month?

$($ii$)$ Balance at the end of the $3$rd month?

$($ii$)$ In what month will Sandra make her final payment?

$($iv$)$ What is the amount of the final payment?

$($v$)$ What is the difference in interest paid between Scenario $3$ and $1?$

$($vi$)$ What is the difference in interest paid between Scenario $3$ and $2?$

$($vii$)$ What is the number of months the loan has been reduced by$?$

Scenario $4($Down payment of $D$ on a loan of $x$ with prepayments of $P$ for first $4$ years$)$

$($i$)$ Total Principal paid in the first month?

$($ii$)$ Balance at the end of the $3$rd month?

$($iii$)$ In what month will Sandra make her final payment?

$($iv$)$ What is the amount of the final payment?

$($v$)$ What is the difference in interest paid between Scenario $4$ and $1?$

$($vi$)$ What is the difference in interest paid between Scenario $4$ and $2?$

$($vii$)$ What is the difference in interest paid between Scenario $4$ and $3?$

$($viii$)$ What is the number of months the loan has been reduced by$?$

Summary

Write a paragraph summarizing your findings and analysis of the scenarios presented and suggest the best course of action for the client.