Question

In: Finance

Please show all your work in Excel, please send me the spreadsheet / workbook. What is...

Please show all your work in Excel, please send me the spreadsheet / workbook.

  1. What is the market value of the following bond?

Coupon 8%

Maturity date 2038

Interest paid semiannually

Par Value $1000

Market interest rate 10%

  1. What is the market value of the following bond?

Coupon 9%

Maturity date 2028

Interest paid semiannually

Par Value $1000

Market interest rate 8%

  1. What is the yield to maturity of the following bond?

Coupon 9%

Maturity date   2027

Interest paid semiannually

Par Value $1000

Market price $955.00

  1. What is the current yield of bond in Question 3?
  1. The risk free rate is 7%, the return in the market is 10%, and the beta is 1.30. What return must you receive to be satisfied that you are being fairly compensated for the risk of the firm?
  1. What should a zero coupon bond maturing for $1000 in 9 years with a 7% market rate sell for?

Page Two

  1. Preferred stock has a dividend of $12 per year. The required return is 6%. What should be the price per share?
  1. Hurricane Corporation expects to grow its dividend by 5% per year. The current dividend is $2 per share. The required return is 8%.
  1. What is the estimated value of a share of common stock?
  2. If price is $40 and dividends were $1.50 per share but expected to grow at 4% per year, what would be the required rate of return?   
  1. Compute the expected return for the following investment

State of nature            Probability                  Return

                              Boom                    25%                             20%

                              Average                60%                             8%

                              Recession             15%                             0%

  1. The following are the expected returns on a portfolio of investments.    What is the expected rate of return on the portfolio?

Investment        # of shares         Price per share        Expected return

A.                    2000                            $20                              10%

B.                    3000                            $10                              15%

                        C.                    1000                            $15                              8%

  1. You take out a $200,000 mortgage for 20 years at 6%.

What is your monthly payment?

What is the principle and interest on the first payment?

What is the principle and interest on the twelfth payment?

How much interest will you pay over the 20 years?

  1. You bought a house 8 years ago with a $250,000 mortgage. It was a 15 year loan with monthly payments which will pay off the loan when you make the last payment. The interest rate was 6%. What are your monthly payment and your current loan balance? How much interest will you pay in the upcoming year?  

Page Three

  1. You want to retire has a millionaire. How much do you need to put away each month if:
    1. You use common stocks and have an average return of 10%?
    2. You use corporate bonds and have an average return of 6%?
    3. You use government bonds and have an average return of 4%?
    4. You put your money in a CD at 2.5% interest rate?

(Please use age 30.)

  1. You are offered a contract with a signing bonus. If they offered you either $215,000 in cash or $2,000 a month for 15 years, guaranteed, which do you take (based strictly on the math)? Your safe rate of return is 7.5%.

  1. You are 30 years old today and planning to retire at age 62. You want to plan your finances for living 35 years past age 62 and die dead broke. You determine you will need $3000 per month from age 62 for the 35 years.

Your plan is to go live in the tropics, on the beach, and live on coconuts and fishing. Also, you need to conclude your retirement savings at age 55 because all your spare money then will be going to your children’s education.  

The question is how much money you will need to save each month between now and 55 so that you can quit contributing.   The expected return on your investments over the whole period is 10% per year. Please ignore inflation.

Solutions

Expert Solution

All 3 bonds pay interest semi-annually. so maturity and market interest rate will also be semi-annual for required calculations.

1. the market value of the bond is $834.53.

Semi-annual maturity 36
Semi-annual interest $40.00
Par value $1,000
Semi-annual market interest rate 5.00%
Market value of bond $834.53

Semi-annual interest and par value are entered as negative values in the formula because otherwise formula will give market value of bond as negative value. bond is purchased in 2020 and will mature in 2038. hence maturity is 18 years.

Calculation

2. the market value of the bond is $1,058.26.

Semi-annual maturity 16
Semi-annual interest $45.00
Par value $1,000
Semi-annual market interest rate 4.00%
Market value of bond $1,058.26

.Calculation

3. the yield to maturity of the bond is 9.91%.

Market price in the formula is entered as negative value as it's a cash outflow.

Yield to maturity calculated by formula is semi-annual. it needs to be multiplied by 2 to make it annual.

Semi-annual maturity 14
Semi-annual interest $45.00
Par value $1,000
Market price $955
Semi-annual Yield to maturity 4.95%
Yield to maturity of the bond 9.91%

Calculation

4. current yield of bond in question 3 = annual interest payment/market price of the bond

current yield of bond in question 3 = ($1,000*9%)/$955 = $90/$955 = 0.0942 or 9.42%


Related Solutions

Please show all your work if you use the Calculator. If done in Excel, please send...
Please show all your work if you use the Calculator. If done in Excel, please send me the spreadsheet / workbook. What is the market value of the following bond? Coupon 8% Maturity date 2038 Interest paid semiannually Par Value $1000 Market interest rate 10% What is the market value of the following bond? Coupon 9% Maturity date 2028 Interest paid semiannually Par Value $1000 Market interest rate 8% What is the yield to maturity of the following bond? Coupon...
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of...
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of the preceding requirements. Show how the solution will change if the following data change: the April 1 work-in-process costs were $66,000 for direct material and $18,000 for conversion. Work in process, April 1—10,000 units: Direct material: 100% complete, cost of .........................................................................................................$ 22,000 Conversion: 20% complete, cost of ................................................................................................................. 4,500 Balance in work in process, April 1 ..................................................................................................................$  26,500 Units started during April ......................................................................................................................................100,000 Units...
PLEASE POST EXCEL SPREADSHEET Please show all computation and underline your final answer in your submission....
PLEASE POST EXCEL SPREADSHEET Please show all computation and underline your final answer in your submission. Income statements and balance sheets follow for Microsoft Corporation. Refer to these financial statements to answer the requirements. (non-operating items are red-marked) MICROSOFT CORPORATION Income Statements For the years ended June 30, (in millions) 2016 2015 Revenue      Product $61,502 $75,956      Service 23,818 17,624 Total revenue 85,320 93,580 Cost of revenue      Product 17,880 21,410      Service and other 14,900 11,628 Total cost...
*Complete the problems in an Excel spreadsheet. Be sure to show your work to receive credit;...
*Complete the problems in an Excel spreadsheet. Be sure to show your work to receive credit; no hard keys. Problem 2-1: Preparing Financial Statements Information below is for Warner Manufacturing, Inc. for the year ended December 31, 20x1 except where beginning of year numbers indicated. All amounts in SAR unless otherwise stated. Using the information below: 1. Prepare an income statement with the proper title 2. Prepare end of year balance sheet with the proper title 3. Calculate net working...
*Complete the problems in an Excel spreadsheet. Be sure to show your work to receive credit;...
*Complete the problems in an Excel spreadsheet. Be sure to show your work to receive credit; no hard keys. Problem 2-1: Preparing Financial Statements Information below is for Warner Manufacturing, Inc. for the year ended December 31, 20x1 except where beginning of year numbers indicated. All amounts in SAR unless otherwise stated. Using the information below: 1. Prepare an income statement with the proper title 2. Prepare end of year balance sheet with the proper title 3. Calculate net working...
PLEASE SHOW ALL WORK IN EXCEL!!!!!!! SHOW CALCULATIONS / FORMULAS IN EXCEL!!!!!!!! Voters arrive at a...
PLEASE SHOW ALL WORK IN EXCEL!!!!!!! SHOW CALCULATIONS / FORMULAS IN EXCEL!!!!!!!! Voters arrive at a polling station at an average of 4 per minute (Poisson) where the service rate is 50 per hour (Poisson). a. What is the average number of voters in the system with 5 voter booths? (10 pts) b. What is the minimum number of voting booths needed to keep the average time in the system under three minutes? (10 pts) PLEASE SHOW ALL WORK IN...
Please use excel and show all work and formulas. (I will give your work a like...
Please use excel and show all work and formulas. (I will give your work a like if you do this) Size (1000s sq. ft) Selling Price ($1000s) 1.26 117.5 3.02 299.9 1.99 139.0 0.91 45.6 1.87 129.9 2.63 274.9 2.60 259.9 2.27 177.0 2.30 175.0 2.08 189.9 1.12 95.0 1.38 82.1 1.80 169.0 1.57 96.5 1.45 114.9 What are the p-values of the t test (for the slope estimate) and F test? What is the coefficient of determination? What is...
Please show step by step work in a spreadsheet or excel so I can follow along:...
Please show step by step work in a spreadsheet or excel so I can follow along: A federal agency responsible for enforcing laws governing weights and measures routinely inspects packages to determine whether the weight of the contents is at least as great as that advertised on the package. A random sample of 30 containers whose packaging states that the contents weigh 8 ounces was drawn. The weight of each package in ounces can be found in the sheet entitled...
For each questions, please show me all your work and explain the answer. 1. A rocket...
For each questions, please show me all your work and explain the answer. 1. A rocket is moving at 1/4 c relative to Earth. At the center of this rocket, a light source suddenly flashes. To an observer at rest in the rocket a. the light pulse will reach the front of the rocket at the same instant that it reaches the back of the rocket. b. the light pulse will reach the front of the rocket before it reaches...
Please Use Excel to Show All Work, I will like your answer :) 15.54 The Tire...
Please Use Excel to Show All Work, I will like your answer :) 15.54 The Tire Rack, America’s leading online distributor of tires and wheels, conducts extensive testing to provide customers with products that are right for their vehicle, driving style, and driving conditions. In addition, the Tire Rack maintains an independent consumer survey to help drivers help each other by sharing their long-term tire experiences. The following data show survey ratings (1 to 10 scale with 10 the highest...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT