האם אתה יודע שאתה יכול להשתמש ב- Excel כדי לחשב את החזר ההלוואה שלך? מאמר זה ינחה אותך בכל השלבים הדרושים כדי לעשות זאת. (ראה גם: מחשבונים למשכנתא: איך הם עובדים .)

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

-> ->

חישוב הלוואה להשכרה חודשית

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

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

- <->

= - PMT (דרג; אורך; נוכחי_ערך; [עתידי_]; [סוג])

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

- <->

נוסחת ה- 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; [future_value]; [type])

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

הנוסחה של 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 $ אנחנו צריכים לנהל משא ומתן על הלוואה בשיעור שנתי שנתי של 58%.

חישוב משכנתא על אורך ההלוואה

עכשיו נראה כיצד לקבל את אורך ההלוואה כאשר אתה יודע את שיעור שנתי, קרן הלווה ואת התשלום החודשי כי הוא להיות repaid. במילים אחרות, כמה זמן אנחנו צריכים להחזיר משכנתא $ 120, 000 עם שיעור של 3. 10% ותשלום חודשי של $ 1, 100?

מספר פרעונות עבור הלוואה

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

= NPER (rate; pmt; present_value; [future_value]; [סוג])

שלושת הטיעונים הראשונים הם שיעור שנתי של ההלוואה, התשלום החודשי הדרוש כדי להחזיר את ההלוואה, ואת קרן הלווה. שני הטיעונים האחרונים הם אופציונליים, ערך ברירת המחדל השיורי הוא 0, המונח ארגומנט לתשלום מראש (עבור 1) או בסוף (עבור 0) הוא גם אופציונלי. <1 +> 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 חודשים.

decomposing את ההלוואה

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

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

= - PPMT (שיעור; num_period; אורך, מנהל; [שיורית]; [terme])

= - INPER (קצב; num_period; אורך; מנהל; [שיורית]; [terme])

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

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

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

התוצאה היא זו המוצגת ב מסך "פירוק הלוואה", על פני התקופה אשר ניתח שהוא "1", כך התקופה הראשונה , או החודש הראשון.בשביל זה, אנחנו משלמים 1161 $. 88, שבור לתוך $ 856, 20 קרן ו 305 $. הריבית

חישוב הלוואה של Excel

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

= - CUMPRINC (דרג, אורך; מנהל; start_date; end_date; סוג)

= - CUMIPMT (שיעור; אורך; מנהל; start_date; end_date; סוג)

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

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

= CUMPRINC (1 + 3, (1) 12 (-1); 10 * 12; 120000; 1; 12; 0)

= CUMIPMT (1 + B2) ^ (1/12) -1, B4 * 12, B3 (1, 12, 0)

= CUMIPMT (1 + 3, 10%) ^ (1/12) -1, 10 * 12, 120000; 1; 12; 0)

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

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

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

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

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

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

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

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

העמודה השלישית היא הקרן שתפרע מדי חודש. לדוגמה, עבור תקופה 40, אנו נפרע 945 $. על בסיס הסכום הכולל החודשי של $ 1, 161. 88. כדי לחשב את הסכום העיקרי שנפדה אנו משתמשים בנוסחה הבאה:

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

העמודה הרביעית היא הריבית, שעבורה אנו מחשבים את הקרן נפרעה על הסכום החודשי שלנו כדי לגלות כיצד (1 + 3, 10%) ^ (1/12) (= 1), 3 $, ; 1; 10 * 12; 120000)

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

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

= 120000 + CUMPRINC (1 + 3, 10%) ^ (1/12), 10 * 12, 120000, 1, 0; 0)

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