An out-of-state family member asked for your assistance with financial planning. First, he is considering purchasing a house and would like you to create a detailed amortization table and calculate cumulative principal paid, as well as cumulative interest throughout the loan, total amount of interest, and interest for selected years. In addition, he is considering a five-year investment in which you invest $75 per month. He would like you to calculate the interest earned per month and the ending values.

1. Open e07m3Personal and save it as e07m3Personal_Lastname_Firstname.

2. Enter formulas on the Loan worksheet to complete the Calculations area.

3. Enter values 1 through 360 in the Payment Number Column.

4. Calculate values for the first payment using appropriate relative, mixed, and absolute references:

a. Beginning Balance: Create a reference to the appropriate value above the amortization table.

b. Monthly Payment: Enter a reference to the calculated monthly payment.

c. Interest Paid: Use the appropriate financial function to calculate the interest payment for the given period.

d. Principal Repayment: Use the appropriate financial function to calculate the principal repayment for the given period.

e. Ending payment: Enter the formula to calculate the ending balance after you make the first payment.

5. Type a reference to display the beginning balance for the second period. Copy formulas down their respective columns. Apply Accounting Number Format to the monetary values.

6. Calculate the following cumulative values:

a. Total interest: Enter the appropriate function to calculate the total interest for the entire loan in cell I6.

b. Cumulative Interest: Use the appropriate financial function to calculate the cumulative interest for each period, starting in cell H10. The final value in cell H369 should be identical to the value calculated in cell E3.

c. Interest Paid Summary: Enter the individual financial functions to calculate total interest paid during specific years in the range I2:I5. The first function calculates total interest for the fifth year only, which is $13,441.15.

7. Format the monetary values with Accounting Number Format.

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

9. 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 shading, such as progressive shading of blue, to each 12-month period of dates. Apply right horizontal alignment and increase the indent three times for the dates in column A.

10. Enter formulas for the first period:

a. Beginning Value: Type 0

b. Interest Earned: Enter a formula to calculate the interest for the first period. Use relative and mixed references only.

c. End-of-Period Invest: Enter a reference to the Deposit per Period found in the Input Area.

d. Ending Value: Calculate the Ending Value, which includes the beginning Value, Interest Earned, and End-of-Period Investment.

11. Calculate the second periods Beginning Value by referencing the previous periods Ending Value. Copy formulas down the columns.

12. Enter the appropriate financial function in cell E75 to calculate the final value of the investment. This value should be identical to the value shown in cell E71.

13. Format monetary values with Accounting Number Format.

14. Adjust margins and insert a page break so that the first three years of investment display on page 1. Center the worksheet data between the left and right margins, and repeat the column headings at the top of page 2. 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 of the Investment worksheet.