Apply Your Knowledge, Calculating Loan Payments

Page EX$4-50-$EX$4-51$ Omit question #$11.$

Do add your initials to cell E$3.$

Check before you submit that it matches Figure $466.$

NOTE: The instructions are not very clear. Don't type in the range in #$3$ on page $4-50.$ Instead just select the range and then enter ONLY the column input cell $($Interest rate$).$ Leave Row input cell blank!

NOTE $2$: Please unblock the data and remove any password protection.

Apply Your Knowledge

Reinforce the skills and apply the concepts you learned in

Calculating Loan Payments

Note: To complete this assignment, you will be required to instructor for information about accessing the Data Files. Instructions: Start Excel. Open the workbook called SC$-$ from which $466.$ You will consider saved

Figure $4-66$

Perform the following tasks:

Select the range B$4$:C$9.$ Use the 'Create from Selection' button $($Formulas tab I Defined Names group$)$ to create names for cells in the range $C4$:$C9$ using the row titles in the range B$4$:B$9.$

Enter the formulas shown in Table $4-4.$

Table $4-4$ Loan Payment Calculator and Interest Rate Schedule Formulas

$\backslash$table$[[$Cell$,$Formula$],[$C$8,=$ Price$-$Down$\_$Payment$],[$C$9,=-$ PMT$($Interest$\_$Rate$/12,12*$Years$,$ Loan$\_$Amount$)],[$F$4,=$ Monthly$\_$Payment$],[$G$4,=12**$ Monthly$\_$Payment$*$Years$+$Down$\_$Payment$],[$H$4,=$ G$4-$Price$]]$

Use the Data Table button in the What$-$If Analysis gallery $($Data tab I Forecast group$)$ to define the range $E4$:$H19$ as a one$-$input data table. Use the Interest Rate in the Loan Payment Calculator as the column input cell.

Use the Page Setup dialog box to select the Fit to and 'Black and white' options. Select the range $B2$:$C9$ and then use the 'Set Print Area' command to set a print area. Use the Print button on the Print screen in Backstage view to print the worksheet. Use the 'Clear Print Area command to clear the print area.