Problem #36 (30 points) Make all calulations in $ milllons. Original investment Hints: Year 1 cash flow (trial value) The problem states that the year 1 cash flow will be between $30 million and $100 million. In cell C10 enter an intial trial value of 50 million as input values to cell C10 in a Data/Table. Annual growth rate (trial value) You can then use values between 30 million and 100 In cell C11 enter an intial trial value of 15%. You can then use values between 5% and 25% per year input values to cell C11 in a Data/Table. Yearly cash flows Cash flow Year Cumulative The problem states that the annual cash flow growth will be between 5% and 25% per year. QUESTION 36: The payback of a project is the number of years it takes before the project's total cash flow is positive. Payback ignores the time value of money. It is interesting, however, to see how differing assumptions on project growth impact payback. Suppose, for example, that a project requires a $300 million investment right now. Use a data table to see how the project payback depends on the year 1 cash flow and the cash flow growth rate. flow will be between $30 million and $100 million. The project yields cash flows for 10 years, and the year 1 cash The annual cash flow growth will be between 5% and 25% per year. (Assume that this growth is the same each year.) 10 Year of payback is the year cumulative cash flow exceeds the original investment. To find this year use the MATCH function. The MATCH function will find the year with largest value in the lookup range that is still less than the lookup value. Year of payback Therefore, the year number that MATCH returns will be one year before the actual payoff tear. Data table for payback as a function of year 1 cash flow (along top) and annual growth rate (along side) 30 40 50 60 70 0 80 90 100 5% 10% 15% 20% 25% Problem #36 (30 points) Make all calulations in $ milllons. Original investment Hints: Year 1 cash flow (trial value) The problem states that the year 1 cash flow will be between $30 million and $100 million. In cell C10 enter an intial trial value of 50 million as input values to cell C10 in a Data/Table. Annual growth rate (trial value) You can then use values between 30 million and 100 In cell C11 enter an intial trial value of 15%. You can then use values between 5% and 25% per year input values to cell C11 in a Data/Table. Yearly cash flows Cash flow Year Cumulative The problem states that the annual cash flow growth will be between 5% and 25% per year. QUESTION 36: The payback of a project is the number of years it takes before the project's total cash flow is positive. Payback ignores the time value of money. It is interesting, however, to see how differing assumptions on project growth impact payback. Suppose, for example, that a project requires a $300 million investment right now. Use a data table to see how the project payback depends on the year 1 cash flow and the cash flow growth rate. flow will be between $30 million and $100 million. The project yields cash flows for 10 years, and the year 1 cash The annual cash flow growth will be between 5% and 25% per year. (Assume that this growth is the same each year.) 10 Year of payback is the year cumulative cash flow exceeds the original investment. To find this year use the MATCH function. The MATCH function will find the year with largest value in the lookup range that is still less than the lookup value. Year of payback Therefore, the year number that MATCH returns will be one year before the actual payoff tear. Data table for payback as a function of year 1 cash flow (along top) and annual growth rate (along side) 30 40 50 60 70 0 80 90 100 5% 10% 15% 20% 25%