Rate of Return for uneven cash flows - non-generic excel formula needed

**mrr2** · 06-09-2012, 12:09 PM

Hi guys, what is the formula (not XIRR since it doesn't allow all cash flows to be positive - at least one has to be negative to allow xirr to work)
to calculate the rate of return for a stream of uneven cash flows, both on a IRR basis and annual average rate of return:

thanks for your help:

3/1/2003 600
5/25/2003 600
12/1/2003 1000
5/5/2004 1500
6/7/2004 600
10/5/2004 600

**shg** · 06-09-2012, 01:07 PM

Rate of return on what? If the initial investment were zero, the return would be infinite. The negative value in XIRR is the initial investment.

**mrr2** · 06-09-2012, 01:22 PM

Thanks shg - the numbers are cash flows, i.e. uneven cash flows and unevenly timed - like inheriting a portfolio of fixed income securities with different maturity dates.
I dont think excel can do this with its pre-installed functions without a manual calculation - what do you think?
Thanks

**shg** · 06-09-2012, 01:27 PM

I think what you're asking makes no sense.

If a stranger starts sending you checks for some unknown reason, what's the rate of return?

**mrr2** · 06-09-2012, 01:36 PM

Sorry, was trying to frame it to make sense - here is an example

http://www.excelforum.com/excel-gene...-function.html

**shg** · 06-09-2012, 01:41 PM

And in that example, it points out that one of the values must be negative:

OK. Presumably at the end of the period, the revenues have grown to some final
figure. You would create a table of the revenues in one column, and the
corresponding date in another. The last entry would be the value the revenues
have grown to (as a negative number), and the valuation date.

**mrr2** · 06-09-2012, 01:45 PM

Right, hence my question - is there a way, not using the xirr function to do the same in a case were there are no negative values in the stream of cash flows.
I know this is reaching far into the imagination, as essentially there is not a correspondence to reality, but in the academic setting such problems are thrown out to us to ponder.
If not, that's fine - I will skip it and ask for relevance in real life. Thanks shg

**shg** · 06-09-2012, 01:47 PM

I can't imagine even a metaphysical context in which it makes sense.

**shg** · 06-09-2012, 02:19 PM

You could ask the question, "If the interest rate is (say) 3%, what investment on (say) 1/1/2003 makes the present value of the cash flow zero?" You could answer that question with Solver:

Please Login or Register  to view this content.

Set B9 to zero by changing B1.

There may a way to do that with another financial function in lieu of Solver, but I don't know it.

MoneyMaker · 06-12-2012, 12:31 AM

Originally Posted by mrr2

Thanks shg - the numbers are cash flows, i.e. uneven cash flows and unevenly timed - like inheriting a portfolio of fixed income securities with different maturity dates.
I dont think excel can do this with its pre-installed functions without a manual calculation - what do you think?
Thanks

So somebody did pay a price for the securities in the portfolio, they didn't come for free. What you are trying to find here is yield on debt.

You would need to know the price that was paid for each of the security and then to find it's yield

I have tools that find yield of individual securities see this YTM Calculator but would have to now investigate about finding yield on a portfolio of securities