On Oct 7, 6:06 am, Chris Gorham
wrote:
Could any one tell me the correct worksheet function that calculates the
total interest (and / or total value) of a series of cashflows. These
cashflows are all positive, but not equal and occur at irregular intervals
over 24 months, whilst the interest rate that is appied is constant
throughout.

It cannot be done -- at least, not based on the information provided.

You cannot use Excel's XIRR() function. That requires that some of
the cash flows have opposite signs. And for good reason. The IRR is
the "interest" (discount) rate at which the present values of all the
cash flows sum to zero. How could all positive or all negative values
sum to zero? That's rhetorical. The answer is: they cannot.

Returning to your question, if all the cash flows are positive (or
negative), there is no way to deduce the interest rate unless you add
one more piece of information, namely: what is the "future" value,
that is the value after all cash flows occur?

That should be clear if you consider the following. Suppose you
deposit $100 each month for 12 months. The interest rate could be 1%
per month, 2%, 3%, 4%. There is simply no way to know. But if I say
that the value after 12 months is $1500, we can compute the interest
-- namely RATE(12, -100, 0, 1500,1).

I could also have written RATE(12,100,0,-1500,1).

Again, that works because some cash flows have opposite signs.

And of course, RATE() was a good choice for my example because the
cash flows were equal and regular. For irregular cash flows, I might
have used XIRR().

HTH.