XIIR Calculation
Using VBA – EXCEL
If you are using VB itself, then you will need to either include the reference to MS Excel or Create it as an object and then run the above code with the declaration to excel.. e.g
Dim Result
Dim obEx As New Excel.Application ‘Early Binding
Result=obEx.WorksheetFunction.XIRR(Conditions)
obEx.Quit
Set obEx = Nothing
OR
Dim obEx2 as Object
Set obEx2 = CreateObject(“Excel.Application”)
Result=obEx2.WorksheetFunction.XIRR(Conditions)
obEx2.Quit
Set obEx2 = Nothing
Using VB.NET
URL : http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.financial.irr.aspx
http://msdn.microsoft.com/en-us/library/daksysx3.aspx
‘ Define money format.
Dim MoneyFmt As String = “###,##0.00″
‘ Define percentage format.
Dim PercentFmt As String = “#0.00″
Dim values(4) As Double
‘ Business start-up costs.
values(0) = -70000
‘ Positive cash flows reflecting income for four successive years.
values(1) = 22000
values(2) = 25000
values(3) = 28000
values(4) = 31000
‘ Use the IRR function to calculate the rate of return.
‘ Guess starts at 10 percent.
Dim Guess As Double = 0.1
‘ Calculate internal rate.
Dim CalcRetRate As Double = IRR(values, Guess) * 100
‘ Display internal return rate.
MsgBox(“The internal rate of return for these cash flows is ” &
Format(CalcRetRate, CStr(PercentFmt)) & ” percent.”)
Private Sub XIRRSample()
‘Print some samples of the XIRR function.
Dim Flow() As Variant
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = -10000#
Flow(2, 1) = #4/1/1984#
Flow(1, 2) = 50674#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = 10000#
Flow(2, 1) = #4/1/1984#
Flow(1, 2) = -50674#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
�
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = -10000#
Flow(2, 1) = #4/1/1989#
Flow(1, 2) = 20660#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = 10000#
Flow(2, 1) = #4/1/1989#
Flow(1, 2) = -20660#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = -10000#
Flow(2, 1) = #4/1/1993#
Flow(1, 2) = 10482#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 5)
Flow(1, 1) = -10000#
Flow(2, 1) = #1/1/1992#
Flow(1, 2) = 2750#
Flow(2, 2) = #3/1/1992#
Flow(1, 3) = 4250#
Flow(2, 3) = #10/30/1992#
Flow(1, 4) = 3250#
Flow(2, 4) = #2/15/1993#
Flow(1, 5) = 2750#
Flow(2, 5) = #4/1/1993#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 4)
Flow(1, 1) = -70000#
Flow(2, 1) = #1/1/1990#
Flow(1, 2) = 10000#
Flow(2, 2) = #1/1/1991#
Flow(1, 3) = 20000#
Flow(2, 3) = #1/1/1992#
Flow(1, 4) = 30000#
Flow(2, 4) = #1/1/1993#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = -10000#
Flow(2, 1) = #4/1/1984#
Flow(1, 2) = 10000#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = -10000#
Flow(2, 1) = #4/1/1984#
Flow(1, 2) = 0#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 2)
Flow(1, 1) = -10000#
Flow(2, 1) = #4/1/1984#
Flow(1, 2) = -100#
Flow(2, 2) = #4/1/1994#
GoSub One_XIRRSample
ReDim Flow(1 To 2, 1 To 512)
Flow(1, 1) = -694369.54
Flow(2, 1) = #10/22/1997#
Dim intCur As Integer
intCur = 2
Dim lngMon As Long
lngMon = 12 + 12 * 1997
Do While lngMon <= 7 + 12 * 2009
Dim bytMon As Byte
Dim intYr As Integer
bytMon = lngMon Mod 12
intYr = lngMon \ 12
If bytMon = 0 Then
bytMon = 12
intYr = intYr – 1
End If
Flow(1, intCur) = 8421.79
Flow(2, intCur) = DateSerial(intYr, bytMon, 25)
intCur = intCur + 1
lngMon = lngMon + 1&
Loop
ReDim Preserve Flow(1 To 2, 1 To intCur – 1)
GoSub One_XIRRSample
Exit Sub
One_XIRRSample:
Dim intCurFlow As Integer
For intCurFlow = LBound(Flow, 2) To UBound(Flow, 2)
Debug.Print Right$(Space$(3) & Format$(intCurFlow, “#,##0″), 3);
Debug.Print ” ” & Right$(Space$(20) & Format$(Flow(1, intCurFlow), “$#,##0.00″), 20);
Debug.Print ” ” & Format$(Flow(2, intCurFlow), “Short Date”);
Debug.Print
Next intCurFlow
Debug.Print “XIRR = ” & Format$(XIRR(Flow()) * 100#, “#,##0.000 000″) & “%”
Debug.Print
Return
End Sub
