AutoSave OFF LoanAnalysis (class) Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Exchange Password Required Enter your password for "zaj5" in Internet Accounts. Internet Accounts. \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G & H & I \\ \hline 1 & \multicolumn{9}{|c|}{ Loan Analysis Worksheet } \\ \hline 2 & & & & & & & & & \\ \hline 3 & \multicolumn{2}{|c|}{ Purchase Price } & $975,000 & & & & & & \\ \hline 4 & & & & & & & & & \\ \hline 5 & Option \# & Down Payment & Nominal Interest Rate/yr & Duration (yrs) & Points & Fees & Loan Value & Actual Amount Borrowed & Monthly Payment \\ \hline 6 & 1 & 10% & 3.25% & 30 & 1 & 1500 & $877,500 & $887,775 & ($3,863.65) \\ \hline 7 & 2 & 7% & 3.50% & 30 & 0 & 0 & $906,750 & $906,750 & ($4,071.71) \\ \hline 8 & & & & & & & & & \\ \hline 9 & & & & & & & & & \\ \hline 10 & & & & & & & & & \\ \hline 11 & & & & & & & & & \\ \hline \end{tabular} 1. 3. 4. Answer 4 Calculation Points and Fees Interest Paid (24 months) Interest Paid (360 months) Total 24 Months Total 360 Months 5. 6. 7 Option \#1 Option \#2 Ethics Answer: 31 32 33 34 35 36 37 38 Option 1 \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} Option 2 \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} J Analyze Data K L M N AutoSave OFF LoanAnalysis (class) Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Exchange Password Required - Analysis E 8 Exchange Password Required Enter your password for "zaj5" in Internet Accounts. 1. How much in total interest was paid during the life of option 1 ? 2. True or False: You will pay more in total interest during the life of Option 1 then you will pay in principle? Explain your answer. 3. How much in total interest was paid during the life of option 2? 4. Which mortgage option is best for you if you plan on owning your home for the next 30 years, and which mortgage is best if you plan on only owning this home for the next 2 years? You should base your answer on interest and out-of-pocket expenses (that do not reduce your principal) not how much you paid total for each option. Use numbers you calculated to support and explain your answers. 5. If you make an extra $100 payment per month to Option 1 , how many years will it take you to pay off your mortgage? Copy your Amortization Option 1 data and paste it into the "Amortization Option 1+$100 " worksheet. Add $100 to each cell in the additional payment column then add the $100 to the principle amount each month. 6. How much interest will your client save over the life of the loan by making the $100 extra monthly payment? Spreadsheet Modeling \& Decision Analysis Case Study Outline 14 7. If you can afford only $1,500 per month, how much will the actual amount borrowed be for option 1 and for option 2? 8. You learn that you can offer your client a 5% interest rate lower interest rate on option 1 saving him thousands of dollars. Your company is in financial hardship and the CFO advises you to say nothing so your firm can pocket the savings. What would you do? - The completed worksheet should satisfy the following conditions. - The data organized in a logical layout. - Relevant, informative titles, columns and row labels. - Proper formatting for titles, labels and numeric values, including currency and % symbols, and appropriate decimal precision. AutoSave OFF LoanAnalysis (class) Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Exchange Password Required Enter your password for "zaj5" in Internet Accounts. Internet Accounts. \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G & H & I \\ \hline 1 & \multicolumn{9}{|c|}{ Loan Analysis Worksheet } \\ \hline 2 & & & & & & & & & \\ \hline 3 & \multicolumn{2}{|c|}{ Purchase Price } & $975,000 & & & & & & \\ \hline 4 & & & & & & & & & \\ \hline 5 & Option \# & Down Payment & Nominal Interest Rate/yr & Duration (yrs) & Points & Fees & Loan Value & Actual Amount Borrowed & Monthly Payment \\ \hline 6 & 1 & 10% & 3.25% & 30 & 1 & 1500 & $877,500 & $887,775 & ($3,863.65) \\ \hline 7 & 2 & 7% & 3.50% & 30 & 0 & 0 & $906,750 & $906,750 & ($4,071.71) \\ \hline 8 & & & & & & & & & \\ \hline 9 & & & & & & & & & \\ \hline 10 & & & & & & & & & \\ \hline 11 & & & & & & & & & \\ \hline \end{tabular} 1. 3. 4. Answer 4 Calculation Points and Fees Interest Paid (24 months) Interest Paid (360 months) Total 24 Months Total 360 Months 5. 6. 7 Option \#1 Option \#2 Ethics Answer: 31 32 33 34 35 36 37 38 Option 1 \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} Option 2 \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} J Analyze Data K L M N AutoSave OFF LoanAnalysis (class) Home Insert Draw Page Layout Formulas Data Review View Automate @ Tell me Exchange Password Required - Analysis E 8 Exchange Password Required Enter your password for "zaj5" in Internet Accounts. 1. How much in total interest was paid during the life of option 1 ? 2. True or False: You will pay more in total interest during the life of Option 1 then you will pay in principle? Explain your answer. 3. How much in total interest was paid during the life of option 2? 4. Which mortgage option is best for you if you plan on owning your home for the next 30 years, and which mortgage is best if you plan on only owning this home for the next 2 years? You should base your answer on interest and out-of-pocket expenses (that do not reduce your principal) not how much you paid total for each option. Use numbers you calculated to support and explain your answers. 5. If you make an extra $100 payment per month to Option 1 , how many years will it take you to pay off your mortgage? Copy your Amortization Option 1 data and paste it into the "Amortization Option 1+$100 " worksheet. Add $100 to each cell in the additional payment column then add the $100 to the principle amount each month. 6. How much interest will your client save over the life of the loan by making the $100 extra monthly payment? Spreadsheet Modeling \& Decision Analysis Case Study Outline 14 7. If you can afford only $1,500 per month, how much will the actual amount borrowed be for option 1 and for option 2? 8. You learn that you can offer your client a 5% interest rate lower interest rate on option 1 saving him thousands of dollars. Your company is in financial hardship and the CFO advises you to say nothing so your firm can pocket the savings. What would you do? - The completed worksheet should satisfy the following conditions. - The data organized in a logical layout. - Relevant, informative titles, columns and row labels. - Proper formatting for titles, labels and numeric values, including currency and % symbols, and appropriate decimal precision