פונקציית XIRR לחישוב שיעור תשואה פנימי (שת"פ)

IRR = Internal Rate Of Return (ובעברית- שת"פ) הוא מדד מקובל לחישוב כדאיות פרוייקט.
פרוייקט כלכלי טיפוסי מורכב מהשקעות גדולות בתחילת הדרך וזרם הכנסות בעתיד, ואנחנו מחפשים את שער הריבית התיאורטי הגלום בפרוייקט; כלומר- אם הפרוייקט לא היה אלא תוכנית חסכון בה מפקידים הרבה כסף בהתחלה ונהנים מהכנסות בהמשך – מה היה שער הריבית של אותה תוכנית חסכון שאינה אלא בת דמותו של הפרוייקט הנ"ל.
טכנית הכוונה לאותו שער ריבית שעבורו הענ"נ (הערך הנוכחי הנקי, NPV = Net Present Value) הוא 0.
משחישבנו את השת"פ – עלינו להשוות אותו לריבית במשק: אם הוא גבוה יותר אזי ההשקעה כדאית (ביחס לאלטרנטיבה), ואם לא- ההשקעה אינה כדאית ועדיף לחסוך בריבית שבמשק ולהרוויח יותר.

אם בפרוייקט לדוגמה יש השקעה של 100 ש"ח בהווה וארבע הכנסות של 30 ש"ח בכל אחת מהשנים הבאות, אזי אנחנו מחפשים R שעבורו מתקיים:

0=-100+30/(1+R)+30/(1+R)²+30/(1+R)³+30/(1+R)⁴

חדי העין ודאי שמו לב שזה פולינום, כלומר- אם נציב (X=1/(1+R נקבל משוואה מהסוג של:

0=-100+30X+30X²+30X³+30X⁴

ולאחר שנפתור אותה ונמצא את X – נוכל מייד לחשב את R.
לפתור משוואות מהסוג הזה בדרך אנליטית (על ידי העברת אגפים, מכנים משותפים, שימוש בנוסחאות וכו') – קשה ואף בלתי אפשרי, ולכן יש לכתוב תוכנית מחשב שתחפש פתרון מקורב שיהיה מספיק מדוייק לצרכינו. יש מספר אלגוריתמים לכך, ובחרתי בשיטת המיתר שאמורה להיות מספיק יעילה:
1. אתחיל עם שתי נקודות: 0.10, 0.10- (שני שערי ריבית סבירים בתור ניחוש התחלתי)
2. אחשב את הענ"נ לפי כל אחת מהנקודות (כלומר – את ערך צד ימין של המשוואה הראשונה)
3. אחשב את נקודת החיתוך עם הציר האופקי של הקו המחבר את שתי הנקודות (בדרך כלל מסומן בתור X אך במקרה שלנו – R)
4. אחזור לצעד 2 עם נקודת החיתוך של המיתר עם הציר ועם R הקודם שהענ"נ שלו היה קרוב יותר לאפס, ואמשיך כך עד שאמצא R שיחזיר ענ"נ מספיק קרוב ל-0 (נניח – סטייה בספרה התשיעית או העשירית מימין לנקודה).

ניצור קודם כל טבלה, ובה סדרות ערכים לשישה פרוייקטים שונים (כולל כמה מקרי קצה שאמורים להחזיר שגיאות):

IF Object_Id('T_Proj','U') Is Not Null Drop Table T_Proj

Go

Create Table T_Proj(Proj Int Not Null,

                    Taarih DateTime Not Null,

                    Kesef Float Not Null);

Go

Insert Into T_Proj Select 1,'20050101',-100;

Insert Into T_Proj Select 1,'20050113',25;

Insert Into T_Proj Select 1,'20050910',25;

Insert Into T_Proj Select 1,'20050920',25;

Insert Into T_Proj Select 1,'20051020',25;

Insert Into T_Proj Select 1,'20080716',25;

Go

Insert Into T_Proj Select 2,'20070415',-500;

Insert Into T_Proj Select 2,'20070724',100;

Insert Into T_Proj Select 2,'20070813',200;

Insert Into T_Proj Select 2,'20080812',300;

Go

Insert Into T_Proj Select 3,'20050101',-100;

Insert Into T_Proj Select 3,'20050113',25;

Insert Into T_Proj Select 3,'20050910',25;

Insert Into T_Proj Select 3,'20050920',-5;

Insert Into T_Proj Select 3,'20051020',25;

Insert Into T_Proj Select 3,'20080716',25;

Go

Insert Into T_Proj Select 4,'20080812',-300;

Go

Insert Into T_Proj Select 5,'20080812',-300;

Insert Into T_Proj Select 5,'20080912',-100;

Go

Insert Into T_Proj Select 6,'20050101',-100;

Insert Into T_Proj Select 6,'20050113',110;

Insert Into T_Proj Select 6,'20050910',-120;

Insert Into T_Proj Select 6,'20050920',130;

Insert Into T_Proj Select 6,'20051020',-140;

Insert Into T_Proj Select 6,'20080716',150;

Go

והפונקציה עצמה:

Create Function dbo.XIRR(@Proj Int)

Returns Float

As

Begin

Declare @Mn Float;

Select    @Mn=0.0001;

Declare    @I Int;

Declare    @R1 Float,

        @R2 Float,

        @R  Float,

        @R1_ Float,

        @R2_ Float;

Select    @R1=-0.10,

        @R2= 0.10,

        @R=0;

Declare    @T DateTime;

Select    @T=(Select Min(Taarih) From T_Proj Where Proj=@Proj);

Declare    @S1 Float,

        @S2 Float;

Select    @S1=(Select    Sum(Kesef/Power((1.+@R1),Cast(DateDiff(D,@T,Taarih) As Float)/365))

            From    T_Proj

            Where    Proj=@Proj),

        @S2=(Select    Sum(Kesef/Power((1.+@R2),Cast(DateDiff(D,@T,Taarih) As Float)/365))

            From    T_Proj

            Where    Proj=@Proj);

Select    @I=1;

While Abs(@S1-@S2)>@Mn And @S1<>@S2 And @I<100 And @T Is Not Null

        Begin

        Select    @I=@I+1;

        Select    @R=@R1-@S1*(@R1-@R2)/(@S1-@S2);

        Select    @R=Case When @R<=-1 Then -0.999 Else @R End; 

        Select    @R1_=Case When Abs(@S1)>Abs(@S2) Then @R2

                        Else @R

                        End,

                @R2_=Case When Abs(@S1)>Abs(@S2) Then @R

                        Else @R1

                        End;

        Select    @R1=@R1_,

                @R2=@R2_;

        Select    @S1=(Select    Sum(Kesef/Power((1.+@R1),Cast(DateDiff(D,@T,Taarih) As Float)/365))

                    From    T_Proj

                    Where    Proj=@Proj),

                @S2=(Select    Sum(Kesef/Power((1.+@R2),Cast(DateDiff(D,@T,Taarih) As Float)/365))

                    From    T_Proj

                    Where    Proj=@Proj);

        End;

Return    Case When @I>=100 Then Null

            When @T Is Null Then Null

            When Abs(@S1)>@Mn And Abs(@S2)>@Mn Then Null

            When ABS(@S1)<ABS(@S2) Then @R1

            Else @R2

            End

End;

המשתנה Mn@  יהיו קבוע קטן מאוד כדי לקבוע את נקודת העצירה ולסנן ערכים לא הגיוניים (ניתן לדייק יותר במגבלות האפשר עם המשתנה, או פחות – אם רוצים לשפר מעט את הביצועים).

המשתנים R1@ ו-R2@ הם שני ערכי הריבית שנבדקים בכל שלב, ונוספו להם מספר משתני עזר.

I@ מונה כמה איטרציות כבר התבצעו ועוצר לאחר מאה כדע למנוע לולאה אינסופית (בבעייה טיפוסית – מתכנסים לפתרון תוך פחות מעשר איטרציות).

מכיוון שהפרשי התאריכים אינם בשנים שלמות – יש לחשב את ההפרש בימים ולחלק ב-365 (אני מתעלם משנים לועזיות מעוברות), T@ מציין את תאריך תחילת הפרוייקט שביחס אליו מחושבים ההפרשים.

הפונקציה מחזירה Null במקרה של תקלות צפויות (כדאי לעיין בתנאי ה-While וה-Return).

החזקה מחושבת באמצעות הפונקציה Power ולא באמצעות הסימן ^ המקובל בסביבות פיתוח אחרות.

כללית יש להפעיל את הפונקציה כך- ;(Select    dbo.XIRR(1 (המספר הוא המונה של הפרוייקט).

בדקתי את תוצאות הפונקציה מול פונקציית XIRR באקסל, והתוצאות מתאימות (יש הפרשים זניחים במרחק רב מהנקודה).