تابع وام در اکسل

 

اگر تا به حال از بانک ها و موسسات مالی وام دریافت کرده باشید، حتماً مایلید بدانید که چگونه سود وام ها و سپرده ها را بر اساس نرخ اعلام شده محاسبه می کنند.

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

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

Loan principle	اصل پول
Interest Rate	نرخ بهره
Pay-Period	دوره پرداخت
Duration of the loan	طول دوره پرداخت
Payment	پرداخت در هر دوره

تذکر: 1

اکسل نرخ بهره را به ازاء دوره پرداخت می گیرد (مثلاً پرداخت ماهانه) اما پاسخها را بر اساس سالانه برمیگرداند و برای تبدیل به ماهانه باید آن را بر 12 تقسیم کنیم.

تذکر 2: مقدار پرداختهایی که در فرمول وارد می شوند بایستی به صورت عدد منفی وارد شوند.

تابع PMT

مثال1: فرض کنیم از یک موسسه مالی یک وام به مبلغ پنج میلیون تومان با نرخ بهره 14% و دوره بازپرداخت 3 ساله گرفته ایم. مبلغ قسط ماهانه آن چقدر است؟

از فرمول به شرح زیر برای مشخص کردن میزان اقساط ماهانه استفاده می کنیم.

=PMT(نرخ بهره ماهانه,تعداد دوره پرداخت,اصل وام دریافتی)

همانطور که در شکل می بینید نرخ بهره سالانه یعنی 14% را بر عدد 12 تقسیم کرده ایم تا  نرخ بهره مانه را بدست آوریم.

 

تذکر: قسط پرداختی هر ماه شامل دو قسمت است. قسمتی از آن مربوط به اصل پول گرفته شده و قسمتی نیز مربوط به پرداخت سود است.

مثال 2: فرض کنیم در مثال 1 تعداد 15 قسط را تا کنون پرداخت کرده باشید. چه مقدار از قسط آخر (قسط 15) مربوط به اصل پول و چه مقدار آن مربوط به سود می باشد؟

برای این منظور از تابع IPMT به شکل زیر استفاده می کنیم.

=IPMT(rate(نرخ بهره),per(دوره پرداخت مورد نظر),nper(تعداد کل دوره های پرداخت),pv(اصل پول)

=IPMT(B2/12,B5,B3,B1)

همانطور که در شکل می بینید، مقدار محاسبه شده برابر 34488.07  می باشد. این عدد مبلغ سود پول را نشان می دهد و با کم کردن آن از مبلغ کل قسط  مبلغ اصل پول نیز که برابر 132400.08  می باشد نیز مشخص میگردد.

تابع NPER

شکل کلی تابع:

=NPER(rate,pmt,pv)

مثال 3: من قصد دارم یک وام  به مبلغ پنج میلیون تومان بگیرم. نرخ بهره آن 15%   و توان پرداخت ماهی 250000 تومان را دارم. تسویه این وام در چند ماه به پایان می رسد؟

=NPER(.15/12,-250000,5000000)

شما می توانید در طی 23 ماه این مبلغ را تسویه نمایید.

تابع Rate

شکل کلی تابع

=rate(nper(تعداد کل دوره های پرداخت),pmt(پرداخت ماهانه),pv(اصل پول)

مثال 4:میخواهم وسیله ای بخرم به قیمت 700000 تومان. پیشنهاد فروشنده اقساط 24 ماهه با پرداخت ماهانه 35000 تومان است. چگونه میتوان نرخ واقعی بهره را بدست آورد.

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

=RATE(24,-35000,700000)

جواب این فرمول 2% می باشد (نرخ بهره ماهانه) و با ضرب آن در عدد 12 ماه بهره 24% خواهد شد. پس بهتر است این پول را از بانکی که نرخ بهره اش 16% وام بگیرم.

تابع PV

شکل کلی تابع

=PV(rate(نرخ بهره),nper(تعداد کل دوره های پرداخت,pmt(پرداخت ماهانه)

مثال 5: نرخ بهره بانک 16% است. اگر بتوانم در طی سال آینده ماهی 50000 تومان قسط بدهم، چقدر می توانم از بانک وام بگیرم؟

=PV(.16/12,12,-50000)

پاسخ 551080 تومان یعنی تقریباً مبلغ 550000 تومان

تابع FV

شکل کلی تابع

=FV(rate(نرخ بهره),nper(تعداد کل دوره های پرداخت,pmt(پرداخت ماهانه),pv(اصل پول)

مثال 6: با مبلغ 100000 تومان در بانک حسابی باز کرده ام و ماهی 50000 تومان به آن اضافه می کنم. بهره پرداختی بانک .515% است. در پایان دو سال چقدر پس انداز دارم.

=FV(0.155/12,24,-50000,-100000)

جواب: مبلغ 1532413 تومان می باشد.

تذکر: تمام مبلغ پرداختی به صورت عدد منفی در فرمولها وارد شده اند.

جمع اعداد یک محدوده حاوی خطا

شاید این موضوع را متوجه شده باشید که اگر به هر تابعی در اکسل یک خطا به عنوان ورودی بدهیم ، خروجی آن تابع خطا خواهد شد .

برای رفع این مشکل می توان از فرمول های برداری به طریقه زیر بهره گرفت .

نکته : پس از نوشتن فرمول به جای اینتر ، Ctrl + Shift+ Enter را همزمان بزنید

=SUM(IFERROR(D2:D5;""))

در ضمن تابع IFFERROR  که در بالا استفاده شده در اکسل 2007 و بالاتر موجود است اگر از اکسل 2003 استفاده می کنید باید از فرمول زیر استفاده نمایید .(حتما در انتها کلید های Ctrl+Shift+Enter را بزنید)

=SUM(IF(ISERROR(D2:D5),"",D2:D5))

فرمول کاربردی و مفید اکسل (Sumproduct)

فرض کنید دو لیست از اعداد دارید .

به صورت بسیار ساده می توان با استفاده از تابع Sumproduct زیگمای A*B را به صورت نمایش داده شده محاسبه نمود .

در این مرحله ممکن است به صورت یک تابع تقریبا بی فایده به نظر برسد اما با مطالعه ادامه مطلب خواهید دید که همه چیز تغییر خواهد کرد و شما نیز به مفید و کاربردی بودن این تابع پی خواهید برد .

فرض کنید شما یک لیست از اطلاعات فروش شامل ستون های نام و نام خانوادگی ، منطقه ، محصول و فروش دارید.

میخواهید بدانید که چه تعداد کالا به آقای Luke فروخته شده است؟

 ساده است، شما با استفاده از فرمول SUMIF براحتی میتوانید این مسئله را حل کنید .

اما صبر کنید ، اگر بخواهید بدانید چه تعداد کالا به آقای Luke در منطقه غرب «west» به فروخته شده است چه خواهید کرد ؟

ما به شما استفاده از تابع کاربردی Sumproduct را پیشنهاد می کنیم.

هرچند راه های دیگری از قبیل استفاده از تابع Sumifs که در آفیس 2007 به بعد گنجانده شده وجود دارد اما در اینجا قصد آموزش تابع Sumproduct را داریم.

فرض کنید داده ها در محدوده A1:A11 قرار دارد .(نام و نام خانوادگی در ستون A ، منطقه در ستون B و فروش در ستون C )

فرمول به صورت زیر خواهد بود .

=SUMPRODUCT((A2:A11="Luke Skywalker")*(B2:B11="west");C2:C11)

یک دقیقه وقت دارید تا بفهمید که چه انجام شده است ؟

همانطور که احتمالا متوجه شده اید تحلیل فرمول فوق به شکل زیر خواهد بود .

مراحل طراحی یک نرم افزار در اکسل

مراحل و نکات مهم در طراحی یک نرم افزار ساده با اکسل


اگر قرار شد نرم افزار ساده ای با اکسل تهیه کنید به موارد زیر دقت کنید :
چه کسی کاربر نرم افزار شما میباشد ؟
عمر استفاده از نرم افزار شما چقدر است ؟
دقت نرم افزار شما در چه حدی میباشد ؟
نمونه نرم افزاری با این کاربری وجود دارد ؟
چه کسانی قرار است از خروجی های ان استفاده نمایند ؟
و سوالهای مختلفی که میتواند در انتخاب ابزار و روش تهیه نرم افزار تاثیر گذار باشد .
مراحل ساخت :
در اولین مرحله باید ساختار فایل را مشخص نمایید . پیشنهاد میگردد یک فایل را که تهیه کنید که حداقل دارای کاربرگهای (شیت) زیر باشد ۱- بانک اطلاعاتی ۲- کاربرگ گزارش ۳- کاربرگ استاندارد ۴- کاربرگ راهنما
بانک اطلاعاتی: جهت ذخیره اطلاعات داده ها
کاربرگ استاندارد : برای اطلاعاتی که تغییر نمیکند مانند مشخصات ثابت افراد یا ماشین آلات
البته این موارد کاملا سلیقه ای میباشد و به سلیقه افراد بستگی دارد .
در مرحله بعد باید تشخیص بدهید چه ستونهایی برای بانک اطلاعاتی نیاز است . این ستونها را میتوان از گزارشهای که نیاز است و یا از مراحل فعالیتها کسب نمود به عنوان مثال برای یک بانک اطلاعاتی خرید میتوان ستونهای زیر را در نظر گرفت .
نام محصول – مبلغ – نوع پرداخت – شخص خریداری – تاریخ خرید – …………
این ستونها ممکن است در فرآیند تکمیل شدن تغییر یایند .
سپس شروع به تعریف گزارشهای که میخواهید مینمایید در تعریف این روابط موارد زیر را به خاطر داشته باشید . فرمول را به داده ها وصل نمایید نه به فرمولها چون در صورت خطا در یک فرمول تمام گزارش ما درای ایراد نگردد .
برای سلولهای کمکی ساختار و بخش منظمی در نظر بگیرید .
در بسیاری موارد فرمولهای ارایه ای کار را بسیار راحت تر میکنند .
سپس گرافیک مورد نظر را اعمال کنید و مراحل امنیت داده ها و فایل را اجرا کنید.