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

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

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

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

0=-100+30/(1+R)+30/(1+R)2+30/(1+R)3+30/(1+R)4

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

0=-100+30X+30X2+30X3+30X4

ולאחר שנפתור אותה ונמצא את 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,

        @Mx Float;

Select    @Mn=0.000000001,

        @Mx=1000000000;

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 Abs(@R)<@Mx And @T Is Not Null

        Begin

        Select    @I=@I+1;

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

        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(@R)>=@Mx Then Null

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

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

            Else @R2

            End

End;

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

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

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

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

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

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

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

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


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

  1. מאת רוני ורד:

    בדיוק למדתי לפני כמה חודשים את קורס תורת המימון באוניברסיטה.
    מגניב לראות יישום של המושגים בתוך עולם ה-DB

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

five × two =