تیم متخصصان شبکه گسترش اندیشه پویا (GAP) در این مطلب آموزشی به بررسی این موضوع میپردازد:
تابع PPMT یکی ازتوابع مالی اکسلبرای محاسبه مبلغ اصل وام در یک قسط مشخص است. این محاسبه با توجه به پرداختهای ثابت دورهای،نرخ بهرهمشخص و تعیین شماره قسط مورد نظر انجام میگیرد.بهعنوان مثال با استفاده از این تابع میفهمیم که از قسط شماره دوم که به بانک میدهیم چه مبلغی از آن واقعا از اصل وام (بدون در نظر گرفتن سود) کم میشود.تابع PPMT برخلافتابع PMTکه مبلغ کل قسط (اصل + سود) را در هر دوره نشان میدهد، فقط مبلغ اصل قسط یعنی بخشی از قسط ماهانه که از کل بدهی کم میشود را محاسبه میکند.در این مطلب از مجله ابتدا فرمول کلی تابع PPMT در اکسل و نحوه کار با آن را همراه مثال یاد میگیریم. سپس تفاوت آن با سایر توابع مالی را نیز توضیح میدهیم.
- با ساختار کلی فرمول تابع PPMT در اکسل آشنا خواهید شد.روش محاسبه مبلغ پرداختی اصل قسط وام با تابع PPMT را همراه مثال یاد خواهید گرفت.با تفاوت تابع PPMT با تابع IPMT و تابع PMT آشنا خواهید شد.با خطاهای رایج در فرمولنویسی با تابع PPMT آشنا خواهید شد.
ساختار کلی فرمول تابع PPMT در اکسل
ویژگی مهم تابع PPMT در اکسل امکان محاسبه مبلغ اصل پرداختی در ماه دلخواه از کل زمان بازپرداخت وام است. به عنوان مثال با آن میتوانیم مبلغ اصل قسط پرداختی را در اولین دوره، آخرین دوره یا هر دوره دلخواه را محاسبه کنیم. ساختار کلی فرمول برای این کار به شرح زیر است.
=PPMT(rate,per,nper,pv,[fv],[type])
که تعریف هر یک از آرگومانها در آن بهصورت زیر هستند.
- rate(اجباری):نرخ بهره مشخص شده توسط وامدهنده در هر دورهper(اجباری):شماره دورهای که میخواهیم مبلغ اصل وام برای آن محاسبه شود. مقدار این آرگومان بین عدد «۱» و مقدار آرگومانnperاست.nper(اجباری):تعداد کل اقساط یا دوره بازپرداختpv(اجباری):ارزش فعلی مبلغ وامfv(اختیاری):ارزش آتی وام در انتهای دوره پرداخت. اگر مقدار مشخصی را برای این آرگومان در فرمول ننویسیم، اکسل بهصورت خودکار آن را «صفر» در نظر میگیرد.type(اختیاری):روش پرداخت اقساط را مشخص میکند. عدد «صفر» نشاندهنده پرداخت مبلغ قسط در انتهای دوره و عدد «۱» نشانه پرداخت در ابتدای دوره است. اگر مقدار مشخصی را برای این آرگومان در فرمول ننویسیم، اکسل بهصورت خودکار آن را «صفر» در نظر میگیرد.
در ادامه بحث نحوه کار با این تابع را بهصورت کامل توضیح میدهیم. اما برای یادگیری نکات تکمیلی و همچنین آشنایی با سایر توابع مالی اکسل پیشنهاد میکنیمفیلم آموزش استفاده از توابع و فرمولنویسی اکسلدر را نیز مشاهده کنید.
مراحل فرمولنویسی با تابع PPMT در اکسل
یکی از سادهترین روشها برای فرمولنویسی تابع 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)
- محاسبه سهم بهره برای ردیف اول
با کپی کردن فرمول در بقیه ردیفها، محاسبه به صورت خودکار انجام میشود. البته برای فرمول دوم لازم است مقدار دوره قسط یاperرا با توجه به شماره ماه تغییر دهیم. در این حالت جدول نهایی به شکل تصویر زیر در میآید.
یادگیری کامل فرمول نویسی با توابع مالی اکسل همراه
نرمافزار اکسل حدود ۵۳ تابع مالی دارد که در محاسبات وام، سرمایهگذاری، اوراق بهادار و استهلاک داراییها مورد استفاده قرار میگیرند. تابع PPMT نیز در کنار توابعی مانند تابع PMT،تابع FV،تابع PVو تابع NPER در دستهتوابع وامو بودجهبندی سرمایه قرار میگیرد. یادگیری روش کار با هر یک از این توابع انجام محاسبات مالی را سادهتر و سریعتر میکند. در این مسیر مجموعه فیلمهای آموزشی راهنمای جامعی برای یادگیری فرمولنویسی انواع توابع مالی اکسل به صورت یکپارچه است. همچنین مجموعههای تخصصیتر برای کسب و کار یا حسابداری نیز برای کاربران حوزههای مختلف مرجعی مناسب است.
پیشنهاد اول برای یادگیری عمومی و افزایش مهارت فرمولنویسی با توابع مالی اکسل فیلمهای منتخب آموزشی زیر است.
- فیلم آموزش استفاده از توابع و فرمولنویسی در اکسل همراه گواهینامه در فرادرسفیلم آموزش ترفندهای کاربردی در اکسل همراه گواهینامه در فرادرس
پیشنهاد دوم مشاهده فیلمهای آموزشی تخصصی زیر است.
- فیلم آموزش کاربرد اکسل در حسابداری و مدیریت همراه گواهینامه در فرادرسفیلم آموزش مدلسازی مالی با اکسل همراه گواهینامه در فرادرسفیلم آموزش تجزیه و تحلیل صورتهای مالی در اکسل همراه گواهینامه در فرادرس
همچنین در دو مجموعه فیلم آموزش زیر امکان انتخاب موارد بیشتر بر حسب نیاز وجود دارد.
- مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرسمجموعه فیلم آموزش اکسل برای کسب و کار در فرادرس
تفاوت تابع PPMT با سایر توابع وام در اکسل
سه تابع PMT، تابع IPMT و تابع PPMT از توابع اصلی محاسبه وام هستند که تفاوت ظریفی با یکدیگر دارند. توجه به این تفاوتها انتخاب بهترین تابع برای محاسبه دلخواه را ساده میکند.
این توابع از نظر روش محاسبات و خروجی، تفاوتهای زیر را با یکدیگر دارند.
- تابع PMT مبلغ پرداخت دورهای یک وام شامل اصل و سود آن را محاسبه میکند.تابع IPMT فقط سود پرداختی وام را محاسبه میکند.تابع PPMT مبلغ پرداخت دورهای اصل وام را محاسبه میکند.
بنابراین در محاسبات مربوط به پرداخت اقساط وام یا سرمایهگذاری با توجه به خروجی مورد نظر خود، تابع را انتخاب میکنیم. از نظر فرمول و تعداد آرگومانها نیز این توابع با یکدیگر تفاوتهای بسیار کمی دارند که در جدول زیر خلاصه آنها را توضیحدادهایم.
در این جدول لازم است توجه کنیم که هر چند ساختار فرمول تابع IPMT و تابع PPMT یکسان است، اما اکسل خروجی متفاوتی را محاسبه میکند. این موضوع به آن معنا است که محاسبه ریاضی انجام گرفته توسط اکسل با توجه به نام تابع تفاوت دارد.
در صورت علاقهمندی به آشنایی بیشتر با مفاهیم هر یک از این توابع در مطلب زیر موارد را بهطور کامل توضیح دادهایم.
خطاهای رایج در فرمولنویسی با تابع PPMT در اکسل
شرط محاسبه بدون خطا در فرمولنویسی با تابع PPMT در اکسل جایگذاری درست آرگومانها و توجه به یکسان بودن واحدهای آنها از نظر «ماه» یا «سال» است. در غیر این صورت با خطاهای زیر روبرو میشویم.
زمانی اتفاق میافتد که مقدار آرگومانper(شماره قسط) کمتر از صفر یا بیشتر از مقدار آرگومانnper(زمان بازپرداخت) باشد.
همه آرگومانها در فرمول تابع PPMT مقدار عددی هستند. بنابراین اگر به اشتباه عبارت متنی یا کاراکتر را بنویسیم، پیام خطای#VALUE!را میبینیم.
اگر در پرداختهای فصلی یا ماهانه واحدهای آرگومان را با آرگومان نرخ بهره سالانه یکسان نکنیم، خروجی محاسبه نادرست خواهد بود. بهعنوان مثال لازم است برای پرداخت ماهانه، نرخ بهره سالانه را بر ۱۲ تقسیم کنیم. به همین ترتیب تبدیل واحد سایر آرگومانها نیز برای دریافت خروجی درست اهمیت دارد.
تابع PPMT یکی از توابع مهم وام در اکسل است که برای محاسبه سهم اصل وام از هر قسط مورد استفاده قرار میگیرد. کاربرد درست این تابع نیاز به تبدیل واحد دقیق نرخ بهره سالانه به دوره بازپرداخت (ماهانه، فصلی و غیره) دارد. این موضوع تضمین محاسبات مالی با این تابع است. در این مطلب از مجله روش فرمولنویسی با تابع PPMT در اکسل را همراه مثال یاد گرفتیم. همچنین با تفاوتهای ظریف آن نسبت به سایر توابع مشابه مانند تابع PMT و تابع IPMT نیز آشنا شدیم. این مطلب مقدمهای برای آشنایی با تابع PPMT بود و علاقهمندان به یادگیری مطالب تکمیلی میتوانندمجموعه فیلم آموزش توابع و فرمول در اکسلدر را مشاهده کنند.
- مجموعه آموزش توابع و فرمول در اکسل – مقدماتی تا پیشرفتهآموزش تجزیه و تحلیل صورت مالی + تحلیل عملی در اکسل + گواهینامهمجموعه آموزش اکسل برای کسب و کارتابع PMT در اکسل چیست؟ – آموزش محاسبه اقساط وام با مثال به زبان سادهتوابع وام در اکسل – راهنمای کاربردی
گسترش اندیشه پویا از سال ۱۳۸۲ در حوزه مشاوره فناوری اطلاعات و آموزش تخصصی فعالیت میکند. برای دریافت مشاوره با ما تماس بگیرید.