تابع PPMT در اکسل – آموزش محاسبه مبلغ اصل قسط با مثال

تابع PPMT یکی از توابع مالی اکسل برای محاسبه مبلغ اصل وام در یک قسط مشخص است. این محاسبه با توجه به پرداخت‌های ثابت دوره‌ای، نرخ بهره مشخص و تعیین شماره قسط مورد نظر انجام می‌گیرد. به‌عنوان مثال با استفاده از این تابع می‌فهمیم که از قسط شماره دوم که به بانک می‌دهیم چه مبلغی از آن واقعا از اصل وام (بدون در نظر گرفتن سود) کم می‌شود. تابع PPMT برخلاف تابع PMT که مبلغ کل قسط (اصل + سود) را در هر دوره نشان می‌دهد، فقط مبلغ اصل قسط یعنی بخشی از قسط ماهانه که از کل بدهی کم می‌شود را محاسبه می‌کند. در این مطلب از مجله فرادرس ابتدا فرمول کلی تابع PPMT در اکسل و نحوه کار با آن را همراه مثال یاد می‌گیریم. سپس تفاوت آن با سایر توابع مالی را نیز توضیح می‌دهیم.

ساختار کلی فرمول تابع PPMT در اکسل

ویژگی مهم تابع PPMT در اکسل امکان محاسبه مبلغ اصل پرداختی در ماه دلخواه از کل زمان بازپرداخت وام است. به عنوان مثال با آن می‌توانیم مبلغ اصل قسط پرداختی را در اولین دوره، آخرین دوره یا هر دوره دلخواه را محاسبه کنیم. ساختار کلی فرمول برای این کار به شرح زیر است.

=PPMT(rate,per,nper,pv,[fv],[type])

که تعریف هر یک از آرگومان‌ها در آن به‌صورت زیر هستند.

• rate (اجباری): نرخ بهره مشخص شده توسط وام‌دهنده در هر دوره
• per (اجباری): شماره دوره‌ای که می‌خواهیم مبلغ اصل وام برای آن محاسبه شود. مقدار این آرگومان بین عدد «۱» و مقدار آرگومان nper است.
• nper (اجباری): تعداد کل اقساط یا دوره‌ بازپرداخت
• pv (اجباری): ارزش فعلی مبلغ وام
• fv (اختیاری): ارزش آتی وام در انتهای دوره پرداخت. اگر مقدار مشخصی را برای این آرگومان در فرمول ننویسیم، اکسل به‌صورت خودکار آن را «صفر» در نظر می‌گیرد.
• type (اختیاری): روش پرداخت اقساط را مشخص می‌کند. عدد «صفر» نشان‌دهنده پرداخت مبلغ قسط در انتهای دوره و عدد «۱» نشانه پرداخت در ابتدای دوره است. اگر مقدار مشخصی را برای این آرگومان در فرمول ننویسیم، اکسل به‌صورت خودکار آن را «صفر» در نظر می‌گیرد.

در ادامه بحث نحوه کار با این تابع را به‌صورت کامل توضیح می‌دهیم. اما برای یادگیری نکات تکمیلی و همچنین آشنایی با سایر توابع مالی اکسل پیشنهاد می‌کنیم فیلم آموزش استفاده از توابع و فرمول‌نویسی اکسل در فرادرس را نیز مشاهده کنید.

مراحل فرمول‌نویسی با تابع PPMT در اکسل

یکی از ساده‌ترین روش‌ها برای فرمول‌نویسی تابع PPMT در اکسل، وارد کردن مستقیم آن در سلول دلخواه است. برای این کار چهار مرحله زیر را به ترتیب انجام می‌دهیم.

1. ورود اطلاعات در جدول اکسل
2. نوشتن نام تابع
3. وارد کردن آرگومان‌ها
4. اجرای فرمول

حال با یک مثال ساده هر مرحله را توضیح می‌دهیم.

فیلم آموزش محاسبه قسط وام در اکسل با تابع PPMT + فرمول‌ها و مثال‌ (رایگان) در فرادرس

کلیک کنید

مرحله اول: ورود اطلاعات

فرض می‌کنیم وامی به مبلغ ۱۰۰ میلیون تومان با نرخ بهره سالانه ۶ درصد و دوره بازپرداخت ۱۲ ماهه گرفته‌ایم. هدف اصلی محاسبه اصل مبلغ قسط در ماه اول است. ابتدا این اطلاعات را مانند تصویر زیر در جدول اکسل وارد می‌کنیم.

مرحله دوم: نوشتن نام تابع

تابع PPMT به‌صورت پیش‌فرض در اکسل تعریف شده است. بنابراین با وارد کردن نام آن در یک سلول خالی دلخواه یا نوار فرمول اکسل، آرگومان‌ها به‌صورت خودکار نمایش داده می‌شوند.

مرحله سوم: ورود آرگومان‌های فرمول

در این مرحله به‌دقت آرگومان‌های فرمول را با همان ترتیب مشخص شده در فرمول وارد می‌کنیم. از آنجا که عدد نرخ بهره به‌صورت سالانه است، آن را بر عدد ۱۲ تقسیم می‌کنیم تا نرخ ماهانه بهره به‌دست آید. به این شکل همه آرگومان‌های فرمول، واحد یکسان دارند. بنابراین فرمول نهایی به شکل زیر در می‌آید.

=PPMT(B3/12,B5,B4,B2)

مرحله چهارم: اجرای فرمول

با زدن دکمه ENTER محاسبه انجام می‌شود.

باتوجه به محاسبه، در قسط اول وام حدود ۸ میلیون و ۱۰۶ هزار تومان از اصل وام کم می‌شود. منفی بودن نتیجه محاسبه نشان‌دهنده جریان نقدی خروجی است. به این معنی که پول از طرف فرد پرداخت شده است و افزایش سرمایه برای او محسوب نمی‌شود. البته می‌توانیم از همان ابتدا مقدار وام را در جدول اکسل عدد منفی بگذاریم تا نتیجه نهایی عددی مثبت شود.

مثال استفاده از تابع PPMT برای محاسبه اصل قسط

انجام محاسبه با تابع PPMT ساده است. با این حال ترفندهای ظریفی به‌خصوص در یکسان کردن واحدهای نرخ بهره و دوره بازپرداخت وام وجود دارد که در مثال‌های این بخش آن‌ها را توضیح می‌دهیم.

فیلم آموزش مدل سازی مالی با اکسل – تحلیل پروژه های اقتصادی و سبد سهام و سرمایه گذاری + گواهینامه در فرادرس

کلیک کنید

مثال ۱: محاسبه با پرداخت‌های فصلی قسط وام

شخصی برای بازسازی خانه‌اش مبلغ ۲۰۰ میلیون تومان وام با نرخ سود سالانه ۱۸ درصد دریافت کرده است. اگر مدت بازپرداخت وام ۳ سال و نحوه پرداخت اقساط آن هر سه ماه یک‌بار (فصلی) باشد، می‌خواهیم مبلغ اصل وام که در چهارمین قسط پرداخت می‌شود را به‌دست آوریم. این فرد مبلغ قسط خود را در ابتدای هر فصل پرداخت می‌کند.

ابتدا اطلاعات را در جدول اکسل وارد می‌کنیم.

نکته اصلی این مثال نحوه پرداخت سه ماهه و نرخ سود سالانه و مدت بازپرداخت وام است. بنابراین برای یکسان کردن واحد آرگومان‌ها در فرمول، عدد سالانه نرخ بهره یا آرگومان rate را بر عدد «۴» تقسیم می‌کنیم. زیرا چهار پرداخت سه ماهه در سال داریم و نرخ بهره نیز در این حالت به‌صورت فصلی یا سه ماهه در می‌آید. به همین ترتیب مدت زمان بازپرداخت وام یا آرگومان nper را در عدد «۴» ضرب می‌کنیم تا هم‌واحد با پرداخت‌های سه‌ ماهه شود. برای اینکه عدد نهایی منفی نباشد، مبلغ وام را در فرمول با علامت منفی می‌نویسیم که نشان‌دهنده جریان نقدی خروجی است.

در نتیجه فرمول به شکل زیر در می‌آید.

=PPMT(B3/4,B5,B4*4,-B2,0,1)

و نتیجه محاسبه مانند جدول زیر خواهد بود.

مثال ۲: درست کردن جدول استهلاک وام

جدول استهلاک وام یک برنامه ماهانه است که نشان می‌دهد از هر قسط چقدر به بهره و چقدر به اصل وام اختصاص دارد. با درست کردن این جدول می‌توانیم زمان تسویه کامل وام را مشخص کنیم و به این شکل مدیریت مالی بهتری داشته باشیم. برای درست کردن این جدول از تابع PMT نیز در کنار تابع PPMT استفاده می‌کنیم.

فرض می‌کنیم فردی یک وام ۳۰۰ میلیون تومانی با نرخ بهره سالانه ۱۸ درصد و دوره بازپرداخت یک ساله از بانک گرفته است. مبلغ قسط در پایان دوره پرداخت می‌شود. بنابراین ابتدا اطلاعات را مانند تصویر زیر در جدول اکسل وارد می‌کنیم.

سپس جدول استهلاک را درست می‌کنیم. این جدول چهار ستون اصلی به شرح زیر دارد.

• ستون اول نشان‌دهنده شماره ماه پرداخت قسط است.
• ستون دوم عدد کل قسط (اصل+سود) است که با تابع PMT آن را به‌دست می آوریم.
• ستون سوم عدد اصل قسط است که با تابع PPMT آن را به‌دست می‌آوریم.
• ستون چهارم عدد سهم بهره از هر ماه است که از تفاضل مقدار دو تابع PMT و تابع PPMT به‌دست می‌آید.

با توجه به جدول اطلاعات اولیه، فرمول‌های زیر را برای ردیف اول می‌نویسیم. نکته مهم، استفاده از علامت $ است که نشان می‌دهد اطلاعات اولیه در هر ردیف یکسان است. در صورت عدم استفاده از این علامت، پیام خطای #NUM! روبرو می‌شویم.

• محاسبه مبلغ کل قسط

=PMT($B$3/12,$B$4*12,-$B$2)

• محاسبه مبلغ اصل قسط در دوره اول

=PPMT($B$3/12,1,$B$4*12,-$B$2)

• محاسبه سهم بهره برای ردیف اول

=F3-G3

با کپی کردن فرمول در بقیه ردیف‌ها، محاسبه به صورت خودکار انجام می‌شود. البته برای فرمول دوم لازم است مقدار دوره قسط یا per را با توجه به شماره ماه تغییر دهیم. در این حالت جدول نهایی به شکل تصویر زیر در می‌آید.

یادگیری کامل فرمول نویسی با توابع مالی اکسل همراه فرادرس

نرم‌افزار اکسل حدود ۵۳ تابع مالی دارد که در محاسبات وام، سرمایه‌گذاری، اوراق بهادار و استهلاک دارایی‌ها مورد استفاده قرار می‌گیرند. تابع PPMT نیز در کنار توابعی مانند تابع PMT، تابع FV، تابع PV و تابع NPER در دسته توابع وام و بودجه‌بندی سرمایه قرار می‌گیرد. یادگیری روش کار با هر یک از این توابع انجام محاسبات مالی را ساده‌تر و سریع‌تر می‌کند. در این مسیر مجموعه فیلم‌های آموزشی فرادرس راهنمای جامعی برای یادگیری فرمول‌نویسی انواع توابع مالی اکسل به صورت یکپارچه است. همچنین مجموعه‌های تخصصی‌تر برای کسب و کار یا حسابداری نیز برای کاربران حوزه‌های مختلف مرجعی مناسب است.

پیشنهاد اول برای یادگیری عمومی و افزایش مهارت فرمول‌نویسی با توابع مالی اکسل فیلم‌های منتخب آموزشی زیر است.

• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ترفندهای کاربردی در اکسل همراه گواهینامه در فرادرس

پیشنهاد دوم مشاهده فیلم‌های آموزشی تخصصی زیر است.

• فیلم آموزش کاربرد اکسل در حسابداری و مدیریت همراه گواهینامه در فرادرس
• فیلم آموزش مدل‌سازی مالی با اکسل همراه گواهینامه در فرادرس
• فیلم آموزش تجزیه و تحلیل صورت‌های مالی در اکسل همراه گواهینامه در فرادرس

همچنین در دو مجموعه فیلم آموزش زیر امکان انتخاب موارد بیشتر بر حسب نیاز وجود دارد.

• مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل برای کسب و کار در فرادرس

تفاوت تابع PPMT با سایر توابع وام در اکسل

سه تابع PMT، تابع IPMT و تابع PPMT از توابع اصلی محاسبه وام هستند که تفاوت ظریفی با یکدیگر دارند. توجه به این تفاوت‌ها انتخاب بهترین تابع برای محاسبه دلخواه را ساده می‌کند.

فیلم آموزش استفاده از توابع مالی برای محاسبه اقساط و بازپرداخت‌ وام در اکسل (رایگان) در فرادرس

کلیک کنید

این توابع از نظر روش محاسبات و خروجی، تفاوت‌های زیر را با یکدیگر دارند.

• تابع PMT مبلغ پرداخت دوره‌ای یک وام شامل اصل و سود آن را محاسبه می‌کند.
• تابع IPMT فقط سود پرداختی وام را محاسبه می‌کند.
• تابع PPMT مبلغ پرداخت دوره‌ای اصل وام را محاسبه می‌کند.

بنابراین در محاسبات مربوط به پرداخت اقساط وام یا سرمایه‌گذاری با توجه به خروجی مورد نظر خود، تابع را انتخاب می‌کنیم. از نظر فرمول و تعداد آرگومان‌ها نیز این توابع با یکدیگر تفاوت‌های بسیار کمی دارند که در جدول زیر خلاصه آن‌ها را توضیح‌داده‌ایم.

در این جدول لازم است توجه کنیم که هر چند ساختار فرمول تابع IPMT و تابع PPMT یکسان است، اما اکسل خروجی متفاوتی را محاسبه می‌کند. این موضوع به آن معنا است که محاسبه ریاضی انجام گرفته توسط اکسل با توجه به نام تابع تفاوت دارد.

در صورت علاقه‌مندی به آشنایی بیشتر با مفاهیم هر یک از این توابع در مطلب زیر موارد را به‌طور کامل توضیح داده‌ایم.

خطاهای رایج در فرمول‌نویسی با تابع PPMT در اکسل

شرط محاسبه بدون خطا در فرمول‌نویسی با تابع PPMT در اکسل جایگذاری درست آرگومان‌ها و توجه به یکسان بودن واحدهای آن‌ها از نظر «ماه» یا «سال» است. در غیر این صورت با خطاهای زیر روبرو می‌شویم.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

۱. خطای !NUM#

زمانی اتفاق می‌افتد که مقدار آرگومان per (شماره قسط) کمتر از صفر یا بیشتر از مقدار آرگومان nper (زمان بازپرداخت) باشد.

۲. خطای !VALUE#

همه آرگومان‌ها در فرمول تابع PPMT مقدار عددی هستند. بنابراین اگر به اشتباه عبارت متنی یا کاراکتر را بنویسیم، پیام خطای #VALUE! را می‌بینیم.

۳. خروجی اشتباه

اگر در پرداخت‌های فصلی یا ماهانه واحدهای آرگومان را با آرگومان نرخ بهره سالانه یکسان نکنیم، خروجی محاسبه نادرست خواهد بود. به‌عنوان مثال لازم است برای پرداخت ماهانه، نرخ بهره سالانه را بر ۱۲ تقسیم کنیم. به همین ترتیب تبدیل واحد سایر آرگومان‌ها نیز برای دریافت خروجی درست اهمیت دارد.

جمع‌بندی پایانی

تابع PPMT یکی از توابع مهم وام در اکسل است که برای محاسبه سهم اصل وام از هر قسط مورد استفاده قرار می‌گیرد. کاربرد درست این تابع نیاز به تبدیل واحد دقیق نرخ بهره سالانه به دوره بازپرداخت (ماهانه، فصلی و غیره) دارد. این موضوع تضمین محاسبات مالی با این تابع است. در این مطلب از مجله فرادرس روش فرمول‌نویسی با تابع PPMT در اکسل را همراه مثال یاد گرفتیم. همچنین با تفاوت‌های ظریف آن نسبت به سایر توابع مشابه مانند تابع PMT و تابع IPMT نیز آشنا شدیم. این مطلب مقدمه‌ای برای آشنایی با تابع PPMT بود و علاقه‌مندان به یادگیری مطالب تکمیلی می‌توانند مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس را مشاهده کنند.