Please use excel functions to do the calculations on the worksheet for full credit! Note that there is more than one tab in this spreadsheet.
future value
| What would the future value of $100 be after 5 years at 10% compound interest? | |||
| N | 5 | ||
| I | 10% | ||
| PV | $100 | ||
| PMT | $0 | FV= | |
| Suppose you currently have $2,000 and plan to purchase a 3-year certificate of deposit (CD) that pays 4% interest compounded annually. How much will you have when the CD matures? | |||
| N | 3 | ||
| I | 4% | ||
| PV | $2,000 | ||
| PMT | $0 | FV= | |
| A company’s sales in 2009 were $100 million. If sales grow at 8%, what will they be 10 years later? | |||
| N | 10 | ||
| I | 8% | ||
| PV ($M) | $100 | ||
| PMT | $0 | FV ($M)= | |
| How much would $1, growing at 5% per year, be worth after 100 years? | |||
| N | 100 | ||
| I | 5% | ||
| PV | $1 | ||
| PMT | $0 | FV= | |
| What would FV be if the growth rate were 10%? | |||
| N | 100 | ||
| I | 10% | ||
| PV | $1 | ||
| PMT | $0 | FV= | |
present value
| Suppose a risk-free bond promises to pay $2,249.73 in 3 years. If the going risk-free interest rate is 4%, how much is the bond worth today? | |||
| N | 3 | ||
| I | 4% | ||
| PMT | $0 | ||
| FV | $2,249.73 | PV= | |
| How would your answer change if the bond matured in 5 rather than 3 years? | |||
| N | 5 | ||
| I | 4% | ||
| PMT | $0 | ||
| FV | $2,249.73 | PV= | |
| If the risk-free interest rate is 6% rather than 4%, how much is the 5-year bond worth today? | |||
| N | 5 | ||
| I | 6% | ||
| PMT | $0 | ||
| FV | $2,249.73 | PV= | |
Interest rate
| Suppose you can buy a U.S. Treasury bond which makes no payments until the bond matures 10 years from now, at which time it will pay you $1,000. What interest rate would you earn if you bought this bond for $585.43? | |||
| N | 10 | ||
| PMT | $0 | ||
| PV | $585.43 | ||
| FV | $1,000 | I = | |
| What rate would you earn if you could buy the bond for $550? | |||
| N | 10 | ||
| PMT | $0 | ||
| PV | $550.00 | ||
| FV | $1,000 | I = | |
| Microsoft earned $0.33 per share in 1997. Fourteen years later, in 2011, it earned $2.75. What was the growth rate in Microsoft’s earnings per share (EPS) over the 14-year period? | |||
| N | 14 | ||
| PMT | $0 | ||
| PV | $0.33 | ||
| FV | $2.75 | I = | |
| If EPS in 2011 had been $2.00 rather than $2.75 what would the growth rate have been? | |||
| N | 14 | ||
| PMT | $0 | ||
| PV | $0.33 | ||
| FV | $2.00 | I = | |
Perpetuity
| What is the present value of a perpetuity that pays ₤1,000 per year, beginning one year from now, if the appropriate interest rate is 5%? | |||
| PMT | £1,000 | ||
| I | 5% | PV= | |
| What would the value be if the perpetuity began its payments immediately? | |||
| The perpetuity formula values payments 1 through infinity. If a payment is to be received immediately, it must be added to the formula result. | |||
| PMT | £1,000 | ||
| I | 5% | PV= | |
Annuity
| What is the PVA of an ordinary annuity with 10 payments of $100 if the appropriate interest rate is 10%? | ||||
| N | 10 | |||
| I | 10% | |||
| PMT | -$100 | |||
| FV | $0 | PV= | ||
| What would the PVA be if the interest rate were 4%? | ||||
| N | 10 | |||
| I | 4% | |||
| PMT | -$100 | |||
| FV | $0 | PV= | ||
| What would the PVAs be if we were dealing with annuities due? | ||||
| Part a | Part b | |||
| BEGIN MODE | BEGIN MODE | |||
| N | 10 | N | 10 | |
| I | 10% | I | 4% | |
| PMT | -$100 | PMT | -$100 | |
| FV | $0 | FV | $0 | |
| PV | PV | |||
| Assume that you are offered an annuity that pays $100 at the end of each year for 10 years. You could earn 8% on your money in other equally risky investments. What is the most you should pay for the annuity? | ||||
| N | 10 | |||
| I | 8% | |||
| PMT | -$100 | |||
| FV | $0 | PV= | ||
| If the payments began immediately, then how much would the annuity be worth? | ||||
| BEGIN MODE | ||||
| N | 10 | |||
| I | 8% | |||
| PMT | -$100 | |||
| FV | $0 | PV= |
NPV
| What is the present value of a 5-year ordinary annuity of $100 plus an additional $500 at the end of Year 5 if the interest rate is 6%? | ||||||
| Interest rate | 6% | |||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 |
| Ann Pmt | $0 | $100 | $100 | $100 | $100 | $100 |
| Lump Sum | $500 | |||||
| Total CFs | $0 | $100 | $100 | $100 | $100 | $600 |
| NPV | ||||||
| What is the present value of the following uneven cash flow stream: $0 at Time 0, $100 at the end of Year 1 (or at Time 1), $200 at the end of Year 2, $0 at the end of Year 3, and $400 at the end of Year 4, assuming the interest rate is 8%? | ||||||
| Interest rate | 8% | |||||
| Year | 0 | 1 | 2 | 3 | 4 | |
| CFs | $0 | $100 | $200 | $0 | $400 | |
| NPV | ||||||
IRR
| An investment costs $465 now and is expected to produce cash flows of $100 at the end of each of the next 4 years, plus an extra lump sum payment of $200 at the end of the 4th year. What is the expected rate of return on this investment? | |||||
| Year | 0 | 1 | 2 | 3 | 4 |
| Ann Pmt | -$465 | $100 | $100 | $100 | $100 |
| Lump Sum | $200 | ||||
| Total CFs | -$465 | $100 | $100 | $100 | $300 |
| IRR | |||||
| An investment costs $465 and is expected to produce cash flows of $100 at the end Year 1, $200 at the end of Year 2, and $300 at the end of Year 3. What is the expected rate of return on this investment? | |||||
| Year | 0 | 1 | 2 | 3 | |
| CFs | -$465 | $100 | $200 | $300 | |
| IRR |
Value of bond
| A bond that matures in six years has a par value of $1,000, an annual coupon payment of $80, and a market interest rate of 9%. What is its price? | ||
| Years to Maturity | 6 | |
| Annual Payment | $80 | |
| Par value | $1,000 | |
| Going rate, rd | 9% | |
| Value of bond = | ||
| Last year a firm issued 30-year, 8% annual coupon bonds at a par value of $1,000. (1) Suppose that one year later the going rate drops to 6%. What is the new price of the bonds, assuming that they now have 29 years to maturity? | ||
| Years to Maturity | 29 | |
| Coupon rate | 8% | |
| Annual Payment | $80 | |
| Par value | $1,000 | |
| Going rate, rd | 6% | |
| Value of bond = |
Yield of bond
| A bond currently sells for $850. It has an eight-year maturity, an annual coupon of $80, and a par value of $1,000. What is its yield to maturity? What is its current yield? | ||||||
| Years to Maturity | 8 | |||||
| Annual Payment | $80.00 | |||||
| Current price | $850.00 | |||||
| Par value = FV | $1,000.00 | |||||
| Going rate, rd =YTM: | ||||||
| Annual Payment | $80.00 | |||||
| Current price | $850.00 | |||||
| Current yield: | ||||||
| A bond currently sells for $1,250. It pays a $110 annual coupon and has a 20-year maturity, but it can be called in 5 years at $1,110. What are its YTM and its YTC? Is it likely to be called if interest rates don't change? | ||||||
| Years to Maturity | 20 | Years to Call | 5 | |||
| Annual Payment | $110 | Annual Payment | $110 | |||
| Current price | $1,250 | Current price | $1,250 | |||
| Par value = FV | $1,000 | Call price | $1,110 | |||
| YTM | YTC | |||||
