There are two problems in this assignment. Put your work of two problems in two separate worksheets in the same Excel file.

Instructions:

• Input: you need to create INPUT area to contain all key assumption data.
• Output: do NOT enter numbers directly. Each of the output cell, e.g. payment, interest, end balance must contain a formula which performs calculations referring the number in the input cell.
• For instance, the given interest rate is 5% in cell A1, the given PV is $100 in cell A2 and the given time period is 2 years in cell A3. To calculate the future value, you must enter =FV(A1, A3,0, -A2,0) , instead of =100*1.05^2 . (30 pts deduction for not setting the input-output relationship appropriately or not using the Excel finance functions)
• Format: Your numerical displays must be in appropriate formats: 1000 separator, two decimal places and $, % symbols.

Color: Fill color in your answer cells.

Problem 1.

Suppose an apartment you want to purchase costs $500,000. You can put down 20% in cash and take out a 30-year fixed rate mortgage loan for the remaining. You believe that you can get an APR of 6.5% on such a mortgage loan at a local bank.

1. Suppose the loan calls for equal monthly payments. Set up a monthly amortization schedule table for the loan (Input: 20%, creating and copying formulas and Excel finance functions: 30%).
2. What is the sum of total payments made over the life of the loan? What is the sum of total interest paid over the life of the loan? What is the difference of the two numbers? (Summation function 10%, fill color in your answer cells)
3. What is the balance of the loan AFTER ten years? (build-in Finance function 10%)

Problem 2.

An MLB baseball player is offered a five-year contract in the payment schedule as follows:

Year
0	$ 2,000,000.00	(signing bonus)
1	$ 2,500,000.00
2	$ 2,500,000.00
3	$ 3,000,000.00
4	$ 3,000,000.00
5	$ 2,800,000.00

1. Tabulate the original pay schedule. Assume the interest rate is 8%. What is present value of his contract? Hint: NPV function (10%)
2. The player wants to see his paycheck increase 5% each year. That is, his Year 1s pay must be 5% higher than his newly designed signing bonus, and Year 2s pay is 5% higher than Year 1s pay and so on. If you are the manager of the team, how should you rearrange his payment schedule that can simultaneously satisfy the players demand and keep the cost of the contract the same, that is, the PV being the same? Tabulate the new payment schedule and compute the PV. (20%)

Note:

1. You must specify the input data
2. The output answers must contain Excel functions by referring to the input data, not directly entering numbers to compute the answer.There are two problems in this assignment. Put your work of two problems in two separate worksheets in the same Excel file.

Instructions:

• Input: you need to create INPUT area to contain all key assumption data.
• Output: do NOT enter numbers directly. Each of the output cell, e.g. payment, interest, end balance must contain a formula which performs calculations referring the number in the input cell.
• For instance, the given interest rate is 5% in cell A1, the given PV is $100 in cell A2 and the given time period is 2 years in cell A3. To calculate the future value, you must enter =FV(A1, A3,0, -A2,0) , instead of =100*1.05^2 . (30 pts deduction for not setting the input-output relationship appropriately or not using the Excel finance functions)
• Format: Your numerical displays must be in appropriate formats: 1000 separator, two decimal places and $, % symbols.

Color: Fill color in your answer cells.

Problem 1.

Suppose an apartment you want to purchase costs $500,000. You can put down 20% in cash and take out a 30-year fixed rate mortgage loan for the remaining. You believe that you can get an APR of 6.5% on such a mortgage loan at a local bank.

1. Suppose the loan calls for equal monthly payments. Set up a monthly amortization schedule table for the loan (Input: 20%, creating and copying formulas and Excel finance functions: 30%).
2. What is the sum of total payments made over the life of the loan? What is the sum of total interest paid over the life of the loan? What is the difference of the two numbers? (Summation function 10%, fill color in your answer cells)
3. What is the balance of the loan AFTER ten years? (build-in Finance function 10%)

Problem 2.

An MLB baseball player is offered a five-year contract in the payment schedule as follows:

Year
0	$ 2,000,000.00	(signing bonus)
1	$ 2,500,000.00
2	$ 2,500,000.00
3	$ 3,000,000.00
4	$ 3,000,000.00
5	$ 2,800,000.00

1. Tabulate the original pay schedule. Assume the interest rate is 8%. What is present value of his contract? Hint: NPV function (10%)
2. The player wants to see his paycheck increase 5% each year. That is, his Year 1s pay must be 5% higher than his newly designed signing bonus, and Year 2s pay is 5% higher than Year 1s pay and so on. If you are the manager of the team, how should you rearrange his payment schedule that can simultaneously satisfy the players demand and keep the cost of the contract the same, that is, the PV being the same? Tabulate the new payment schedule and compute the PV. (20%)

Note:

1. You must specify the input data
2. The output answers must contain Excel functions by referring to the input data, not directly entering numbers to compute the answer.
3. You MUST answer this in Excel with the correct excel formulas