Data management Hands- Chapter 7

Open e07h2Salary_LastFirst if you closed it at the end of Hands-On Exercise 2, and save it as e07h3Salary_LastFirst, changing h2 to h3. Click the 3-Finance sheet tab.

You will calculate the periodic interest rate and number of payment periods before you can calculate the present value of the loan.

Click cell E3, type =B3/B5, and then press Enter.

The periodic rate, 0.438%, is the result of dividing the APR by the number of payments per year.

Click cell E4, type = B4*B5, and then press Enter.

The total number of monthly payments, 36, is the product of the number of years the loan is outstanding and the number of payments per year.

Click cell E2, click Financial in the Function Library group on the Formulas tab, scroll through the list, and then select PV.

The Function Argument dialog box opens.

Click cell E3 to enter that cell reference in the Rate box, click in the Nper box, and then click cell E4. Click in the Pmt box, type -B2, and then click OK.

The result is $19,944.64 based on three years of $600 monthly payments with an APR of 5.25%. You entered a negative sign before the Pmt argument to display the result as a positive value. If you do not enter a negative sign, Excel will display the loan as a negative value.

Apply Accounting Number Format to cell E2. Save the workbook.

Figure 7.33 PV Function

Figure 7.33 Full Alternative Text

Step 2 ?Insert Formulas in a Loan Amortization Table

Now you will create an amortization table. The column labels and payment numbers have already been entered into the worksheet. You will enter formulas to show the beginning loan balance for each payment, the monthly payment, interest paid, and principal repayment. Refer to Figure 7.34 as you complete Step 2.

Click cell B8, type =E2, and then press Tab.

You entered a reference to the original loan amount because that is the beginning balance for the first payment. Referencing the original cell is recommended instead of typing the value directly in the cell due to internal rounding. Furthermore, if you change the original input values, the calculated loan amount will change in both cells B8 and E2.

Type =B$2 in cell C8 and press Ctrl+Enter. Drag the cell C8 fill handle to copy the payment to the range C9:C43.

The monthly payment is $600.00. You entered a reference to the original monthly payment so that if you change it in cell B2, Excel will update the values in the Monthly Payment column automatically. The cell reference must be a mixed (B$2) or absolute ($B$2) reference to prevent the row number from changing when you copy the formula down the column later.

Click cell D8. Click Financial in the Function Library on the Formulas tab and select IPMT to open the Function Arguments dialog box. Type E$3 in the Rate box and type A8 in the Per box. Type E$4 in the Nper box, type -E$2 in the PV box, and then click OK. Drag the cell D8 fill handle to copy the IPMT function to the range D9:D43.

The IPMT function calculates the interest of a specific payment based on the starting balance of $19,944.64 with a periodic interest of .438% over 36 payments. By keeping cell A8 as a relative cell address, the function adjusts the period to match the specific period of evaluation.

Click cell E8, click Financial in the Function Library, and then select PPMT to open the Function Arguments dialog box. Type E$3 in the Rate box and type A8 in the Per box. Type E$4 in the Nper box, type -E$2 in the PV box, and then click OK. Drag the cell E8 fill handle to copy the PPMT function to the range E9:E43.

To calculate the principal repayment, subtract the interest of the first payment $87.26 from the monthly payment of $600. The remaining portion of the $512.74 payment goes toward paying down the principal owed. Using the PPMT function automatically completed these calculations.

Click in cell F8 and type =B8-E8. Drag the cell F8 fill handle to copy the formula to the range F9:F43.

This formula calculates the ending balance after the first payment is made. The ending balance of $19,431.90 is calculated by subtracting the amount of principal in the payment $512.74 from the balance currently owed $19,944.64. The copied formulas show negative results until you complete the Beginning Balance column next.

Click in cell B9, type =F8, and then press Ctrl+Enter. Drag the cell B9 fill handle to copy the cell reference to the range B10:B43.

The beginning balance of the second payment is also the ending balance of the first payment. The easiest method to populate the column is by referencing the ending balance from the prior month (cell F8). However, this can also be calculated by subtracting the previous principal repayment value (such as $512.74) from the previous month’s beginning balance (such as $19,944.64). The formula results in column F are now positive numbers. The ending balance in cell F43 should be $0, indicating that the loan has been completely paid off.

Select the range B8:F8 and apply Accounting Number Format. Select the range B9:F43 and apply Comma Style.

You formatted the first row of the amortization table with Accounting Number Format and applied Comma Style to the remaining payment rows.

Enter SUM functions in cells C44, D44, and E44.

The total of the monthly payments in cell C44 is $21,600, which includes principal and interest. The total interest paid over the three years in cell D44 is $1,655.36. The total of the principal repayment in cell E44 is $19,944.64, which is the amount of the loan in cell E2.

Select the range C44:E44. Click the Home tab, click Cell Styles in the Styles group, and then select Total. Save the workbook.

Figure 7.34 shows the top and bottom portions of the amortization table with rows 16 through 37 hidden.

Figure 7.34 Loan Amortization Table

Figure 7.34 Full Alternative Text