قبل از گرفتن وام ، خوب خواهد بود که تمام پرداخت های مربوط به آن را محاسبه کنید. این امر باعث می شود که وام گیرنده در آینده از مشکلات و ناامیدی های مختلف غیر منتظره ای صرفه جویی کند وقتی معلوم شود که اضافه پرداخت بیش از حد بزرگ است. ابزارهای اکسل می توانند به این محاسبه کمک کنند. بیایید نحوه محاسبه پرداخت وام سالانه را در این برنامه دریابیم.
محاسبه پرداخت
اول از همه ، باید گفت كه دو نوع پرداخت وام وجود دارد:
- تمایز؛
- سالانه
در یک طرح متفاوت ، مشتری مبلغ مساوی مبلغ پرداختی را بر روی وام به علاوه پرداخت بهره به صورت ماهیانه به بانک می پردازد. میزان پرداخت بهره هرماه کاهش می یابد ، زیرا بدنه وامی که از آن محاسبه می شود کاهش می یابد. بنابراین ، کل پرداخت ماهانه نیز کاهش می یابد.
یک طرح سالیانه از یک رویکرد کمی متفاوت استفاده می کند. مشتری ماهانه همان مبلغ کل مبلغ پرداختی را که شامل پرداختهای مربوط به بدنه وام و پرداختهای بهره است انجام می دهد. در ابتدا ، پرداخت بهره برای کل مبلغ وام محاسبه می شود ، اما با کاهش بدنه ، تعهدات کاهش می یابد. اما به دلیل افزایش ماهانه مبلغ پرداختی بر روی وام ، مبلغ کل پرداخت بدون تغییر باقی می ماند. بنابراین ، با گذشت زمان ، درصد علاقه به کل پرداخت ماهانه کاهش می یابد و نسبت پرداخت توسط بدن افزایش می یابد. علاوه بر این ، کل پرداخت ماهانه خود در کل مدت وام تغییر نمی کند.
فقط با محاسبه پرداخت سالیانه ، ما متوقف خواهیم شد. علاوه بر این ، این موضوع قابل توجه است ، زیرا در حال حاضر بیشتر بانک ها از این طرح خاص استفاده می کنند. این برای مشتریان راحت است ، زیرا در این حالت کل مبلغ پرداخت تغییر نمی کند ، ثابت باقی می ماند. مشتریان همیشه می دانند که چقدر باید بپردازند.
مرحله 1: محاسبه اقساط ماهانه
برای محاسبه سهم ماهانه هنگام استفاده از طرح سالیانه در اکسل یک عملکرد ویژه وجود دارد - PMT. متعلق به رده اپراتورهای مالی است. فرمول این عملکرد به شرح زیر است:
= PLT (نرخ؛ nper؛ ps؛ bs؛ نوع)
همانطور که مشاهده می کنید ، این عملکرد دارای تعداد نسبتاً زیادی آرگومان است. درست است ، دو مورد آخر اختیاری هستند.
استدلال پیشنهاد نرخ بهره را برای یک دوره خاص نشان می دهد. به عنوان مثال ، اگر نرخ سالانه استفاده می شود ، اما وام ماهانه پرداخت می شود ، باید نرخ سالانه بر اساس تقسیم شود 12 و از نتیجه به عنوان یک استدلال استفاده کنید. اگر از نوع پرداخت سه ماهه استفاده شده باشد ، در این حالت نرخ سالانه باید با تقسیم شود 4 و غیره
"نپر" تعداد کل دوره های بازپرداخت وام را نشان می دهد. یعنی اگر یک وام با پرداخت ماهانه یک سال گرفته شود ، تعداد دوره ها در نظر گرفته می شود 12اگر به مدت دو سال باشد ، تعداد دوره ها است 24. اگر وام به مدت دو سال با پرداخت سه ماهه گرفته شود ، تعداد دوره ها برابر است 8.
روان مقدار فعلی را در حال حاضر نشان می دهد. به عبارت ساده ، این مبلغ کل وام در ابتدای وام است ، یعنی مبلغی که وام می گیرید ، بدون احتساب بهره و سایر پرداختهای اضافی.
"Bs" ارزش آینده است این مقدار ، که بدنه وام در زمان تکمیل توافق وام خواهد بود. در بیشتر موارد ، این استدلال است "0"، از آنجا که وام گیرنده در پایان مدت وام باید به طور کامل به وام دهنده پرداخت کند. آرگومان مشخص شده اختیاری است. بنابراین ، اگر افت کند ، برابر با صفر در نظر گرفته می شود.
استدلال "نوع" زمان محاسبه را تعیین می کند: در پایان یا در آغاز دوره. در حالت اول ، مقدار آن را می گیرد "0"و در دوم - "1". بیشتر موسسات بانکی دقیقاً از گزینه با پرداخت در پایان دوره استفاده می کنند. این استدلال نیز اختیاری است و در صورت حذف آن صفر در نظر گرفته می شود.
اکنون زمان آن رسیده است که با استفاده از عملکرد PMT به یک نمونه خاص از محاسبه اقساط ماهانه بروید. برای محاسبه ، ما از جدول با داده های منبع استفاده می کنیم ، جایی که نرخ سود وام مشخص شده است (12%) مبلغ وام (500000 روبل) و مدت وام (24 ماه) علاوه بر این ، پرداخت به صورت ماهانه در پایان هر دوره انجام می شود.
- عنصری را روی برگه ای که در آن نتیجه محاسبه نمایش داده می شود ، انتخاب کنید و بر روی نماد کلیک کنید "درج عملکرد"در نزدیکی نوار فرمول قرار داده شده است.
- پنجره راه اندازی شده است. جادوگران عملکرد. در دسته "مالی" نام را انتخاب کنید "PLT" و روی دکمه کلیک کنید "خوب".
- پس از آن ، پنجره آرگومانهای اپراتور باز می شود. PMT.
در زمینه پیشنهاد درصد را برای دوره وارد کنید. این کار به صورت دستی و به سادگی با تعیین درصد انجام می شود ، اما ما آن را در یک سلول جداگانه روی برگه مشخص کرده ایم ، بنابراین پیوندی به آن می دهیم. مکان نما را در قسمت فیلد قرار می دهیم و سپس بر روی سلول مربوطه کلیک می کنیم. اما ، همانطور که به یاد می آوریم ، در جدول ما نرخ سود سالانه تعیین شده است و مدت زمان پرداخت آن برابر با یک ماه است. بنابراین ، ما نرخ سالانه ، یا بهتر پیوند را به سلول موجود در آن ، به عدد تقسیم می کنیم 12مربوط به تعداد ماه در سال. تقسیم مستقیماً در قسمت پنجره آرگومان انجام می شود.
در زمینه "نپر" مدت وام تعیین شده است. او با ما برابر است 24 ماهها می توانید یک شماره را در قسمت وارد کنید 24 به صورت دستی ، اما ما ، مانند مورد قبلی ، پیوندی به موقعیت این نشانگر در جدول اصلی نشان می دهیم.
در زمینه روان مبلغ وام اولیه ذکر شده است. او برابر است 500000 روبل. مانند موارد قبلی ، پیوندی به عنصر ورق که در آن این شاخص وجود دارد ، نشان می دهیم.
در زمینه "Bs" مقدار وام را پس از پرداخت کامل نشان می دهد. همانطور که یادآوری می کنیم ، این مقدار تقریباً همیشه صفر است. شماره را در این قسمت تنظیم کنید "0". اگرچه این استدلال در کل قابل حذف است.
در زمینه "نوع" در آغاز یا در پایان ماه نشان دهید که پرداخت انجام می شود. در اینجا ، مانند اکثر موارد ، در پایان ماه تولید می شود. بنابراین ، شماره را تنظیم کنید "0". همانطور که در مورد آرگومان قبلی ، شما نمی توانید چیزی را در این زمینه وارد کنید ، سپس برنامه به طور پیش فرض فرض می کند که حاوی مقداری برابر با صفر است.
پس از وارد کردن تمام داده ها ، بر روی دکمه کلیک کنید "خوب".
- پس از آن ، نتیجه محاسبه در سلول نمایش داده می شود که در پاراگراف اول این کتابچه راهنمای کاربر را برجسته کرده ایم. همانطور که مشاهده می کنید ، میزان پرداخت کل وام ماهانه است 23536.74 روبل. در مقابل این علامت "-" سردرگم نشوید. بنابراین اکسل نشان می دهد که این یک هزینه نقدی ، یعنی ضرر است.
- برای محاسبه کل مبلغ پرداخت کل مدت وام ، با در نظر گرفتن بازپرداخت بدن وام و بهره ماهانه ، کافی است مبلغ پرداخت ماهانه را ضرب کنید (23536.74 روبل) به تعداد ماهها (24 ماه) همانطور که مشاهده می کنید ، مبلغ کل پرداختی برای کل مدت وام در پرونده ما بوده است 564881.67 روبل.
- حالا می توانید میزان اضافه پرداخت وام را محاسبه کنید. برای این کار ، از کل مبلغ پرداخت وام ، از جمله بهره و بدنه وام ، مبلغ اولیه وام گرفته شده را تفریق کنید. اما به یاد داریم که اولین این مقادیر قبلاً امضا شده است "-". بنابراین ، در مورد خاص ما ، معلوم می شود که آنها باید به هم خورده باشند. همانطور که مشاهده می کنید ، کل بازپرداخت وام برای کل دوره بالغ شده است 64 881.67 روبل.
درس: جادوگر ویژگی های اکسل
مرحله 2: جزئیات پرداخت
و اکنون ، با کمک سایر اپراتورهای اکسل ، ما جزئیات پرداخت ماهانه را انجام می دهیم تا ببینیم در یک ماه خاص وام ما چه مقدار پرداخت می کنیم و چقدر سود دارد. برای این اهداف ، ما در اکسل جدول آورده ایم که داده ها را پر خواهیم کرد. ردیف های این جدول با دوره مربوطه ، یعنی به ماه ، مطابقت دارد. با توجه به اینکه دوره وام با ما است 24 ماه ، سپس تعداد ردیف ها نیز مناسب خواهند بود. ستون ها حاکی از پرداخت بدنه وام ، پرداخت بهره ، کل پرداخت ماهانه است که این جمع دو ستون قبلی و همچنین مبلغ باقیمانده قابل پرداخت است.
- برای تعیین میزان پرداخت توسط وام ، از این تابع استفاده کنید OSPLT، که فقط برای این اهداف طراحی شده است. مکان نما را روی سلول موجود در ردیف تنظیم کنید "1" و در ستون "بازپرداخت وام وام". روی دکمه کلیک کنید "درج عملکرد".
- برو به جادوگر ویژگی. در دسته "مالی" نام را علامت گذاری کنید OSPLT و دکمه را فشار دهید "خوب".
- پنجره آرگومان های اپراتور OSPLT شروع می شود. دارای نحوی زیر است:
= OSPLT (شرط بندی ؛ دوره ؛ نپر ؛ روان ؛ BS)همانطور که مشاهده می کنید ، آرگومان های این عملکرد تقریباً کاملاً با آرگومان های اپراتور مطابقت دارد PMTفقط به جای یک استدلال اختیاری "نوع" استدلال مورد نیاز اضافه شده است "دوره". این تعداد دوره پرداخت و در مورد خاص ما ، تعداد ماه را نشان می دهد.
ما فیلدهای قبلاً آشنا از پنجره آرگومان عملکرد را پر می کنیم OSPLT همان داده هایی که برای عملکرد استفاده شده است PMT. فقط با توجه به اینکه در آینده فرمول با استفاده از نشانگر fill کپی می شود ، باید کلیه پیوندها را در قسمتها مطلق قرار دهید تا تغییر نکنند. برای این کار ، یک علامت دلار را در مقابل هر مقدار مختصات به صورت عمودی و افقی قرار دهید. اما انجام این کار ساده تر با برجسته کردن مختصات و فشار دادن کلید عملکرد F4. علامت دلار به طور خودکار در مکان های مناسب قرار می گیرد. همچنین فراموش نکنید که نرخ سالانه باید با تقسیم شود 12.
- اما ما یک استدلال جدید دیگر داریم که عملکرد آن را نداشت. PMT. این استدلال "دوره". در قسمت مربوطه ، پیوند را به اولین سلول ستون تنظیم کنید "دوره". این عنصر برگه شامل یک عدد است "1"، که تعداد ماه اول وام را نشان می دهد. اما برخلاف فیلدهای قبلی ، در قسمت مشخص شده ما پیوند را به صورت نسبی رها می کنیم و آن را مطلق نمی سازیم.
بعد از وارد کردن تمام داده هایی که در مورد آنها صحبت کردیم ، روی دکمه کلیک کنید "خوب".
- پس از آن ، در سلولی که قبلاً اختصاص داده بودیم ، میزان بازپرداخت در بدنه وام برای ماه اول نمایش داده می شود. او خواهد کرد 18،536.74 روبل.
- سپس ، همانطور که در بالا ذکر شد ، ما باید این فرمول را با استفاده از نشانگر پر کننده ، در سلولهای باقیمانده ستون کپی کنیم. برای انجام این کار ، مکان نما را در گوشه سمت راست پایین سلول که حاوی فرمول است تنظیم کنید. مکان نما به یک صلیب تبدیل می شود که به آن نشانگر پر می گویند. دکمه سمت چپ ماوس را نگه دارید و آن را به انتهای جدول بکشید.
- در نتیجه ، تمام سلولهای موجود در ستون پر می شوند. اکنون برنامه بازپرداخت وام ماهانه داریم. همانطور که گفته شد ، میزان پرداخت این ماده با هر دوره جدید افزایش می یابد.
- حال باید محاسبه ماهانه پرداختهای بهره را انجام دهیم. برای این اهداف از اپراتور استفاده خواهیم کرد PRPLT. اولین سلول خالی را در ستون انتخاب کنید پرداخت بهره. روی دکمه کلیک کنید "درج عملکرد".
- در پنجره شروع جادوگران عملکرد در رده "مالی" ما انتخاب را انجام می دهیم PRPLT. روی دکمه کلیک کنید "خوب".
- پنجره آرگومانهای عملکرد شروع می شود. PRPLT. نحو آن به شرح زیر است:
= PRPLT (شرط بندی ؛ دوره ؛ نپر ؛ روان ؛ BS)همانطور که مشاهده می کنید ، آرگومان های این عملکرد کاملاً با عناصر مشابه اپراتور یکسان است OSPLT. بنابراین ، ما به همین سادگی داده های مشابه را وارد پنجره ای می کنیم که در پنجره آرگومان قبلی وارد کردیم. ما در عین حال پیوند موجود در زمینه را فراموش نمی کنیم "دوره" باید نسبی باشد و در همه زمینه های دیگر مختصات باید به شکل مطلق کاهش یابد. پس از آن بر روی دکمه کلیک کنید "خوب".
- سپس ، نتیجه محاسبه میزان پرداخت سود وام برای ماه اول در کادر مناسب نمایش داده می شود.
- با استفاده از نشانگر پر ، فرمول را در عناصر باقیمانده ستون کپی می کنیم ، بنابراین یک برنامه پرداخت ماهانه برای بهره وام دریافت می کنیم. همانطور که می بینیم ، همانطور که قبلاً گفته شد ، از ماه به ماه ارزش این نوع پرداخت کاهش می یابد.
- حال باید کل پرداخت ماهانه را محاسبه کنیم. برای این محاسبه ، شما نباید به هیچ عملیاتی متوسل شوید ، زیرا می توانید از یک فرمول حسابی ساده استفاده کنید. محتویات سلول های ماه اول ستون ها را اضافه کنید "بازپرداخت وام وام" و پرداخت بهره. برای انجام این کار ، علامت را تنظیم کنید "=" به اولین سلول خالی یک ستون "کل پرداخت ماهانه". سپس بر روی دو عنصر فوق کلیک می کنیم و یک علامت بین آنها قرار می دهیم "+". روی کلید کلیک کنید وارد شوید.
- در مرحله بعد ، با استفاده از نشانگر پر ، مانند سایر موارد ، ستون را با داده پر کنید. همانطور که مشاهده می کنید در کل مدت قرارداد ، کل پرداخت ماهانه از جمله پرداخت توسط وام و بهره وام خواهد بود 23536.74 روبل. در واقع ، ما قبلاً این شاخص را با استفاده از محاسبه کرده ایم PMT. اما در این حالت ، آن را با وضوح بیشتری ارائه می شود ، دقیقاً به عنوان مبلغ پرداختی بر روی وام و بهره.
- حال باید داده ها را به ستون اضافه کنید ، که ماهانه مانده حساب وام را که هنوز نیاز به پرداخت دارد ، نشان می دهد. در سلول اول ستون "مانده قابل پرداخت" محاسبه ساده ترین خواهد بود ما باید از مبلغ اولیه وام که در جدول با داده های اولیه نشان داده شده است ، مبلغ پرداخت بر اساس وام برای ماه اول در جدول محاسبه را تفریق کنیم. اما با توجه به اینکه یکی از اعدادی که قبلاً علامت آن را داریم "-"، سپس آنها را نباید برد ، بلکه تاشو کرد. ما این کار را انجام می دهیم و روی دکمه کلیک می کنیم وارد شوید.
- اما محاسبه مانده تعادل پس از ماه های دوم و بعدی تا حدودی پیچیده تر خواهد بود. برای این کار ، باید در ابتدای وام از مبلغ پرداختی از بدنه وام مبلغ کل مبلغ پرداختی در بدنه وام را برای دوره قبل تفریق کنیم. علامت را تنظیم کنید "=" در سلول دوم ستون "مانده قابل پرداخت". در مرحله بعد ، پیوند سلول را نشان می دهیم ، که شامل مبلغ وام اولیه است. با برجسته کردن و فشار دادن کلید ، آن را مطلق کنید F4. سپس یک علامت می گذاریم "+"، از آنجا که مقدار دوم در مورد ما منفی خواهد بود. پس از آن بر روی دکمه کلیک کنید "درج عملکرد".
- شروع می کند جادوگر ویژگیکه در آن شما باید به گروه بروید "ریاضی". در آنجا کتیبه را برجسته می کنیم خلاصه و روی دکمه کلیک کنید "خوب".
- پنجره آرگومان عملکرد شروع می شود خلاصه. عملگر مشخص شده برای جمع آوری داده ها در سلول ها ، که ما باید در ستون انجام دهیم ، خدمت می کند "بازپرداخت وام وام". دارای نحوی زیر است:
= SUM (number1؛ number2؛ ...)آرگومان ها اشاراتی به سلولهایی هستند که شامل اعداد هستند. مکان نما را بر روی زمینه قرار می دهیم "شماره 1". سپس دکمه سمت چپ ماوس را نگه دارید و دو سلول اول ستون را روی برگه انتخاب می کنیم "بازپرداخت وام وام". در این زمینه ، همانطور که می بینیم ، پیوندی به دامنه نمایش داده می شود. از دو قسمت تشکیل شده است که توسط یک روده بزرگ از هم جدا شده است: پیوندها به سلول اول دامنه و آخرین قسمت. برای اینکه بتوانیم فرمول مشخص شده را با استفاده از نشانگر fill در آینده کپی کنیم ، قسمت اول لینک مربوط به دامنه را مطلق می سازیم. آن را انتخاب کرده و روی کلید عملکرد کلیک کنید F4. قسمت دوم پیوند همچنان نسبی است. حال هنگام استفاده از نشانگر پر ، اولین سلول از محدوده ثابت خواهد شد و آخرین با کشیدن پایین کشیده می شود. این همان چیزی است که ما برای تحقق اهداف خود نیاز داریم. بعد روی دکمه کلیک کنید "خوب".
- بنابراین ، نتیجه مانده بدهی اعتبار پس از ماه دوم در سلول نمایش داده می شود. حالا با شروع از این سلول ، فرمول را با استفاده از نشانگر fill پر می کنیم.
- محاسبه ماهانه مانده وام برای کل دوره وام. همانطور که انتظار می رود ، در پایان مدت این مقدار صفر است.
بنابراین ، ما فقط پرداخت وام را محاسبه نکردیم بلکه نوعی ماشین حساب وام را سامان دادیم. که براساس طرح سالیانه عمل خواهد کرد. اگر در جدول اصلی ما ، به عنوان مثال ، اندازه وام و نرخ سود سالانه را تغییر دهیم ، در جدول نهایی داده ها به طور خودکار محاسبه می شوند.بنابراین ، می تواند نه تنها یکبار برای یک مورد خاص استفاده شود ، بلکه در موقعیت های مختلف نیز برای محاسبه گزینه های اعتبار با توجه به طرح سالیانه مورد استفاده قرار می گیرد.
درس: عملکردهای مالی در اکسل
همانطور که مشاهده می کنید ، با استفاده از برنامه اکسل در خانه ، می توانید به راحتی کل وام ماهانه را طبق برنامه سالانه با استفاده از اپراتور برای این اهداف محاسبه کنید. PMT. علاوه بر این ، با استفاده از توابع OSPLT و PRPLT می توانید مبلغ پرداختی بر روی وام و بهره را برای مدت مشخص محاسبه کنید. با استفاده از همه این توابع توابع ، می توان یک ماشین حساب وام قدرتمند ایجاد کرد که می تواند بیش از یک بار برای محاسبه یک پرداخت سالانه استفاده شود.
