USE THE PV FUNCTION

Because Angela determined the monthly payment for an automobile, you will use the PV function to calculate the loan amount. Other variables, such as trade-in value of the current vehicle, are usually considered, but you will exclude those variables. Refer to Figure 7.30 as you complete Step 1.

a. 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.

b. 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.

c. Type =B4*B5 in cell E4 and press Enter. The total number of monthly payments, 48, is the product of the number of years the loan is outstanding and the number of payments per year.

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

e. 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,444.57 based on four years of $450 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.

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

INSERT FORMULAS IN A LOAN AMORTIZATION TABLE

Angela wants you to create an amortization table. The column labels and payment numbers have already been entered into the worksheet. Now 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.31 as you complete Step 2.

a. 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.

b. Type =B$2 in cell C8 and press Ctrl+Enter. Drag the cell C8 fill handle to copy the payment to the range C9:C55. The monthly payment is $450.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.

c. Click cell D8, click Financial in the Function Library on the Formulas tab, select IPMT to open the Function Arguments dialog box, type E$3 in the Rate box, 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:D55. The IPMT function calculates the interest of a specific payment based on the starting balance of $19,444.57 with a periodic interest of .438% over 48 payments. By keeping cell A8 as a relative cell address, the function adjusts the period to match the specific period of evaluation.

d. Click cell E8, click Financial in the Function Library, select PPMT to open the Function Arguments dialog box, type E$3 in the Rate box, 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:E55. To calculate the principal repayment, subtract the interest of the first payment $85.07 from the monthly payment of $450. The remaining portion of the payment $364.93 goes toward paying down the principal owed. Using the PPMT function automatically completed these calculations.

e. Click in cell F8 and type =B8-E8. Drag the cell F8 fill handle to copy the formula to the range F9:F55 This calculates the ending balance after the first payment is made. The ending balance of $19,079.64 is calculated by subtracting the amount of principal in the payment $364.93 from the balance currently owed $19,444.57. The copied formulas show negative results until you complete the Beginning Balance column next.

f. 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:B55

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 $364.93) from the previous month’s beginning balance (such as $19,444.57). The formula results in column F are now positive numbers. The ending balance in cell F55 should be $0, indicating that the loan has been completely paid off.

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

h. Enter SUM functions in cells C56, D56, and E56. i. Select the range C56:E56, click the Home tab, click Cell Styles in the Styles group, and then select Total. Save the workbook. You calculated totals for the appropriate columns, noting that column B is a running balance and cannot be logically totaled. Figure 7.31 shows the top and bottom portions of the amortization table with rows 11 through 47 hidden.

CALCULATE CUMULATIVE INTEREST WITH THE CUMIPMT FUNCTION

The loan amortization table shows how much of each payment is interest and how much pays down the principal. However, Angela wants you to include a column to show the cumulative interest after each payment. Refer to Figure 7.32 as you complete Step 3.

a. Click cell H8, click Financial in the Function Library group on the Formulas tab, and then select CUMIPMT. The Function Arguments dialog box displays so that you can enter the arguments for the CUMIPMT function.

b. Type the following arguments: E$3 in the Rate box, E$4 in the Nper box, E$2 in the Pv box, and A$8 in the Start_period box. Make sure the cell references you enter in the Rate, Nper, Pv, and Start_period boxes are mixed as shown to prevent the row number from changing as you copy the formula down the column.

c. Type A8 in the End_period box. This reference should be relative so that it reflects the current month’s payment number as you copy the formula down the column.

d. Press Tab, type 0 in the Type box, and then click OK. The cumulative interest for the first payment is the same as the first payment’s interest. However, the formula displays a negative result, as indicated by the parentheses.

e. Edit the function by typing – between = and CUMIPMT to convert the results to a positive value. Press Enter. The cumulative interest at the end of the first payment is identical to the interest on the first payment.

f. Copy the function through cell H55. Save the workbook. The cumulative interest in cell H55 should match the total interest paid calculated in cell D56: $2,155.43.

CALCULATE CUMULATIVE PRINCIPAL WITH THE CUMPRINC FUNCTION

Angela wants to see the cumulative principal paid after making each loan payment. You will use the CUMPRINC function to calculate the cumulative principal paid. Refer to Figure 7.33 as you complete Step 4.

a. Click cell I8, click Financial in the Function Library group on the Formulas tab, and then select CUMPRINC. The Function Arguments dialog box displays so that you can enter the arguments for the CUMPRINC function.

b. Type E$3 in the Rate box, E$4 in the Nper box, E$2 in the Pv box, A$8 in the Start_period box, A8 in the End_period box, press Tab, and then type 0 in the Type box.

c. Click OK and edit the function by typing – between = and CUMPRINC. Press Ctrl+Enter.

d. Copy the function from cell I8 to the range I9:I55. The cumulative principal in cell I55 should match the total principal repayment calculated in cell E56: $19,444.57.

e. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07h3Salary_LastFirst.

USE THE PV FUNCTION

Because Angela determined the monthly payment for an automobile, you will use the PV function to calculate the loan amount. Other variables, such as trade-in value of the current vehicle, are usually considered, but you will exclude those variables. Refer to Figure 7.30 as you complete Step 1.

a. 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.

b. 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.

c. Type =B4*B5 in cell E4 and press Enter. The total number of monthly payments, 48, is the product of the number of years the loan is outstanding and the number of payments per year.

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

e. 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,444.57 based on four years of $450 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.

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

INSERT FORMULAS IN A LOAN AMORTIZATION TABLE

Angela wants you to create an amortization table. The column labels and payment numbers have already been entered into the worksheet. Now 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.31 as you complete Step 2.

a. 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.

b. Type =B$2 in cell C8 and press Ctrl+Enter. Drag the cell C8 fill handle to copy the payment to the range C9:C55. The monthly payment is $450.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.

c. Click cell D8, click Financial in the Function Library on the Formulas tab, select IPMT to open the Function Arguments dialog box, type E$3 in the Rate box, 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:D55. The IPMT function calculates the interest of a specific payment based on the starting balance of $19,444.57 with a periodic interest of .438% over 48 payments. By keeping cell A8 as a relative cell address, the function adjusts the period to match the specific period of evaluation.

d. Click cell E8, click Financial in the Function Library, select PPMT to open the Function Arguments dialog box, type E$3 in the Rate box, 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:E55. To calculate the principal repayment, subtract the interest of the first payment $85.07 from the monthly payment of $450. The remaining portion of the payment $364.93 goes toward paying down the principal owed. Using the PPMT function automatically completed these calculations.

e. Click in cell F8 and type =B8-E8. Drag the cell F8 fill handle to copy the formula to the range F9:F55 This calculates the ending balance after the first payment is made. The ending balance of $19,079.64 is calculated by subtracting the amount of principal in the payment $364.93 from the balance currently owed $19,444.57. The copied formulas show negative results until you complete the Beginning Balance column next.

f. 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:B55

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 $364.93) from the previous month’s beginning balance (such as $19,444.57). The formula results in column F are now positive numbers. The ending balance in cell F55 should be $0, indicating that the loan has been completely paid off.

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

h. Enter SUM functions in cells C56, D56, and E56. i. Select the range C56:E56, click the Home tab, click Cell Styles in the Styles group, and then select Total. Save the workbook. You calculated totals for the appropriate columns, noting that column B is a running balance and cannot be logically totaled. Figure 7.31 shows the top and bottom portions of the amortization table with rows 11 through 47 hidden.

CALCULATE CUMULATIVE INTEREST WITH THE CUMIPMT FUNCTION

The loan amortization table shows how much of each payment is interest and how much pays down the principal. However, Angela wants you to include a column to show the cumulative interest after each payment. Refer to Figure 7.32 as you complete Step 3.

a. Click cell H8, click Financial in the Function Library group on the Formulas tab, and then select CUMIPMT. The Function Arguments dialog box displays so that you can enter the arguments for the CUMIPMT function.

b. Type the following arguments: E$3 in the Rate box, E$4 in the Nper box, E$2 in the Pv box, and A$8 in the Start_period box. Make sure the cell references you enter in the Rate, Nper, Pv, and Start_period boxes are mixed as shown to prevent the row number from changing as you copy the formula down the column.

c. Type A8 in the End_period box. This reference should be relative so that it reflects the current month’s payment number as you copy the formula down the column.

d. Press Tab, type 0 in the Type box, and then click OK. The cumulative interest for the first payment is the same as the first payment’s interest. However, the formula displays a negative result, as indicated by the parentheses.

e. Edit the function by typing – between = and CUMIPMT to convert the results to a positive value. Press Enter. The cumulative interest at the end of the first payment is identical to the interest on the first payment.

f. Copy the function through cell H55. Save the workbook. The cumulative interest in cell H55 should match the total interest paid calculated in cell D56: $2,155.43.

CALCULATE CUMULATIVE PRINCIPAL WITH THE CUMPRINC FUNCTION

Angela wants to see the cumulative principal paid after making each loan payment. You will use the CUMPRINC function to calculate the cumulative principal paid. Refer to Figure 7.33 as you complete Step 4.

a. Click cell I8, click Financial in the Function Library group on the Formulas tab, and then select CUMPRINC. The Function Arguments dialog box displays so that you can enter the arguments for the CUMPRINC function.

b. Type E$3 in the Rate box, E$4 in the Nper box, E$2 in the Pv box, A$8 in the Start_period box, A8 in the End_period box, press Tab, and then type 0 in the Type box.

c. Click OK and edit the function by typing – between = and CUMPRINC. Press Ctrl+Enter.

d. Copy the function from cell I8 to the range I9:I55. The cumulative principal in cell I55 should match the total principal repayment calculated in cell E56: $19,444.57.

e. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07h3Salary_LastFirst.

USE THE PV FUNCTION

Because Angela determined the monthly payment for an automobile, you will use the PV function to calculate the loan amount. Other variables, such as trade-in value of the current vehicle, are usually considered, but you will exclude those variables. Refer to Figure 7.30 as you complete Step 1.

a. 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.

b. 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.

c. Type =B4*B5 in cell E4 and press Enter. The total number of monthly payments, 48, is the product of the number of years the loan is outstanding and the number of payments per year.

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

e. 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,444.57 based on four years of $450 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.

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

INSERT FORMULAS IN A LOAN AMORTIZATION TABLE

Angela wants you to create an amortization table. The column labels and payment numbers have already been entered into the worksheet. Now 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.31 as you complete Step 2.

a. 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.

b. Type =B$2 in cell C8 and press Ctrl+Enter. Drag the cell C8 fill handle to copy the payment to the range C9:C55. The monthly payment is $450.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.

c. Click cell D8, click Financial in the Function Library on the Formulas tab, select IPMT to open the Function Arguments dialog box, type E$3 in the Rate box, 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:D55. The IPMT function calculates the interest of a specific payment based on the starting balance of $19,444.57 with a periodic interest of .438% over 48 payments. By keeping cell A8 as a relative cell address, the function adjusts the period to match the specific period of evaluation.

d. Click cell E8, click Financial in the Function Library, select PPMT to open the Function Arguments dialog box, type E$3 in the Rate box, 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:E55. To calculate the principal repayment, subtract the interest of the first payment $85.07 from the monthly payment of $450. The remaining portion of the payment $364.93 goes toward paying down the principal owed. Using the PPMT function automatically completed these calculations.

e. Click in cell F8 and type =B8-E8. Drag the cell F8 fill handle to copy the formula to the range F9:F55 This calculates the ending balance after the first payment is made. The ending balance of $19,079.64 is calculated by subtracting the amount of principal in the payment $364.93 from the balance currently owed $19,444.57. The copied formulas show negative results until you complete the Beginning Balance column next.

f. 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:B55

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 $364.93) from the previous month’s beginning balance (such as $19,444.57). The formula results in column F are now positive numbers. The ending balance in cell F55 should be $0, indicating that the loan has been completely paid off.

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

h. Enter SUM functions in cells C56, D56, and E56. i. Select the range C56:E56, click the Home tab, click Cell Styles in the Styles group, and then select Total. Save the workbook. You calculated totals for the appropriate columns, noting that column B is a running balance and cannot be logically totaled. Figure 7.31 shows the top and bottom portions of the amortization table with rows 11 through 47 hidden.

CALCULATE CUMULATIVE INTEREST WITH THE CUMIPMT FUNCTION

The loan amortization table shows how much of each payment is interest and how much pays down the principal. However, Angela wants you to include a column to show the cumulative interest after each payment. Refer to Figure 7.32 as you complete Step 3.

a. Click cell H8, click Financial in the Function Library group on the Formulas tab, and then select CUMIPMT. The Function Arguments dialog box displays so that you can enter the arguments for the CUMIPMT function.

b. Type the following arguments: E$3 in the Rate box, E$4 in the Nper box, E$2 in the Pv box, and A$8 in the Start_period box. Make sure the cell references you enter in the Rate, Nper, Pv, and Start_period boxes are mixed as shown to prevent the row number from changing as you copy the formula down the column.

c. Type A8 in the End_period box. This reference should be relative so that it reflects the current month’s payment number as you copy the formula down the column.

d. Press Tab, type 0 in the Type box, and then click OK. The cumulative interest for the first payment is the same as the first payment’s interest. However, the formula displays a negative result, as indicated by the parentheses.

e. Edit the function by typing – between = and CUMIPMT to convert the results to a positive value. Press Enter. The cumulative interest at the end of the first payment is identical to the interest on the first payment.

f. Copy the function through cell H55. Save the workbook. The cumulative interest in cell H55 should match the total interest paid calculated in cell D56: $2,155.43.

CALCULATE CUMULATIVE PRINCIPAL WITH THE CUMPRINC FUNCTION

Angela wants to see the cumulative principal paid after making each loan payment. You will use the CUMPRINC function to calculate the cumulative principal paid. Refer to Figure 7.33 as you complete Step 4.

a. Click cell I8, click Financial in the Function Library group on the Formulas tab, and then select CUMPRINC. The Function Arguments dialog box displays so that you can enter the arguments for the CUMPRINC function.

b. Type E$3 in the Rate box, E$4 in the Nper box, E$2 in the Pv box, A$8 in the Start_period box, A8 in the End_period box, press Tab, and then type 0 in the Type box.

c. Click OK and edit the function by typing – between = and CUMPRINC. Press Ctrl+Enter.

d. Copy the function from cell I8 to the range I9:I55. The cumulative principal in cell I55 should match the total principal repayment calculated in cell E56: $19,444.57.

e. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07h3Salary_LastFirst.

USE THE PV FUNCTION

USE THE PV FUNCTION

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

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

INSERT FORMULAS IN A LOAN AMORTIZATION TABLE

INSERT FORMULAS IN A LOAN AMORTIZATION TABLE

CALCULATE CUMULATIVE INTEREST WITH THE CUMIPMT FUNCTION

CALCULATE CUMULATIVE INTEREST WITH THE CUMIPMT FUNCTION

CALCULATE CUMULATIVE PRINCIPAL WITH THE CUMPRINC FUNCTION

CALCULATE CUMULATIVE PRINCIPAL WITH THE CUMPRINC FUNCTION

c. Click OK and edit the function by typing – between = and CUMPRINC. Press Ctrl+Enter.

c. Click OK and edit the function by typing – between = and CUMPRINC. Press Ctrl+Enter.