Question
please use excel and show the formulas Please combine your Chp.7 and Chp.9 models so that there is one spreadsheet that shows how much total
please use excel and show the formulas
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Introduction To Machine Learning In Quantitative Finance An Advanced Textbooks In Mathematics
Authors: Hao Ni, Xin Dong, Jinsong Zheng, Guangxi Yu
1st Edition
1786349361, 9781786349361
