Use your model for the profitability of an average Hollywood movie from Exercise 1.8. The variability of movie profitability has received much study and is the subject of plenty of casual conversation.22 Suppose the costs (production plus marketing) are uncertain and follow a PERT distribution (minimum = $50 million, mode = $100 million, maximum = $450 million). Movie production can take anywhere from 6 to 24 months (following a uniform distribution). Domestic box office receipts also follow a PERT distribution (minimum = $0, mode = $0, maximum = $200 millionnote that this assumption reflects the fact that many movies never run in theatres at all). Home video sales are still based on domestic box office receipts, but follow a PERT distribution (minimum = 20%, mode = 80%, maximum = 180%). Hit movies (defined as those with greater than $100 million in domestic box office receipts) earn $25 million from premium TV and $25 million from free TVhigher than the figures used in Exercise 1.8.

Estimate the internal rate of return for films.23 Use the Excel function XIRR (for monthly rates of return).24 Note that the IRR function will produce Excel errors if the true internal rate of return is a large negative number. The XIRR function also encounters problems. These are related to the initial guess (the third, and optional, argument in the function). The default guess is 0.1, and this will produce errors if the true internal rate of return is negative and large enough (in absolute value). To remedy this problem, use an initial guess equal to 0.1 * sign(sum of cash flows). When the sum of the cash flows is negative (positive), this results in an initial guess of 0.1 (+0.1). XIRR will then correctly estimate the internal rate of return for both negative and positive values.

What fraction of films earn a negative return? What fraction earns returns above 30%? (You can compare these fractions to those identified in the footnote. They will not be the same, but they should be qualitatively similar