As a loan officer at Tri-State Mortgage Company, you want to analyze loans that were managed by another officer who just retired. You will review the number of years to date for loans, identify pay-off years, and rates. In addition, you want to calculate summary statistics by 30-and 15-year loans. Furthermore, you want to calculate the cumulative interest and principal paid to date as well as the interest and principal payment for the current month, December 2021. Finally, you will analyze each loan to make a recommendation. To perform these calculations, you will use date, financial, conditional, and logical functions. Refer to Figure 7.35 as you complete this exercise.

a. Open e07p2Loans and save it as e07p2Loans_LastFirst.

b. Calculate the number of years since each loan was created by completing the following steps: • Click cell K6 in the Summary sheet, click the Formulas tab, click Date & Time in the Function Library group, and then select YEARFRAC. • Type J6 in the Start_date box, type B$2 in the End_date box, and then click OK. • Double-click the cell K6 fill handle to copy the function to the range K7:K16.

c. Calculate the year in which each loan matures by completing the following steps: • Click cell L6, click Date & Time, and then select YEAR. • Type J6 in the Serial_number box and click OK. • Edit the function by typing +H6 after the closing parenthesis. • Double-click the cell L6 fill handle to copy the function to the range L7:L16. d. Calculate the periodic rate and APR for each loan by completing the following steps: • Click cell G6, click Financial in the Function Library group, scroll down, and then select RATE. • Type I6 in the Nper box, type -E6 in the Pmt box, type D6 in the Pv box, and then click OK. • Double-click the cell G6 fill handle to copy the function to the range G7:G16. • Click cell F6, type =G6*B$3, and then press Ctrl+Enter. • Double-click the cell F6 fill handle to copy the formula to the range F7:F16.

e. Calculate the number of 30-year loans and the number of 15-year loans by completing the following steps: • Click cell B20, click More Functions in the Function Library group, point to Statistical, scroll down, and then select COUNTIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, and then click OK. • Drag the cell B20 fill handle across to cell C20.

f. Calculate the average loan amount for 30-year loans and for 15-year loans by completing the following steps: • Click cell B21, click More Functions, point to Statistical, and then select AVERAGEIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, type $D6:$D16 in the Average_range box, and then click OK. • Drag the cell B21 fill handle across to cell C21.

g. Calculate the total loan amount for 30-year loans and for 15-year loans by completing the fol-lowing steps: • Click cell B22, click Math & Trig in the Function Library group, scroll down, and then select SUMIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, type $D6:$D16 in the Sum_range box, and then click OK. • Drag the cell B22 fill handle across to cell C22.

h. Calculate the number of 30-year loans that are more than $350,000 and the number of 15-year loans that are more than $350,000 by completing the following steps: • Click cell B26, click More Functions, point to Statistical, scroll down, and then select COUNTIFS. • Type $D6:$D16 in the Criteria_range1 box, type “>350000” in the Criteria1 box, type $H6:$H16 in the Criteria_range2 box, type, type B18 in the Criteria2 box, and then click OK. • Drag the cell B26 fill handle across to cell C26.

i. Identify the highest 30-year loan that is more than $350,000 and the highest 15-year loan that is more than $350,000 by completing the following steps: • Click cell B27, click More Functions, point to Statistical, and then select MAXIFS. • Type $D6:$D16 in the Criteria_range1 box, type “>350000” in the Criteria1 box, type $H6:$H16 in the Criteria_range2 box, type B18 in the Criteria2 box, and then click OK. • Drag the cell B27 fill handle across to cell C27.

j. Adapt Step i to enter the MINIFS function in cell B28 and copy it to cell C28. k. Calculate the number of months that have transpired between their start dates and December 31, 2021, by subtracting the number of years from 2021, multiplying it by 12 months, and then adding the difference in months from the loan date and December 31, 2021, by complet-ing the following steps: • Click the Cumulative sheet tab and click K11. • Type =(YEAR(M$2)-YEAR(J11))*12+MONTH(M$2)-MONTH(J11) and press Ctrl+Enter. • Double-click the cell K11 fill handle to copy the formula to the range K12:K21.

l. Calculate the cumulative interest paid by each customer for the duration of the loan to date (December 31, 2021) by completing the following steps: • Click cell L11, click Financial in the Function Library group, and then select CUMIPMT. • Type G11 in the Rate box, type I11 in the Nper box, type D11 in the PV box, type 1 in the Start_period box, type K11 in the End_period box, press Tab, type 0 in the Type box, and then click OK. Edit the function by typing – to the right of the equal sign. • Double-click the cell L11 fill handle to copy the formula to the range L12:L21.

m. Calculate the cumulative principal paid by each customer for the duration of the loan to date (December 31, 2021) by completing the following steps: • Click cell M11, click Financial in the Function Library group, and then select CUMPRINC. • Type G11 in the Rate box, type I11 in the Nper box, type D11 in the PV box, type 1 in the Start_period box, type K11 in the End_period box, press Tab, type 0 in the Type box, and then click OK. Edit the function by typing – to the right of the equal sign. • Double-click the cell M11 fill handle to copy the formula to the range M12:M21.

n. Calculate the interest paid in the December 31, 2021, payment for each loan by completing the following steps: • Click cell N11, click Financial in the Function Library group, and then select IPMT. • Type G11 in the Rate box, type K11 in the Per box, type I11 in the Nper box, type -D11 in the PV box, and then click OK. • Double-click the cell N11 fill handle to copy the formula to the range N12:N21.

0. Calculate the principal paid in the December 31, 2021, payment for each loan by completing the following steps: • Click cell O11, click Financial in the Function Library group, and then select PPMT. • Type G11 in the Rate box, type K11 in the Per box, type I11 in the Nper box, type -D11 in the PV box, and then click OK. • Double-click the cell O11 fill handle to copy the formula to the range O12:O21.

p. Select the range L11:O21 and apply Accounting Number Format.

q. Display a message of either Keep the current rate or See if lower rate to refinance by completing the following steps: • Click cell P11, click Logical in the Function Library group on the Formulas tab, and then select IF. • Type AND(E11>M$4,F11>M$5) in the Logical_test box, type K$6 in the Value_if_true box, type K$7 in the Value_if_false box, and then click OK. • Double-click the cell P11 fill handle to copy the formula to the range P12:P21.

r. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets. s. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07p2Loans_LastFirst.

As a loan officer at Tri-State Mortgage Company, you want to analyze loans that were managed by another officer who just retired. You will review the number of years to date for loans, identify pay-off years, and rates. In addition, you want to calculate summary statistics by 30-and 15-year loans. Furthermore, you want to calculate the cumulative interest and principal paid to date as well as the interest and principal payment for the current month, December 2021. Finally, you will analyze each loan to make a recommendation. To perform these calculations, you will use date, financial, conditional, and logical functions. Refer to Figure 7.35 as you complete this exercise.

a. Open e07p2Loans and save it as e07p2Loans_LastFirst.

b. Calculate the number of years since each loan was created by completing the following steps: • Click cell K6 in the Summary sheet, click the Formulas tab, click Date & Time in the Function Library group, and then select YEARFRAC. • Type J6 in the Start_date box, type B$2 in the End_date box, and then click OK. • Double-click the cell K6 fill handle to copy the function to the range K7:K16.

c. Calculate the year in which each loan matures by completing the following steps: • Click cell L6, click Date & Time, and then select YEAR. • Type J6 in the Serial_number box and click OK. • Edit the function by typing +H6 after the closing parenthesis. • Double-click the cell L6 fill handle to copy the function to the range L7:L16. d. Calculate the periodic rate and APR for each loan by completing the following steps: • Click cell G6, click Financial in the Function Library group, scroll down, and then select RATE. • Type I6 in the Nper box, type -E6 in the Pmt box, type D6 in the Pv box, and then click OK. • Double-click the cell G6 fill handle to copy the function to the range G7:G16. • Click cell F6, type =G6*B$3, and then press Ctrl+Enter. • Double-click the cell F6 fill handle to copy the formula to the range F7:F16.

e. Calculate the number of 30-year loans and the number of 15-year loans by completing the following steps: • Click cell B20, click More Functions in the Function Library group, point to Statistical, scroll down, and then select COUNTIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, and then click OK. • Drag the cell B20 fill handle across to cell C20.

f. Calculate the average loan amount for 30-year loans and for 15-year loans by completing the following steps: • Click cell B21, click More Functions, point to Statistical, and then select AVERAGEIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, type $D6:$D16 in the Average_range box, and then click OK. • Drag the cell B21 fill handle across to cell C21.

g. Calculate the total loan amount for 30-year loans and for 15-year loans by completing the fol-lowing steps: • Click cell B22, click Math & Trig in the Function Library group, scroll down, and then select SUMIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, type $D6:$D16 in the Sum_range box, and then click OK. • Drag the cell B22 fill handle across to cell C22.

h. Calculate the number of 30-year loans that are more than $350,000 and the number of 15-year loans that are more than $350,000 by completing the following steps: • Click cell B26, click More Functions, point to Statistical, scroll down, and then select COUNTIFS. • Type $D6:$D16 in the Criteria_range1 box, type “>350000” in the Criteria1 box, type $H6:$H16 in the Criteria_range2 box, type, type B18 in the Criteria2 box, and then click OK. • Drag the cell B26 fill handle across to cell C26.

i. Identify the highest 30-year loan that is more than $350,000 and the highest 15-year loan that is more than $350,000 by completing the following steps: • Click cell B27, click More Functions, point to Statistical, and then select MAXIFS. • Type $D6:$D16 in the Criteria_range1 box, type “>350000” in the Criteria1 box, type $H6:$H16 in the Criteria_range2 box, type B18 in the Criteria2 box, and then click OK. • Drag the cell B27 fill handle across to cell C27.

j. Adapt Step i to enter the MINIFS function in cell B28 and copy it to cell C28. k. Calculate the number of months that have transpired between their start dates and December 31, 2021, by subtracting the number of years from 2021, multiplying it by 12 months, and then adding the difference in months from the loan date and December 31, 2021, by complet-ing the following steps: • Click the Cumulative sheet tab and click K11. • Type =(YEAR(M$2)-YEAR(J11))*12+MONTH(M$2)-MONTH(J11) and press Ctrl+Enter. • Double-click the cell K11 fill handle to copy the formula to the range K12:K21.

l. Calculate the cumulative interest paid by each customer for the duration of the loan to date (December 31, 2021) by completing the following steps: • Click cell L11, click Financial in the Function Library group, and then select CUMIPMT. • Type G11 in the Rate box, type I11 in the Nper box, type D11 in the PV box, type 1 in the Start_period box, type K11 in the End_period box, press Tab, type 0 in the Type box, and then click OK. Edit the function by typing – to the right of the equal sign. • Double-click the cell L11 fill handle to copy the formula to the range L12:L21.

m. Calculate the cumulative principal paid by each customer for the duration of the loan to date (December 31, 2021) by completing the following steps: • Click cell M11, click Financial in the Function Library group, and then select CUMPRINC. • Type G11 in the Rate box, type I11 in the Nper box, type D11 in the PV box, type 1 in the Start_period box, type K11 in the End_period box, press Tab, type 0 in the Type box, and then click OK. Edit the function by typing – to the right of the equal sign. • Double-click the cell M11 fill handle to copy the formula to the range M12:M21.

n. Calculate the interest paid in the December 31, 2021, payment for each loan by completing the following steps: • Click cell N11, click Financial in the Function Library group, and then select IPMT. • Type G11 in the Rate box, type K11 in the Per box, type I11 in the Nper box, type -D11 in the PV box, and then click OK. • Double-click the cell N11 fill handle to copy the formula to the range N12:N21.

0. Calculate the principal paid in the December 31, 2021, payment for each loan by completing the following steps: • Click cell O11, click Financial in the Function Library group, and then select PPMT. • Type G11 in the Rate box, type K11 in the Per box, type I11 in the Nper box, type -D11 in the PV box, and then click OK. • Double-click the cell O11 fill handle to copy the formula to the range O12:O21.

p. Select the range L11:O21 and apply Accounting Number Format.

q. Display a message of either Keep the current rate or See if lower rate to refinance by completing the following steps: • Click cell P11, click Logical in the Function Library group on the Formulas tab, and then select IF. • Type AND(E11>M$4,F11>M$5) in the Logical_test box, type K$6 in the Value_if_true box, type K$7 in the Value_if_false box, and then click OK. • Double-click the cell P11 fill handle to copy the formula to the range P12:P21.

r. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets. s. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07p2Loans_LastFirst.

As a loan officer at Tri-State Mortgage Company, you want to analyze loans that were managed by another officer who just retired. You will review the number of years to date for loans, identify pay-off years, and rates. In addition, you want to calculate summary statistics by 30-and 15-year loans. Furthermore, you want to calculate the cumulative interest and principal paid to date as well as the interest and principal payment for the current month, December 2021. Finally, you will analyze each loan to make a recommendation. To perform these calculations, you will use date, financial, conditional, and logical functions. Refer to Figure 7.35 as you complete this exercise.

a. Open e07p2Loans and save it as e07p2Loans_LastFirst.

b. Calculate the number of years since each loan was created by completing the following steps: • Click cell K6 in the Summary sheet, click the Formulas tab, click Date & Time in the Function Library group, and then select YEARFRAC. • Type J6 in the Start_date box, type B$2 in the End_date box, and then click OK. • Double-click the cell K6 fill handle to copy the function to the range K7:K16.

c. Calculate the year in which each loan matures by completing the following steps: • Click cell L6, click Date & Time, and then select YEAR. • Type J6 in the Serial_number box and click OK. • Edit the function by typing +H6 after the closing parenthesis. • Double-click the cell L6 fill handle to copy the function to the range L7:L16. d. Calculate the periodic rate and APR for each loan by completing the following steps: • Click cell G6, click Financial in the Function Library group, scroll down, and then select RATE. • Type I6 in the Nper box, type -E6 in the Pmt box, type D6 in the Pv box, and then click OK. • Double-click the cell G6 fill handle to copy the function to the range G7:G16. • Click cell F6, type =G6*B$3, and then press Ctrl+Enter. • Double-click the cell F6 fill handle to copy the formula to the range F7:F16.

e. Calculate the number of 30-year loans and the number of 15-year loans by completing the following steps: • Click cell B20, click More Functions in the Function Library group, point to Statistical, scroll down, and then select COUNTIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, and then click OK. • Drag the cell B20 fill handle across to cell C20.

f. Calculate the average loan amount for 30-year loans and for 15-year loans by completing the following steps: • Click cell B21, click More Functions, point to Statistical, and then select AVERAGEIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, type $D6:$D16 in the Average_range box, and then click OK. • Drag the cell B21 fill handle across to cell C21.

g. Calculate the total loan amount for 30-year loans and for 15-year loans by completing the fol-lowing steps: • Click cell B22, click Math & Trig in the Function Library group, scroll down, and then select SUMIF. • Type $H6:$H16 in the Range box, type B18 in the Criteria box, type $D6:$D16 in the Sum_range box, and then click OK. • Drag the cell B22 fill handle across to cell C22.

h. Calculate the number of 30-year loans that are more than $350,000 and the number of 15-year loans that are more than $350,000 by completing the following steps: • Click cell B26, click More Functions, point to Statistical, scroll down, and then select COUNTIFS. • Type $D6:$D16 in the Criteria_range1 box, type “>350000” in the Criteria1 box, type $H6:$H16 in the Criteria_range2 box, type, type B18 in the Criteria2 box, and then click OK. • Drag the cell B26 fill handle across to cell C26.

i. Identify the highest 30-year loan that is more than $350,000 and the highest 15-year loan that is more than $350,000 by completing the following steps: • Click cell B27, click More Functions, point to Statistical, and then select MAXIFS. • Type $D6:$D16 in the Criteria_range1 box, type “>350000” in the Criteria1 box, type $H6:$H16 in the Criteria_range2 box, type B18 in the Criteria2 box, and then click OK. • Drag the cell B27 fill handle across to cell C27.

j. Adapt Step i to enter the MINIFS function in cell B28 and copy it to cell C28. k. Calculate the number of months that have transpired between their start dates and December 31, 2021, by subtracting the number of years from 2021, multiplying it by 12 months, and then adding the difference in months from the loan date and December 31, 2021, by complet-ing the following steps: • Click the Cumulative sheet tab and click K11. • Type =(YEAR(M$2)-YEAR(J11))*12+MONTH(M$2)-MONTH(J11) and press Ctrl+Enter. • Double-click the cell K11 fill handle to copy the formula to the range K12:K21.

l. Calculate the cumulative interest paid by each customer for the duration of the loan to date (December 31, 2021) by completing the following steps: • Click cell L11, click Financial in the Function Library group, and then select CUMIPMT. • Type G11 in the Rate box, type I11 in the Nper box, type D11 in the PV box, type 1 in the Start_period box, type K11 in the End_period box, press Tab, type 0 in the Type box, and then click OK. Edit the function by typing – to the right of the equal sign. • Double-click the cell L11 fill handle to copy the formula to the range L12:L21.

m. Calculate the cumulative principal paid by each customer for the duration of the loan to date (December 31, 2021) by completing the following steps: • Click cell M11, click Financial in the Function Library group, and then select CUMPRINC. • Type G11 in the Rate box, type I11 in the Nper box, type D11 in the PV box, type 1 in the Start_period box, type K11 in the End_period box, press Tab, type 0 in the Type box, and then click OK. Edit the function by typing – to the right of the equal sign. • Double-click the cell M11 fill handle to copy the formula to the range M12:M21.

n. Calculate the interest paid in the December 31, 2021, payment for each loan by completing the following steps: • Click cell N11, click Financial in the Function Library group, and then select IPMT. • Type G11 in the Rate box, type K11 in the Per box, type I11 in the Nper box, type -D11 in the PV box, and then click OK. • Double-click the cell N11 fill handle to copy the formula to the range N12:N21.

0. Calculate the principal paid in the December 31, 2021, payment for each loan by completing the following steps: • Click cell O11, click Financial in the Function Library group, and then select PPMT. • Type G11 in the Rate box, type K11 in the Per box, type I11 in the Nper box, type -D11 in the PV box, and then click OK. • Double-click the cell O11 fill handle to copy the formula to the range O12:O21.

p. Select the range L11:O21 and apply Accounting Number Format.

q. Display a message of either Keep the current rate or See if lower rate to refinance by completing the following steps: • Click cell P11, click Logical in the Function Library group on the Formulas tab, and then select IF. • Type AND(E11>M$4,F11>M$5) in the Logical_test box, type K$6 in the Value_if_true box, type K$7 in the Value_if_false box, and then click OK. • Double-click the cell P11 fill handle to copy the formula to the range P12:P21.

r. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets. s. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07p2Loans_LastFirst.

a. Open e07p2Loans and save it as e07p2Loans_LastFirst.

a. Open e07p2Loans and save it as e07p2Loans_LastFirst.

p. Select the range L11:O21 and apply Accounting Number Format.

p. Select the range L11:O21 and apply Accounting Number Format.