Looking at the Problem 6.37 tab, I am trying to figure out how to find the Desired first year retirement income adjusted for inflation, Return on portfolio investment, and the amount needed to fund retirement income in question A.. Also, the Return on Portfolio Investment would be the same on all the other questions, right?

Problem 6.31 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, Row Rosenstinck 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: 14.00% PV of Current Contract: PV of Team's Offer: PV of Counter Offer: $12,847,212.00 $11,274,540.00 $14,519,340.00 Solution using NPV function Discount Rate: 14.00% PV of Current Contract: PV of Team's Offer: PV of Counter Offer: $12,847,215.41 $11,374,540.65 $14,519,339.52 Which of the three contracts has the highest present value? Best Value: Counter Offer Problem 6.32 Future Value Gary Kornig will turn 30 years old next year. He comes up with a plan to save for his retirement at 65 years of age. Currently, he has saved $6,950 in an IRA account earning 8.3 percent annually. He also currently has invested an inheritance of $5,000 in money market account earning 5.25 percent and plans to leave it as part of his retirement savings. He has set himself a retirement target of $1,000,000. He plans to put aside a fixed amount every year, starting next year, in a mutual fund that will earn 9 percent annually. How much will he have to save every year in order to achieve his 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: 29 Retirement age: IRA Account Current balance in IRA: Return on IRA account: Years to retirement: $6,950 8.30% 36 Value of IRA at retirement age: Value of IRA at retirement age: $122,633.53 $122,633.53 Money Market Account Current balance in Money Market: Return on Money Market account: Years to retirement: $5,000 5.25% 36 Value of Money Market at retirement age: Value of Money Market at retirement age: $31,547.56 $31,547.56 Savings Needed Target retirement balance: Future value of current savings: Amount needed to reach target: Expected return on mutual fund: Years to retirement: $1,000,000.00 $154,181.09 $845,818.91 9.00% 36 Annual deposit needed to reach target: Annual deposit needed to reach target: $3,582.09 $3,582.09 65 Problem 6.34 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 you annual returns of 12 percent, your 401(k) investment will earn you 9.5 percent annually, and the money market account earns 5.25 percent, compounded monthly. a. If you do not save another penny for the next 15 years, how much will you have from your current savings 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: 50 Stock Portfolio Current value of stock portfolio: Expected return on portfolio: Years to retirement: $150,000.00 12.00% 15 Expected value of portfolio at age 65 (formula): Expected value of portfolio at age 65 (function): $821,034.86 $821,034.86 401k Plan Current value of 401k portfolio: Expected return on portfolio: Years to retirement: $250,000.00 9.50% 15 Expected value of portfolio at age 65 (formula): Expected value of portfolio at age 65 (function): $975,330.48 $975,330.48 Money Market Account Current value of stock portfolio: Expected return on portfolio: Years to retirement: Frequency of compounding: $50,000.00 5.25% 15 12 Expected value of portfolio at age 65 (formula): Expected value of portfolio at age 65 (function): $109,706.14 $109,706.14 Retirement age: 65 Total of all three investments: $1,906,071.48 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 you have in total at retirement? Annual investment in 401k plan: Expected return on portfolio: $12,000 12.00% Value of 401k plan investments (formula): Value of 401k plan investments (function): $366,482.77 $366,482.77 Total investment amount at retirement: $366,482.77 c. Assume that you expect to live another 25 years after retirement (i.e., until age 90). You now take all of your investments (use scenario from part b), and invest it in an account paying 8 percent. If you plan to use all your savings starting a year from retirement, how much can you withdraw every year for the next 25 years and leave nothing in your account at age 90? Hint: Solve for the PMT using the present value of an annuity equation and then use the PMT function: PMT(rate,nper,pv,fv,type). Amount available at retirement: $2,272,554.25 Length of planned withdrawals (years): 25 Expected return on investments: 8.00% Amount of each yearly withdrawal (formula): Amount of each yearly withdrawal (function): $212,889.63 $212,889.63 d. If you wanted a perpetuity, how much will you be able to withdraw each year starting a year from now without touching your principal? Type of payment: Perpetuity Present value of perpetuity: $2,272,554.25 Expected return on investment: 8.00% Amount of each yearly withdrawal: $181,804.34 Problem 6.35 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 from Bank of America. 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: $129,482.00 $20,000.00 $109,482.00 5.75% 5 12 Monthly payment on loan: Monthly payment on loan: $2,103.89 $2,103.89 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 # 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 $109,482.00 5.75% 5 12 Payment $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 Interest $525.51 $510.32 $502.68 $494.99 $487.27 $479.51 $471.71 $463.88 $456.00 $448.09 $440.15 $432.16 $424.14 $416.07 $407.97 $399.83 $391.65 $383.43 $375.17 $366.88 $358.54 $350.16 $341.74 $333.29 $324.79 $316.25 $307.67 $299.04 Principle $1,578.38 $1,585.95 $1,593.57 $1,601.21 $1,608.90 $1,624.38 $1,632.18 $1,640.01 $1,647.89 $1,655.80 $1,663.74 $1,671.73 $1,679.75 $1,687.82 $1,695.92 $1,704.06 $1,712.24 $1,720.46 $1,728.72 $1,737.01 $1,745.35 $1,753.73 $1,762.15 $1,770.60 $1,779.10 $1,787.64 $1,796.22 $1,804.85 Balance $109,482.00 $107,903.62 $106,317.00 $104,724.11 $103,122.89 $101,514.00 $99,897.37 $98,272.99 $96,640.81 $95,000.80 $93,352.91 $91,697.11 $90,033.37 $88,361.64 $84,991.07 $83,298.15 $81,594.09 $79,881.85 $78,161.40 $76,432.68 $74,695.67 $72,950.32 $71,196.59 $69,434.44 $67,663.84 $65,884.74 $64,097.09 $62,300.87 $60,496.02 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: $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $2,103.89 $35.19 $290.38 $281.68 $272.93 $264.14 $255.31 $246.44 $237.52 $228.56 $219.56 $210.52 $201.43 $192.30 $183.12 $173.90 $164.64 $155.33 $145.98 $136.58 $127.13 $117.65 $108.11 $98.53 $88.91 $79.23 $69.52 $59.75 $49.94 $40.08 $30.17 $20.22 $10.22 $1,813.51 $1,822.21 $1,830.96 $1,839.75 $1,848.58 $1,857.45 $1,866.37 $1,875.33 $1,884.33 $1,902.46 $1,911.59 $1,920.77 $1,929.99 $1,939.25 $1,948.56 $1,957.91 $1,967.31 $1,976.76 $1,986.24 $1,995.78 $2,005.36 $2,014.98 $2,024.66 $2,034.37 $2,044.14 $2,053.95 $2,063.81 $2,073.72 $2,083.67 $2,093.67 $2,103.62 $58,682.51 $56,860.30 $55,029.34 $53,189.59 $51,341.01 $49,483.56 $47,617.19 $45,741.86 $43,857.53 $41,964.16 $40,061.69 $38,150.10 $36,229.44 $34,299.34 $32,360.09 $30,411.53 $28,453.61 $26,486.30 $24,509.54 $22,523.30 $20,527.52 $18,522.16 $16,507.18 $14,482.52 $12,448.15 $10,404.01 $8,350.06 $6,286.25 $4,212.54 $2,128.87 $35.19 $109,482.00 $16,268.67 $108,040.42 109.446.81 Problem 6.37 TVM Comprehensive Assume you will start on a job as soon as you graduate. You plan to start saving for your retirement when you turn 25 years old. (Assume you are 21 years at the time of graduation. Everybody needs a break!) 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). You can invest, starting when you turn 25 years old, in a portfolio fund. 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 your return on the portfolio investment 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: 21 25 65 85 $40,000 5.00% 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 one and two assuming your rate of return to be 8 percent per year, and then 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 only 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)