WACC MODEL

Please combine your Chp.7 and Chp.9 models so that there is one spreadsheet that shows how much total money the company will raise from both stock and debt issuances. Assume the amount raised by the company is the total amount of capital for the company. Once you have combined the two models, please add the following outputs to your model:

• weight of equity
• weight of debt
• WACC

Your model should also add the following input:

• tax rate

Please pay attention to ascetics. Your model should be easy to navigate and formatted in a way that is user-friendly (use borders and boxes to help organize all of the information).

NPV MODEL

You are thinking about making a 14 year private equity investment. You build a model to determine whether you should invest in the private equity investment. Your model's outputs are:

• The NPV of the 14 year project
• The IRR of the 14 year project
• The Decision to "ACCEPT" or "REJECT" the project (this is best done using an 'IF' statement)

Your model requires the following inputs:

• Initial investment amount that you must pay today (at year 0)
• A % loss of the initial investment that is experienced each year during the first 5 years (e.g., the investment loses 2% of the initial investment each year for the first 5 years)
• A % gain on the initial investment that is experienced each year during the remaining 9 years (years 6-14) (e.g., the investment gained 2% of the initial investment each year during years 6-14)
• Expected Return => if from investor perspective = WACC => if from corporate perspective

WACC MODEL

If you have built the model correctly, you will have a WACC of 8.2% given the following inputs:

• number of bonds issued = 50,000
• principal per bond = $1,000
• coupon rate = 5.0%
• Yield-to-Maturity = 10.0%
• Dividend at year 0 = $3.55
• Dividend Growth rate during years 1-10 = 5.5%
• Dividend terminal growth rate = 2.2%
• Beta of company = 1.2
• Risk-free rate = 3.0%
• Expected market return = 8.5%
• Total shares of stock = 500,000
• Tax rate = 35.0%

NPV MODEL

If you have built the model correctly, you will have a NPV of $488 and IRR of 8.6% given the following inputs:

• Initial investment = $12,500
• % loss of the initial investment from (years 1-5) = -0.5%
• % gain on the initial investment (years 6-14) = 25.3%
• expected return = WACC from WACC model above