1. You plan to buy a piece of machinery that costs $1.2 million. You'll put down 20% and finance the balance at 9% per year for 3 years, with monthly payments.
a. Create an Excel model that calculates the monthly payment and an amortization schedule for the loan.
b. You plan to replace the machinery with a more advanced model at the end of 3 years. You predict you will be able to sell the old machinery for $500,000 at that time, so you want to set up your loan so that rather than completely amortizing the amount borrowed, you end up at the end of 3 years still owing $500,000. (This is the same as setting your loan up with a $500,000 balloon payment at the end of the loan.) Set up an Excel model that calculates your monthly payment under this new scenario and construct an amortization schedule for the loan. Verify that your loan balance at the end of three years is, indeed, $500,000.
2. You are 45 years old and plan on retiring at age 60. You are in good health and expect to live to age 85. Your retirement account earns 9% per year.
a. How much must you save per month, with your first contribution at the end of the month, until retirement if you wish to withdraw $15,000 per month in retirement income? Your first withdrawal will be at the end of the first month of retirement and your last retirement contribution will be on the day you retire.
b. You don't think you can save that much per month. How much must you save per month if you delay retirement from age 60 to age 65?
c. Upon second thought, you've realized that with inflation, $15,000 per month is unlikely to meet your consumption and travel needs when you retire. Rather, you would like to guarantee that during each month of retirement you have income that has the same purchasing power that $15,000 has now. You expect inflation to be 3% per year, including your years in retirement. How much must you save per month until retirement to fund this new plan assuming you will retire at age 65? Hint: your retirement withdrawal will increase by the inflation rate every month; it won't be an annuity (which has level payments). This means you will need to project each month's retirement withdrawal and use the NPV function to find the present value of all of the payments. Project these monthly withdrawals out to the right and use monthly compounding for the inflation to make the calculation of your monthly withdrawals easier. Another hint: Your first withdrawal WON'T be $15,000! It will be much bigger than that!
3. Your auto dealer offers you a lease on an $80,000 car. The terms of the lease are as follows: You must make an initial payment of $5,000. Then, your monthly payment is $799 per month for 5 years. At the end of 5 years you can either turn in the car (and pay a penalty if you've driven it too many miles, or trashed it, but let's ignore that clause for now), or you can purchase the car outright at the end of the lease for $45,000.
What is the implied annual interest rate on this contract? That is, if this were a loan, then what would be the annual rate on the loan? Hint: use the RATE function, which will give you a monthly rate. Multiply that monthly rate by 12 to get the annual rate.
4. Consider a 20-year, $1,000 par value, 7% coupon bond with semi-annual payments.
a. What is the price if the yield to maturity is 9%?
b. What is the yield to maturity if the price is $1,250? Hint: Don't forget the RATE function will give you the 6-month rate. You need the annual rate.
c. What is the current yield (current yield is not the yield to maturity!) if the price is $1,250?
d. Suppose the bond can be called in 5 years for a 10% premium; that is, if the company calls the bond, it will pay $1,100 rather than $1,000 to redeem the bond. If the price of the bond now is $1,250, then what is the bond's yield to call? How does this compare to the yield to maturity? Why are they different?
5. You have 4 different bonds in your portfolio:
· $1,000 par value 10-year 15% coupon bonds with semiannual payments with a yield to maturity of 10%.
· $1,000 par value 10-year 5% coupon bonds with semiannual payments with a yield to maturity of 10%.
· $1,000 par value 20-year zero coupon bonds with semiannual payments with a yield to maturity of 10%.
· $1,000 par value 5-year 15% coupon bonds with semiannual payments with a yield to maturity of 10%.
a. Calculate the price of each of the bonds.
b. Suppose the yield to maturity on each of the bonds declines from 10% to 8%. Calculate the new price of each bond and calculate the percent change in bond price due to the yield change.
c. What can you say about the interest rate sensitivity of bonds? What types of bonds have the biggest percentage price change due to a change in interest rates, and what types of bonds have the smallest percentage price change?
6. You have been offered an investment opportunity that has the following cash flows:
Year 0 1 2 3 4 5
Cash Flow ? 200 400 -100 500 200
a. If other investments with comparable risk are earning 12%, then how much should you be willing to pay for this investment?
b. If you can purchase these cash flows for an initial investment of $800, then what is the annual rate of return on this investment? Hint: With what you know at this point, you have to use trial and error.
7. Consider a security that pays $100 per year forever. Securities of this riskiness are earning a rate of return of 7%.
a. What should be the price of the security?
b. What should be the price of the security one year from today?
c. Suppose you purchased the security today, received the first cash flow a year from now, and then sold it at the price you calculated in part b. What would be your rate of return on your investment? Hint: The rate of return over a 1-year period is the capital gains plus the cash payments divided by the initial price: annual return = (P1 - P0 + CF1)/P0.
d. What can you conclude from this observation?
8. Suppose a security pays $100 at the end of one year. This payment will grow at a rate of 20% a year for 2 years, and then will grow at a rate of 10% for one year, and then grow at a rate of 5% thereafter forever. Securities of this riskiness have a rate of return of 9%. See Sections 5.6 and 5.8 for valuing this type of security. Hint: Draw a timeline!
a. What should be the price of this security?
b. What should be the price of the security one year from today?
c. Suppose you purchased the security today, received the first cash flow a year from now, and then sold it at the price you calculated in part b. What would be your rate of return on your investment?