Financial Calculation: Return on Investment

[Guest]

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.

 

[Claud Balls]

You would probably have the most luck entering data through a user form.
I'm not familiar with IRR, can you give me an explanation of what
exactly it is/does?

 

[Guest]

Claud,

Excel's IRR function uses an iteration process to find the interest rate
where Net Present Value of the stream of cash flows equals zero. The basic
economics text formula for Present Value is P = A(((1+i)^n - 1)/(i(1+i)^n))
where i=interest rate, n=number of years or periods, A=Annual cash return,
P=Initial cash outlay. For example, suppose I want to create an annuity
stream that will give me $1000/yr for 3 years, from an investment earning
10%. I would have to deposit $2487 into the account initially. After the
three annual withdrawals, the account would be empty. What IRR does is this:
you give it the $2487 deposit and the three $1000 returns in an array, and
it will give back that this investment was worth 10%. Rather than give IRR a
fixed array of cash flows, I want to input three pieces of information:
Initial Investment, a single value for Annual Return, and a single value for
how many years this investment will be in place. Then have my user-friendly
equivalent of IRR tell me what that investment was worth, in percentage.
That way I can change the timespan of the investment without having to re-do
the spreadsheet.

Mark