חישוב ריבית מצטברת לתקופה || אקסל 2010/2013

G Gabi

Member
חישוב ריבית מצטברת לתקופה || אקסל 2010/2013

שלום,

ביצעתי לא מעט ניסיונות עם כל מיני פונ' המופיעות בעמוד התמיכה הזה באתר של Microsoft, אך, לצערי - לא הצלחתי להגיע למענה.

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

אם אפשר לחשוב ריבית לפי מספר הימים המדויק, הדבר יהיה עוד יותר טוב. אבל, אסתפק גם בסטיה של 15+- ימים (תחילת / סוף חודש).

הלוואה לדוגמא:
מועד נטילת ההלוואה: 02/09/2016.
מועד פרעון ההלוואה: 30/11/2018.
ריבית נומינאלית שנתית: 17.9% (מי לא ילווה עם ריבית כזו
)
תקופות חישוב הריבית השנתית: 4 (רבעוני).
שיטת ריבית: ריבית דריבית.
בסיס שנתי: 365 ימים.

מהי הפונ' באקסל בה יש להשתמש ע"מ לחשב את הריבית שתצטבר (או, את הערך הנוכחי של כלל ההלוואה קרן+ריבית) לתאריך 15/03/2017?

זו רק דוגמא. במסמך אותו אני מנסה להכין, יש מספר הלוואות שניטלו בתאריכים שונים.

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

תודה רבה!
 

iyyi

New member
הפונקציה לחישוב ריבית משולמת בתקופה מסוימת היא IPMT

בקובץ המצורף בשורה 2 יש תאים צהובים שהם תאי הקלט לפרטי ההלוואה.
בהמשך השורה יש חישוב של מספר התקופות הכולל להחזר ההלוואה, הריבית התקופתית (במקרה שנתת זאת ריבית רבעונית) ומספר תקופות התשלום עד תאריך הדוח המבוקש (בדוגמה שלך 15/3/17).
בתא L2 יש נוסחת מערך המחשבת את סכום תשלומי הריבית בכל תקופת ההלוואה.
בתא M2 יש סיכום של הריבית המשולמת עד תאריך הדוח המבוקש. במקרה שלך זה 2 תשלומים עד 15/3/17. שים לב שנוסחאות מערך צריך לסיים את הכנסתן בצירוף המקשים Ctrl+Shift+Enter

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

מצורפים: צילום מסך וקובץ הדגמה.

 

G Gabi

Member
אמ.. הו, וואו. חשכו עיניי


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

בשעה+ האחרונה "השתעשעתי" עם הקובץ שהעלית. ניסיתי ללמוד אותו, עם הצלחה חלקית בלבד.

שאלות בנוגע לדברים שכתבת:
"בתא L2 יש נוסחת מערך המחשבת את סכום תשלומי הריבית בכל תקופת ההלוואה." ההתרשמות שלי שמדובר בתשלום ריבית עבור התקופה הראשונה בלבד. שכן תשלום הריבית בתקופה השניה תהיה מעט גבוהה יותר בגלל "ריבית דריבית".
בהלוואות אותן אני מנסה לחשב, אין *תשלום* ריבית בכל סוף תקופה, אלא התשלום עבור הריבית הוא אך ורק במועד פרעון הלוואה. במועד הפרעון, משלמים את הקרן כולה ואת כל הריבית שהצטברה.
תקופות הריבית (המצטברת) הן כל רבעון.

"בתא M2 יש סיכום של הריבית המשולמת עד תאריך הדוח המבוקש. במקרה שלך זה 2 תשלומים עד 15/3/17."
בדוגמא אין 2 תקופות מלאות. על-פניו נראה שהעניין הזה נלקח בחשבון שכן סך הריבית ששולמה (8.01) היא בוודאי לא עבור תקופה אחת והיא לא אמורה להיות עבור 2 תקופות מלאות.
אם "מקצינים" את המצב, ונבחר בתאריך חישוב שהוא בתוך הרבעון הראשון למתן ההלוואה, נניח 10/10/2016 הרי שנקבל שגיאה ב"ריבית משולמת". אבל, המשולמת היא עניין אחד, השאלה כמה ריבית נצברה לתאריך החישוב. (אחת ההלוואות אותן אני מבקש לבדוק ניתנה במהלך נובמבר 2015 ותאריך הבדיקה הוא 31/12/2015
).

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

~~~~~~
אני תוהה לגבי צורת החישוב של הריבית התקופתית.
In your Excel: =(1+B2)^(E2/12)-1
Should it be: =(1+B2(E2/12))^(E2/12)-1 ?
נניח מצב של ריבית שנתית בת 8%, בחישוב רבעוני.
הריבית האפקטיבית לפי השורה השניה תצא:
(1+0.08/4)^4 - 1 = 1.02^4 - 1 = 0.0824
אם נבצע אותה אותה פעולה עם פונ' EFFECT:
ריבית נומינאלית של 8%, תקופות: 4 (כל רבעון):
=EFFECT(0.08,4) = 0.0824

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

~~~~~~~~~~~

אולי אני "מאבד ראש" וכותב שטויות. I have no idea. אאשים את השעה...

על הבונוס של לוח שפיצר. הלכתי עוד יותר לאיבוד עם פונ' האקסל שבו...
 

iyyi

New member
זה שמדובר בהלוואה הנפרעת בסוף התקופה זה משהו שחייבים לציין!

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

 

G Gabi

Member
תודה רבה!! כ"כ מצטער על התגובה המאוחרת...

לא שמתי לב להתראה בתפוז ולא הספקתי להתפנות לבדוק בפורום. נכנסתי עכשיו ומסתבר שאני בדיליי של 11 ימים. סליחה!
בנוסף, סליחה שלא כתבתי בצורה ברורה בהודעה הראשונה.
&nbsp
מקווה שבמהלך סוף השבוע אתפנה שוב לעניין בשבילו ביקשתי עזרה.
תודה רבה!
 
למעלה