A bond has a face value of $1,000, an annual coupon rate of 3.70%, an yield to maturity of 7.4%, makes 2 (semi-annual) coupon payments per year, and 6 periods to maturity (or 3 years to maturity). Determine the duration of this bond. Notice that there is a function “Duration” in Excel, but you probably would like to check your answer with an analytical solution based on the specific duration formula. Note: This is an excel based assignment so please work it out on excel highlighting the formulas. thank you

A bond has a face value of $1,000, an annual coupon rate of 3.70%, an yield to maturity of 7.4%, makes 2 (semi-annual) coupon payments per year, and 6 periods to maturity (or 3 years to maturity). Determine the duration of this bond. Notice that there is a function “Duration” in Excel, but you probably would like to check your answer with an analytical solution based on the specific duration formula. Note: This is an excel based assignment so please work it out on excel highlighting the formulas. thank you

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 37P

See similar textbooks

Similar questions

A project does not necessarily have a unique IRR. (Refer to the previous problem for more information on IRR.) Show that a project with the following cash flows has two IRRs: year 1, 20; year 2, 82; year 3, 60; year 4, 2. (Note: It can be shown that if the cash flow of a project changes sign only once, the project is guaranteed to have a unique IRR.)
It is January 1 of year 0, and Merck is trying to determine whether to continue development of a new drug. The following information is relevant. You can assume that all cash flows occur at the ends of the respective years. Clinical trials (the trials where the drug is tested on humans) are equally likely to be completed in year 1 or 2. There is an 80% chance that clinical trials will succeed. If these trials fail, the FDA will not allow the drug to be marketed. The cost of clinical trials is assumed to follow a triangular distribution with best case 100 million, most likely case 150 million, and worst case 250 million. Clinical trial costs are incurred at the end of the year clinical trials are completed. If clinical trials succeed, the drug will be sold for five years, earning a profit of 6 per unit sold. If clinical trials succeed, a plant will be built during the same year trials are completed. The cost of the plant is assumed to follow a triangular distribution with best case 1 billion, most likely case 1.5 billion, and worst case 2.5 billion. The plant cost will be depreciated on a straight-line basis during the five years of sales. Sales begin the year after successful clinical trials. Of course, if the clinical trials fail, there are no sales. During the first year of sales, Merck believe sales will be between 100 million and 200 million units. Sales of 140 million units are assumed to be three times as likely as sales of 120 million units, and sales of 160 million units are assumed to be twice as likely as sales of 120 million units. Merck assumes that for years 2 to 5 that the drug is on the market, the growth rate will be the same each year. The annual growth in sales will be between 5% and 15%. There is a 25% chance that the annual growth will be 7% or less, a 50% chance that it will be 9% or less, and a 75% chance that it will be 12% or less. Cash flows are discounted 15% per year, and the tax rate is 40%. Use simulation to model Mercks situation. Based on the simulation output, would you recommend that Merck continue developing? Explain your reasoning. What are the three key drivers of the projects NPV? (Hint: The way the uncertainty about the first year sales is stated suggests using the General distribution, implemented with the RISKGENERAL function. Similarly, the way the uncertainty about the annual growth rate is stated suggests using the Cumul distribution, implemented with the RISKCUMUL function. Look these functions up in @RISKs online help.)
In the financial world, there are many types of complex instruments called derivatives that derive their value from the value of an underlying asset. Consider the following simple derivative. A stocks current price is 80 per share. You purchase a derivative whose value to you becomes known a month from now. Specifically, let P be the price of the stock in a month. If P is between 75 and 85, the derivative is worth nothing to you. If P is less than 75, the derivative results in a loss of 100(75-P) dollars to you. (The factor of 100 is because many derivatives involve 100 shares.) If P is greater than 85, the derivative results in a gain of 100(P-85) dollars to you. Assume that the distribution of the change in the stock price from now to a month from now is normally distributed with mean 1 and standard deviation 8. Let EMV be the expected gain/loss from this derivative. It is a weighted average of all the possible losses and gains, weighted by their likelihoods. (Of course, any loss should be expressed as a negative number. For example, a loss of 1500 should be expressed as -1500.) Unfortunately, this is a difficult probability calculation, but EMV can be estimated by an @RISK simulation. Perform this simulation with at least 1000 iterations. What is your best estimate of EMV?
Bruin Properties is in escrow to buy a 175,000 square foot shopping center in Camarillo, California for $35,000,000. Bruin Properties can borrow $24,000,000 fixed rate fully amortizing over 30 years at a 6.0% annual interest rate with equal monthly payments of principal and interest or it can borrow $28,000,000 fixed rate fully amortizing over 30 years at a 7.0% annual interest rate with equal monthly payments of principal and interest. What is the incremental annual borrowing cost for the additional $4,000,000 loan amount if each loan would be outstanding for the full 30 year term? a.13.0% b.12.4% c.11.5% d. 7.0%
A 10% coupon $1,000 par value bond with four years to maturity is currently selling for $900. The bond pays coupon payments on a semiannual basis. If interest rates move in the corporation's favor, the bond will be called for $1,050. What is the bond's yield to maturity? 13.30% 11.65% 10.00% 8.48%
Please answer along with the excel formulas - 1. Sue now has $125. How much would she have after 8 years if she leaves it invested at 8.5% with annual compounding? $205.83 $216.67 $228.07 $240.08 $252.08 2. Suppose you have $1,500 and plan to purchase a 5-year certificate of deposit (CD) that pays 3.5% interest, compounded annually. How much will you have when the CD matures? $1,781.53 $1,870.61 $1,964.14 $2,062.34 $2,165.46 3. Last year Rocco Corporation's sales were $225 million. If sales grow at 6% per year, how large (in millions) will they be 5 years later? $271.74 $286.05 $301.10 $316.16 $331.96
company wants to issue a 15-year Bond with $1,000 face value and an 8% Annual coupon. the bond can be sold to investors for $1,010 per Bond but will incur transaction cost of $15 per Bond if the company tax rate is 21%. what is the after-tax cost of debt financing to the firm?
Match the following terms to their definitions: valuation, return on investment, discount rate, terminal value 1. The value of an investment at the end of an investment period 2. The return that investors expect to earn for putting their capital at risk 3. The amount of profit made on a given investment 4. An estimate of the economic worth of an asset or business a. Discount rate b. Valuation c. Return on investment d. Terminal value
Assume that at the beginning of the year, you purchase an investment for $5,480 that pays $138 annual income. Also assume the investment’s value has decreased to $5,080 by the end of the year. What is the rate of return for this investment?
Your answer is partially correct. An independent contractor for a transportation company needs to determine whether she should upgrade the vehicle she currently owns or trade her vehicle in to lease a new vehicle. If she keeps her vehicle, she will need to invest in immediate upgrades that cost $5,200 and it will cost $1,300 per year to operate at the end of year that follows. She will keep the vehicle for 5 years; at the end of this period, the upgraded vehicle will have a salvage value of $3,800. Alternatively, she could trade in her vehicle to lease a new vehicle. She estimates that her current vehicle has a trade-in value of $9,800 and that there will be $4,100 due at lease signing. She further estimates that it will cost $2,900 per year to lease and operate the vehicle. The independent contractor's MARR is 11%. Compute the EUAC of both the upgrade and lease alternatives using the insider perspective. Click here to access the TVM Factor Table Calculator. 1943.56 EUAC(keep): $…
Analyzing and Reporting Financial Statement Effects of Bond Transactions On January 1 of the current year, Banek Inc. issued $700,000 of 8%, nine-year bonds for $618,173, which implies a market (yield) rate of 10%. Semiannual interest is payable on June 30 and December 31 of each year. a. Show Excel inputs to confirm the bond issue price. Note: Round your answer to two decimal places. rate= nper= pmt= FV= 0.05 18 (28,000) (700,000) ▼ Answer = $ 125,901.16 * b. Indicate the financial statement effects using the template for (1) bond issuance, (2) semiannual interest payment and discount amortization on June 30 of the current year, and (3) semiannual interest payment and discount amortization on December 31 of the current year. Note: For each account category, indicate the appropriate account name. Enter "N/A" for any account category that is not used for a given transaction. Note: Indicate a decrease in an account category by including a negative sign with the amount. Note: Round your…
Calculate the annual interest and the semiannual interest payment for the following corporate bond issues with a face value of $1,000. Note: Do not round intermediate calculations. Round your answers to 2 decimal places. Annual Semiannual Annual Interest Interest Interest Rate Amount Payment 4.40% 5.10% 3.05% 3.60%