Reference for the excel spreadsheet (this is from the example discussed in class): B C D G Input Loan Summary Loan Amount 500,000.00 Total Payment Total Interest| Total Principal 3 Interest Rate (annual] 4.75% ($700,048.80) $ 200,048.74 ($500,000.06) Loan Period (months) 180 Payments (PMT) ($3,889.16) Month # Payment Interest Principal Remainder Balance 500,000.00 ($3,889.16) $1,979.17 ($1,909.99] $ 498,090.01 (53,889.16) $1,971.61 ($1,917.55) $ 496,172.46 ($3,889.16) $1,964.02 ($1,925.14) $ 494,247.32 ($3,889.16) $1,956.40 ($1,932.76] 5 492,314.56 ($3,889.16) $1,948.75 ($1,940.41) $ 490,374.15 ($3,889.16) $1,941.06 ($1,948.10] $ 488,426.05 $3,889.16) $1,933.35 ($1,955.81) $ 486,470.24 ($3,889.16) $1,925.61 ($1,963.55] 5 484,506.69 ($3,889.16) $1,917.84 ($1,971.32] $ 482,535.37 10 ($3,889.16) $1,910.04 (51,979.12) $ 480,556.25 Instructions: 1. Dollar values and interest rates should be rounded to 2 decimals. 2. Convert the loan term to months. 3. The data format for all payments, including principal, monthly payments, interest payments and principal payments should be set to "Currency" (specifically, $). The 3 data format for interest rate should be set to "Percentage". Please refer to the example discussed in class on how to format cells.4. Use the Excel formulas discussed in the example in class. For example, for month # 1 in the reference amortization table given above, the formulas used are: Payment: =ROUND(PMT($B$3/12,$B$4,$B$2,0), 2) Interest (for the first month): =ROUND(E9*($B$3/12),2) Principal (for the first month): =B10+C10 Remainder Balance (for the first month): =E9+D10 For the loan summary table: Total Payment: =$B$5*180 Total Interest: =SUM(C10:C189) Total Principal: =SUM(D10:D189)To get the values for the remaining months, select cells B10 through E10. When you rest the mouse cursor over the bottom-right part of the selected area, the cursor will turn to a crosshair (a cross-shaped cursor). Drag the crosshair all the way down to the last month of payment. This populates all the cells until that row with the amortization schedule. You should hide the cells for payments intermediate months (e.g., between months 11 and 350) by selecting cells in those rows and using Home > Format > Hide & Unhide > Hide Rows. This removes the intermediate rows from view and creates a shorter table for better presentation. (This has also been discussed in class.)