Please Help, my team and I have different answers.

Problem 6.34 Present Value Tirade Owens, a professional athlete, currently has a contract that will pay him a large amount in the first year of his contract and smaller amounts thereafter. He and his agent have asked the team to restructure the contract. The team, though reluctant, obliged. Tirade and his agent came up with a counter offer. What are the present values of each of the contracts using a 14 percent discount rate? Year 1 2 3 4 Current Contract $8,125,000.00 $3,650,000.00 $2,715,000.00 $1,822,250.00 Team's Offer $4,000,000.00 $3,825,000.00 $3,850,000.00 $3,925,000.00 Counter Offer $5,250,000.00 $7,550,000.00 $3,625,000.00 $2,800,000.00 Hint: Find the present value of the contracts first using formulas and then by using the NPV function: NPV(rate,value1,value2, ...). Make sure that all cells are properly formatted. Solution using formulas Discount Rate: PV of Current Contract: PV of Team's Offer: PV of Counter Offer: Solution using NPV function Discount Rate: PV of Current Contract: PV of Team's Offer: PV of Counter Offer: Which of the three contracts has the highest present value? Best Value: Problem 6.35 Future Value Gary Kornig will be 30 years old next year and wants to retire when he is 65. So far he has saved (1) $6,950 in an IRA account in which his money is earning 8.3 percent annually and (2) $5,000 in money market account in which he is earning 5.25 percent annually. Gary wants to have $1 million when he retires. Starting next year, he plans to invest the same amount every year until he retires in a mutual fund in which he expects to earn 9 percent annually. How much will Gary have to invest every year to achieve his savings goal? Hint: Determine what his current savings will grow to by his retirement age, using the future value of a lump equation as well as the FV function: FV(rate,nper,pmt,pv,type). Then solve for the monthly deposit necessary to accumulate the difference, using an equation and then the PMT financial function: PMT(rate,nper,pv,fv,type). Make sure that all cells are properly formatted. Current age: Retirement age: IRA Account Current balance in IRA: Return on IRA account: Years to retirement: Value of IRA at retirement age: Value of IRA at retirement age: Money Market Account Current balance in Money Market: Return on Money Market account: Years to retirement: Value of Money Market at retirement age: Value of Money Market at retirement age: Savings Needed Target retirement balance: Future value of current savings: Amount needed to reach target: Expected return on mutual fund: Years to retirement: Annual deposit needed to reach target: Annual deposit needed to reach target: Problem 6.38 Computing Annuity Payments Babu Baradwaj is saving for his son's college tuition. His son is currently 11 years old and will begin college in seven years. Babu has an index fund investment worth $7,500 that is earning 9.5 percent annually. Total expenses at the University of Maryland, where his son says he plans to go, currently total $15,000 per year, but are expected to grow at roughly 6 percent each year. Babu plans to invest in a mutual fund that will earn 11 percent annually to make up the difference between the college expenses and his current savings. In total, Babu will make seven equal investments with the first starting today and with the last being made a year before his son begins college. a. What will be the present value of the fours years of college expenses at the time Babu's son starts college? Assume a discount rate of 5.5 percent. Hint: Determine the inflated cost of each of the four years of college and then compute the present value of college expenses at the time the son begins college. Use the future value of a lump sum equation as well as the FV function: FV(rate,nper,pmt,pv,type). Next, use present value equations and then the NPV financial function: NPV(rate,value1,value2, ...). Make sure that all cells are properly formatted. Annual cost of college tuition today (t0): Son's current age: Expected increase in annual tuition costs (g): Son's age when entering college: Four year tuition costs Years from now FV calculation Tuition costs 0 1 2 3 Discount rate (i): Present value of tuition costs (formula): Present value of tuition costs (function): b. What will be the value of the index mutual fund when the son just starts college? Balance in index fund: Return on the fund: Value of the fund at start of college (formula): Value of the fund at start of college (function): c. What is the amount that Babu will have to have saved when his son turns 18 if Babu plans to cover all of his son's college expenses? college expenses? PV of tuition costs - FV of investment: d. How much will Babu have to invest every year in order to have enough funds to cover all his son's expenses? Hint: Solve for the PMT using the future value of an annuity equation and then use the PMT financial function: PMT(rate,nper,pv,fv,type). Remember that the payments will begin today. Expected return on mutual fund: Savings required: Required annuity payment: Required annuity payment: Problem 6.39 Annuities You are now 50 years old and plan to retire at age 65. You currently have a stock portfolio worth $150,000, a 401(k) retirement plan worth $250,000 and a money market account worth $50,000. Your stock portfolio is expected to provide annual returns of 12 percent, your 401(k) investment will earn 9.5 percent annually, and the money market account earns 5.25 percent, compounded monthly. a. If you do not save another penny, what will be the total value of your investments when you retire at age 65? Hint: Determine the future value of each account and then the grand total of all three. Use the future value of a lump sum equation as well as the FV function: FV(rate,nper,pmt,pv,type) Current age: Retirement age: Stock Portfolio Current value of stock portfolio: Expected return on portfolio: Years to retirement: Expected value of portfolio at age 65 (formula): Expected value of portfolio at age 65 (function): 401k Plan Current value of 401k portfolio: Expected return on portfolio: Years to retirement: Expected value of portfolio at age 65 (formula): Expected value of portfolio at age 65 (function): Money Market Account Current value of stock portfolio: Expected return on portfolio: Years to retirement: Frequency of compounding: Expected value of portfolio at age 65 (formula): Expected value of portfolio at age 65 (function): Total of all three investments: b. Assume you plan to invest $12,000 every year in your 401K plan for the next 15 years (starting next year). How much will your investments be worth when you retire at 65? Annual investment in 401k plan: Expected return on portfolio: Value of 401k plan investments (formula): Value of 401k plan investments (function): Total investment amount at retirement: c. Assume that you expect to live 25 years after you retire (until age 90). Today, at age 50, you take all of your investments and place them in an account that pays 8 percent (use the scenario from part b in which you continue saving). If you start withdrawing funds starting at age 66, how much can you withdraw every year (e.g., an ordinary annuity) and leave nothing in your account after a 25th and final withdrawal at age 90? equation and then use the PMT function: Hint: Solve for the PMT using the present value of an annuity PMT(rate,nper,pv,fv,type). Amount available at retirement: Length of planned withdrawals (years): Expected return on investments: Amount of each yearly withdrawal (formula): Amount of each yearly withdrawal (function): d. You want your current investments, which are described in the problem statement, to support a perpetuity that starts a year from now. How much can you withdraw each year without touching your principal? Type of payment: Present value of perpetuity: Expected return on investment: Amount of each yearly withdrawal: Problem 6.40 Loan Amortization Trevor Diaz is looking to purchase a Mercedes Benz SL600 Roadster, which has an invoice price of $121,737 and a total cost of $129,482. Trevor plans to put down $20,000 and will pay the rest by taking on a 5.75 percent five-year loan. What is the monthly payment on this auto loan? Prepare an amortization table using Excel. a. What is the monthly payment on this auto loan? Hint: Use the present value of an annuity equation to solve for the monthly payment and then use the PMT financial function to solve: PMT(rate,nper,pv,fv,type). The present value of the annuity is the total amount borrowed. Make sure that all cells are properly formatted. Cost of new car: Down payment: Loan amount: Interest rate on loan: Term of loan (years): Frequency of payment: Monthly payment on loan: Monthly payment on loan: b. Prepare an amortization table using Excel. Hint: Insert the proper equation in each column and copy down the appropriate number of periods. Calculate total interest, principal, payments, and ending balance using the template below. Loan amount: Interest rate on loan: Term of loan: Frequency of payment: Payment # Payment Interest Principle Balance $0.00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 Totals: Problem 6.41 Loan Amortization The Sundarams are buying a new 3,500 square foot house in Muncie, Indiana and will borrow $237,000 from Bank One at a rate of 6.375 percent for 15 years. a. What will be their monthly loan payment? Hint: Use the present value of an annuity equation to solve for the monthly payment and then use the PMT financial function to solve: PMT(rate,nper,pv,fv,type). The present value of the annuity is the total amount borrowed. Make sure that all cells are properly formatted. Home loan amount: Interest rate on loan: Term of loan (years): Frequency of payment: Monthly payment on loan (formula): Monthly payment on loan (function): Prepare an amortization schedule using Excel. Hint: Insert the proper equation in each column and copy down the appropriate number of periods. Calculate total interest, principal, payments, and ending balance using the template below. Loan amount: Interest rate on loan: Term of loan (years): Frequency of payment: Payment # Payment Interest Principle Balance $0.00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 Totals: Problem 6.42 TVM Comprehensive Assume you will start working as soon as you graduate from college. You plan to start saving for your retirement onyour 25th birthday and retire on your 65th birthday. Currently you plan to retire when you turn 65 years old. After retirement, you expect to live at least until you are 85. You wish to be able to withdraw $40,000 (in today's dollars) every year from the time of your retirement until you are 85 years old (i.e., for a period of 20 years). The average inflation rate is likely to be 5 percent. a. Calculate the lump sum you need to have accumulated at age 65 to be able to draw the desired income. Assume that the annual return on your investments is likely to be 10 percent. Hint: First calculate the inflated value of the yearly retirement income desired for the first year in retirement. Then use the present value of a growing annuity equation to solve for the lump sum required to generate the retirement income stream. Make sure that all cells are properly formatted. Current age: Age when you begin to save for retirement: Age at which you plan to retire: Expected life span: Desired yearly retirement income in today's dollars: Average expected rate of inflation: Desired first year retirement income adjusted for inflation: Return on portfolio investment: Amount needed at retirement to fund retirement income: b. What is the dollar amount you need to invest every year, starting at age 26 and ending at age 65 (i.e., for 40 years) to reach the target lump sum at age 65? Hint: Solve for the payment in the future value of an annuity equation and then solve using the PMT financial function: PMT(rate,nper,pv,fv,type). Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): c. Now answer questions a. and b. assuming the rate of return to be 8 percent per year, then again at 15 percent per year. Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): d. Now assume you start investing for your retirement when you turn 30 years old and analyze the situation under rate of return assumptions of (i) 8 percent, (ii) 10 percent, and (iii) 15 percent. Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): e. Repeat the analysis by assuming that you start investing when you are 35 years old. Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function): Return on portfolio investment: Amount needed at retirement to fund retirement income: Number of years to save for retirement: Annuity payment required (formula): Annuity payment required (function)