Please put it into excel with the formulas
Please build the model so that it is user friendly, represents all cash flows on a timeline, and directly solves the quiz questions (see rubric below). If you are unable to build the model, you may answer the selection "watch video" on the first quiz question, which will show you a step-by-step video (you will lose 3 points if you select "watch video"). If you do not need to watch the video, then select "do not watch video" and you will not get any points deducted from the quiz.
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