קבעו החזרי הלוואות עם נוסחאות אקסל

תוכן העניינים

• הבנת המשכנתא שלך
• חשב את התשלום החודשי
• חשב את שיעור הריבית השנתי
• קביעת אורך הלוואה
• פירוק ההלוואה
• חישוב הלוואות באקסל
• הפחתת ההלוואה
• יצירת לוח זמנים להלוואות

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

מאמר זה הוא מדריך צעד אחר צעד להגדרת חישובי הלוואות.

Takeaways מפתח

• השתמש ב- Excel כדי לקבל טיפול במשכנתא באמצעות קביעת התשלום החודשי שלך, הריבית ולוח הזמנים של ההלוואה שלך. אתה יכול להסתכל יותר לעומק על פירוט הלוואה באמצעות excel וליצור לוח זמנים להחזר שעובד עבורך. קיימים חישובים עבור כל שלב שתוכלו לצבוט כדי לענות על הצרכים הספציפיים שלכם. פירוק ובחינת ההלוואה שלב אחר שלב יכול לגרום לתהליך ההחזר להיות פחות מכריע וניתן לניהול.

הבנת המשכנתא שלך

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

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

חשב את התשלום החודשי

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

הנוסחה, כפי שמוצג בתצלום המסך למעלה, נכתבת באופן הבא:

= -PMT (שיעור; אורך; ערך הנוכחי;;)

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

הנוסחה של Excel המשמשת לחישוב התשלום החודשי של ההלוואה היא:

= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

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

חשב את שיעור הריבית השנתי

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

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

= RATE (Nper; pmt; present_value;;)

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

הנוסחה של Excel המשמשת לחישוב שיעור ההלוואות היא:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

הערה: יש לתת סימן שלילי לנתונים המתאימים בתשלום החודשי. זו הסיבה שישנו סימן מינוס לפני הנוסחה. תקופת השיעור היא 0.294%.

אנו משתמשים בנוסחה = (1 + B5) הוא 12-1 ^ = (1 + 0.294%) ^ 12-1 כדי להשיג את השיעור השנתי של ההלוואה שלנו, שהיא 3.58%. במילים אחרות, כדי לשאול 120, 000 $ על פני 13 שנים כדי לשלם 960 $ לחודש, עלינו לנהל משא ומתן על הלוואה בשיעור מקסימאלי של 3.58%.

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

קביעת אורך הלוואה

כעת נראה כיצד לקבוע את משך ההלוואה כשאתה יודע את התעריף השנתי, את הקרן שהושאלה ואת התשלום החודשי שיש לפרוע. במילים אחרות, כמה זמן נצטרך להחזיר משכנתא בסך 120, 000 $ בשיעור של 3.10% ותשלום חודשי של 1, 100 $?

הנוסחה שנשתמש בה היא NPER, כפי שמוצג בתצלום המסך למעלה, והיא נכתבת באופן הבא:

= NPER (שיעור; pmt; הווה_ערך;;)

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

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

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

אנו משתמשים בנוסחה = B5 / 12 = 127.97 / 12 למספר השנים להשלמת החזר ההלוואה. במילים אחרות, כדי לשאול 120, 000 $, בשיעור שנתי של 3.10% ולשלם 1, 100 $ לחודש, עלינו להחזיר לפדיון 128 חודשים או 10 שנים ו -8 חודשים.

פירוק ההלוואה

תשלום הלוואה מורכב מקרן וריבית. הריבית מחושבת לכל תקופה - לדוגמא, ההחזר החודשי לאורך 10 שנים ייתן לנו 120 תקופות.

הטבלה לעיל מציגה את פירוט ההלוואה (תקופה כוללת השווה ל 120) באמצעות הנוסחאות PPMT ו- IPMT. הטיעונים של שתי הנוסחאות זהים ומחולקים כדלקמן:

= -PPMT (קצב; מספר_פרודיה; אורך; עיקרי;;)

הטיעונים זהים לנוסחת ה- PMT שכבר נראתה, למעט "num_period", שמתווסף כדי להציג את התקופה בה פירק את ההלוואה בהתחשב ב קרן וריבית. להלן דוגמא:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

התוצאה מוצגת בתצלום המסך מעל "פירוק הלוואות" לאורך התקופה שניתחה, שהיא "אחת;" כלומר התקופה הראשונה או החודש הראשון. אנו משלמים 1, 161.88 $ המחולקים ל קרן 856.20 $ וריבית של 305.68 $.

חישוב הלוואות באקסל

ניתן גם לחשב את החזר הקרן והריבית למספר תקופות כגון 12 החודשים הראשונים או 15 החודשים הראשונים.

= -CUMPRINC (קצב; אורך; עיקרי; תאריך התחלה; תאריך תאריך; סוג)

אנו מוצאים את הטיעונים, השיעור, האורך, העיקרי והמונח (שהם חובה) שכבר ראינו בחלק הראשון עם הנוסחה PMT. אבל כאן, אנו זקוקים גם לוויכוחים "start_date" ו- "end_date". "התחלה_תאריך" מציין את תחילת התקופה שיש לנתח, ו"סיום_תאריך "מציין את סיום התקופה שיש לנתח.

להלן דוגמא:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

התוצאה מוצגת במסך המסך "Cumul שנה 1", כך שהתקופות המנותחות נעות בין אחת ל -12 מהתקופה הראשונה (החודש הראשון) ועד השניים עשר (החודש ה -12). במשך שנה היינו משלמים קרן בסך 10, 419.55 דולר וריבית 3, 522.99 דולר.

הפחתת ההלוואה

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

יצירת לוח זמנים להלוואות

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

בעמודה של התקופה הראשונה, הזן "1" כתקופה הראשונה ואז גרור את התא למטה. במקרה שלנו אנו זקוקים ל 120 תקופות שכן תשלום הלוואה ל -10 שנים כפול 12 חודשים שווה ל- 120.

העמודה השנייה היא הסכום החודשי שאנחנו צריכים לשלם בכל חודש - שהוא קבוע לאורך כל לוח ההלוואות. כדי לחשב את הסכום, הכנס את הנוסחה הבאה לתא של התקופה הראשונה שלנו:

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

העמודה השלישית היא הקרן שתוחזר מדי חודש. לדוגמה, לתקופה ה -40, אנו נפרע 945.51 $ קרן בסכום החודשי הכולל של 1, 161.88 $.

כדי לחשב את סכום הקרן שנפדה, אנו משתמשים בנוסחה הבאה:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

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

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

העמודה החמישית מכילה את הסכום שנותר לשלם. לדוגמא, לאחר התשלום ה -40 נצטרך לשלם 83, 994.69 דולר על 120, 000 $.

הנוסחה היא כדלקמן:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

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