I have attached the excel sheet that has multiple tabs and multiple questions per tab. I do have all of the attachments I can send after.
#1 Assignment 5.4 Exercises Problem 1: Calculating Holding Period Return5 Points Information about three securities is listed below: SecurityBeginning of Year PriceEnd of Year PriceInterest or Dividend Paid During Year Stock A$106.35$112.40$1.85 Stock B$1.65$1.85$0.00 Bond X$1,052.00$1,028.00$47.00 Use the information provided above to answer the following questions. a) What is the Holding Period Return for each of these securities? b) Suppose that during the year, the executives of Stock A decided to spend spent $185 million repurchasing the company's shares. How, if at all, does this information affect the calculation of the Holding Period Return? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs, and be sure to answer both parts of the question. Input / Output area: SecurityStarting Price ($)Ending Price ($)Income ($)Change in PriceCapital Gain (loss) + Dividend Yield= Holding Period Return Stock A$ -ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Stock B$ -ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! Bond X$ -ERROR:#DIV/0!ERROR:#DIV/0!ERROR:#DIV/0! b) Intepretation: How does repurchasing shares affect the calculation for Holding Period Return? (briefly explain here) This is the Student Template, provided in the assignment instructions October 2019 #2 Assignment 5.4 Exercises Problem 2: Calculating Holding Period Return5 Points Information about three securities is listed below: SecurityBeginning of Year PriceEnd of Year PriceInterest or Dividend Paid During Year Preferred A$10.00$10.00$0.75 Common B$38.00$36.50$3.35 Bond C$985.00$1,035.00$78.00 Use the information provided above to answer the following questions for EACH security. a) What is the Capital Gain (loss) in dollar terms? What is the Capital Gain Yield (%)? b) What is the Dividend Yield (%)? c) What is the total Holding Period Return (%)? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 #3 Assignment 5.4 Exercises Problem 3: Calculating Return Components5 Points An investor purchases one share of stock for $50. After one year, they sell the share for $55. During the year, they receive $7 in dividends. a) What was the dividend yield, in percentage terms? b) What was the capital gain from price appreciation on the stock, in percentage terms? c) What was the total return in dollars? What was the total return, in percentage terms? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 #4 Assignment 5.4 Exercises Problem 4: Calculating Dollar Returns with Exchange Rates5 Points An American investor purchases a single Eurobond from their personal financial advisor. The bond is denominated in Euros, but the investor uses their American dollars to make the purchase. The bond sells for 1023 euros, and has a par value of 1000 euros. At the time of purchase, the exchange rate is $1 US Dollar per 1.24 Euros. The coupon rate on the bond is 8%, paid annually. One year later, the coupon is paid and the investor sells the bond for 918 euros. The exchange rate at the time of sale has fallen to $1 US Dollar per 1.10 Euros. a) What was the purchase price in US Dollars? b) How much money did the investor earn from coupon interest, in dollars? How much did they lose due to the decline in the bond's price? How much did they gain from the change in the exchange rate? c) What was the bond's selling price, in US Dollars? d) What was the investor's total earnings during the year, in US Dollars? e) The bond fell in value dramatically during the year. Its price fell by over 10% in Euros! Nevertheless, the investor sold the bond for more money, in US Dollars, than they bought it for, and earned a substantial return. How is this possible? Explain. Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs, and be sure to answer all parts of the question. Input area: Purchase Price (euros) Par Value (euros) Exchange Rate at Purchase (USD/EUR) Coupon Rate (%) Selling Price (euros) Exchange Rate at Sale (USD/EUR) Output area: a)Purchase Price (dollars)ERROR:#DIV/0!e) Intepretation: How is it possible to earn a substantial return even though the bond's price fell dramatically? (briefly explain here) b)Coupon Interest Earned (dollars)ERROR:#DIV/0! Capital Loss (dollars)ERROR:#DIV/0! Gain from Exchange Rate (dollars)ERROR:#DIV/0! c)Sale Price (dollars)ERROR:#DIV/0! d)Total Earnings (dollars)ERROR:#DIV/0! Total Earnings (%)ERROR:#DIV/0! This is the Student Template, provided in the assignment instructions October 2019 #5 Assignment 5.4 Exercises Problem 5: Determining Issue Costs5 Points A growing company wants to raise $275 million in a new stock issue. Its investment banker indicates the sale of stock will require 9 percent underpricing to attract new investors. They will also charge an 8 percent spread. Underpricing is expressed as a percentage of the current public stock price, while the spread represents a cut of the issue price. The company's current stock price is $38 per share. a) At what price will the shares be sold to the public? b) What price per share will the company actually receive? c) How many shares must the company sell to raise the desired funds? d) How much money will the investment banking syndicate earn on the sale? e) What is the total cost of raising the funding, due to both investment bank costs and underpricing? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs. Input area: Desired Funds ($) Current Stock Price ($ per share) Underpricing (%) Investment Banker's Spread (%) Output area: a)Price shares will be sold at:$ - b)Net price company will receive:$ - c)Shares that must be issued:ERROR:#DIV/0! d)Investment bank earnings:ERROR:#DIV/0! Cost of underpricing:ERROR:#DIV/0! e)Total cost of raising funds:ERROR:#DIV/0! Cost as percentage of funds raised:ERROR:#DIV/0! This is the Student Template, provided in the assignment instructions October 2019 #6 Assignment 5.4 Exercises Problem 6: Determining Issue Costs10 Points A large, mature company wants to raise $680 million in a new stock issue. Its lead investment banker indicates the sale of stock will require 5 percent underpricing to attract new investors. They will also charge a 4 percent spread. The company expects they will incur $1,850,000 in legal and adminstrative fees to raise these funds. The company's current stock price is $53 per share. a) At what price will the shares be sold to the public? b) What price per share will the company actually receive? c) How many shares must the company sell to raise the desired funds? d) How much money will the investment banking syndicate earn on the sale? e) What is the total cost of raising the funding, including all costs? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 #7 Assignment 5.4 Exercises Problem 7: Impact of Financial Leverage5 Points Behemoth Enterprises needs to raise $10 Billion to fund a major new project designed to reinvigorate the giant company's growth. The company's top executives are debating whether to raise the money by issuing debt or by issuing new shares of stock. Their team of analysts predict that if the project goes through, the company's Earnings Before Interest and Taxes (EBIT) will increase to $9.7 Billion. However, if the company uses debt to fund the project, they will have to pay interest of 5% on this new debt, along with $200 million in annual sinking fund payments. This would be on top of the $2.75 Billion the company already pays in interest on its existing debt, plus $2.3 Billion in annual sinking fund payments. If the company instead chooses to issue new shares of stock, their investment banker predicts they will be able to issue additional shares at $10 per share. The company currently has 8.7 Billion shares outstanding at $11.50 per share. The company's effective tax rate is 21%. a) If the company raises the funding with equity, what will be its times-interested earned ratio? What will be its times-burden-covered ratio? What will be its earnings per share? b) If the company raises the funding with debt, what will be its times-interested earned ratio? What will be its times-burden-covered ratio? What will be its earnings per share? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs. Input area: Expected EBIT After Project ($ millions) Funding Needed ($ millions) Annual Interest on Existing Debt ($ millions) Interest Rate on New Debt (%) Sinking Fund Payments on Existing Debt ($ millions) Sinking Fund Payment on New Debt ($ millions) Common Stock Price ($ per share) Price of New Issue of Stock ($ per share) Common Shares Outstanding (millions) Effective Tax Rate (%) Output area: a)Equity Funding Times-Interest-Earned RatioERROR:#DIV/0! Times-Burden-Covered RatioERROR:#DIV/0! New Shares to Issue (millions)ERROR:#DIV/0! Earnings per ShareERROR:#DIV/0! b)Debt Funding Interest Payments on New Debt ($ millions)$ - Times-Interest-Earned RatioERROR:#DIV/0! Times-Burden-Covered RatioERROR:#DIV/0! Earnings per ShareERROR:#DIV/0! This is the Student Template, provided in the assignment instructions October 2019 #8 Assignment 5.4 Exercises Problem 8: Impact of Financial Leverage10 Points Pipsqueak Co. has an exciting opportunity to buy out its main local competitor for $200,000. Doug Kindle, owner/operator of the business, calculates the combined EBIT of the merged companies would be $350,000. To pay for the acquisition, he is considering taking out a loan with his local bank, backed by his home mortgage, at an interest rate of 8%. In addition, $7500 of principal payments would be due each year. Fortunately, the company only has a small amount of existing debt, necessitating $10,000 in annual interest payments and $3000 in principal payments. However, the owner of Pipsquek's competitor has offered to instead take stock as a form of payment, rather than cash. In this case, Doug would issue shares of stock at $10 per share, and the competitor's current owner would become a part-owner in Pipsqueak Co. Doug's accountant tells him his current stock is worth about $15 per share. Doug owns all 80,000 existing shares. The company's tax rate is 21%. a) If the company raises the funding with equity, what will be its times-interested earned ratio? What will be its times-burden-covered ratio? What will be its earnings per share? b) If the company raises the funding with debt, what will be its times-interested earned ratio? What will be its times-burden-covered ratio? What will be its earnings per share? c) Which option would you suggest Doug pursue? Why? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019