c. In the spreadsheet shown in figure 9.4, what is the effect of a prepayment of $200 per month? What prepayment would be necessary to pay off the loan in 25 years instead of 30 years? Create the original models shown below. Then make changes in different cells, so I can see your changes. Insert one textbox to report and explain your solutions to above questions. model simple Joan - Microsoft Exce File Home Insert Page Layout Formulas Data Review View Calibri 11 AA= General ate BII A === pboard Font Alignment E Number % Conditional Form: Formatting as Tabl Styles F37 A D G H Dynamic Loan Calculation Model with Prepayment in Excel Loan Amount Interest Rate Number of Years $150,000 8.00% 30 E8*12 Number of Months 360 Interest Rate/Month 0.67%17/12 Monthly Loan Payment $1,100.65 PMT (E11, E10, E6, 0) $C$20 $ E$15 824+C24 $100.00 Excel Spreadsheet Dynamic Model Example of a Simple Loan A $100 Prepayment every Month-Loan is paid off in Month 270 Month Normal Prepay Payment Amount Payment Total Principle Owed $150,000 1 $1,100.65 $100.00 $1,200.65 2 $1,100.65 $100.00 $1,200.65 3 $1,100.65 $100.00 $1,200.65 4 $1.100.65 $100.00 $1,200.65 5 $1,100.65 $100.00 $1,200.65 $149.799 E23*(1+5E511)-024 $149,597 $149,394 $149.189 $148,983 Copy the Cells in Row 24 into Rows 25 through Row 383 to get 360 Months of Results Sheet1 Shoot2 Shoot3 Figure: 9.4