Excel Assignment

Directions:  Please place your name in the first cell (A1) of the excel spreadsheet.  Work each problem in excel using the appropriate excel function.  There are 11 problems.  Clearly label the answers in excel.  No credit will be given for problems that are not worked with the appropriate excel function.  I must be able to "see" the excel function used to solve the problem when I click on the answer.  This means that if you work a problem and only type in the answer into the excel spreadsheet NO CREDIT WILL BE GIVEN FOR THAT PROBLEM.  This is not a team or group assignment.  You are required to do your own work.  If you have a question about one of the problems, please come see me and DO NOT DISCUSS the problem(s) with your classmates.  WORKING TOGETHER is not ALLOWED.  In your excel spreadsheet, please type the following statements:  "All the work in this spreadsheet is my own work.  I did not work with others."

1.  Pam and Tony are planning to retire at age 65.  During their retirement, they would like to have quarterly income of $25,000 for 25 years from their retirement account (account will be depleted at the end). 

a.  If they can earn 9% per year on their investments during retirement, how much will they need to have at age 65 in their retirement account to meet their quarterly requirements?

b.  Pam and Tony are both 35 years old.  They have not started saving for retirement yet but they plan to make monthly contributions to their retirement account.  Given the amount they need to have for retirement from part a., how much do they need to invest each month to meet their retirement needs?  Assume they can earn 12% per year on their investments.

2. Suppose you are interested in an investment that will pay you $15,000 (single cash flow) in 18 years.  Similar investments are offering a return of 7.5% per year (opportunity cost).  What would you be willing to pay for the investment?  If the price of the investment is $4,000, would you buy the investment?  Explain

3. You are interested in purchasing a stock.  According to your estimates, you will receive a dividend of $1.10 (D1) at the end of this year, $1.25 (D2) at the end of next year, $1.40 (D3) at the end of year three, $1.55 (D4) at the end of the fourth year, and $1.70 (D5)  at the end of the fifth year.  Immediately upon receiving the fifth dividend, you expect to sell the stock for $90.  If your required rate of return is 10%, what would you be willing to pay for this stock?

4. If you invest $10,000 today and plan to make annual investments of $5,000 per year for the next 35 years, how much will you accumulate if your investments earn 9% per year? 

5. If a company doubles its earnings in 6 years, what is the compounded annual growth rate? 

6. How long will it take $10,000 to reach $20,000 when it grows at 10 percent per year?

7. A 7.5 percent coupon bond with 20 years left to maturity can be called in 9 years. The call premium is one year of coupon payments. It is offered for sale at $985.  The par value is $1,000.  

a. What is the yield to maturity is the bond offering? (Assume interest payments are paid semi-annually.)

b. What is the yield to call of the bond? (Assume that interest payments are paid semi-annually.)

8. You wish to buy a house that costs $175,000.  You have enough funds saved for a 15 percent down payment.  The bank offers you a 30-year mortgage loan at 4.50 percent. 

a. What are the monthly payments?

b. How much interest will you pay over the life of the loan?

c. If you pay an extra $100 per month, how long will it take to pay off the mortgage?

9. Calculate the price of a 8 percent coupon bond with 14 years left to maturity and a market interest rate of 9 percent. Assume the par value is $1,000 and interest payments are semi-annual.

10. What annual interest rate would you need to earn if you wanted a $800 per month contribution to grow to $900,000 in 25 years? 

11. Compute the average return, standard deviation, and coefficient of variation for MasterCard and Visa returns for the past five years.  Discuss which investment would be preferred.