Annuity Formula
 

Hi,

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.

Thanks...Chris

Annuity Formula
 

try the help index for XIRR

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris Gorham" wrote in message
...
Hi,

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.

Thanks...Chris

Annuity Formula
 

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.

Annuity Formula
 

This cannot be done with a function. You would need to build a table for this.
The most likely difficulty you will run into is the compounding period. If
interest is compounded with every cash flow, the table is easy. Just add
interest on the running balance at each cash flow point.

If interest is compounded at regular intervals, rather than with each cash flow,
post back and we'll help you with the calculations.

--
Regards,
Fred


"Chris Gorham" wrote in message
...
Hi,

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.

Thanks...Chris