IRR Business Rule
Hi, is there a standard IRR calculation that can be shared?
thanks,
Greg
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
