Reference no: EM131444094
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.
|
MasterCard
|
Visa
|
|
|
2012
|
32
|
49
|
|
2013
|
70
|
47
|
|
2014
|
3
|
18
|
|
2015
|
13
|
18
|
|
2016
|
6
|
1
|
|
Partners system choose different mixes of risk-free
: Choose one of the below three questions to answer (not all unless you want to). Summarize how each of the "real assets" improves the potential opportunities for the hospitals investing in the LTP. (You need at least 30 combinations to finish each ..
|
|
What is your monthly lease payment
: The finance fee is the net capitalization cost plus the residual, times the money factor, and the monthly sales tax is the depreciation payment plus the finance fee, times the tax rate. What APR is the dealer quoting you? What is your monthly leas..
|
|
Find out the plug-in bandwidth bw
: For the Parade2005 data of the preceding exercise, obtain a kernel density estimate of the earnings for the full data set. It will be necessary to transform the data to logarithms (why?). Comment on the result. Be sure to try out some arguments to..
|
|
What should be the required return on baker stock
: The common stock of Baker Incorporated is 30% more volatile than the market as a whole. You estimate the risk-free rate to be 4% and the required return on the market to be 12%. What should be the required return on Baker's stock?
|
|
What is the compounded annual growth rate
: 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?
|
|
What is the p-value of the test
: Suppose a new standardized test is given to 100 randomly selected third-grade students in New Jersey. The sample average score on the test is 58 points, and the sample standard deviation, sY, is 8 points.
|
|
Identify at least two of the intellectual virtues
: Please identify at least two of the intellectual virtues, and describe the differences between the virtues and their corresponding vices. From your own experience, present an example of the virtue or vice from each of the pairs you explain.
|
|
How much money will be in the account
: The first deposit will occur one year from today (that is, at t = 1) and the last deposit will occur 15 years from today (that is, at t = 15). How much money will be in the account 15 years from today?
|
|
What was going on during the situation
: To help in your critical thinking process, the Game Plan, located on pages 38-42 in your textbook, consisting of 10 strategies, was created to help assist you in making critical decisions. One of the 10, Keep an Intellectual Journal, will be the m..
|