Please put it into excel with the formulas
BOND MODEL
Your company wants to issue 30 year fixed bonds in the debt markets to raise money. Your model has the following two outputs:
Price of the bonds you are issuing
How much money will be raised
Your model requires the following inputs:
number of bonds issued
principal per bond
coupon rate
Yield-to-Maturity
Assume the bonds pay coupons annually.
If you have built the model correctly, you will have a total amount raised of $1,098,908 given the following inputs for a 30 year, fixed bond, paid annually:
number of bonds issued = 2,000
principal per bond = $1,000
coupon rate = 2.4%
Yield-to-Maturity = 5.5%
STOCK MODEL
Your company is looking to raise money by issuing shares of stock. You build a model with the following output:
Price of 1 share of stock
Total money raised from stock issuance
Your model has the following inputs:
Dividend at year 0
Dividend Growth rate during years 1-10
2nd Stage Terminal dividend growth rate
Expected return
Total shares of stock
STOCK MODEL
If you have built the model correctly, you will have a total amount raised of $19,872,064 given the following inputs:
Dividend at year 0 = $3.55
Dividend Growth rate during years 1-10 = 5.5%
2nd Stage Terminal dividend growth rate = 3.5%
Expected return =8.9%
Total shares of stock = 250,000