Question

Please consider the following information on a potential investment project that a company is considering to open a new office location. Initial cost of building and equipment is $15 million Expected to have a useful life of 30 years At the end of the project the building and its equipment are expected to be sold for a $5,000,000 salvage value The building and its equipment will be depreciated over their 30-year life using straight-line depreciation to a zero balance The building is to be constructed on land leased for $300,000 per year Net working capital must be increased by $250,000 This amount would be recovered at the end of the 30-year life. Annual revenues from the new office will be $2,400,000 Of this $2,400,000 in revenues, $500,000 will be drawn away from the firm’s main office The new office will incur about $1,000,000 per year in other expenses Both expenses and revenues are expected to remain approximately constant over the new office’s 30-year life Marginal tax rate is 30% Cost of capital 15%

Calculate NPV, IRR, and PI of the project

Answer 1

The above spreadsheet shows all the cashflows from Year 0 to Year 30.

The revenue and expeses will remain constant throughought the year.

The annual depreciation is the cost of the building and equipment divided by the number of years, 15 million / 30 = 0.5 million

The purchase of the building and equipment as well as the increase in net working capital is an outflow of cash and is denoted as negative

Depreciation, purchase/sale of building and equipment and networking capital are added to Net income to give Operating Cashflow

In year 30, we use the after tax value of the salvage value [5,000,000 x (1 - 30) = 3,500,000] since the equipment was depreciated to 0, and we add back 250,000 of the net working capital.

To find NPV we use the formula = NPV(15%,C12:AF12) + B12 to get -$11,450,755.15

To find IRR, we use the formula = IRR(B12:AF12) to get 1.83%

Profitability index = PV of future cashflows / initial cost

PV of future cashflows = NPV(15%,C12:AF12) = $3,799,244.85

Initial cost = $15,250,000

Profitablitiy index = 3,799,244.85/15,250,000 = 24.91%