fair enough: the concept of ROI is to gauge value in an investment. sum of returns (increased revenue) from the new machine compared to the cost of it. thats as i understand it.
so $40m to buy, approx $14m pa in gross margin. Payback between year 3 and 4. ROI 14/40 by years?
I didn't ask for nor need an explanation of the concept (first response). I asked for how you __apply__ the concept, which you cover in the second response -- sort of.
No mention of how you apply depreciation in the ROI calculation. And IMHO, we shouldn't. AFAIK, it would factor into the calculations only if you want to look at after-tax profit (net profit, not gross profit). And even then, depreciation is only a (small) factor in the tax calculation; i.e. depreciation is a tax deduction.
So the questions are.... (1) Did you include depreciation in your operating cost estimate ($13.5M per year)? (2) How many years (months) did you use for the straight-line depreciation? And (3) What is your marginal tax bracket?
If the answer is "no" for #1, we don't need the answers to #2 and #3 if you agree that depreciation should not factor into the ROI calculation.
-----
That said, does the following design meet your needs.
Select or hover the cursor over cells to see formulas and constants. Click on the copy icon (near the upper left) to copy-and-paste into a new Excel worksheet.
Cells like B2 have the Custom format $0\.000,\M to display the actual value $40,000,000 as $40.000M, for example.
Yellow highlighted cells are the amounts to be entered.