Computing Cmpd Return on Irregular Investments

[Ron]

Im am working in Excel 2002. I have made irregular
investments in my stock portfolio over the years and the
portfolio itself has changed in that time. Is there a way
to compute the return on each investment amount given the
total current value of the portfolio. For example,
investments made as follows:

Contribution #1 on 3/4/99: $10,000
Contribution #2 on 6/5/01: $5,000
Contribution #3 on 9/10/02: $15,000

Total value of portfolio at 3/10/04 $45,000

Is there a way to compute the compound annual return on
each of the three contributions?

 

[Norman Harker]

Hi Ron!

In A1:B4 I have:

4-Mar-99 -10000
5-Jun-01 -5000
10-Sep-02 -15000
10-Mar-04 45000


In A6 I have:

=XIRR(B1:B4,A1:A4)
Returns: 14.1998463869095%

I'm assuming all your dates were in US notation mm/dd/yy. I've also
assumed that there have been no income distributions.

XIRR:
Returns the annual effective interest rate for a schedule of cash
flows received at specified dates
Syntax:
=XIRR(values,dates,guess)

To computer the return on each contribution, as opposed to the return
on the combined investment, you need to provide more data. In essence
I need a method of apportioning the terminal value of the portfolio.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.