Question

Bill Clinton reportedly was paid $15 million to write his book My L . Suppose the book took three years to write. In the time he spent writing, Clinton could have been paid to make speeches. Given his popularity, aume that he could earn $8 million per year (paid at the end of the year) speaking instead of writing. Awame his cost of capital is 10% per year. Complete the steps below using cell references to given data or previous cakulations. In some cases, a simple cell reference is all you need. To copypaste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specifike Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. What is the NPV of agreeing to write the book ( ring any royalty payments)? Assume that once the book is finished, it is expected to generate royalties of S5 million in the first year (paid at the end of the year) and these royalties are expected to decrease at a rate of 30% per year in perpetuity. What is the NPV of the book with the royalty payments? $15.000.000 2.000.000 Advance payment for book Foregone speaking fees Cost of capital Number of years What is the NPV of agreeing to write the book ( oring any royalty payments)? PV of speaking fees NPV of book deal b. Assume that, once the book is finished, it is expected to generate royalties of $5 million in the first year (paid at the end of the year) and these royalties are expected to decrease at a rate of 30% per year in perpetuity. What is the NPV of the book with the royalty payments! Royalties for book Growth rate of royalties PV of perpetuity at year 3 PV of perpetuity at year o NPV of book deal Requirements 1. Start Excel - completed 2. In cell DIS, by using cell references and the function PV, calculate the present value of the forgone speaking fees (1 pt.) Note: The output of the expression or function you typed in this cell is expected as a negative number. 3. In cell D16, by using cell references, calculate the NPV of the book deal without royalties) (1 pt.). 4. In cell D23, by using cell references, cakulate the pre v alue of the future royalty payments (1 pt.). In cell D24, by using cell references and the function PV,cakulate the present value (as of year 0) of the future royalty payments (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number 6. In cell D25, by using cell references, cakulate the NPV of the book deal with royalties) 1 pt.) 7. Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed

Please answer in excel formula format. This is the full question with all the information.

Answer 1

Present value of speaking fees Net Present Value (NPV) Cost of capital Timeline 2 IRR Cash flows -15 8 8 19.89 Case 1 10% 4.89 27.76% Now royalties of $5million at end of year 4 and the it will decrese at 30% Hence, Total value of outflow from perpetuity at time t=3 years is Payements/(1+l-x), where I is the cost of capital and x is rate of decrease Hence, Total value of inflow at time t=3 years = 8 - Payements/(1+l-x) Payements/(1+l-x) = Hence, Total value of inflow at time t=3 years = 8 - Payements/(1+l-x) = -0.33 Hence, Presnt value of royalties from 4th year onwards is 8.33/((1.10)^3]6.26 5/(1-.1-.3) = 8.33 Case 2 Cost of capital Timeline Present value of speaking fees Net Present Value (NPV) IRR Cash flows 13.63 -15 8 -0.33 10% -1.37 3.01%