VIEWS: 183 PAGES: 13 CATEGORY: Personal Finance POSTED ON: 6/30/2010
How to Start Investing in the Stock Market document sample
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 Current Contract Team's Offer Counter Offer 1 $8,125,000.00 $4,000,000.00 $5,250,000.00 2 $3,650,000.00 $3,825,000.00 $7,550,000.00 3 $2,715,000.00 $3,850,000.00 $3,625,000.00 4 $1,822,250.00 $3,925,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 Solution using NPV function Discount Rate: Discount Rate: PV of Current PV of Current Contract: PV of Team's Offer: PV of Team's Offer: PV of Counter Offer: PV of Counter Offer: Which of the three contracts has the highest present value? Best Value: 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: 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.33 Computing Annuity Payments Babu Baradwaj is planning to save for his son’s college tuition. His son is currently 11 years old and will begin college in seven years. He has an index fund investment of $7,500 earning 9.5 percent annually. College expenses in a state university in Maryland currently costs $15,000 per year but are expected to grow at roughly 6 percent every year. Babu plans to invest a certain amount in a mutual fund that will earn 11 percent annually to make up the difference between the tuition cost and his current savings. In all he will make seven payments starting today with the last payment being a year from the time the son will begin college. a. What will be the present value of the fours years of college expenses just when the 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): Expected increase in annual tuition costs (g): Son's current age: 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 the father has to have saved when his son turns 18? PV of tuition costs - FV of investment: d. How much will the father have to invest every year in order for him to have enough funds to cover all tuition 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.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: 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 you have in total at retirement? 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 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 Hint: Solve for the PMT using the present value of an annuity equation and then use the PMT function: 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. 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: Present value of perpetuity: Expected return on investment: Amount of each yearly withdrawal: 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: 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.36 Loan Amortization The Sundarams are buying a new 3,500 square feet house in Muncie, Indiana and will borrow $237,000 from Bank One at a rate of 6.375 percent for 15 years. a. What is their monthly 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 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 (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.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: 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):