חישוב ריבית צבורה, כאשר הריבית משתנה כל תקופה, כגון ריבית החשב הכללי

היי

נניח שצריך לחשב ריבית צבורה מיום היווצרות עד יום התשלום, כאשר הריבית בתקופה זו משתנה. משמע, לדוגמה, ברבעון ראשון הצבירה היא לפי ריבית 3%, ברבעון לאחר מכן הצבירה היא לפי ריבית 3.25%, ברבעון לאחר מכן הצבירה היא לפי 4% וכך הלאה. הנתון שעומד בפני זה נתוני הריבית והמועדים בהם חלה ריבית זו.

לצורך ההדגמה הורדתי נתונים של ריבית פיגורים מאתר של החשב הכללי במשרד האוצר. הנוסחה תהיה דינאמית, כלומר תגלה בעצמה את כל טווח הריביות שבין מועד ההיווצרות למועד התשלום. נניח תאריך היווצרות 1.3.2004 ותאריך תשלום 1.5.2006.

שלב 1

ניצור עמודה של ריבית לתקופה וריבית מצטברת (ריבית דריבית):

הריבית חושבה כריבית רציפה בנוסחה (EXP(r*t, כאשר r הוא ריבית לשנה ו- t הוא תקופה בשנים. על מנת שהריבית הרציפה לשנה לא תעלה על הריבית המפורסמת, קודם אני מוציא LN ממנה. כלומר, אם ריבית שפורסמה היא 12.8 לשנה, הרי ש-

EXP(LN(12.8/100 + 1) * 1) = 1.128

כלומר, אם אחלץ LN מתוך ריבית ולאחר מכן אחשב ריבית רציפה לתקופה של שנה, אני אחזור לאותה הריבית שפורסמה, ולא לתוצאה גבוהה יותר.

בנוסף, אני מחשב ריבית שנצברה בתקופה עד למועד הבא. כלומר, התוצאה בתא C11 היא ריבית שבשורה הקודמת B10 לתקופה שבין התאריך A10 ל- A11. כלומר, ריבית שנצברה עד כה.

בעמודה D מחשובת ריבית מצטברת עד למועד:

שלב 2

תקופה שבין מועד היווצרות למועד התשלום תתחלק ל-3:

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

חישוב ריבית צבורה עד למועד הפרסום הראשון שלאחר ההיווצרות:

מכיוון שהנתונים מהאתר ממוינים בסדר עולה, ניתן להשתמש בפונקציה LOOKUP אשר תמצא את הערך המדויק או הקטן יותר מהערך אותו מחפשים או פונקציה MATCH עם פרמטר אחרון 1 (קטן מ-), מאותה הסיבה. אני אחפש את תאריך ההיווצרות ואמצא את תאריך הפרסום שקדם לתאריך ההיווצרות. ברור שהתאריך הבא אחריו יהיה תאריך פרסום שיהיה מאוחר יותר מתאריך ההיווצרות. תאריך זה אני מחפש. לכן הנוסחה תהיה:

INDEX(dates_range, MATCH(inception_date, dates_range, 1)+1)

כאשר:

dates_range – טווח תאריכים שבעמודה A

inception_date – תאריך היווצרות

וריבית שמצברה עד מועד זה העמודה D היא:

INDEX(cum_interest_range, MATCH(inception_date, dates_range, 1)+1)

כאשר:

cum_interest_range – טווח ריבית צבורה שבעמודה D

שלב 3

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

למציאת תאריך הפרסום שקדם לתאריך התשלום:

LOOKUP(pay_date, dates_range)

כאשר:

dates_range – טווח תאריכים שבעמודה A

pay_date – תאריך תשלום

LOOKUP(pay_date, dates_range, cum_interest_range)

כאשר:

cum_interest_range – טווח ריבית צבורה שבעמודה D

ריבית צבורה בתקופת הביניים הזו היא מנה של השני בראשון:

שלב 4

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

LOOKUP(inception_date, dates_range, interest_range)

כאשר:

inception_date – תאריך היווצרות

dates_range – טווח תאריכים שבעמודה A

interest_range -טווח ריביות שפורסמו בעמודה B

חישוב הריבית הצבורה:

EXP(LN(interest/100+1)*(first_date - inception_date)/365)

כאשר:

first_date – תאריך פרסום ראשון שלאחר מועד ההיווצרות.

כאמור, באותו האופן מוצאים את הריבית בתקופה האחרונה.

התוצאה הסופית היא מכפלה של הריביות הצבורות בכל התקופות:

כאמור, אפשר לאחד הכל לנוסחה אחת.

סיימנו. תיהנו!