قبل از گرفتن وام، محاسبه تمام پرداخت ها بر روی آن بسیار راحت خواهد بود. این امر موجب صرفه جویی در وام گیرنده در آینده از مشکلات و ناامیدی های غیرمنتظره می شود، زمانی که معلوم شود که بازپرداخت بیش از حد زیاد است. اکسل ابزار می تواند در این محاسبه کمک کند. بیایید نحوه محاسبه پرداخت وام قرضه را در این برنامه بیابیم.
محاسبه پرداخت
اولا باید بگویم دو نوع پرداخت اعتباری وجود دارد:
- تمایز
- انعطاف پذیری
با یک طرح متمایز، مشتری سهم ماهیانه از پرداخت به بانک را در صندوق وام به همراه پرداخت بهره پرداخت می کند. مبلغ پرداخت بهره هر ماه کاهش می یابد به عنوان بدن وام که از آن محاسبه می شود کاهش می یابد. بنابراین مبلغ کلی ماهانه نیز کاهش می یابد.
یک طرح سالانه از یک روش کمی متفاوت استفاده می کند. مشتری ماهانه همان مبلغ کل مبلغ پرداختی را تشکیل می دهد که شامل پرداخت ها در بخش وام و پرداخت بهره است. در ابتدا، پرداخت های بهره به میزان کامل وام پرداخت می شود، اما با کاهش هزینه، بهره کاهش می یابد. اما مبلغ کل پرداخت به علت افزایش ماهانه در پرداخت ها در بخش وام باقی می ماند. بنابراین، با گذشت زمان، نسبت بهره در کل پرداخت ماهانه کاهش می یابد، در حالی که نسبت پرداخت در هر بدن افزایش می یابد. در عین حال، مجموع پرداخت ماهانه خود را در طول دوره کل وام تغییر نمی کند.
فقط در محاسبه پرداخت سالیانه، ما متوقف می شویم. علاوه بر این، این مربوط است، زیرا در حال حاضر اکثر بانک ها از این طرح خاص استفاده می کنند. همچنین برای مشتریان مناسب است، زیرا در این صورت، کل مبلغ پرداخت تغییر نمی کند، باقی مانده ثابت می شود. مشتریان همیشه می دانند چقدر هزینه می کنند.
مرحله 1: محاسبه هزینه ماهانه
برای محاسبه هزینه ماهانه هنگام استفاده از طرح سالانه در Excel، یک تابع خاص وجود دارد - PMT. آن متعلق به دسته اپراتورهای مالی است. فرمول این تابع به شرح زیر است:
= PMT (نرخ؛ nper؛ ps؛ bs؛ نوع)
همانطور که می بینید، تابع مشخص شده تعداد زیادی از استدلال ها را دارد. درست است که دو نفر از آنها اجباری نیستند.
اظهار نظر "شرط" نرخ بهره را برای یک دوره مشخص نشان می دهد. اگر، برای مثال، نرخ سالانه استفاده می شود، اما وام به صورت ماهیانه پرداخت می شود، و سپس نرخ سالانه باید تقسیم شود 12 و از نتیجه به عنوان یک بحث استفاده کنید. اگر روش پرداخت سه ماهه استفاده شود، در این مورد نرخ سالانه باید تقسیم شود 4 و غیره
"Kper" تعداد کل دوره بازپرداخت وام را مشخص می کند. بدین ترتیب، اگر وام برای یک سال با پرداخت ماهیانه گرفته شود، سپس تعداد دوره ها در نظر گرفته می شود 12اگر برای دو سال، پس از آن تعداد دوره ها است 24. اگر وام به مدت دو سال با پرداخت سه ماهه برداشته شود، تعداد دوره ها برابر است 8.
"Ps" ارزش فعلی را نشان می دهد. به عبارت ساده، این مبلغ کل وام در ابتدای وام است، یعنی مبلغی که قرض گرفته اید، به استثنای بهره و سایر پرداخت های اضافی.
"ب" - این ارزش آینده است. این ارزش، که در زمان تکمیل قرارداد وام، وام و وام است. در بیشتر موارد این استدلال است "0"، از آنجا که قرض دهنده در پایان دوره وام باید به طور کامل از وام دهنده پرداخت. استدلال مشخص اختیاری است بنابراین، اگر آن را کاهش می دهد، آن را به صفر است.
اظهار نظر "نوع" زمان محاسبه را تعیین می کند: در پایان یا در آغاز دوره. در اولین مورد، مقدار آن طول می کشد "0"، و در دوم - "1". اکثر موسسات بانکی دقیقا گزینه ای را با پرداخت در پایان دوره استفاده می کنند. این استدلال نیز اختیاری است، و اگر شما آن را حذف کنید، در نظر گرفته شده است که آن صفر است.
اکنون زمان آن است که به یک مثال خاص از محاسبه هزینه ماهیانه با استفاده از عملکرد PMT بروید. برای محاسبه، ما از یک جدول با داده های اصلی استفاده می کنیم که در آن نرخ بهره وام مشخص شده است (12%)، مبلغ وام (500،000 روبل) و مدت وام (24 ماه) در این مورد، پرداخت در ماه هر ماه انجام می شود.
- عنصر در ورق که در آن نتیجه محاسبات نمایش داده خواهد شد را انتخاب کنید و روی نماد کلیک کنید "قرار دادن عملکرد"در نزدیکی نوار فرمول واقع شده است.
- پنجره راه اندازی شده است. کارشناسی ارشد. در رده "مالی" نام را انتخاب کن "PLT" و بر روی دکمه کلیک کنید "خوب".
- پس از آن، پنجره استدلال اپراتور باز می شود. PMT.
در حوزه "شرط" باید مقدار سود را برای دوره وارد کنید. این را می توان به صورت دستی با صرفه جویی درصد، اما در اینجا آن را در یک سلول جداگانه در ورق فهرست ذکر شده است، بنابراین ما یک لینک به آن اشاره کرد. مکان نما را در قسمت تنظیم کنید و سپس روی سلول مربوطه کلیک کنید. اما، به یاد داشته باشید، نرخ بهره سالانه را در جدول داریم و دوره پرداخت با یک ماه برابر است. بنابراین، ما نرخ سالانه را تقسیم می کنیم، یا به عبارتی مرجع به سلول که در آن است، بر اساس تعداد 12مربوط به تعداد ماهها در یک سال است. تقسیم به طور مستقیم در زمینه پنجره استدلال انجام می شود.
در حوزه "Kper" دوره وام را تنظیم کنید. او با ما برابر است 24 ماه ها شما می توانید یک عدد را در فیلد وارد کنید 24 به صورت دستی، اما ما، همانطور که در مورد قبلی، یک لینک را به محل این شاخص در جدول اصلی ارائه می دهیم.
در حوزه "Ps" ارزش اولیه وام را نشان می دهد. او برابر است 500،000 روبل. همانطور که در موارد قبلی، اشاره به عنصر ورق حاوی این شاخص را نشان می دهد.
در حوزه "ب" بعد از پرداخت کامل مقدار وام را نشان می دهد. به یاد داشته باشید، این مقدار تقریبا صفر است. شماره را در این فیلد قرار دهید "0". اگر چه این استدلال را می توان کاملا حذف کرد.
در حوزه "نوع" نشان می دهد در آغاز و یا در پایان ماه پرداخت ساخته شده است. ما، همانطور که در بیشتر موارد، آن را در پایان ماه ساخته شده است. بنابراین، شماره را تنظیم کنید "0". همانطور که در مورد استدلال قبلی است، در این زمینه شما می توانید هر چیزی را وارد کنید، و سپس برنامه به طور پیش فرض فرض می شود که یک مقدار صفر است.
پس از وارد شدن تمام داده ها، بر روی دکمه کلیک کنید "خوب".
- پس از این، نتیجه محاسبه در سلول ما در بخش اول این کتابچه انتخاب شده نمایش داده می شود. همانطور که می بینید، ارزش مبلغ پرداخت کلی وام ماهانه است 23536.74 روبل. نشانه "-" در برابر این مقدار اشتباه نکنید. بنابراین اکسل اشاره می کند که این جریان نقدی است، یعنی از دست دادن.
- برای محاسبه کل مبلغ پرداختی برای کل دوره وام، با توجه به بازپرداخت وام صندوق و بهره ماهانه، کافی است که مقدار مبلغ ماهانه (23536.74 روبل) برای چند ماه (24 ماه) همانطور که می بینید، کل مبلغ پرداختی برای کل مدت وام در مورد ما به مبلغ 564881.67 روبل.
- اکنون می توانید مبلغ بازپرداخت وام را محاسبه کنید. برای این منظور، باید مبلغ اولیه وام گرفته شده را از مبلغ کل پرداخت وام، از جمله وام و وام بدست آورید. اما ما به یاد می آوریم که اول از این ارزش ها قبلا ثبت شده است "-". بنابراین، در مورد خاص ما، معلوم می شود که آنها باید به هم خورده شوند. همانطور که می بینید، کل مبلغ بازپرداخت وام برای کل دوره به مبلغ 64881.67 روبل.
درس: جادوگر عملکرد اکسل
مرحله 2: جزئیات پرداخت
در حال حاضر، با کمک اپراتورهای دیگر، اکسل جزئیات ماهانه پرداخت می کند تا ببیند چه مقدار در ماه خاصی برای وام پرداخت می شود و چه مقدار سود. برای این اهداف، ما در اکسل یک جدول ارائه می کنیم که با داده ها پر می کنیم. ردیف های این جدول به دوره مربوطه، یعنی ماه، مربوط می شود. با توجه به اینکه دوره اعتبار ما این است 24 ماه، سپس تعداد ردیف نیز مناسب خواهد بود. ستون ها نشان می دهد که پرداخت وام بدن، پرداخت بهره، کل مبلغ ماهانه، که مجموع دو ستون قبلی است، و همچنین مبلغ باقیمانده پرداخت می شود.
- برای تعیین مقدار پرداخت در بدن از وام استفاده از تابع OSPLTکه فقط برای این منظور اهمیت دارد. مکان نما در سلول، که در خط قرار دارد "1" و در ستون "پرداخت در بدن از وام". ما دکمه را فشار می دهیم "قرار دادن عملکرد".
- به جادوگر تابع. در رده "مالی" علامت گذاری به نام OSPLT و دکمه را فشار دهید "خوب".
- پنجره استدلال اپراتور OSPLT راه اندازی شده است. این نحو زیر دارد:
= OSPLT (نرخ؛ دوره؛ Kper؛ PS؛ B)همانطور که می بینید، استدلالات این تابع تقریبا به طور کامل با استدلال های اپراتور همخوانی دارد PMT، فقط به جای استدلال اختیاری "نوع" افزوده مورد نیاز "دوره". این نشان می دهد که تعداد دوره بازپرداخت و در مورد خاص ما، تعداد ماه است.
فیلدهای پنجره تابع آشنا را پر کنید OSPLT داده های مشابه که برای عملکرد استفاده شد PMT. تنها با توجه به این واقعیت که در آینده، کپی کردن فرمول از طریق نشانگر پر کردن استفاده می شود، لازم است همه لینک ها را در زمینه ها به طور کامل حذف کنیم تا آنها تغییر نکنند. برای انجام این کار، باید علامت دلار را در مقابل هر مقدار مختصات به صورت عمودی و افقی قرار دهید. اما ساده تر این است که این کار را با انتخاب مختصات و فشار دادن کلید عملکرد انجام دهید. F4. علامت دلار به صورت خودکار در مکان های مناسب قرار می گیرد. همچنین فراموش نکنید که نرخ سالانه باید تقسیم شود 12.
- اما ما با یک استدلال جدید کنار گذاشته شدیم که عملکردی نداشت PMT. این استدلال "دوره". در فیلد مربوطه، لینک را به اولین سلول ستون تنظیم می کنیم. "دوره". این آیتم حاوی یک عدد است "1"که نشان دهنده تعداد ماه اول اعتبار است. اما بر خلاف فیلدهای قبلی، در زمینه مشخص شده، نسب پیوند را ترک می کنیم و از آن مرجع مطلق نیستیم.
پس از وارد شدن تمام اطلاعاتی که ما در مورد آن صحبت کردیم، بر روی دکمه کلیک کنید "خوب".
- پس از آن، در سلولی که ما قبلا اختصاص داده بودیم، مبلغ پرداختی به وام برای ماه اول نمایش داده خواهد شد. او خواهد ساخت 18536.74 روبل.
- سپس، همانطور که در بالا ذکر شد، ما باید این فرمول را به سلول های باقی مانده ستون با استفاده از نشانگر پر کنید کپی کنید. برای انجام این کار، مکان نما را در گوشه پایین سمت راست سلول قرار دهید که شامل فرمول است. سپس مکان نما به یک صلیب تبدیل می شود که نشانگر پر می شود. پایین دکمه سمت چپ ماوس را نگه دارید و آن را به انتهای جدول بکشید.
- در نتیجه، تمام سلول های ستون پر می شوند. اکنون ما یک برنامه بازپرداخت وام ماهانه داریم. همانطور که در بالا ذکر شد، میزان پرداخت این مقاله با هر دوره جدید افزایش می یابد.
- اکنون ما باید یک محاسبه ماهانه از پرداخت سود انجام دهیم. برای این منظور ما از اپراتور استفاده خواهیم کرد PRPLT. اولین سلول خالی در ستون را انتخاب کنید. "پرداخت بهره". ما دکمه را فشار می دهیم "قرار دادن عملکرد".
- در پنجره راه اندازی کارشناسی ارشد در رده "مالی" انتخاب نام را انتخاب کنید PRPLT. یک کلیک بر روی دکمه انجام دهید. "خوب".
- پنجره های آرگومان تابع شروع می شود. PRPLT. نحو آن به شرح زیر است:
= PRPLT (نرخ؛ دوره؛ Kper؛ PS؛ B)همانطور که می بینید، استدلال های این تابع کاملا مشابه با اپراتور است OSPLT. بنابراین، ما به سادگی در پنجره همان اطلاعاتی را که ما در پنجره استدلال قبلی وارد کرده ایم وارد کنید. فراموش نکنید که لینک در این زمینه "دوره" باید نسبی باشد و در همه زمینه های دیگر مختصات باید به شکل مطلق کاهش یابد. پس از آن روی دکمه کلیک کنید "خوب".
- سپس نتیجه محاسبه میزان پرداخت بهره وام برای ماه اول در سلول مناسب نمایش داده می شود.
- با استفاده از نشانگر پر کردن، ما یک کپی از فرمول را به عناصر باقی مانده ستون ایجاد می کنیم، بدین ترتیب یک برنامه پرداخت ماهانه برای بهره وام دریافت می کنیم. همانطور که قبلا ذکر شد، ارزش این نوع پرداخت را از ماه به ماه کاهش می دهد.
- اکنون ما باید کل مبلغ ماهانه را محاسبه کنیم. برای این محاسبه، شما نباید به یک اپراتور متوسل شوید، زیرا می توانید یک فرمول حساب ساده استفاده کنید. محتویات سلولهای ماه اول ستون ها را مرتب کنید "پرداخت در بدن از وام" و "پرداخت بهره". برای این کار علامت را تنظیم کنید "=" در اولین سلول ستون خالی "پرداخت ماهانه کل". سپس بر روی دو عنصر بالا کلیک کنید، علامت آنها را بین آنها قرار دهید "+". ما بر روی کلید فشار می دهیم وارد شوید.
- بعد، با استفاده از نشانگر پر، همانطور که در موارد قبلی، ستون را با داده ها پر کنید. همان طور که می بینید، در طول مدت قرارداد، مبلغ کل مبلغ ماهانه، از جمله پرداخت به صورت وام و پرداخت بهره، خواهد بود 23536.74 روبل. در واقع ما قبلا با کمک این رقم محاسبه کردیم PMT. اما در این مورد، آن را به وضوح ارائه شده است، دقیقا به عنوان مقدار پرداخت در بدن از وام و بهره.
- اکنون شما نیاز به اضافه کردن اطلاعات به ستون که در آن تعادل مبلغ وام که هنوز هم باید پرداخت شود، ماهانه نمایش داده می شود. در سلول اول ستون "تعادل قابل پرداخت" محاسبه ساده ترین خواهد بود. ما باید از ارزش اولیه وام، که در جدول با داده های اولیه، پرداخت در بخش وام برای ماه اول در جدول محاسبه شده است، را برداریم. اما، با توجه به این واقعیت که یکی از اعداد ما در حال حاضر با علامت است "-"، آنها را نباید برداشته، بلکه بریزید. این کار را انجام دهید و روی دکمه کلیک کنید. وارد شوید.
- اما محاسبه تعادل که بعد از ماه های دوم و بعد پرداخت می شود، تا حدودی دشوار خواهد بود. برای انجام این کار، ما باید در ابتدای وام، مبلغ کل مبلغ پرداختی را در بخش وام برای دوره قبلی از وام بدست آوریم. علامت را تنظیم کنید "=" در سلول دوم ستون "تعادل قابل پرداخت". بعد، لینک به سلول که شامل مبلغ اولیه وام است، مشخص کنید. ما با انتخاب و فشار دادن کلید آن را مطلق می دانیم. F4. سپس علامت بزنید "+"، از آنجا که مقدار دوم برای ما منفی است. پس از آن روی دکمه کلیک کنید "قرار دادن عملکرد".
- شروع می شود جادوگر تابعکه در آن شما نیاز به حرکت به رده "ریاضی". در اینجا کتیبه را انتخاب می کنیم "SUMM" و بر روی دکمه کلیک کنید "خوب".
- پنجره argument function شروع می شود. مبلغ. اپراتور مشخص شده به منظور خلاصه کردن داده ها در سلول، که ما باید در ستون انجام دهیم، عمل می کنیم "پرداخت در بدن از وام". این نحو زیر دارد:
= SUM (number1؛ number2؛ ...)استدلال ارجاع به سلول هایی است که حاوی اعداد هستند. ما مکان نما را در این زمینه قرار داده ایم. "شماره 1". سپس دکمه سمت چپ ماوس را نگه دارید و دو سلول اول ستون روی برگ را انتخاب کنید. "پرداخت در بدن از وام". همانطور که می بینید، لینک به محدوده در این زمینه نمایش داده می شود. این شامل دو قسمت است که توسط یک کولون جدا شده اند: اشاره به سلول اول محدوده و آخرین. برای اینکه قادر به کپی فرمول نشان داده شده در آینده با استفاده از نشانگر پر شدن باشد، بخش اول مرجع را به محدوده مطلق می رسانیم. آن را انتخاب کنید و بر روی کلید تابع کلیک کنید. F4. بخش دوم لینک است نسبی است. در حال حاضر هنگام استفاده از نشانگر پر، اولین سلول محدوده ثابت خواهد شد و آخرین بقیه به عنوان حرکت می کند. این چیزی است که ما باید به اهدافمان برسیم. بعد، روی دکمه کلیک کنید "خوب".
- بنابراین، نتیجه تعادل اعتبار بدهی پس از ماه دوم در سلول نمایش داده می شود. حالا با شروع از این سلول، ما فرمول را به عناصر خالی ستون با استفاده از نشانگر پر می کنیم.
- محاسبه ماهانه وام های وام برای کل دوره اعتبار ساخته شده است. همانطور که باید باشد، در پایان مدت این مقدار صفر است.
بنابراین، ما نه تنها محاسبه پرداخت وام را انجام دادیم، بلکه نوعی ماشین حساب وام را نیز سازمان دادیم. کدامیک تحت طرح درآمد سالانه کار خواهد کرد. اگر در جدول منبع ما، به عنوان مثال، اندازه وام و نرخ بهره سالانه را تغییر دهید، سپس در جدول نهایی، داده ها به صورت خودکار محاسبه می شوند. بنابراین، می توان آن را نه تنها برای یک مورد خاص استفاده کرد، بلکه می تواند در موقعیت های مختلف برای محاسبه گزینه های وام با استفاده از طرح سالانه استفاده شود.
درس: توابع مالی در اکسل
همانطور که می بینید، با استفاده از اکسل در خانه، شما می توانید به طور کلی پرداخت وام ماهانه با استفاده از طرح سالانه، با استفاده از اپراتور PMT. علاوه بر این، با کمک توابع OSPLT و PRPLT شما می توانید مبلغ پرداختی را در مورد وام و سود برای مدت مشخص شده محاسبه کنید. با استفاده از تمام این دسته از توابع با هم، این امکان ایجاد یک ماشین حساب وام قدرتمند است که می تواند بیش از یک بار برای محاسبه پرداخت سالیانه استفاده می شود.
