One important variable in choosing to finance a project is the yield. This case study is concerned with taking a decision to finance a profitable project by a company. Of course, the company has many choices for financing including borrowing from a bank, issuing bonds or/and issuing stocks. Our company looked at all the alternatives available and decided to issue bonds. In the following case, the company has two alternatives, and they are asking your advice. Read the case carefully, and use EXCEL to provide answers to the required questions

The investment bank which will underwrite the issue for QP suggests that the market would be receptive to a 10-year bond with a face value of 4 million with a $140,000 annual coupon (paid semiannual every 6 months is $70,000). Alternatively, QP has the option to raise the $2.5 million by issuing 10-year zero-coupon bonds with a face value of $5.5 million.

1-Calculate the annualized yield to maturity for option one

2-Calculate the annualized yield to maturity for option two

3- Which option would the company prefer? Why?

4-Suppose the yield on the first option after 5 years of the issue became 5% annually on a similar issue of the same risk in the market. Then what would be the value of their issue?

5-Use Excel sheet to calculate the yield to maturity for option one and option two using the following information in the table below. Which option do you think the company should choose? Draw in a diagram for the YTM options one and two (make the numbers in the table your x-axis and the yield your y-axes)? Show when the company is indifferent between choosing options 1 and 2?

e Case:

Qatar Petrol (QP) Corporation has a new project that it expects to produce a cash flow of $6.5 million in 10 years. To finance the project, the company needs to borrow $2.5 million today. The project will produce a cash flow of $140,000 per year that the company can use to service the annual coupon payments.

Semi-Annual Coupon Rate for Option one	Face Value for Zero Coupon Bond option two	YTM for Option one	YTM for Option two
0.01	4,000,000
0.005	4,500,000
0.0175	5,000,000
0.035	5,500,000
0.08	5,800,000
0.095	6,000,000
0.11	6,200,000

Please could you show excel formula