installing XIRR

[jIM]

I do not have XIRR function to calculate IRR for my investments. I
only deposit, do not withdraw, can someone help me with formula or
installing XIRR.

The formula others use:

([old value-.5* deposits]/[end value+.5*deposits])-1

this worked well when combining 6 accounts (two 401k, 2 Roth IRA, 2
Rollover IRA), => 4.7% IRR, but did not do well with each sub account
(no account had less than a 30% IRR).

suggestions?

 

[Jim Cone]

Try typing XIRR into the help box at the top right corner of the
Excel window.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"jIM" <[email protected]>
wrote in message
I do not have XIRR function to calculate IRR for my investments. I
only deposit, do not withdraw, can someone help me with formula or
installing XIRR.

The formula others use:
([old value-.5* deposits]/[end value+.5*deposits])-1
this worked well when combining 6 accounts (two 401k, 2 Roth IRA, 2
Rollover IRA), => 4.7% IRR, but did not do well with each sub account
(no account had less than a 30% IRR).
suggestions?

 

[David Biddulph]

Did you try typing XIRR into Excel help, and following the instructions
there?

 

[joeu2004]

I do not have XIRR function to calculate IRR for my investments.  I
only deposit, do not withdraw, can someone help me with formula
or installing XIRR.

XIRR is part of the Analysis Toolpak, at least for Excel 2003. It
would be on your Excel installation disk.

But if you do not make withdrawals, XIRR is overkill, and using it
might only confuse you.

The formula others use:
([old value-.5* deposits]/[end value+.5*deposits])-1

this worked well when combining 6 accounts

Off-hand, it is not obvious to me why this formula works. It might
only be an approximation, perhaps based on a "continuous compounding"
formula.

No matter. It is not that complicated. The IRR, aka CAGR, is simply:

(endValue / begValue) ^ (1 / nPeriods) - 1

The simply "^" means "to the power of". That formula assumes that
"nPeriods" is in years. "1 / nPeriods" could also be written
"periodsPerYear / nPeriods", where "nPeriods" is some number of sub-
year units (e.g. days, weeks, months, quarters).

Alternatively, you could use one of the following function-based
formula:

=rate(nPeriods, 0, -begValue, endValue)

=rate(periodsPerYear / nPeriods, 0, -begValue, endValue)

Caveat: Many financial professional compute the IRR (not the CAGR) as
follows:

=periodsPerYear * rate(nPeriods, 0, -begValue, endValue)

HTH.

 

[Pete_UK]

If you look in Excel Help for XIRR you will see:

"...
If this function is not available, run the Setup program to install
the Analysis ToolPak. After you install the Analysis ToolPak, you must
enable it by using the Add-Ins command on the Tools menu.
..."

And you can click on How? below this message to get full details of
how to go about this.

Hope this helps.

Pete