I want the return % from uneven annual cash contributions over time (8
contributions in 22 years) that has resulted in a current value that is
greater than the contributions.

terry wrote:
> I want the return % from uneven annual cash contributions over time (8
> contributions in 22 years) that has resulted in a current value that is
> greater than the contributions.

I infer that not only are the contributions themselves uneven, but also
the frequency of the contributions is irregular. In that case, XIRR
might be solution you are looking for.

If you have Excel 2003 (and perhaps some earlier revisions) and you
installed the Analysis ToolPak add-in, look at the Help page for XIRR.
If you have trouble adapting that function to your data, post back here
with details.

PS: If the frequency of the uneven contributions are regular -- or if
they can be made to seem regular, e.g. by assigning zero cash flows to
non-contribution periods -- you might want to look at IRR. In Excel
2003 (at least), that is a normal worksheet function, not an add-in.

I have IRR but not XIRR. IRR does not ask for the ending value of the cash
inputs, so it is not possible to determine the return. I know the value
today and I know the cash inputs over the 22 years--seems like there should
be a formula to determine return but I can't find it in Excel.

"(E-Mail Removed)" wrote:

> terry wrote:
> > I want the return % from uneven annual cash contributions over time (8
> > contributions in 22 years) that has resulted in a current value that is
> > greater than the contributions.
>
> I infer that not only are the contributions themselves uneven, but also
> the frequency of the contributions is irregular. In that case, XIRR
> might be solution you are looking for.
>
> If you have Excel 2003 (and perhaps some earlier revisions) and you
> installed the Analysis ToolPak add-in, look at the Help page for XIRR.
> If you have trouble adapting that function to your data, post back here
> with details.
>
> PS: If the frequency of the uneven contributions are regular -- or if
> they can be made to seem regular, e.g. by assigning zero cash flows to
> non-contribution periods -- you might want to look at IRR. In Excel
> 2003 (at least), that is a normal worksheet function, not an add-in.
>
>

terry wrote:
> IRR does not ask for the ending value of the cash
> inputs, so it is not possible to determine the return.

I do not understand what you mean by that. I think you have a
misunderstanding about how to use IRR. IRR wants the cash flows, and
the "ending value" is one cash flow.

> I know the value today and I know the cash inputs over the
> 22 years--seems like there should be a formula to determine
> return but I can't find it in Excel.

I suggest that you post the numbers or a hypothetical example.

Suppose you invested varying amounts each year for 21 years, and you
cash-out or you know the ending value after 22 years. To use IRR, you
would model this as 21 negative cash flows and 1 positive cash flow,
the ending value. Then ostensibly, the IRR is IRR(A1:A22). (But sigh,
you might need to add the "guess" parameter to work around Excel's
self-limited implementation.)