Click cell B2, click the Formulas tab, then click Date & Time in the Function Library group. Select NOW and click OK (on a Mac, click Done) to enter todays date in the cell.

Click cell D5, ensure the Formulas tab is displayed, click Logical in the Function Library group, and then select IF.

Type C5<=$E$14 in the Logical_test box, type $D$14*B5 in the Value_if_true box, and then type 0 in the Value_if_false box. Click OK. (On a Mac, click Done.)

Double-click the fill handle to copy the contents of cell D5 down the column, click Auto Fill Options to the lower-right of the copied cells, and then select Fill Without Formatting to ensure that the Bottom Double border remains applied to cell D10.

Calculate the Amount Financed by doing the following:

Click cell E5 and type =B5-D5.

Use the E5 fill handle to copy the function down, stopping at cell E10.

Apply Bottom Double border to cell E10.

Calculate the Rate by doing the following:

Click cell F5. Click Lookup & Reference in the Function Library group on the Formula tab and select VLOOKUP.

Type C5 in the Lookup_value box, type $A$14:$B$19 in the Table_array box, type 2 in the Col_index_num box, and then click OK.

Double-click the F5 fill handle to copy the function down the column.

Click Auto Fill Options and select Fill Without Formatting.

Calculate the required periodic payment by doing the following:

Click cell G5, click Financial in the Function Library Group, scroll down, and then select PMT.

Type F5/$D$17 in the Rate box, type $E$17 in the Nper box, type E5 in the Pv box, and then click OK.

Double-click the F5 fill handle to copy the function down the column.

Click Auto Fill Options and select Fill Without Formatting.

Calculate the required periodic payment by doing the following:

Click cell G5, click Financial in the Function Library Group, scroll down, and then select PMT.

Type F5/$D$17 in the Rate box, type $E$17 in the Nper box, type E5 in the Pv box, and then click OK.

Double-click cell G5s fill handle to copy the function down the column.

Click the Auto Fill Options button and select Fill Without Formatting.

Select the range B5:B10. Click Quick Analysis, click Totals, and then select Sum from the Quick Analysis Gallery. (On a Mac, select the range B5:B10 and click AutoSum on the ribbon.)

Click cell E11 and type =AVERAGE(E5:E10) to calculate the average amount financed.

Click cell A1 to return to the top of the worksheet.