לוח חובות עם נוסחאות PMT, IPMT ו- IF - מדריך ודוגמאות

אנו יכולים להשתמש בנוסחאות PMT, IPMT ו- IF של Excel כדי ליצור לוח חובות. ראשית, עלינו להגדיר את המודל על ידי הזנת הנחות חוב. בדוגמה זו אנו מניחים שהחוב הוא 5,000,000 $, תקופת התשלום היא 5 שנים ושיעור הריבית ריבית ריבית מתייחסת לסכום שגובה המלווה ללווה עבור כל צורת חוב שניתנה, בדרך כלל מבוטאת כ אחוז מהמנהל. להיות 4.5%.

1. יתרת הפתיחה בלוח החובות שלנו שווה לסכום ההלוואה של 5 מיליון דולר, ולכן בתא E29 אנו נכנסים = B25 כדי לקשר אותו לתשומת ההנחה. לאחר מכן נוכל להשתמש בנוסחת PMT כדי לחשב את התשלום הכולל לתקופה הראשונה = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . הנוסחה מחשבת את סכום התשלום באמצעות סכום ההלוואה, תקופת הריבית והריבית הנקובים בסעיף ההנחה.

לוח חובות

2. בתא E28, הזן את התקופה בה אנו נמצאים, והיא 1. בתא E29 הזן = E28 + 1 ומלא את הנוסחה מימין. לאחר מכן, השתמש בנוסחת IPMT כדי לברר את תשלום הריבית לתקופה הראשונה = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .

3. התשלום העיקרי הוא ההפרש בין סך התשלום לתשלום הריבית, שהוא = E30-E31 . יתרת הסגירה היא יתרת הפתיחה בתוספת תשלום הקרן שבוצע, וזה = E29 + E32 . יתרת הפתיחה לתקופה 2 היא יתרת הסגירה לתקופה 1, שהיא = E33 .

4. העתק את כל הנוסחאות מתא E29 ל- E33 לעמודה הבאה, ואז העתק הכל ימינה. בדוק אם יתרת הסגירה לתקופה 5 = 0 כדי לוודא כי נעשה שימוש בנוסחאות ומספרים נכונים.

5. שימו לב שיש כמה הודעות שגיאה שמתחילות מתקופה 6 כי יתרת הפתיחה היא 0. כאן נוכל להשתמש בפונקציית IF כדי לנקות את השגיאות. בתא E30, הקלד = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . הנוסחה קובעת כי אם יתרת הפתיחה נמוכה מ- 0, ערך התשלום הכולל יוצג כ- 0.

6. בתא 31, הקלד = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . נוסחה זו דומה לזו הקודמת, הקובעת שאם יתרת הפתיחה נמוכה מ- 0, הרי שתשלום הריבית יוצג כ- 0.

7. העתק את התא E30 ו- E31, לחץ על SHIFT + חץ ימינה ואז CTRL + R כדי למלא ימינה. אתה אמור לראות שכל הודעות השגיאה מוצגות כעת כ- 0.

XNPV XIRR עם פונקציות DATE ו- IF

אנו יכולים לחשב את NPV ו- IRR בהתבסס על תאריכים ספציפיים באמצעות פונקציות Excel XNPV ו- XIRR עם הפונקציות DATE ו- IF.

8. עבור לתא E6 והזן = DATE (E5,12,31) להצגת התאריך. העתק מימין. תראה את #VALUE! הודעה לאחר 2021. נוכל לתקן זאת באמצעות פונקציית IFERROR = IFERROR (DATE (E5,12,31), ””) .

9. כעת אנו יכולים להתחיל לחשב את ה- NPV ואת ה- IRR. ראשית, עלינו להזין את סכומי תזרים המזומנים החופשיים. אנו מניחים כי סכומי ה- FCF מתקופה 1 עד 5 הם -1,000, 500, 600, 700, 900. בתא C37 נכניס שיעור היוון של 15%. בתא B37, חישב את ה- NPV באמצעות הנוסחה XNPV = XNPV (C37, E35: I35, E6: I6) .

10. בתא B38, חישב את ה- IRR באמצעות נוסחת XIRR = XIRR (E35: I35, E6: I6) .

הוספת OFFSET ל- XNPV ו- XIRR

אנו יכולים לשנות לנוסחאות XNPV ו- XIRR כדי ליצור נוסחאות דינמיות יותר באמצעות פונקציית OFFSET.

11. בתא B42, שנה את הנוסחה ל- = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . הנוסחה היא דינמית יותר מכיוון שאם מספר התקופות יגדל, אז גם תקופות תזרים המזומנים החופשיות יגדלו. איננו צריכים לשנות את נוסחת ה- NPV אם תקופת התחזית ארוכה יותר. עבור פונקציית IRR, שנה אותה ל- = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .

12. לאחר התאמת הנוסחה למספר התקופות, עלינו לקזז את התאריכים. בתא B42, שנה את הנוסחה ל- = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . זה מאפשר לנוסחאות NPV ו- IRR לאסוף את המספר הנכון של תזרים מזומנים חופשי עם השינוי במספר התקופות.

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

  • נוסחת PMT לחישוב סכום תשלום חוב: = PMT (ריבית, מספר תנאים, ערך נוכחי)
  • נוסחת IPMT לחישוב תשלום ריבית: = IPMT (ריבית, תקופה, מספר מונחים, ערך נוכחי)
  • נוסחת XNPV למציאת הערך הנוכחי הנקי: = XNPV (שיעור היוון, תזרימי מזומנים חופשיים, תאריכים)
  • נוסחת XIRR למציאת שיעור התשואה הפנימי: = XIRR (תזרימי מזומנים חופשיים, תאריכים)
  • נוסחת OFFSET לחישוב NPV דינמי: = XNPV (שיעור היוון, FCF ראשון: OFFSET (FCF ראשון, 0, # תקופות - 1), תאריך ראשון: קיזוז (תאריך ראשון, 0, # תקופות - 1))
  • נוסחת OFFSET לחישוב IRR דינמי: = XIRR (FCF ראשון: OFFSET (1st FCF, 0, # נקודות - 1), תאריך ראשון: OFFSET (תאריך ראשון, 0, # תקופות - 1))

משאבים אחרים

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

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