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.

PROBLEMS

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!

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?