Finance

Finance

When working these problems, please make sure of the following:
1. Use Excel. Don’t use a calculator.
2. You needn’t type into your Excel spreadsheet the problem wording. If you really want the problem wording in your spreadsheet, you can cut and paste from this Word document into your spreadsheet, but this isn’t necessary. Just number your answers.
3. Don’t hardwire numbers into your formulas. If you need a number that is already in your spreadsheet, then use a cell reference to refer to it; don’t copy it down and retype it or copy it electronically. Be sure to have a parameters section for each problem and a section where you do the calculations. I want to be able to change the inputs to the problems and get correct answers for the new parameters.
4. Label you parameters.
5. Work the problems going from top to bottom…. that is, start Problem 1 somewhere near Row 1 and Column A, and start Problem 2 somewhere below that and also in Column A. You can do all of the problems on the same worksheet tab. Please don’t use a separate tab for each problem.
6. When there are multiple steps in a problem, please break the problem up into those steps. Don’t try and create really complicated formulas that do the whole thing in one step.
7. Don’t email, post, send, or share your Excel solution files with anyone. Don’t ask someone to share his or her solution files with you. You may discuss the problems with each other and even work on the problems together in pairs or larger groups, but each of you, individually, must program your Excel spreadsheet solutions yourself and submit them yourself.
8. When in doubt DRAW A TIME LINE!
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?
e. Think about the decision to call a bond from the perspective of the company issuing the bond. Under what market conditions would you want to call a bond? That is, suppose that the call protection on some bonds you issued expired today. The bonds have 15 years left until maturity, 7% annual coupons with semiannual payments and the bonds can be called at a 10% premium, that is, the call price is $1,100. What market conditions would induce you to call the bonds?
f. Think about the decision to call a bond from the perspective of the person (investor) who has purchased the bond. Suppose you own the bonds in part e, above, and the company decided to call the bonds. Under what market conditions would you be unhappy about the call, and why? Are there any market conditions under which you would be happy about the call? Why?
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. Consider a security that pays $100 at the end of this year, and then increases the amount paid each year at a rate of 4% a year. Securities of this riskiness are earning a rate of return of 7%.
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?
d. What can you conclude from your observation?
9. 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%.
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?