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: 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. NEVER enter numbers directly in the output cells to compute. For instance, suppose 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. A. 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%). B. 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) C. 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 A. Assume the interest rate is 8%. What is present value of his contract? (10%) B. The player wants to see his paycheck increase 5% each year. That is, his next year's pay is 5% higher than whatever the signing bonus is, and the second year's pay is 5% higher than the first year's pay and so on. If you are the manager of the team, how his payment schedule should be rearranged and the present value of his contract can stay the same? (20%) Your answer worksheet should: (1) Specify the Excel functions that you used. (2) Tabulate the original pay schedule and new pay schedule and calculated the PV. (3) Show how each year's pay is linked with previous year's pay in the output data cells
