The Boston Culinary Institute is evaluating a classroom remodeling project. The cost of the remodel will be $300,000 and will be depreciated over 5 years using the straight line method. The remodeled room will accomodate 5 extra students per year. Each student pays annual tuition of $22,000. The before tax incremental cost of a student (e.g., the cost of food prepared and consumed by a student) is $2,000 per year. The company’s tax rate is 40% and the company requires a 12% rate of return on the remodeling project.
Required:
Assuming a 5 year time horizon, what is the internal rate of return of the remodeling project? Should the company invest in the remodel?
Cash flow per year:
Revenue [formula]
Less costs other than depreciation [formula]
Depreciation [formula]
Title [formula]
Title [formula]
Net income [formula]
Title [formula]
Cash flow [formula]
Annuity factor equals cost divided by annual cash flow: [formula]
For Excel to determine the internal interest rate you must provide a matrix of cash flows as follows:
Cash out: [amount]
Cash in year 1: [amount]
Cash in year 2: [amount]
Cash in year 3: [amount]
Cash in year 4: [amount]
Cash in year 5: [amount]
Excel internal rate of return (IRR) formula: [formula]