simple machine purchase ROI

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,856
folks, i need to calculate ROI and estimated payback period on a machine purchase. I understand the concept but not how to lay it out. i have the machine costs, straight line depreciation, running costs, and revenue. how do i lay it out? anyone got a simple file i could use?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
i have the machine costs, straight line depreciation, running costs, and revenue.

Needless to say, it might help if you provided that data -- whatever data that you have.

I understand the concept

.... And share whatever you do know. For example, given that data, how would you apply the concept to ``calculate ROI and estimated payback period``?
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,856
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.

machine cost is $40m
revenue estimated to be $2.2m per month, so $28m app pa
costs to operate $13.5m pa

what else do i need?
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,856
so $40m to buy, approx $14m pa in gross margin. Payback between year 3 and 4. ROI 14/40 by years?
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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.

ROI of equip.xlsx
ABCD
1AnnualMonthly
2Investment$40.000M
3Revenue$26.400M$2.200M
4Op Costs$13.500M
5Gross Profit$12.900M$1.075M
6Annual ROI32.25%
7Payback Period3.1738
Sheet1


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.
 
Solution

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,856
thanks Joeu2004. i did include depreciation in the calculation of the net cashflows. i have used straight line depreciation with a useful life of 30 years. I have no idea of the marginal tax bracket. based n the simple calc you provided above, i have what i need now. cheers
 

Forum statistics

Threads
1,144,390
Messages
5,724,074
Members
422,535
Latest member
navjeet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top