XIRR Question

G

geffen5

New Member
Joined
Aug 23, 2006
Messages
10
I want to compute the IRR of a series of payments and a terminal value. The payments and terminal value are the results of other calculations. The cells containing these values are not adjacent. Below is what i am doealing with. I want the cash outlfows to be a part of all calculations, but then calculate the irr based on the Multiple of Net Revenue exit value (at 1/1/10).

Any help is greatly appreciated


Cash Outflows Multiple of Net Revenue
1/1/2007 1/1/2009 3.0x 4.0x 5.0x

$(9,000) $(1,105) $12,633 $16,053 $19,474
(9,000) (1,013) 12,330 15,466 18,601
(9,000) (935) 12,074 14,968 17,862
(9,000) (868) 11,855 14,542 17,229
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
IRR can handle discontiguous ranges, but XIRR cannot. You will have to use VBA.

Suppose that your outflows and corresponding dates were in B1:B2 and A1:A2, and that your terminal value/date was in E12 and D12.

To get an answer, take these steps.

1. In the VBE, set a reference to the Analysis ToolPak
Tools>References> check atpvbaen.xls

2. Copy the following into a code module
Code: 
Function MyXIRR(Values, Dates)
Dim i As Long, arrVals, arrDts, GuessNum As Double


    If Values.Count <> Dates.Count Then
        MyXIRR = CVErr(xlErrNum)
        Exit Function
    End If
    
    If Values.Count = 1 Then
        MyXIRR = CVErr(xlErrNA)
        Exit Function
    End If

ReDim arrVals(1 To Values.Count) As Double
ReDim arrDts(1 To Dates.Count) As Date
    
    For i = 1 To Values.Count
        arrVals(i) = CDbl(Values(i))
        arrDts(i) = CDate(Dates(i))
    Next i
    
    GuessNum = Sgn(Application.Sum(arrVals)) / 10
    
    MyXIRR = xirr(arrVals, arrDts, GuessNum)
End Function

3. Call the function in your worksheet like this:
=MyXIRR((B1:B2,E12),(A1:A2,D12))

Note the parentheses separating the arguments.
 
Upvote 0
Bob - it looks like this would work, but I had problems with it (on XL 2003 - I haven't tried it on XL2K).

If a range has more than one area then rgRange(XX) will not refer to the 'XXth' cell in the defined range if XX is greater than the number of cells in the first area. I think you would have to load the values and dates into the two arrays by specifying the areas in the supplied ranges, not just the cells in sequence. This seems to work:
Code: 
Function MyXIRR(Values As Range, Dates As Range)
Dim i          As Long
Dim iArea      As Long
Dim iCell      As Long
Dim arrVals()  As Double
Dim arrDts()   As Date
Dim GuessNum   As Double

If Values.Count <> Dates.Count Then
    MyXIRR = CVErr(xlErrNum)
    Exit Function
End If

If Values.Count = 1 Then
    MyXIRR = CVErr(xlErrNA)
    Exit Function
End If

ReDim arrVals(1 To Values.Count)
ReDim arrDts(1 To Dates.Count)

i = 1

For iArea = 1 To Values.Areas.Count
    For iCell = 1 To Values.Areas(iArea).Count
        arrVals(i) = CDbl(Values.Areas(iArea).Cells(iCell))
        arrDts(i) = CDate(Dates.Areas(iArea).Cells(iCell))
        i = i + 1
    Next iCell
Next iArea

GuessNum = Sgn(Application.Sum(arrVals)) / 10

MyXIRR = xirr(arrVals, arrDts, GuessNum)
End Function

When testing by calling the function and supplying range arguments to it, it produces an "Error 2036" return, which seems to indicate a type-conversion problem somewhere, but on the s/sheet it produces the same results as the XIRR function applied to a continuous range. This, in turn, seems to indicate that I don't know enough about error codes, I guess...
 
Upvote 0
Thanks for the enhancement. I did not think to check that situation.

My light testing seems to indicate that your UDF is working correctly.
 
Upvote 0
Well, it was kind of funny - I had tried to use XNPV on a discontinuous range about three weeks ago, concluded that it couldn't be done, and re-arranged my worksheet to make the required range(s - one for date, one for value) continuous. It wasn't a big deal - I only had to insert / delete a couple of rows - but the timing was just a little bit off for this to have been quite useful!

Any idea on the "Error 2036" return? - although the function works, I don't know how that is cropping up, or what effect it might have...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,650
Messages
6,120,734
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top