Enter a function in cell B9 on the Investments worksheet that calculates the total of the values in the range B3:B8.

b. Copy the function that you just created in cell B9 and paste it into cells C9 and G9.

c. Enter a formula in cell E3 on the Investments worksheet that calculates the growth rate for the investments. Your formula should first subtract the value in the Invested Principal column from the value in the Current Balance column. Then, divide this result by the value in the Invested Principal column.

d. Copy the formula in cell E3 and paste it into the range E4:E8.

e. Copy the formula in cell E3 and paste it into cell E9 using the Paste Formulas option.

f. Enter a formula in cell D3 on the Investments worksheet that divides the Current Balance by the total in cell C9. Add an absolute reference to C9 in this formula.

g. Copy the formula in cell D3 and paste it into the range D4:D8.

h. In cell G3 on the Investments worksheet, use the Future Value (FV) function to calculate the future value of the investment in 2 years. Use the Target Growth Rate to define the Rate argument. This is not an annuity, therefore there are no periodic investments. Use the Current Balance to define the Present Value (PV) argument. Assume that the investment is made at the beginning of the period.

i. Copy the function in cell G3 and paste it into the range G4:G8.

j. Enter a function in cell B10 on the Investments worksheet that calculates the average of the values in the range B3:B8.

k. Copy the function in cell B10 and paste it into cells C10 and G10.

l. On the Mortgage worksheet (a page/bullet in the file), use the data provided to enter a formula in cell B6 to calculate the principal of the loan that will be required to purchase the house.

m. On the Mortgage worksheet, use the PMT function in cell B7 to calculate the monthly payments of the mortgage. Use cell locations from this worksheet to define each argument of the function. Assume that payments are made at the end of each month.

n. On the Auto Lease worksheet (another page/bullet in the file), use the PMT function in cell B6 to calculate the monthly lease payments. Use cell locations from this worksheet to define each argument of the function. Assume that the lease payments are due at the beginning of each month. 2

o. On the Auto Lease worksheet, use Goal Seek to change the Annual Interest rate in cell B2 so the monthly payments are exactly $200. 16. In cell E2 on the Summary worksheet (the last bullet/page in the file), use a cell reference to display the value in cell B9 in the Investments worksheet.

p. In cell E3 on the Summary worksheet, use a cell reference to display the value in cell G9 in the Investments worksheet.

18. Enter a formula in cell F4 on the Summary worksheet that subtracts the Principal of Investments from the 2-Year Future Value of Investments.

p Enter a formula in cell F5 on the Summary worksheet that calculates the amount of mortgage payments that will be made over 2 years. Your formula should multiply the value in B7 on the Mortgage worksheet by 24.

q. Enter a formula in cell F6 on the Summary worksheet that calculates the amount of lease payments that will be made over 2 years. Your formula should multiply the value in B6 on the Auto Lease worksheet by 24.

r. Enter a formula in cell F7 on the Summary worksheet that subtracts the sum of the values in the range F5:F6 from the value in cell F4.

s Sort the data in the range A2:G8 on the Investments worksheet. Sort the data based on the values in the Invested Principal column in ascending order (smallest to largest). For duplicate values in this column, sort using the values in the Target Growth Rate column in descending order (largest to smallest).

Arial 11 AA ze General Paste E- Conditional Formatting $ %. Format as Table Cell Styles BI U I a. A Insert 58 Delete Format Clipboard Font Alignment Number Styles Cells Editing J9 Xfx A B D E F G H Personal Investments 1 Percent to Total Growth Rate 2 Year Future Value 2 Investment Fund 3 Emerging Markets 4 Energy 5 Treasury Bond 6 Mid Cap Domestic 7 Corporate Bond 8 Domestic Dividend Invested Principal 10,000 10,000 10,000 15,000 15,000 20,000 Current Balance 10,000 10,800 10.400 14.250 15.750 19,400 Target Growth Rate 10.5% 9.0% 4.5% 9.5% 5.0% 8.0% 9 Total 10 Average 11 12 13 15 16 17 18 Investments Mortgage Auto Lease Summary I 3 I Type here to search I a. A Format as Table 2 Cell Styles Clipboard 2 Font Alignment Number 5 Styles B7 X Fox --PMT(B2/12,360,36,0) B C D E F G H Mortgage Information 2 4.90% 3 30 4 275,000 Annual Interest Rate Repayment Years Price of House Down Payment Principal of Loan Monthly Payments 5 55,000 6 7 8 9 10 17 12 12 Summary BIU General - A- . Merge & Center $ % Clipboard Font Alignment ly Number A2 XV fx Annual Interest Rate A B D E F G H 1 K Automobile Lease Annual Interest Rate 2 2.50% Terms of Lease (in months) 3 48 4 Price of Car 26.750 Residual Value at end of Lease 5 19,000 Monthly Payments 6 7 OD 10 Automobile Lease Annual Interest Rate Terms of Lease (in months) 48 Price of Car 26.750 Residual Value at end of Lease 19.000 Monthly Payments 12 13 14 15 board Font Alignment Number 12 > A B D E F G H Financial Summary Principal of Investments 12 Year Future Value of Investments 2 Year Investment Gains Two Years of Mortgage Payments 6 Two Years of Lease Payments 7 Investment Gains Less Mortgage and Lease Payments 8 9 10 11 12 13 14 15 16 17 18 Mortgage Auto Lease Investments Summary Circular References