PLEASE CAN YOU ANSWER THIS QUESTION STEP BY STEP WITH EXPLANATION ON HOW TO DO THE CALCULATION IN EXCEL.

You are evaluating a project to produce a movie and are wondering how much you can afford to pay the actors.

Actors Compensation

The shooting of the movie will commence in month 5 and will take 12 months to complete. Actors are paid on a monthly basis, and their salary grows by 1% each month. The first salary payment to actors will occur at the end of month 5 and the remaining 11 payments will occur at the end of each of the next eleven months.

The Movie

After the movie is released, it will be shown in theaters for 5 months, producing cash inflows at the end of each month. The first cash inflow, to be received at the end of month 18, is estimated to be $25 million. For the remaining four months, the inflow will decrease at a monthly rate of 10%, as the viewership gradually declines and the picture moves to budget theaters.

The Decision

You are now negotiating with several famous actors and wonder about the following question: what is the maximum amount of the first monthly payment to the actors so that the movie creates at least $20 million in present value terms? Assume that the actors salary is the only expense in the movie production, that there are no taxes in this particular example, and that the annual discount rate is 24% APR with monthly compounding. This discount rate reflects the high risk and uncertainty associated with producing a movie.