Personal Financial Management ANALYSIS An out-of-state family member asked for your assistance with financial planning. First, he is con CASE sidering purchasing a house and would like you to create a detailed amortization table and calcu. late cumulative principal paid, as well as cumulative interest throughout the loan, total amount COLLABORATION of interest, and interest for selected years. In addition, he is considering a five-year investment in 0 CASE which you invest $75 per month. He would like you to calculate the interest earned per month and the ending values. Once you have completed the work, you will upload your file to OneDrive to allow for review. Student 1: a. Open 07m Personal and save it as e07m Personal LastFirst b. Enter formulas on the Loan worksheet to complete the Calculations area, which is range E2:E5 c. Enter values through 360 in the Payment Number column, starting in cell A10. d. Calculate values for the first payment on row 10 using appropriate relative, mixed, and abso- lute references: Beginning Balance: Create a reference to the appropriate value above the amortization table. Monthly Payment: Enter a reference to the calculated monthly payment. Interest Paid: Use the appropriate financial function to calculate the interest payment for the given period Principal Repayment: Use the appropriate financial function to calculate the principal repayment for the given period. Ending Balance: Enter the formula to calculate the ending balance after you make the first payment e. Type a reference to display the beginning balance for the second period. Copy formulas down their respective columns to row 369. Apply Accounting Number Format to the monetary values. f. Calculate the following cumulative values: Total Interest: Enter the appropriate financial function to calculate the total interest for the entire loan in cell 16 Cumulative Interest: Use the appropriate financial function to calculate the cumulative interest for each period, starting in cell 10. The final value in cell H 369 should be identi- cal to the value calculated in cell 16. Cumulative Principal: Use the appropriate financial function to calculate the cumulative principal for each period, starting in cell 110. The final value in cell 1369 should match the loan amount in cell E3. Interest Paid Summary: Enter individual financial functions to calculate total interest paid during specific years in the range 12:15. The first function calculates total interest for the fifth year only, which is $13.441.15. g. Format monetary values with Accounting Number Format h. Set appropriate margins and page scaling to fit one page so that if you decide to print the Loan worksheet, all columns fit across each page. Repeat the headings on row 9 on all pages. Create a footer with your name and the worksheet tab code on the right side of the Loan worksheet. i. Save the file to OneDrive to share with student 2. Student 2: 1. Open c07m 3 Personal_Last First and save it as e07m3Personal_LastFirst_LastFirst, using your name after the first student's name, k. Display the Investment worksheet and in cell A12 enter a reference to the original start of the first investment period date. In cell A13. enter the DATE function with nested YEAR, MONTH. and DAY functions with appropriate arguments. Ensure that the month number represents the next month. Copy the formula down the column and apply different but complementary shad- ing, such as starting with Dark Blue, Text 2. Lighter 80% for the first 12 months, applying 504 CHAPTER 7. Mid-Level Exercises Personal Financial Management ANALYSIS An out-of-state family member asked for your assistance with financial planning. First, he is con CASE sidering purchasing a house and would like you to create a detailed amortization table and calcu. late cumulative principal paid, as well as cumulative interest throughout the loan, total amount COLLABORATION of interest, and interest for selected years. In addition, he is considering a five-year investment in 0 CASE which you invest $75 per month. He would like you to calculate the interest earned per month and the ending values. Once you have completed the work, you will upload your file to OneDrive to allow for review. Student 1: a. Open 07m Personal and save it as e07m Personal LastFirst b. Enter formulas on the Loan worksheet to complete the Calculations area, which is range E2:E5 c. Enter values through 360 in the Payment Number column, starting in cell A10. d. Calculate values for the first payment on row 10 using appropriate relative, mixed, and abso- lute references: Beginning Balance: Create a reference to the appropriate value above the amortization table. Monthly Payment: Enter a reference to the calculated monthly payment. Interest Paid: Use the appropriate financial function to calculate the interest payment for the given period Principal Repayment: Use the appropriate financial function to calculate the principal repayment for the given period. Ending Balance: Enter the formula to calculate the ending balance after you make the first payment e. Type a reference to display the beginning balance for the second period. Copy formulas down their respective columns to row 369. Apply Accounting Number Format to the monetary values. f. Calculate the following cumulative values: Total Interest: Enter the appropriate financial function to calculate the total interest for the entire loan in cell 16 Cumulative Interest: Use the appropriate financial function to calculate the cumulative interest for each period, starting in cell 10. The final value in cell H 369 should be identi- cal to the value calculated in cell 16. Cumulative Principal: Use the appropriate financial function to calculate the cumulative principal for each period, starting in cell 110. The final value in cell 1369 should match the loan amount in cell E3. Interest Paid Summary: Enter individual financial functions to calculate total interest paid during specific years in the range 12:15. The first function calculates total interest for the fifth year only, which is $13.441.15. g. Format monetary values with Accounting Number Format h. Set appropriate margins and page scaling to fit one page so that if you decide to print the Loan worksheet, all columns fit across each page. Repeat the headings on row 9 on all pages. Create a footer with your name and the worksheet tab code on the right side of the Loan worksheet. i. Save the file to OneDrive to share with student 2. Student 2: 1. Open c07m 3 Personal_Last First and save it as e07m3Personal_LastFirst_LastFirst, using your name after the first student's name, k. Display the Investment worksheet and in cell A12 enter a reference to the original start of the first investment period date. In cell A13. enter the DATE function with nested YEAR, MONTH. and DAY functions with appropriate arguments. Ensure that the month number represents the next month. Copy the formula down the column and apply different but complementary shad- ing, such as starting with Dark Blue, Text 2. Lighter 80% for the first 12 months, applying 504 CHAPTER 7. Mid-Level Exercises