How to set up Return on Investment spreadsheet

  • Thread starter Thread starter Ken Burgin
  • Start date Start date
K

Ken Burgin

Hi - I want to set up a simple calculator that will show opening cost of a
piece of equipment, number of years used before worthless and savings each
year. Results will be the eturn on purchase price.

How do I do this? Any help appreciated...

Thanks

Ken
Sydney
 
Hi Ken!

If your equipment cost $2000 and the annual saving was 50 per month
for 60 months

=(1+RATE(60,50,-2000,0,0))^12-1
Returns: 18.7091167089518% per annum effective

The key bit here is:

=RATE(60,50,-2000,0,0)
Which on it's own returns: 1.43947810009151%

This reflects money out (hence negative 2000) at the beginning. Money
comes in (hence positive 50) for 50 periods with a zero final value
and assuming you wait until the end of the first month for your
return.

Since I'm measuring time in months the RATE function returns the rate
per month. I therefore "nest" the function in the compound interest
formula (1+i)^n-1

It's all based upon assumptions. In your case there's an assumption
that the return will not increase or decrease over time, that the
equipment will last 60 months and not more or less, and that the
return each month will be the same. And then there's the inestimable
joys of seeing your spouse happy and being able to watch the cricket
in peace.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday 20th July: Bulgaria (St. Elijah's
Day), Colombia (Independence Day), North Cyprus (Peace and Freedom
Day), Italy (Festa del Redentore), Japan (Marine Day), Norway
(Birthday of Crown Prince Haakon), Solomon Islands (Special Holiday
Renbel Province), USA (Moon Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Some other things to consider.

Make sure you reduce the purchase price by any value you receive from
existing equipment that you may be able to sell as a result of purchasing
this new piece of equipment.

If you would have had to purchase a new piece of equipment in a few years
because the current equipment would become obsolete or would not support a
growing demand you should consider adding back the cost of the equipment you
would have otherwise had to purchase.

For a great discussion on the topic see

www.stern.nyu.edu\~adamodar

And click on the following links

Classes & Support | Corporate Finance | Lecture Notes | Measuring Returns

See page 72 and on regarding opportunity costs.

PC
 
Hi Paul!

Wow! Another fan of Damodaran! All comments of course are agreed.

One point that is often overlooked in these calcs are the difficult /
subjective estimates of the non-financial aspects of calculations. I
referred to them in jest but they are significant in the decision
making process. And that's after we have attempted the calculations
involved with risk adjustments many of which are in themselves
difficult to quantify.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday 20th July: Bulgaria (St. Elijah’s
Day), Colombia (Independence Day), North Cyprus (Peace and Freedom
Day), Italy (Festa del Redentore), Japan (Marine Day), Norway
(Birthday of Crown Prince Haakon), Solomon Islands (Special Holiday
Renbel Province), USA (Moon Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top