Excel Return Function

nxdmsandkaskdjaqd

Hi

There's a function within excel that enables you to calculate the return on your investments over time and takes into account when additional monies are added to your pot.

Could someone let me know the fuction name please and maybe how to use.

Aegis

It's the XIRR function. You create a 2-column table of data with dates in one column and net inflows to the portfolio in another. To ensure that the function knows the end value, you need to assume that the entire current value is sold and transferred out on the last date, resulting in a negative value in the last column. After that you use the following syntax:

=XIRR(Values,Dates[,Estimate])

The last bit is usually unnecessary, but if an error message comes up it can sometimes be fixed by putting a small positive or negative value in as the estimate.

Hope that helps.

nxdmsandkaskdjaqd

I have used the example quoted in excel help, but the formular:

=XIRR(A2:A6,B2:B6,0.1)

Returns #VALUE!

The formular is selecting the right cols and rows, so that's right.

Anyone able to help?

SnowMan

Are you using excel 2003?

You may find that the XIRR function is not installed in your version of excel. Is it in the list of functions?

If you go to the on-line help in excel and type in XIRR you will be able to see the help for the function. You may need to load in the Analysis ToolPak. The help should tell you how to do that.

Lokolo

nxdmsandkaskdjaqd wrote: »

I have used the example quoted in excel help, but the formular:

=XIRR(A2:A6,B2:B6,0.1)

Returns #VALUE!

The formular is selecting the right cols and rows, so that's right.

Anyone able to help?

Make sure the cell is big enough to include all the numbers. If you want to display £100023030303030303030303 in a cell thats 2cm, it won't, it will just display #value.

nxdmsandkaskdjaqd

I just found the problem.

I copied the example in excel help. The dates were formatted January 1, 2008.

When I converted to 01/02/2012 it worked.

Thanks

nxdmsandkaskdjaqd

Just want to delete the last message as I managed to sort.