Question

Your company is considering the purchase of a new production system with an installed cost of $1,250,000. The cost will be depreciated on a straight-line basis to zero over the five-year life of the project, and the system can be sold at the end of the project for $225,000. It will provide additional revenue of $685,000 in the first year, and the additional revenue is expected to grow 5% per year thereafter. The associated cost of goods sold is estimated to be 30% of revenue, and other operating expenses are estimated to be 23% of revenue. The project will also require an initial working capital investment of $180,000, which will be recovered at the end of the project. If the tax rate is 21% and the required rate of return is 10%, what is the NPV of this project?

Please use excel, this is how I need to answer it and it's confusing to me

Answer 1

Depreciation per year = 1,250,000/5 = $250,000 per year

Now let us use excel to determine opearting cash flows as shown below (all numbers have been rounded to nearest whole number for illustration but not for calculations):

	Years
	1	2	3	4	5
Additional revenue	685,000	719,250	755,213	792,973	832,622
Cost of goods sold	205,500	215,775	226,564	237,892	249,787
Other operating expenses	157,550	165,428	173,699	182,384	191,503
Depreciation	250,000	250,000	250,000	250,000	250,000
EBIT	71,950	88,048	104,950	122,697	141,332
Tax @ 21%	15,110	18,490	22,039	25,766	29,680
Net income	56,841	69,558	82,910	96,931	111,652

EBIT	71,950	88,048	104,950	122,697	141,332
Add: Depreciation	250,000	250,000	250,000	250,000	250,000
less: tax	-         15,110	-         18,490	-         22,039	-         25,766	-         29,680
Operating cash flow	306,841	319,558	332,910	346,931	361,652

Next we will compute total cash flow:

	Years
	0	1	2	3	4	5
Operating cash flow		306,841	319,558	332,910	346,931	361,652
Changes in NWC	-           180,000					180,000
Capital spending	-       1,250,000					177,750
Total cash flow	-       1,430,000	306,841	319,558	332,910	346,931	719,402

Now we can compute the NPV which is = -1,430,000 + 306,841/1.1 + 319,558/1.1^2 + 332,910/1.1^3 + 346,931/1.1^4 + 719,402/1.1^5

= $46,814.37

Thus NPV = $46,814.37

The formulas used in excel can be seen in the image below: