محاسبه پرداخت سالیانه در مایکروسافت اکسل
قبل از گرفتن وام، محاسبه تمام پرداخت ها بر روی آن بسیار راحت خواهد بود. این امر موجب صرفه جویی در قرض گیرنده در آینده از مشکلات و ناامیدی های غیرمنتظره می شود، زمانی که مشخص شود که بازپرداخت بیش از حد زیاد است. ابزار اکسل می تواند در این محاسبه کمک کند. بیایید نحوه محاسبه پرداخت وام قرضه را در این برنامه بیابیم.
محاسبه پرداخت
اولا باید بگویم که دو نوع پرداخت اعتباری وجود دارد:
- تمایز
- سالیانه
با یک طرح متفاوت، مشتری یک سهم ماهیانه از پرداخت به بانک را در صندوق وام به علاوه پرداخت بهره پرداخت می کند. مبلغ پرداخت بهره هر ماه کاهش می یابد به عنوان بدن وام که از آن محاسبه می شود کاهش می یابد. بنابراین، پرداخت کل ماهانه نیز کاهش می یابد.
یک طرح سالانه از یک روش کمی متفاوت استفاده می کند. مشتری ماهانه مقدار مشابهی از کل پرداخت می کند که شامل پرداخت ها در بخش وام و پرداخت بهره است. در ابتدا، پرداخت های بهره به میزان کامل وام پرداخت می شود، اما با کاهش هزینه، بهره کاهش می یابد. اما مبلغ کل پرداخت به علت افزایش ماهانه در پرداخت ها در بخش وام بدون تغییر باقی می ماند. بنابراین، با گذشت زمان، نسبت بهره در کل پرداخت ماهانه کاهش می یابد، در حالی که نسبت پرداخت در هر بدن افزایش می یابد. در عین حال، مجموع پرداخت ماهانه خود را در طول دوره کل وام تغییر نمی کند.
فقط در محاسبه پرداخت سالیانه، ما متوقف می شویم. علاوه بر این، این مربوط است، زیرا در حال حاضر اکثر بانک ها از این طرح خاص استفاده می کنند. همچنین برای مشتریان مناسب است، زیرا در این صورت مبلغ کل پرداخت تغییری نخواهد کرد، باقی مانده ثابت می شود. مشتریان همیشه می دانند چقدر هزینه می کنند.
مرحله 1: هزینه ماهیانه را محاسبه کنید
برای محاسبه هزینه ماهانه هنگام استفاده از طرح سالانه در Excel، یک تابع خاص - PMT وجود دارد . آن متعلق به دسته اپراتورهای مالی است. فرمول این تابع به شرح زیر است:
=ПЛТ(ставка;кпер;пс;бс;тип)
همانطور که می بینید، تابع مشخص شده تعداد زیادی استدلال دارد. درست است که دو نفر از آنها اجباری نیستند.
استدلال نرخ ، نرخ بهره را برای یک دوره خاص نشان می دهد. اگر، برای مثال، نرخ سالانه استفاده می شود، اما وام به صورت ماهانه پرداخت می شود، سپس نرخ سالانه باید به 12 تقسیم شود و نتیجه به دست آمده باید به عنوان یک استدلال استفاده شود. اگر روش پرداخت سه ماهه استفاده شود، در این مورد نرخ سالانه باید به 4 و غیره تقسیم شود.
"Nper" به تعداد کل دوره بازپرداخت وام اشاره دارد. بدین معنی که اگر وام به مدت یک سال با پرداخت ماهانه به اتمام برسد، در صورتی که برای دو سال تعداد دوره ها 12 باشد ، تعداد دوره ها 24 است . اگر وام به مدت دو سال با پرداخت سه ماهه برداشته شود، تعداد دوره ها 8 است .
"Ps" ارزش فعلی را نشان می دهد. به عبارت ساده، این مبلغ کل مبلغ وام در ابتدای وام است، یعنی مبلغی که قرض گرفته اید، به استثنای بهره و سایر پرداخت های اضافی.
"B" ارزش آتی است. این ارزش، که در زمان تکمیل قرارداد وام، وام و وام است. در بیشتر موارد، این استدلال برابر با "0" است ، زیرا وام گیرنده در پایان مدت وام باید مبلغ کامل را به وام دهنده پرداخت کند. استدلال مشخص اختیاری است. بنابراین، اگر آن را کاهش می دهد، آن را به صفر است.
آرگومان نوع زمان محاسبه را تعیین می کند: در پایان یا در آغاز یک دوره. در اولین مورد، مقدار "0" طول می کشد و در مرحله دوم - "1" . اکثر موسسات بانکی دقیقا گزینه ای را با پرداخت در پایان دوره استفاده می کنند. این استدلال نیز اختیاری است، و اگر شما آن را حذف کنید، در نظر گرفته شده است که آن صفر است.
اکنون زمان آن است که به یک مثال خاص از محاسبه هزینه ماهانه با استفاده از عملکرد PMT بروید. برای محاسبه، ما با استفاده از جدول با داده های اولیه، که در آن نرخ بهره وام ( 12٪ )، مبلغ وام ( 500،000 روبل ) و مدت وام ( 24 ماه ) نشان داده شده است. در این مورد، پرداخت در ماه هر سال انجام می شود.
- عنصر در ورق که در آن نتیجه محاسبات نمایش داده می شود، را انتخاب کنید و بر روی نماد تابع Insert واقع در نوار فرمول قرار دهید.
- جادوگر تابع راه اندازی شده است. در بخش "مالی" نام "PMT" را انتخاب کرده و روی دکمه "OK" کلیک کنید.
- بعد از آن، پنجره استدلال اپراتور PMT باز می شود.
در فیلد «Rate» باید مبلغ سود مورد نظر برای دوره را وارد کنید. این می تواند به سادگی با قرار دادن یک درصد به صورت دستی انجام شود، اما در اینجا آن را در یک سلول جداگانه در صفحه ذکر شده است، بنابراین به آن لینک خواهیم داد. مکان نما را در قسمت تنظیم کنید و سپس روی سلول مناسب کلیک کنید. اما، به یاد داشته باشید، نرخ بهره سالانه را در جدول داریم و دوره پرداخت معادل یک ماه است. بنابراین، نرخ سالانه را تقسیم می کنیم، یا به عبارتی ارجاع به سلول که در آن قرار دارد، با شماره 12 ، مربوط به تعداد ماه در یک سال است. تقسیم به طور مستقیم در زمینه پنجره استدلال انجام می شود.
در حوزه "Nper" دوره وام را تعیین می کند. ما آن را 24 ماه است. شما می توانید شماره 24 را به صورت دستی وارد کنید، اما، همانطور که در مورد قبلی، مرجع مکان این شاخص در جدول منبع را نشان می دهد.
در "PS" ارزش اولیه وام را نشان می دهد. این برابر است با 500000 روبل . همانطور که در موارد قبلی، لینک را به عنصر ورق حاوی این شاخص نشان می دهیم.
در بخش "BS" مقدار وام بعد از پرداخت کامل آن مشخص می شود. به یاد داشته باشید، این مقدار تقریبا صفر است. شماره "0" را در این قسمت تنظیم کنید. اگر چه این استدلال را می توان در کل حذف کرد.
در قسمت "Type" ما در ابتدای یا پایان ماه پرداخت انجام می شود. ما، همانطور که در بیشتر موارد، آن را در پایان ماه ساخته شده است. بنابراین، عدد را به "0" تنظیم کنید . همانطور که در مورد استدلال قبلی است، در این زمینه شما می توانید هر چیزی را وارد کنید، و سپس به طور پیش فرض برنامه فرض می کند که یک مقدار برابر با صفر است.
پس از وارد کردن تمام داده ها، روی دکمه «OK» کلیک کنید.
- بعد از این نتیجه محاسبات در سلول نمایش داده می شود که ما در بخش اول این کتابچه انتخاب کردیم. همانطور که می بینید، ارزش مجموع پرداخت ماهانه به وام 23،536.74 روبل است . با علامت "-" در مقابل این مقدار اشتباه نکنید. بنابراین اکسل اشاره می کند که این جریان نقدی است، یعنی از دست دادن.
- به منظور محاسبه کل مبلغ پرداخت برای کل دوره وام، با توجه به بازپرداخت وام و ماهیت بهره، کافی است که مبلغ پرداخت ماهانه ( 23،536.74 روبل ) را با تعداد ماهها ( 24 ماه ) ضرب کنید. همانطور که می بینید، کل مبلغ پرداختی برای کل مدت وام در پرونده ما به 564،881.67 روبل رسید .
- اکنون می توانید مبلغ بازپرداخت وام را محاسبه کنید. برای این که شما باید از مبلغ کل پرداخت وام، از جمله وام و وام، مبلغ اولیه وام گرفته شده را بردارید. اما ما به یاد می آوریم که اول از این مقادیر با "-" مشخص شده است. بنابراین، در مورد خاص ما، معلوم می شود که آنها باید به هم خورده شوند. همانطور که می بینید، کل مبلغ بازپرداخت وام برای کل دوره به 64،881.67 روبل رسید .
مرحله 2: جزئیات پرداخت
در حال حاضر، با کمک اپراتورهای دیگر، اکسل جزئیات ماهانه را پرداخت می کند تا ببیند چه مقدار در ماه خاصی برای وام پرداخت می شود و چه میزان مبلغ سود. برای این منظور، ما در اکسل یک جدول را می خواهیم که با داده ها پر می کنیم. ردیف های این جدول به دوره مربوطه، یعنی ماه، مربوط می شود. با توجه به اینکه دوره اعتباری در کشور ما 24 ماه است، تعداد خطوط نیز مناسب خواهد بود. ستون ها نشان می دهد که پرداخت وام بدن، پرداخت بهره، مجموع پرداخت ماهانه، که مجموع دو ستون قبلی است، و همچنین مبلغ باقیمانده پرداخت می شود.
- برای تعیین میزان پرداخت در قسمت وام ما از تابع OSPLT استفاده می کنیم که برای این منظور در نظر گرفته شده است. مکان نما را در سلول قرار دهید، که در ردیف "1" قرار دارد و در ستون "پرداخت در قسمت وجه" قرار دارد . بر روی دکمه "درج تابع" کلیک کنید .
- به کارشناسی ارشد توابع بروید در بخش "مالی"، نام "OSPLT" را علامت بزنید و دکمه "OK" را کلیک کنید.
- پنجره استدلال اپراتور OSPLT راه اندازی شده است. این نحو زیر دارد:
=ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)همانطور که می بینید، استدلالات این تابع تقریبا به طور کامل با استدلال عملگر PMT همخوانی دارد، فقط به جای استدلال اختیاری "Type" استدلال واجب "Period" اضافه می شود. این نشان می دهد که تعداد دوره بازپرداخت و در مورد خاص ما، تعداد ماه است.
ما فیلدهای پنجره عملکرد OSPL را که قبلا برای ما آشناست، با داده های مشابهی که برای عملکرد PMT استفاده می شود، پر می کنیم. تنها با توجه به این واقعیت است که در آینده، کپی کردن فرمول از طریق نشانگر پر شدن، مورد استفاده قرار می گیرد، لازم است تمام پیوندهای موجود در زمینه ها را به طور کامل حذف کنیم تا آنها تغییر نکنند. برای انجام این کار، باید علامت دلار را در مقابل هر مقدار مختصات به صورت عمودی و افقی قرار دهید. اما ساده تر این کار را با انتخاب مختصات و فشار دادن کلید F4 انجام می دهید. علامت دلار به صورت خودکار در مکان های مناسب قرار می گیرد. همچنین فراموش نکنید که نرخ سالانه باید به 12 برسد .
- اما ما با یک استدلال جدید دیگر که عملکرد PMT ندارد، باقی مانده است. این استدلال "دوره" است . در فیلد مربوطه، لینک را به اولین سلول ستون Period اضافه می کنیم. این عنصر از برگه شامل شماره "1" است که نشان دهنده شماره اول ماه اعتبار است. اما بر خلاف زمینه های قبلی، در زمینه مشخص شده نسبیت پیوند را ترک می کنیم و از مرجع کامل آن را محاسبه نمی کنیم.
پس از وارد کردن تمام اطلاعاتی که ما در مورد بالا صحبت کردیم، روی دکمه OK کلیک کنید.
- پس از آن، در سلولی که ما قبلا اختصاص داده بودیم، مبلغ پرداختی در بخش وام برای ماه اول نمایش داده خواهد شد. این 18،536.74 روبل خواهد بود.
- سپس، همانطور که در بالا ذکر شد، باید این فرمول را با سلول های باقی مانده ستون با استفاده از نشانگر پر کنید. برای انجام این کار، مکان نما را در گوشه پایین سمت راست سلول که حاوی فرمول است قرار دهید. مکان نما به یک صلیب تبدیل می شود که نشانگر پر است. دکمه سمت چپ ماوس را پایین نگه داشته و آن را به انتهای جدول بکشید.
- در نتیجه، تمام سلول های ستون پر می شوند. اکنون ما یک برنامه بازپرداخت وام ماهانه داریم. همانطور که در بالا ذکر شد، میزان پرداخت این مقاله با هر دوره جدید افزایش می یابد.
- حالا ما باید یک محاسبه ماهانه از پرداخت سود انجام دهیم. برای این منظور، از PELP اپراتور استفاده خواهیم کرد . سلول اول خالی را در ستون "پرداخت سود" انتخاب کنید . بر روی دکمه "درج تابع" کلیک کنید .
- در پنجره راه اندازی جادوگر تابع در بخش "مالی" ، نام PDLP را انتخاب می کنیم . با کلیک بر روی دکمه "OK" یک کلیک انجام دهید.
- پنجره تابع PRLPT شروع می شود . نحو آن به شرح زیر است:
=ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)همانطور که می بینید، استدلال های این تابع کاملا با اپراتور OSPLT مشابه هستند. بنابراین، ما به سادگی وارد همان پنجره ای می کنیم که در پنجره استدلال قبلی وارد شده ایم. ما فراموش نکنیم که لینک در قسمت "دوره" باید نسبی باشد و در تمام زمینه های دیگر مختصات باید به شکل مطلق کاهش یابد. پس از آن روی دکمه «OK» کلیک کنید.
- سپس نتیجه محاسبه میزان پرداخت بهره وام برای ماه اول در سلول مناسب نمایش داده می شود.
- پس از استفاده از نشانگر پر کردن، ما یک کپی از فرمول را به عناصر باقی مانده ستون می دهیم، بدین ترتیب یک برنامه پرداخت ماهیانه برای بهره وام به دست می آوریم. همانطور که قبلا ذکر شد، ارزش این نوع پرداخت از ماه به ماه کاهش می یابد.
- حالا ما باید کل مبلغ ماهانه را محاسبه کنیم. برای این محاسبه، شما نباید به یک اپراتور متوسل شوید، زیرا میتوانید از یک فرمول حساب ساده استفاده کنید. ما محتویات سلول های ماه اول ستون ها "پرداخت در قسمت وام" و "پرداخت درمورد" را اضافه می کنیم . برای انجام این کار، علامت "=" را در سلول اول خالی ستون "Total monthly payment" تنظیم کنید . سپس بر روی دو عنصر بالا کلیک کنید و علامت + را بین آنها قرار دهید. روی کلید Enter کلیک کنید .
- بعد، با استفاده از نشانگر پر، همانطور که در موارد قبلی، ستون را با داده ها پر کنید. همانطور که می بینید، در طول مدت قرارداد، مبلغ کل مبلغ ماهانه، از جمله پرداخت به وام بدن و بهره، 23،536.74 روبل خواهد بود. در واقع، ما قبلا با استفاده از PMT این رقم را محاسبه کردیم. اما در این مورد، آن را به وضوح ارائه شده است، دقیقا به عنوان مقدار پرداخت در بدن از وام و بهره.
- حالا شما نیاز به اضافه کردن اطلاعات به ستون که در آن تعادل مبلغ وام که هنوز هم باید پرداخت شود، ماهانه نمایش داده می شود. در اولین سلول ستون "تعادل قابل پرداخت" محاسبه ساده ترین خواهد بود. ما باید از اندازه اولیه وام، که در جدول با داده های اولیه، پرداخت در بخش وام برای ماه اول در جدول محاسبه شده است، را برداریم. اما، با توجه به این واقعیت که یکی از اعداد ما در حال حاضر با نشانه "-" آمده است، پس آنها را نباید برداشته شود، بلکه برعکس. این کار را انجام دهید و روی دکمه Enter کلیک کنید.
- اما محاسبه تعادل که بعد از ماه های دوم و بعد پرداخت می شود، تا حدودی دشوار خواهد بود. برای انجام این کار، ما باید ابتدا وام از ابتدای قرض دادن کل مبلغ پرداخت وجه وام را برای دوره قبلی دور کنیم. علامت "=" را در سلول دوم ستون "Balance payable" تنظیم کنید . بعد، لینک به سلول که شامل مبلغ اولیه وام اولیه است مشخص کنید. ما با انتخاب و فشار دادن کلید F4 مطلق می کنیم. سپس علامت "+" را وارد میکنیم، زیرا مقدار دوم برای ما منفی است. پس از آن بر روی دکمه "درج عملکرد" کلیک کنید .
- کارشناسی ارشد توابع راه اندازی شده است، که در آن شما نیاز به حرکت به رده "ریاضی" . در اینجا کتیبه "SUM" را انتخاب می کنیم و روی دکمه "OK" کلیک کنید.
- پنجره argument از تابع SUM راه اندازی می شود. اپراتور مشخص شده برای خلاصه کردن داده ها در سلول استفاده می شود که ما باید در ستون «پرداخت در قسمت وام» انجام دهیم . این نحو زیر دارد:
=СУММ(число1;число2;…)این استدلالها مراجع سلولی هستند که حاوی اعداد هستند. ما مکان نما را در فیلد "شماره 1" قرار می دهیم. سپس دکمه سمت چپ ماوس را نگه دارید و دو سلول اول ستون "پرداخت وجه از وام" را در برگه انتخاب کنید. همانطور که می بینید، لینک به محدوده در این زمینه نمایش داده می شود. این شامل دو بخش است که توسط یک کولون جدا شده اند: اشاره به سلول اول دامنه و آخرین. برای اینکه قادر به کپی فرمول نشان داده شده در آینده با استفاده از نشانگر پر شدن باشد، بخش اول مرجع را به محدوده مطلق می رسانیم. آن را انتخاب کنید و بر روی کلید عملکرد F7 کلیک کنید. بخش دوم لینک است نسبی است. در حال حاضر هنگام استفاده از نشانگر پر کننده، اولین سلول محدوده ثابت خواهد شد و آخرین بقیه در صورت حرکت به پایین کشید. این چیزی است که ما باید برای رسیدن به اهدافمان انجام دهیم. بعد، روی دکمه "OK" کلیک کنید.
- بنابراین، نتیجه تعادل اعتبار بدهی پس از ماه دوم در سلول نمایش داده می شود. اکنون، با شروع از این سلول، ما فرمول را به عناصر خالی ستون با استفاده از نشانگر پر می کنیم.
- محاسبه ماهانه از تعادل در پرداخت وام ساخته شده برای کل دوره اعتبار. همانطور که باید باشد، در پایان مدت این مقدار صفر است.
بنابراین، ما نه تنها محاسبه پرداخت وام را انجام دادیم، بلکه یک نوع ماشین حساب وام را نیز سازماندهی کردیم. کدام یک تحت برنامه سالانه کار خواهد کرد. اگر در جدول منبع ما، به عنوان مثال، مقدار وام و نرخ بهره سالانه را تغییر دهید، سپس در جدول نهایی، داده ها به صورت خودکار محاسبه می شوند. بنابراین، می توان آن را نه تنها برای یک مورد خاص استفاده کرد، بلکه می تواند در موقعیت های مختلف برای محاسبه گزینه های اعتباری با توجه به طرح سالیانه استفاده شود.
همانطور که می بینید، با استفاده از اکسل در خانه، شما می توانید به طور کلی پرداخت وام به صورت ماهانه را با استفاده از طرح سالانه، با استفاده از اپراتور PMT برای این منظور محاسبه کنید. علاوه بر این، با استفاده از عملکردهای OSPLT و PRLPT، می توان میزان پرداخت ها را به صورت وام و همچنین سود برای یک دوره معین محاسبه کرد. با استفاده از تمام این دسته از توابع با هم، ممکن است یک ماشین حساب وام قدرتمند است که می تواند بیش از یک بار برای محاسبه پرداخت سالیانه استفاده می شود.