XIRR non contiguous references

[tloano]

In attempt to create an editable database for an investment account utilizing
XIRR, I found that I only know how to get XIRR to work if I put the "present
value" and "present date" in the same columns, or rows, as the other data
(previous invested amounts and their dates). I understand I can indiviually
enter the actual values into the XIRR but that would take forever.
I want to have a separate collumn for "present value" and "present date".

How do I write XIRR for the following?
A B C D
1 Invested Date Pres Val Pres Date
2 $99 01/02/04 XIRR 03/05/04
3 $125 11/06/05 XIRR 01/19/06
4 $57 03/19/06 XIRR 03/20/06

Thanks
tloano

 

[Fred Smith]

What does 'XIRR' in column C represent? Using row 2 as an example, you invested
$99 on 1/2/04, it's worth, say, $120 on 3/5/04, and you want to calculate the
rate of return, in say, column E? If so, the Rate function will work better than
XIRR, as in:

=rate((d2-b2)/365,0,a2,-c2)

Does this help?

 

[tloano via OfficeKB.com]

Fred, Thanks for the quick response.
Sorry I didn't clarify very well. Improving the table a bit, I want to
calculate the XIRR in Column E for the value at the time of evaluation of the
entire account at the point in time in column D. All of these buys are made
in the same account such as buys of a mutual fund at different times.
How do I write XIRR for the following?
A B C D E
Value at
1 Amount Date of Evaluation Time of
Invested Buy Date Evaluation XIRR
2 $99 01/02/04 03/05/04 $103 "XIRR"
3 $125 11/06/05 01/19/06 $227 "XIRR"
4 $57 03/19/06 03/20/06 $301 "XIRR"

Lets say that the date is 01/19/06 (C3), therefore row D isn't filled in yet.
I want to calculate the XIRR for my two investements(A2 and A3...total $224)
which now (01/19/06) are worth $227. The result will go into cell E3. I'm
having trouble filling in the XIRR formula properly to include everything. I
want to be able to do this so that I can look back at past XIRRs (annualized
rates of return) to see how performance has changed. I only know two ways to
write the XIRR formula. One requires inputing all of the values for each
cell into the formula (no way Jose), the other is by reference (ex: XIRR(a2:
a4,b2:b4). By reference is great but it requires putting the "Value at Time
of Evaluation" and "Evaluation Date" directly beneath the "Amount Invested"
and "Date of Buy" columns. I want to be able to update the database without
rearranging formulas or re-entering results. As it is now, I don't know how
to make the above table work. I don't know how to properly reference the
values which are in a different column. I've tried several things and dug
around but havn't found answer.
Thanks for your help.
Steven

What does 'XIRR' in column C represent? Using row 2 as an example, you invested
$99 on 1/2/04, it's worth, say, $120 on 3/5/04, and you want to calculate the
rate of return, in say, column E? If so, the Rate function will work better than
XIRR, as in:

=rate((d2-b2)/365,0,a2,-c2)

Does this help?

In attempt to create an editable database for an investment account utilizing
XIRR, I found that I only know how to get XIRR to work if I put the "present

[quoted text clipped - 12 lines]

Thanks
tloano