# Problem with XIRR function returning #NUM!

1. ## Problem with XIRR function returning #NUM!

I am using XIRR to calculate the IRR on stock market investments.
Outflows are the result of 1) buying shares and 2) paying broker fees.
Inflows are the result of 1) Dividends and 2) selling shares. It works
most of the times, but in a particular case, it fails and returns #NUM!
(if called from VBA using application.run method, the function returns
"Error 2036").

Cash flow data fed to XIRR is as follows :

Flows : (\$6,138.49), \$40.00, \$4,817.98, \$40.00, (\$1,741.00), \$1,712.00
Dates : 24-Aug-2000, 2-Oct-2000, 22-Dec-2000, 3-Jan-2001,4-Jun-2005,
5-Jun-2005

However, if I change the last date from 5-jun-2005 to 6-jun-2005, XIRR
seems to work properly. Any clue anybody ?

Thanks in advance for support.

--
Francois_Provost

2. ## Re: Problem with XIRR function returning #NUM!

Try supplying a value for the last argument, Guess. Others have mentioned that
-0.1 usually works in all situations.

On Sun, 5 Jun 2005 16:33:56 +0100, Francois_Provost
<Francois_Provost.1q61x9@news.excelbanter.com> wrote:

>
>I am using XIRR to calculate the IRR on stock market investments.
>Outflows are the result of 1) buying shares and 2) paying broker fees.
>Inflows are the result of 1) Dividends and 2) selling shares. It works
>most of the times, but in a particular case, it fails and returns #NUM!
>(if called from VBA using application.run method, the function returns
>"Error 2036").
>
>Cash flow data fed to XIRR is as follows :
>
>Flows : (\$6,138.49), \$40.00, \$4,817.98, \$40.00, (\$1,741.00), \$1,712.00
>Dates : 24-Aug-2000, 2-Oct-2000, 22-Dec-2000, 3-Jan-2001,4-Jun-2005,
>5-Jun-2005
>
>However, if I change the last date from 5-jun-2005 to 6-jun-2005, XIRR
>seems to work properly. Any clue anybody ?
>
>Thanks in advance for support.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1