*ANSWERS MUST BE DONE IN EXCEL AND SHOW FORMULAS*

Objective: This assignment provides some business and personal applications of the four basic time value of money formulas we discussed in class. It is very important to pay attention to the timing of deposits and/or withdrawals (the beginning versus the end of the period) when determining the requested values. Upon completion of the assignment, you will be able to determine the present and future values for equal and unequal cash streams.

Protocols: The assignment must be completed in Excel or Google Sheets. You may work with one partner in the same course section and you will upload one copy of the completed spreadsheet to Canvas. The two of you will decide which partner is responsible for uploading the spreadsheet for the team. Display all dollar values as dollars and cents (e.g., $ 10.46), and display all percentages with two decimal places (e.g., 1.14%, not 0.01).

Problem 1: Determine the present value of each investment specified below. Assume a 14 percent discount rate, annual discounting, and each projected future receipt is received at the end of the year. Point allocations for each part of this problem appear below. Note: I must be able to click on each cell containing your computed values and see the formula or function used to determine its value. If the cell contains only a typed value, no credit will be earned.

Year	Z	Y	X
1	$ 7,500		$ 7,500
2	7,500
3	7,500
4	7,500
5	7,500	$ 7,500
6		7,500	25,000
7		7,500
8		7,500
9		7,500
10		7,500	7,500

1. The present value of investment Z. (2 points)
2. The present value of investment Y. (3 points)
3. The present value of investment X. You must show the present value of each cash flow and the total present value; four values must appear on the spreadsheet. (4 points)

Problem 2: Your aunt Maria is planning for retirement in 12 years. She currently has a portfolio made up of $50,000 in a money market savings account and $125,000 in a mutual fund. She receives an annual bonus from her current employer that she plans to deposit in the savings account at the end of each year for the next 12 years. The annual rates of return for the savings account and mutual fund, and the annual amount deposited in the savings account appear in the following table.

The annual rate of return on savings account	4.00 percent
The annual rate of return on mutual fund	7.50 percent
The end-of-year savings deposit for years 1 - 6	$2,500
The end-of-year savings deposit for years 7 - 12	$5,000

1. Compute the total amount of money in Aunt Marias portfolio when she retires at the end of 12 years. To accomplish this, you must show the following values: 1. The future value of the initial savings balance. (2 points) 2. The future value of the initial mutual fund balance. (2 points) 3. The future value of the annual deposits to the savings account over the 12-year horizon. (4 points) 4. The total future value of the portfolio. (1 point)
2. When she retires, suppose your aunt deposits the total amount of money determined in part (A) in a new account earning 5.00 percent annually. If she expects to live another 20 years once she retires, how much can she withdraw each month to end up with a zero balance in the account upon her death? Assume she receives the withdrawal at the end of each month. (2 points)