software

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 quot;present
valuequot; and quot;present datequot; 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 quot;present valuequot; and quot;present datequot;.

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

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?

--
Regards,
Fredquot;tloanoquot; lt;u21587@uwegt; wrote in message news:5fdb0f71acb4d@uwe...
gt; In attempt to create an editable database for an investment account utilizing
gt; XIRR, I found that I only know how to get XIRR to work if I put the quot;present
gt; valuequot; and quot;present datequot; in the same columns, or rows, as the other data
gt; (previous invested amounts and their dates). I understand I can indiviually
gt; enter the actual values into the XIRR but that would take forever.
gt; I want to have a separate collumn for quot;present valuequot; and quot;present datequot;.
gt;
gt; How do I write XIRR for the following?
gt; A B C D
gt; 1 Invested Date Pres Val Pres Date
gt; 2 $99 01/02/04 XIRR 03/05/04
gt; 3 $125 11/06/05 XIRR 01/19/06
gt; 4 $57 03/19/06 XIRR 03/20/06
gt;
gt; Thanks
gt; tloano
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 quot;XIRRquot;
3 $125 11/06/05 01/19/06 $227 quot;XIRRquot;
4 $57 03/19/06 03/20/06 $301 quot;XIRRquot;

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 quot;Value at Time
of Evaluationquot; and quot;Evaluation Datequot; directly beneath the quot;Amount Investedquot;
and quot;Date of Buyquot; 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.
StevenFred Smith wrote:
gt;What does 'XIRR' in column C represent? Using row 2 as an example, you invested
gt;$99 on 1/2/04, it's worth, say, $120 on 3/5/04, and you want to calculate the
gt;rate of return, in say, column E? If so, the Rate function will work better than
gt;XIRR, as in:
gt;
gt;=rate((d2-b2)/365,0,a2,-c2)
gt;
gt;Does this help?
gt;
gt;gt; In attempt to create an editable database for an investment account utilizing
gt;gt; XIRR, I found that I only know how to get XIRR to work if I put the quot;present
gt;[quoted text clipped - 12 lines]
gt;gt; Thanks
gt;gt; tloano

--
Message posted via www.officekb.com