Forum Discussion

GregHertling's avatar
GregHertling
New Contributor III
3 years ago

IRR Business Rule

Hi, is there a standard IRR calculation that can be shared?

thanks,

Greg

Finance
  • chris_rothermel's avatar
    chris_rothermel
    2 years ago

    Thanks for posting this Jack and also thank you Andy Moore.  With your ideas I wrote this osXIRR function that uses a class called CashFlows that holds Amount and Date.

    'Amounts and Dates go in here in this object
Dim CashFlows As New List( Of CashFlowDates )

'Series of cash flows 
cashFlows.Add( New CashFlowDates( -1000, New DateTime(2000, 1, 1) ) )
cashFlows.Add( New CashFlowDates(  1200, New DateTime(2001, 1, 1) ) )

'--RESULT sent to error log
api.LogMessage( $"XIRR: {osXIRR(cashFlows)}" )

    It worked great.  In Excel my calculated annual IRR is 0.199402373269127 and the dotNet calc using the Microsoft library produces 0.199402373269121.  Both of which easily round to 20%.

    Here's the full code:

    Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine


Namespace OneStream.BusinessRule.Finance.osXIRR

	'------------------------------------------------------------------------------------------------------------
	'Reference Code: 		In Excel the XIRR function has two required inputs -- amounts and dates range
	'					
	'Description:			This class is a data structure
	'
	'Usage as a List:		Dim cashFlows = New List(Of OneStream.BusinessRule.Finance.MSXIRR.CashFlowDates )
	'						cashFlows.Add( New CashFlowDates( thisDataCell.CellAmount, New DateTime(thisYear, thisMonth, thisDay) ) ) )
	'
	'Assumptions:			We do not want a Dictionary because the XIRR function in Excel _can_ have multiple values for one date		
	'						
	'Created By:			Chris Rothermel
	'
	'Updated:				November 29, 2023
	'------------------------------------------------------------------------------------------------------------
	Public Class CashFlowDates
	   
		Public Property cfAmount As Double
	    Public Property cfDate As DateTime
		
		Public Sub New(ByVal amount As Double, ByVal thisDate As DateTime)
	        cfAmount = amount
	        cfDate = thisDate.Date
	    End Sub

	End Class	

	
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
							
				Select Case api.FunctionType
					
					 						
					Case Is = FinanceFunctionType.Calculate
												
					Case Is = FinanceFunctionType.CustomCalculate
						'================================================================================
				
						'Amounts and Dates go in here in this object
						Dim CashFlows As New List( Of CashFlowDates )
	
						'Series of cash flows 
						cashFlows.Add( New CashFlowDates( -1000, New DateTime(2000, 1, 1) ) )
						cashFlows.Add( New CashFlowDates(  1200, New DateTime(2001, 1, 1) ) )

						'--RESULT sent to error log
						api.LogMessage( $"XIRR: {osXIRR(cashFlows)}" )
						
						'================================================================================
				End Select

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
			
		End Function
		
	Public Function osXIRR(ByVal CashFlows As List(Of CashFlowDates), Optional ByVal guess As Double = 0.00001) As Double
	'------------------------------------------------------------------------------------------------------------
	'Reference Code: 	This mimics Excel's XIRR function by using the IRR function from Microsoft.Financial 
	'
	'Description:		The XIRR function is the extended internal rate of return that considers the cash flows, discount rates, and 
	'					the corresponding dates to measure the Return accurately.  XIRR is like IRR but uses dates.  This implementation
	'					uses Microsoft.Financial.IRR to compute XIRR by giving IRR a list of values by day which computes a daily rate.
	'					The daily rate is converted to an annual rate.
	'
	'					
	'Usage Example:				
	'					'Amounts and Dates go in here in this object
	'					Dim CashFlows As New List(Of CashFlowDates )
	'					
	'					'Populate the object with a series of cash flows and dates
	'					cashFlows.Add( New CashFlowDates( -1000, New DateTime(2000, 1, 1) ) )
	'					cashFlows.Add( New CashFlowDates(  1200, New DateTime(2001, 1, 1) ) )
	'
	'					osXIRR(cashFlows) 	
	'					
	'osXIRR Result:		0.199402373269121		-> Which Is about 20% 
	'
	'Versus Excel IRR:	0.199402373269127		Using the conversion from daily to annual (1+dailyIRR)^365-1 
	'
	'Versus Excel XIRR: 0.199402377929688 		'Yes, in Excel IRR And XIRR produce slightly different results
	'
	'
	'Parameters:
	'
	'	CashFlows:		This is a List of CashFlowDates which is the data structure for Amounts and Dates.  		
	'
	'	guess:			"In most cases you do not need to provide guess for the XIRR calculation. If omitted, guess is assumed to be 0.1 (10 percent)."
	'						-- Microsoft - https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d
	'					Pitfall -- That may be for XIRR, but for IRR with .1 we error out with "Arguments are not valid."  Use 0.00001
	'
	'Created By:			Chris Rothermel			on November 30 2023
	'
	'Updated:				
	'------------------------------------------------------------------------------------------------------------

		'Given the CashFlows we can find the start and end dates 
		'	https://learn.microsoft.com/en-us/answers/questions/186877/find-maximum-value-in-list-by-condition-linq-vb-ne
		Dim startDate As Date = (From cashFlow In CashFlows Order By cashFlow.cfDate).First.cfDate
		Dim endDate	As Date = (From cashFlow In CashFlows Order By cashFlow.cfDate).Last.cfDate 
		
		'And knowing the start and end dates we can build a daily array of values which we pad with zeros
		Dim daysAmtArray As Double() = Enumerable.Repeat( 0.0, DateDiff(DateInterval.Day, startDate, endDate) + 1).ToArray
			

		'Add the cfAmount for all the cashflows to the daysAmtArray
		For Each cashFlow In cashFlows
		
			'We start with zero so adding the value is good, and if there are multiple values for repeating dates we sum them up for IRR
			daysAmtArray( DateDiff(DateInterval.Day, startDate, cashFlow.cfDate) ) = daysAmtArray( DateDiff(DateInterval.Day, startDate, cashFlow.cfDate) ) + cashFlow.cfAmount			
			
		Next	
	
		'We use the Try command in case the IRR function aborts, if it does abend we return no results but do not otherwise halt processing.
		Try 
			
			'Now we're ready to use Microsoft's IRR function which is normally given annual amounts, not daily amounts.
			'	To convert the daily amounts to annual amounts we compute it
			Return Math.Pow((1 + Financial.IRR( daysAmtArray, guess)), 365) -1
		
		Catch ex As Exception
			Return Nothing
			
		End Try
		
	End Function
	
End Class

End Namespace

     

     

     

     

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    There is an IRR method in the MS standard Financial class. This method accepts constant time spans, but you can also generate a list of payments that is daily. Every day will be 0 except for when there is a payment. Simple example:

     

    ' add this at the top to make the function available 
Imports Microsoft.VisualBasic.Financial
....

Dim flowPayments as new List(of Double)
flowPayments.add(0)      ' day 1
flowPayments.add(342.3)  ' day 2
' ... etc etc


Dim dailyIrr  As Double = Financial.IRR(flowPayments.ToArray(), 0.00000001)
Dim yearlyIrr As Double = Math.Pow((1 + dailyIrr),365)-1

     

     

     

    • chris_rothermel's avatar
      chris_rothermel
      Contributor
      2 years ago

      Thanks for posting this Jack and also thank you Andy Moore.  With your ideas I wrote this osXIRR function that uses a class called CashFlows that holds Amount and Date.

      'Amounts and Dates go in here in this object
Dim CashFlows As New List( Of CashFlowDates )

'Series of cash flows 
cashFlows.Add( New CashFlowDates( -1000, New DateTime(2000, 1, 1) ) )
cashFlows.Add( New CashFlowDates(  1200, New DateTime(2001, 1, 1) ) )

'--RESULT sent to error log
api.LogMessage( $"XIRR: {osXIRR(cashFlows)}" )

      It worked great.  In Excel my calculated annual IRR is 0.199402373269127 and the dotNet calc using the Microsoft library produces 0.199402373269121.  Both of which easily round to 20%.

      Here's the full code:

      Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine


Namespace OneStream.BusinessRule.Finance.osXIRR

	'------------------------------------------------------------------------------------------------------------
	'Reference Code: 		In Excel the XIRR function has two required inputs -- amounts and dates range
	'					
	'Description:			This class is a data structure
	'
	'Usage as a List:		Dim cashFlows = New List(Of OneStream.BusinessRule.Finance.MSXIRR.CashFlowDates )
	'						cashFlows.Add( New CashFlowDates( thisDataCell.CellAmount, New DateTime(thisYear, thisMonth, thisDay) ) ) )
	'
	'Assumptions:			We do not want a Dictionary because the XIRR function in Excel _can_ have multiple values for one date		
	'						
	'Created By:			Chris Rothermel
	'
	'Updated:				November 29, 2023
	'------------------------------------------------------------------------------------------------------------
	Public Class CashFlowDates
	   
		Public Property cfAmount As Double
	    Public Property cfDate As DateTime
		
		Public Sub New(ByVal amount As Double, ByVal thisDate As DateTime)
	        cfAmount = amount
	        cfDate = thisDate.Date
	    End Sub

	End Class	

	
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
							
				Select Case api.FunctionType
					
					 						
					Case Is = FinanceFunctionType.Calculate
												
					Case Is = FinanceFunctionType.CustomCalculate
						'================================================================================
				
						'Amounts and Dates go in here in this object
						Dim CashFlows As New List( Of CashFlowDates )
	
						'Series of cash flows 
						cashFlows.Add( New CashFlowDates( -1000, New DateTime(2000, 1, 1) ) )
						cashFlows.Add( New CashFlowDates(  1200, New DateTime(2001, 1, 1) ) )

						'--RESULT sent to error log
						api.LogMessage( $"XIRR: {osXIRR(cashFlows)}" )
						
						'================================================================================
				End Select

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
			
		End Function
		
	Public Function osXIRR(ByVal CashFlows As List(Of CashFlowDates), Optional ByVal guess As Double = 0.00001) As Double
	'------------------------------------------------------------------------------------------------------------
	'Reference Code: 	This mimics Excel's XIRR function by using the IRR function from Microsoft.Financial 
	'
	'Description:		The XIRR function is the extended internal rate of return that considers the cash flows, discount rates, and 
	'					the corresponding dates to measure the Return accurately.  XIRR is like IRR but uses dates.  This implementation
	'					uses Microsoft.Financial.IRR to compute XIRR by giving IRR a list of values by day which computes a daily rate.
	'					The daily rate is converted to an annual rate.
	'
	'					
	'Usage Example:				
	'					'Amounts and Dates go in here in this object
	'					Dim CashFlows As New List(Of CashFlowDates )
	'					
	'					'Populate the object with a series of cash flows and dates
	'					cashFlows.Add( New CashFlowDates( -1000, New DateTime(2000, 1, 1) ) )
	'					cashFlows.Add( New CashFlowDates(  1200, New DateTime(2001, 1, 1) ) )
	'
	'					osXIRR(cashFlows) 	
	'					
	'osXIRR Result:		0.199402373269121		-> Which Is about 20% 
	'
	'Versus Excel IRR:	0.199402373269127		Using the conversion from daily to annual (1+dailyIRR)^365-1 
	'
	'Versus Excel XIRR: 0.199402377929688 		'Yes, in Excel IRR And XIRR produce slightly different results
	'
	'
	'Parameters:
	'
	'	CashFlows:		This is a List of CashFlowDates which is the data structure for Amounts and Dates.  		
	'
	'	guess:			"In most cases you do not need to provide guess for the XIRR calculation. If omitted, guess is assumed to be 0.1 (10 percent)."
	'						-- Microsoft - https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d
	'					Pitfall -- That may be for XIRR, but for IRR with .1 we error out with "Arguments are not valid."  Use 0.00001
	'
	'Created By:			Chris Rothermel			on November 30 2023
	'
	'Updated:				
	'------------------------------------------------------------------------------------------------------------

		'Given the CashFlows we can find the start and end dates 
		'	https://learn.microsoft.com/en-us/answers/questions/186877/find-maximum-value-in-list-by-condition-linq-vb-ne
		Dim startDate As Date = (From cashFlow In CashFlows Order By cashFlow.cfDate).First.cfDate
		Dim endDate	As Date = (From cashFlow In CashFlows Order By cashFlow.cfDate).Last.cfDate 
		
		'And knowing the start and end dates we can build a daily array of values which we pad with zeros
		Dim daysAmtArray As Double() = Enumerable.Repeat( 0.0, DateDiff(DateInterval.Day, startDate, endDate) + 1).ToArray
			

		'Add the cfAmount for all the cashflows to the daysAmtArray
		For Each cashFlow In cashFlows
		
			'We start with zero so adding the value is good, and if there are multiple values for repeating dates we sum them up for IRR
			daysAmtArray( DateDiff(DateInterval.Day, startDate, cashFlow.cfDate) ) = daysAmtArray( DateDiff(DateInterval.Day, startDate, cashFlow.cfDate) ) + cashFlow.cfAmount			
			
		Next	
	
		'We use the Try command in case the IRR function aborts, if it does abend we return no results but do not otherwise halt processing.
		Try 
			
			'Now we're ready to use Microsoft's IRR function which is normally given annual amounts, not daily amounts.
			'	To convert the daily amounts to annual amounts we compute it
			Return Math.Pow((1 + Financial.IRR( daysAmtArray, guess)), 365) -1
		
		Catch ex As Exception
			Return Nothing
			
		End Try
		
	End Function
	
End Class

End Namespace