The Barnett Corporation is analyzing the proposed purchase of a new machine for $2,750,000. The proposed machine has an estimated economic life of four years but will be treated as three-year MACRS property for depreciation purposes. The machine will increase the firm's capacity, and it is expected to contribute $1,100,000 annually to earnings before depreciation and taxes. The firm is in a 32% tax bracket and estimates its cost of capital to be 16%.
***The numbers above should be hard entered somewhere on your spreadsheet as given variables. The spreadsheet itself should be comprised of formulas, so that you can change a variable (such as asset cost, tax rate or discount rate) and the spreadsheet will automatically recalculate NPV and IRR.
Calculate the NPV of this investment using an Excel spreadsheet. Please show all work using excel, including what you used for formulas.
Calculate the IRR, also in Excel. Please show all work/formulas you used.
0 | 1 | 2 | 3 | 4 | |
MACRS % | 33.33% | 44.45% | 14.81% | 7.41% | |
Investment | -2,750,000 | ||||
EBDT | 1,100,000 | 1,100,000 | 1,100,000 | 1,100,000 | |
Depreciation | -916,575 | -1,222,375 | -407,275 | -203,775 | |
EBT | 183,425 | -122,375 | 692,725 | 896,225 | |
Tax (32%) | -58,696 | 39,160 | -221,672 | -286,792 | |
Net Income | 124,729 | -83,215 | 471,053 | 609,433 | |
Cash Flows | -2,750,000 | 1,041,304 | 1,139,160 | 878,328 | 813,208 |
NPV | $6,092 | ||||
IRR | 16.12% |
Depreciation = Investment x MACRS %
Cash Flows = Investment + Net Income + Depreciation
NPV and IRR can be calculated using the same function in excel with 16% discount rate.
Get Answers For Free
Most questions answered within 1 hours.