Your family is considering purchasing a house and investing in a business venture. You started the structure for a loan amortization table and the investment table. You will complete the first five years of the 20-year loan amortization table. To complete the table, you will enter formulas to calculate the beginning balance, monthly payment, and ending balance. You will use financial functions to calculate the interest and principal paid for each monthly payment. In addition, you want to calculate cumulative interest after the first year, total interest over the life of the loan, and the amount of principal paid after the first year. You also want to see how many months half or more of the payment is for interest. You will then focus your attention on completing an investment table using date functions, formulas, and a financial function to calculate the future value of the investment.

a. Open e07m2Finances and save it as e07m2Finances_LastFirst. b. Enter =D2 in cell B9 to reference the loan amount for the beginning balance it the Loan worksheet.

c. Enter a formula in cell C9 to reference the monthly payment in cell D3. Use a mixed reference and copy the formula to the range C10:C68.

d. Insert the IPMT function in cell D9 to calculate the interest paid for the first month using mixed cell references to the input area for the Rate, Nper, and PV arguments and using cell A9 for the Per argument. Make sure the result is a positive value and copy the function to the range D10:D68.

e. Insert the PPMT function in cell E9 to calculate the principal paid for the first month using mixed cell references to the input area for the Rate, Nper, and PV arguments and using cell A9 for the Per argument. Make sure the result is a positive value and copy the function to the range E10:E68.

f. Calculate the ending balance in cell F9 by subtracting the Principal Repayment from the Begin-ning Balance. Copy the formula to the range F10:F68.

g. Enter a formula in cell B10 to reference the first month’s ending balance in cell F9. Copy the formula to the range B11:B68.

h. Format the range B9:F68 with Accounting Number Format. i. Calculate cumulative values by completing the following steps: • Insert the CUMIPMT function in cell F2 that calculates the cumulative interest paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMIPMT function in cell F3 that calculates the total cumulative interest paid for the entire loan. Use A9 for the Start_period and D6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMPRINC function in cell F4 that calculates the cumulative principal paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value.

j. Insert the COUNTIF function in cell F5 that counts the number of payment periods in which the interest in the loan amortization table is higher than one-half of the monthly payment (cell D4). Apply General number format to the cell. k. Display the Investment sheet and insert the YEAR function in cell D4 that extracts the year from cell D3 and adds the number of years (cell B3) to identify the payoff year. Apply General number format.

l. Type =D3 in cell A7 to refer to the start date. Type 0 in cell B7, type =E7 in cell B8, and then copy the formula from cell B8 to the range B9:B54. Type a mixed reference to cell D2 in cell D7 and copy the formula from cell D7 to the range D8:D54.

m. Enter a formula in cell C7 that multiplies the beginning balance in cell B7 to the result of divid-ing the APR by the No. of Pmts per Year. Use mixed and relative cell references correctly. Copy the formula to the range C8:C54.

n. Type a formula in cell E7 to calculate the ending balance by adding the Beginning Balance, Interest Earned, and End-of-Period Investment for row 7. Copy the formula to the range E8:E54. o. Insert the DATE function in cell A8 with a nested YEAR function, MONTH function, and DAY function in the respective arguments. Within the Month argument, add 1 to the result of the MONTH function to display one month from the date in cell A7. Use cell A7 in the nested argu-ments. Copy the function to the range A9:A54 but preserve the fill color in those cells.

p. Insert a financial function in cell E56 to calculate the future value of the investment. Use refer-ences to the respective cells in the input area for the arguments.

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

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

Your family is considering purchasing a house and investing in a business venture. You started the structure for a loan amortization table and the investment table. You will complete the first five years of the 20-year loan amortization table. To complete the table, you will enter formulas to calculate the beginning balance, monthly payment, and ending balance. You will use financial functions to calculate the interest and principal paid for each monthly payment. In addition, you want to calculate cumulative interest after the first year, total interest over the life of the loan, and the amount of principal paid after the first year. You also want to see how many months half or more of the payment is for interest. You will then focus your attention on completing an investment table using date functions, formulas, and a financial function to calculate the future value of the investment.

a. Open e07m2Finances and save it as e07m2Finances_LastFirst. b. Enter =D2 in cell B9 to reference the loan amount for the beginning balance it the Loan worksheet.

c. Enter a formula in cell C9 to reference the monthly payment in cell D3. Use a mixed reference and copy the formula to the range C10:C68.

d. Insert the IPMT function in cell D9 to calculate the interest paid for the first month using mixed cell references to the input area for the Rate, Nper, and PV arguments and using cell A9 for the Per argument. Make sure the result is a positive value and copy the function to the range D10:D68.

e. Insert the PPMT function in cell E9 to calculate the principal paid for the first month using mixed cell references to the input area for the Rate, Nper, and PV arguments and using cell A9 for the Per argument. Make sure the result is a positive value and copy the function to the range E10:E68.

f. Calculate the ending balance in cell F9 by subtracting the Principal Repayment from the Begin-ning Balance. Copy the formula to the range F10:F68.

g. Enter a formula in cell B10 to reference the first month’s ending balance in cell F9. Copy the formula to the range B11:B68.

h. Format the range B9:F68 with Accounting Number Format. i. Calculate cumulative values by completing the following steps: • Insert the CUMIPMT function in cell F2 that calculates the cumulative interest paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMIPMT function in cell F3 that calculates the total cumulative interest paid for the entire loan. Use A9 for the Start_period and D6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMPRINC function in cell F4 that calculates the cumulative principal paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value.

j. Insert the COUNTIF function in cell F5 that counts the number of payment periods in which the interest in the loan amortization table is higher than one-half of the monthly payment (cell D4). Apply General number format to the cell. k. Display the Investment sheet and insert the YEAR function in cell D4 that extracts the year from cell D3 and adds the number of years (cell B3) to identify the payoff year. Apply General number format.

l. Type =D3 in cell A7 to refer to the start date. Type 0 in cell B7, type =E7 in cell B8, and then copy the formula from cell B8 to the range B9:B54. Type a mixed reference to cell D2 in cell D7 and copy the formula from cell D7 to the range D8:D54.

m. Enter a formula in cell C7 that multiplies the beginning balance in cell B7 to the result of divid-ing the APR by the No. of Pmts per Year. Use mixed and relative cell references correctly. Copy the formula to the range C8:C54.

n. Type a formula in cell E7 to calculate the ending balance by adding the Beginning Balance, Interest Earned, and End-of-Period Investment for row 7. Copy the formula to the range E8:E54. o. Insert the DATE function in cell A8 with a nested YEAR function, MONTH function, and DAY function in the respective arguments. Within the Month argument, add 1 to the result of the MONTH function to display one month from the date in cell A7. Use cell A7 in the nested argu-ments. Copy the function to the range A9:A54 but preserve the fill color in those cells.

p. Insert a financial function in cell E56 to calculate the future value of the investment. Use refer-ences to the respective cells in the input area for the arguments.

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

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

Your family is considering purchasing a house and investing in a business venture. You started the structure for a loan amortization table and the investment table. You will complete the first five years of the 20-year loan amortization table. To complete the table, you will enter formulas to calculate the beginning balance, monthly payment, and ending balance. You will use financial functions to calculate the interest and principal paid for each monthly payment. In addition, you want to calculate cumulative interest after the first year, total interest over the life of the loan, and the amount of principal paid after the first year. You also want to see how many months half or more of the payment is for interest. You will then focus your attention on completing an investment table using date functions, formulas, and a financial function to calculate the future value of the investment.

a. Open e07m2Finances and save it as e07m2Finances_LastFirst. b. Enter =D2 in cell B9 to reference the loan amount for the beginning balance it the Loan worksheet.

c. Enter a formula in cell C9 to reference the monthly payment in cell D3. Use a mixed reference and copy the formula to the range C10:C68.

d. Insert the IPMT function in cell D9 to calculate the interest paid for the first month using mixed cell references to the input area for the Rate, Nper, and PV arguments and using cell A9 for the Per argument. Make sure the result is a positive value and copy the function to the range D10:D68.

e. Insert the PPMT function in cell E9 to calculate the principal paid for the first month using mixed cell references to the input area for the Rate, Nper, and PV arguments and using cell A9 for the Per argument. Make sure the result is a positive value and copy the function to the range E10:E68.

f. Calculate the ending balance in cell F9 by subtracting the Principal Repayment from the Begin-ning Balance. Copy the formula to the range F10:F68.

g. Enter a formula in cell B10 to reference the first month’s ending balance in cell F9. Copy the formula to the range B11:B68.

h. Format the range B9:F68 with Accounting Number Format. i. Calculate cumulative values by completing the following steps: • Insert the CUMIPMT function in cell F2 that calculates the cumulative interest paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMIPMT function in cell F3 that calculates the total cumulative interest paid for the entire loan. Use A9 for the Start_period and D6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMPRINC function in cell F4 that calculates the cumulative principal paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value.

j. Insert the COUNTIF function in cell F5 that counts the number of payment periods in which the interest in the loan amortization table is higher than one-half of the monthly payment (cell D4). Apply General number format to the cell. k. Display the Investment sheet and insert the YEAR function in cell D4 that extracts the year from cell D3 and adds the number of years (cell B3) to identify the payoff year. Apply General number format.

l. Type =D3 in cell A7 to refer to the start date. Type 0 in cell B7, type =E7 in cell B8, and then copy the formula from cell B8 to the range B9:B54. Type a mixed reference to cell D2 in cell D7 and copy the formula from cell D7 to the range D8:D54.

m. Enter a formula in cell C7 that multiplies the beginning balance in cell B7 to the result of divid-ing the APR by the No. of Pmts per Year. Use mixed and relative cell references correctly. Copy the formula to the range C8:C54.

n. Type a formula in cell E7 to calculate the ending balance by adding the Beginning Balance, Interest Earned, and End-of-Period Investment for row 7. Copy the formula to the range E8:E54. o. Insert the DATE function in cell A8 with a nested YEAR function, MONTH function, and DAY function in the respective arguments. Within the Month argument, add 1 to the result of the MONTH function to display one month from the date in cell A7. Use cell A7 in the nested argu-ments. Copy the function to the range A9:A54 but preserve the fill color in those cells.

p. Insert a financial function in cell E56 to calculate the future value of the investment. Use refer-ences to the respective cells in the input area for the arguments.

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

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

g. Enter a formula in cell B10 to reference the first month’s ending balance in cell F9. Copy the formula to the range B11:B68. g. Enter a formula in cell B10 to reference the first month’s ending balance in cell F9. Copy the formula to the range B11:B68.

h. Format the range B9:F68 with Accounting Number Format. i. Calculate cumulative values by completing the following steps: • Insert the CUMIPMT function in cell F2 that calculates the cumulative interest paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMIPMT function in cell F3 that calculates the total cumulative interest paid for the entire loan. Use A9 for the Start_period and D6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMPRINC function in cell F4 that calculates the cumulative principal paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value. h. Format the range B9:F68 with Accounting Number Format. i. Calculate cumulative values by completing the following steps: • Insert the CUMIPMT function in cell F2 that calculates the cumulative interest paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMIPMT function in cell F3 that calculates the total cumulative interest paid for the entire loan. Use A9 for the Start_period and D6 for the End_period arguments. Make sure the result is a positive value. • Insert the CUMPRINC function in cell F4 that calculates the cumulative principal paid for the first year. Use A9 for the Start_period and B6 for the End_period arguments. Make sure the result is a positive value.

j. Insert the COUNTIF function in cell F5 that counts the number of payment periods in which the interest in the loan amortization table is higher than one-half of the monthly payment (cell D4). Apply General number format to the cell. k. Display the Investment sheet and insert the YEAR function in cell D4 that extracts the year from cell D3 and adds the number of years (cell B3) to identify the payoff year. Apply General number format. j. Insert the COUNTIF function in cell F5 that counts the number of payment periods in which the interest in the loan amortization table is higher than one-half of the monthly payment (cell D4). Apply General number format to the cell. k. Display the Investment sheet and insert the YEAR function in cell D4 that extracts the year from cell D3 and adds the number of years (cell B3) to identify the payoff year. Apply General number format.

l. Type =D3 in cell A7 to refer to the start date. Type 0 in cell B7, type =E7 in cell B8, and then copy the formula from cell B8 to the range B9:B54. Type a mixed reference to cell D2 in cell D7 and copy the formula from cell D7 to the range D8:D54. l. Type =D3 in cell A7 to refer to the start date. Type 0 in cell B7, type =E7 in cell B8, and then copy the formula from cell B8 to the range B9:B54. Type a mixed reference to cell D2 in cell D7 and copy the formula from cell D7 to the range D8:D54.

m. Enter a formula in cell C7 that multiplies the beginning balance in cell B7 to the result of divid-ing the APR by the No. of Pmts per Year. Use mixed and relative cell references correctly. Copy the formula to the range C8:C54.m. Enter a formula in cell C7 that multiplies the beginning balance in cell B7 to the result of divid-ing the APR by the No. of Pmts per Year. Use mixed and relative cell references correctly. Copy the formula to the range C8:C54.

n. Type a formula in cell E7 to calculate the ending balance by adding the Beginning Balance, Interest Earned, and End-of-Period Investment for row 7. Copy the formula to the range E8:E54. o. Insert the DATE function in cell A8 with a nested YEAR function, MONTH function, and DAY function in the respective arguments. Within the Month argument, add 1 to the result of the MONTH function to display one month from the date in cell A7. Use cell A7 in the nested argu-ments. Copy the function to the range A9:A54 but preserve the fill color in those cells. n. Type a formula in cell E7 to calculate the ending balance by adding the Beginning Balance, Interest Earned, and End-of-Period Investment for row 7. Copy the formula to the range E8:E54. o. Insert the DATE function in cell A8 with a nested YEAR function, MONTH function, and DAY function in the respective arguments. Within the Month argument, add 1 to the result of the MONTH function to display one month from the date in cell A7. Use cell A7 in the nested argu-ments. Copy the function to the range A9:A54 but preserve the fill color in those cells. n. Type a formula in cell E7 to calculate the ending balance by adding the Beginning Balance, Interest Earned, and End-of-Period Investment for row 7. Copy the formula to the range E8:E54. o. Insert the DATE function in cell A8 with a nested YEAR function, MONTH function, and DAY function in the respective arguments. Within the Month argument, add 1 to the result of the MONTH function to display one month from the date in cell A7. Use cell A7 in the nested argu-ments. Copy the function to the range A9:A54 but preserve the fill color in those cells.

p. Insert a financial function in cell E56 to calculate the future value of the investment. Use refer-ences to the respective cells in the input area for the arguments. p. Insert a financial function in cell E56 to calculate the future value of the investment. Use refer-ences to the respective cells in the input area for the arguments. p. Insert a financial function in cell E56 to calculate the future value of the investment. Use refer-ences to the respective cells in the input area for the arguments.

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

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