Reference Problem one:

Using an expected return of E[r] = 7.0% per year, determine the NPV^Fair for the following stream of cash flows (CFs) associated with V Laboratories common stock:

The current price of V Labs stock is $50 per share.

The first dividend, paid at t = 1, is $2.00 per share. Subsequent dividends are paid annually.

The growth rate of the dividend stream is 5% per year. Dividends are received in each year from t = 1 to t = 20 (including at time 20)

The stock is sold for $32.50 per share in 20 years immediately after receiving the 20^th dividend.

WITHOUT using Excel (yet), compute by hand (i.e. using the equations from Chapter 2), the fair NPV of V Labs stock.

Of course, since you seek the NET present value, you need to include all the negative CFs too (of which there is only one)

Draw a good timeline!

Report the fair NPV with least 4 digits of precision. Do not include a dollar sign or commas in your answer.

Be careful with the sign. Answer: - 10.1676

Question 1:

Using the same cash flow timeline described in Problem 1, build an Excel spreadsheet with:

time (running from t = 0 to 20) in column B (in cells B4 to B24), and

the cash flows in column C (cells C4 to C24).

Then use Excels IRR command to compute the IRR of the cash flows associated with V Labs common stock.

Theres some Excel guidance below, which you may want to read before you build your spreadsheet.

NOTE: For those of you that are new to Excel, do not manually type in each of the 20 dividend cash flows. Instead, enter the first dividend (of 2.00) lets say in cell C5, and then below that (in cell C6) enter the following equation: =C5*1.05 (dont forget the leading equal sign). Then use Excels copy/paste commands to copy cell C6 to cells C7 to C24 (which will give a total of 20 dividends, paid at the end of years 1 to 20). If you dont know about copy/paste commands, Google it and find a video on YouTube. Its simple and powerful.

If you do this correctly, then the dividend at time 20 (in cell C24) will be approximately $5.053900. Then add $32.50 in cell C24 to the dividend value, which will give an overall CF at t = 20 of 37.553900. In Cell C24 your equation should read: =C23*1.05 + 32.50

Then enter the cost of the stock (paid at time zero) in cell C4 (just prior to the CF at t = 1 of $2.00). Make sure you enter the cost as a negative CF (-50). Then use Excels IRR command (in any blank cell) to compute the IRR of the 21 cash flows you just constructed.

ALSO: As discussed in the chapter, Excels IRR command tends to report the percent IRR rather imprecisely (i.e. with too much rounding). For example, if the IRR value is actually 3.278%, Excel will report to the screen a value of 3%. In order to see the additional precision, youll need to right click the cell with the IRR computation, then choose Format Cells from the drop down menu, and then select Percent (or preferably Number) and several digits (say 8) of precision. Choosing Number, rather than Percent will format the IRR as a decimal, not as a percent.

Please enter the IRR as a PERCENTAGE, not as a decimal, with at least 4 digits of precision. For example, if the IRR is computed to be 0.04293, then enter an answer of 4.293 which has 4 significant digits of precision.

HINT: The answer is between 4.0 and 6.0 (percent).

Answer 1?

Question 2:

For the cash flow timeline described in Problem 1, and the spreadsheet you constructed in Problem 2, use Excels NPV command to compute the NPV when r = 7%. (0.07).

It should match what your reported earlier in Problem 1. Check that it does.

If it doesnt match, and instead you computed -49.71 via Excels NPV command (which is wrong), its because you didnt enter the discount rate in the NPV command as a decimal. i.e. you entered 7, not 0.07 as you should have. Fix that, and youll be OK.

HINT: As a check, if you compute the NPV with a discount rate of 0%, then you should get $48.63 (which is simply the sum of all 21 cash flows).

Enter the NPV for a 7% discount rate, using at least 4 digits of precision. Be careful with the sign.

Answer 2?