In article <AUaYb.24574$h91.1293[at]newssvr27.news.prodigy.com> , Tad Borek wrote:
- quote -

Thanks Tad, this is an excellent suggestion.

i

Tad Borek <borekfm[at]pacbell.net> writes:

- quote -

I believe the module you need to load is called
the Analysis Toolpak.

--
Rich Carreiro rlcarr[at]animato.arlington.ma.us

Ignoramus17304 wrote:

- quote -

You're right, the math-by-hand route is a bear. There's a cash-flow
function built into Excel that can do this for you though.
The function is XIRR() which handles cash flows at irregular intervals.
It isn't loaded with the basic install, you might need to add in the
function set (see the Help file in Excel).

All you need to consider are your deposits and withdrawals, and the
ending account value. The dividends happen "internally" so won't effect
the rate of return calculation. Or rather, you don't really need to
consider them unless you got them out of the account, in which case
you'd treat them as a withdrawal of cash from the system.


Set up a spreadsheet:

Col-A Col-B
Date 1 first deposit (or starting account value)
Date 2 deposit/wdl
Date 3 deposit/wdl
....
Date N "withdrawal" (final value)

Signs are important here...all deposits are negative, all withdrawals
are positive. You can reverse col A - B, I think it's more intuitive to
put the date first.

Then create a cell with the cell references for the ranges:
=XIRR(cashflow1:cashflowN, date1:dateN)
and the calculated result will be your internal rate of return (IRR)
over that date range. Which is one, though not the only, way of
measuring the return on your account.

If you want to check IRR for intermediate periods, you would just insert
a "final value" equal to the total account value on a given date, and
reset your cell ranges. Ditto if you want to begin at something other
than the account-opening date; the first "deposit" is simply the initial
account value.

Just keep track of the sign convention - it follows the standard finance
convention - money going INTO the account is negative, money coming OUT
is positive, including your final value (essentially you act as if you
withdrew the entire account on the last date, and see what rate of
return was necessary to provide that series of cash flows).

-Tad

I have a brokerage account. During the last several years, I funded
the account a few times, received dividends, etc etc. I would like to
know how I can calculate my return over a given period. I think that I
understand the math behind it, but actually doing the calculation
*right* is quite difficult. So, I would prefer to find some piece of
software that could actually get my comma separated/excel log of all
starting positions, historical prices, my contributions, dividends
paid etc, and calculate return.

i