M
Mark Schreiber
Looking for a method to calculate the ROI (Return on Investment) directly
from First Cost, Annual Return and Number of Years. The built-in spreadsheet
function IRR does this, but only for a fixed array of data entry. For
example, if you know the project service life will be seven years, you could
put the First Cost into cell A1, then the annual returns in cells A2:A8.
Then the formula to calculate ROI in cell A10 would be "=IRR(A1:A8,0.1)" and
it would automatically calculate it.
But, I want to be able to use this for projects of varying service life
periods. Rather than have a string of annual cash flows set into a fixed
array of cells, there will be just the 3 parameters for cost, return, years.
To do this manually using Compound Interest tables calculate the annuity
ratio, then flip through the tables until you find a corresponding value at
the specified number of years. Looking for an elegant way to essentially do
that using formulae or user-defined function.
from First Cost, Annual Return and Number of Years. The built-in spreadsheet
function IRR does this, but only for a fixed array of data entry. For
example, if you know the project service life will be seven years, you could
put the First Cost into cell A1, then the annual returns in cells A2:A8.
Then the formula to calculate ROI in cell A10 would be "=IRR(A1:A8,0.1)" and
it would automatically calculate it.
But, I want to be able to use this for projects of varying service life
periods. Rather than have a string of annual cash flows set into a fixed
array of cells, there will be just the 3 parameters for cost, return, years.
To do this manually using Compound Interest tables calculate the annuity
ratio, then flip through the tables until you find a corresponding value at
the specified number of years. Looking for an elegant way to essentially do
that using formulae or user-defined function.