توابع مالی اکسل برای حسابداران | کاربرد، فرمول و مثال

توابع مالی اکسل (Financial Functions) مجموعه‌ای از فرمول‌های آماده‌اند که محاسباتی مثل استهلاک دارایی، اقساط وام، ارزش آینده سرمایه‌گذاری و ارزیابی طرح‌های توجیهی را در چند ثانیه انجام می‌دهند. اکسل بیش از 50 تابع مالی دارد، اما یک حسابدار یا تحلیلگر مالی در عمل با حدود 15 تا 20 تابع از آن‌ها سروکار دارد.
در این مقاله توابع مالی پرکاربرد را در پنج دسته معرفی می‌کنیم و برای هر تابع، ساختار (سینتکس) و یک مثال عددی می‌آوریم. اگر به‌دنبال آشنایی کلی با کاربرد اکسل در حسابداری هستید، پیشنهاد می‌کنیم ابتدا مقاله آموزش اکسل در حسابداری را بخوانید و سپس به این مقاله برگردید.

توابع مالی اکسل کجا هستند؟

برای دسترسی به فهرست کامل، در اکسل به تب Formulas بروید و از گروه Function Library گزینه Financial را بزنید. همچنین می‌توانید نام تابع را مستقیم در سلول بنویسید و علامت = را در ابتدای آن بگذارید.

نکات مهم قبل از شروع کار با توابع اکسل

رعایت این سه اصل، جلوی بیشتر خطاهای رایج را می‌گیرد:

هم‌واحد بودن نرخ و دوره: اگر اقساط ماهانه است، نرخ سالانه را بر 12 تقسیم کنید و تعداد دوره را به ماه بدهید (مثلاً وام 3 ساله = 36).

علامت جریان نقدی: در اکسل، پولی که می‌پردازید منفی و پولی که دریافت می‌کنید مثبت است. به همین دلیل خروجی PMT معمولاً منفی نمایش داده می‌شود.

جداکننده آرگومان‌ها: بسته به تنظیمات ویندوز، ممکن است به‌جای کاما (,) از نقطه‌ویرگول (;) استفاده شود.

مهم ترین توابع مالی اکسل

تحلیل گر مالی زمان زیادی از وقت خود را صرف انجام محاسبات، نمودارها، مدل های مالی و...توسط نرم افزار اکسل می‌کند. توابع مالی و حسابداری فراوانی (بیش از 55 تابع) در اکسل وجود دارد. در اینجا بخشی از آنها معرفی می‌شود.

برخی توابع مهم در اکسل را به 5 گروه  تقسیم می‌کنیم.

1) توابع استهلاک

استهلاک یعنی تخصیص بهای تمام‌شده دارایی ثابت در طول عمر مفید آن. اکسل سه روش پرکاربرد را پشتیبانی می‌کند.

تابع SLN (استهلاک خط مستقیم)

مبلغ استهلاک را در هر دوره به‌صورت یکسان محاسبه می‌کند.

مثال: دارایی با بهای 120,000,000 ریال، ارزش اسقاط 20,000,000 ریال و عمر مفید 5 سال:

=SLN(120000000, 20000000, 5)

نتیجه: 20,000,000 ریال استهلاک در هر سال.

تابع SYD (روش مجموع سنوات)

استهلاک را به‌صورت نزولی، با ضریبی که از مجموع ارقام سال‌های عمر مفید به‌دست می‌آید، محاسبه می‌کند. استهلاک سال‌های اول بیشتر از سال‌های آخر است.

مثال: همان دارایی بالا برای سال اول:

=SYD(120000000, 20000000, 5, 1)

نتیجه: حدود 33,333,333 ریال.

تابع DB (استهلاک نزولی با نرخ ثابت)

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

مثال:

=DB(120000000, 20000000, 5, 1)

نتیجه: حدود 36,120,000 ریال برای سال اول.

2) توابع وام و اقساط

تابع PMT (مبلغ قسط)

مبلغ قسط ثابت دوره‌ای وام را با نرخ سود ثابت محاسبه می‌کند.

مثال: وام 500,000,000 ریالی با نرخ سالانه 23٪ و بازپرداخت 36 ماهه:

=PMT(23%/12, 36, 500،000،000)

نتیجه: حدود 19,350,000- ریال (علامت منفی یعنی پرداخت). برای نمایش مثبت، پیش از مبلغ وام علامت منفی بگذارید.

تابع IPMT (سهم بهره از قسط)

مشخص می‌کند در یک قسط معین، چه مقدار بهره پرداخت می‌شود.

مثال: سهم بهره در قسط اول وام بالا:

=IPMT(23%/12, 1, 36, 50،0000،000)

نتیجه: حدود 9,583,333- ریال.

تابع PPMT (سهم اصل از قسط)

مشخص می‌کند در یک قسط معین، چه مقدار از اصل وام بازپرداخت می‌شود.

مثال:

=PPMT(23%/12, 1, 36, 500،000،000)

نتیجه: حدود 9,770,000- ریال. مجموع PPMT و IPMT هر دوره همیشه برابر PMT همان دوره است. با ساختن جدول اقساط (جدول استهلاک وام) و کشیدن این دو فرمول به پایین، می‌توانید روند کاهش اصل وام را ماه به ماه ببینید.

تابع NPER (تعداد دوره‌ها)

می‌گوید با یک قسط مشخص، پرداخت وام چند دوره طول می‌کشد.

مثال: اگر قسط ماهانه 20,000,000 ریال بپردازید:

=NPER(23%/12, -20000000, 500000000)

نتیجه: حدود 34٫4 ماه.

تابع RATE (نرخ سود هر دوره)

نرخ سود هر دوره را از روی تعداد اقساط، مبلغ قسط و اصل وام به‌دست می‌آورد. برای مثال، وقتی نرخ واقعی یک وام یا لیزینگ را نمی‌دانید، از این تابع استفاده می‌کنید.

مثال:

=RATE(36, -19355000, 500000000)

نتیجه: حدود 1٫92٪ در ماه. برای نرخ سالانه آن را در 12 ضرب کنید.

3) توابع ارزش زمانی پول

تابع FV (ارزش آینده)

ارزش آینده سرمایه‌ گذاری با نرخ ثابت و پرداخت‌های یکسان دوره‌ای را حساب می‌کند.

مثال: پس‌انداز ماهانه 10,000,000 ریال به مدت 12 ماه با نرخ سالانه 24٪:

=FV(24%/12, 12, -10000000)

نتیجه: حدود 134,120,000 ریال.

تابع PV (ارزش فعلی)

معلوم می‌کند مبلغی که در آینده دریافت می‌کنید، امروز چقدر ارزش دارد.

مثال: ارزش امروز 1,000,000,000 ریال که سه سال دیگر دریافت می‌شود، با نرخ تنزیل 20٪ در سال:

=PV(20%, 3, 0, -1000000000)

نتیجه: حدود 578,703,704 ریال.

تابع FVSCHEDULE (ارزش آینده با نرخ‌های متغیر)

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

مثال: سرمایه 100,000,000 ریال با نرخ‌های 20٪، 25٪ و 30٪ در سه سال متوالی:

=FVSCHEDULE(100000000, {0.2, 0.25, 0.3})

نتیجه: 195,000,000 ریال.

تابع EFFECT (نرخ بهره موثر سالانه)

نرخ اسمی را با توجه به تعداد دفعات سودسازی در سال به نرخ موثر تبدیل می‌کند.

مثال: نرخ اسمی 24٪ با سودسازی ماهانه:

=EFFECT(24%, 12)

نتیجه: حدود 26٫82٪.

4) توابع ارزیابی طرح‌های سرمایه‌گذاری

این توابع در تهیه طرح توجیهی و مقایسه پروژه‌ها بیشترین کاربرد را دارند.

تابع NPV (ارزش خالص فعلی)

جریان‌های نقدی آتی را با نرخ تنزیل به ارزش امروز می‌رساند. (نرخ تنزیل یعنی حداقل بازده‌ای که از سرمایه‌گذاری انتظار دارید).

نکته مهم:

NPV فرض می‌کند اولین جریان نقدی در پایان دوره اول رخ می‌دهد. بنابراین سرمایه‌گذاری اولیه (زمان صفر) را داخل تابع نیاورید و جداگانه با نتیجه جمع کنید.

مثال: فرض کنید می‌خواهید دستگاهی بخرید و باید بدانید این سرمایه‌گذاری به‌صرفه است یا نه.

هزینه خرید (سرمایه‌گذاری اولیه): 500 میلیون ریال

سود (جریان نقدی ورودی) در چهار سال بعد: به ترتیب 150، 200، 250 و 300 میلیون ریال

نرخ تنزیل (بازده مورد انتظار شما): 20٪ در سال

داده‌ها را در اکسل این‌طور بچینید (مبالغ به میلیون ریال):

سپس این فرمول را بنویسید:

=NPV(20%, B3:B6) + B2

در این فرمول، بخش NPV(20%, B3:B6) ارزش امروز چهار سود سالانه را حساب می‌کند. عدد B2 منفی است، پس با جمع شدن، هزینه خرید از آن کم می‌شود.

نتیجه: حدود 53٫2 میلیون ریال.

تفسیر: NPV مثبت است، یعنی مجموع سودهای آینده، پس از کسر ارزش زمانی پول با نرخ 20٪، از هزینه خرید بیشتر است. پس این پروژه با نرخ بازده مورد انتظار شما توجیه مالی دارد. اگر NPV منفی می‌شد، پروژه به نرخ 20٪ نمی‌رسید.

تابع XNPV (ارزش خالص فعلی با تاریخ مشخص)

مشابه NPV است، اما برای جریان‌های نقدی که فاصله زمانی‌شان یکسان نیست و تاریخ مشخص دارند.

در XNPV اولین مقدار به‌عنوان جریان زمان صفر در نظر گرفته می‌شود و لازم نیست آن را بیرون تابع بیاورید.

تابع IRR (نرخ بازده داخلی)

نرخی را پیدا می‌کند که با آن NPV پروژه صفر می‌شود. هرچه IRR بالاتر باشد، پروژه سودآورتر است.

مثال: برای همان جریان‌های مثال NPV (از 500- تا 300 در B2:B6):

=IRR(B2:B6)

نتیجه: حدود 24٫9٪. اگر این نرخ از نرخ هزینه سرمایه شما بیشتر باشد، پروژه قابل قبول است. IRR دست‌کم یک جریان منفی و یک جریان مثبت لازم دارد.

تابع XIRR (نرخ بازده داخلی با تاریخ مشخص)

نسخه تاریخ‌دار IRR برای جریان‌هایی که در بازه‌های نامنظم رخ می‌دهند.

IRR فرض می‌کند سودهای دریافتی با خود همان نرخ IRR دوباره سرمایه‌گذاری می‌شوند که گاهی غیرواقعی است. MIRR اجازه می‌دهد نرخ تامین مالی و نرخ سرمایه‌گذاری مجدد را جداگانه تعیین کنید.

مثال: =MIRR(B2:B6, 20%, 15%)

نکته درباره تاریخ در XNPV و XIRR: این توابع فقط تاریخ‌هایی را قبول می‌کنند که اکسل آن‌ها را تاریخ واقعی (میلادی) بشناسد. تاریخ شمسی که به‌صورت متن (مثل 1405/02/15) تایپ شده، خطا می‌دهد. پیش از استفاده، تاریخ‌ها را به

تاریخ میلادی تبدیل کنید.

5) توابع اوراق بهادار

تابع ACCRINT (بهره انباشته اوراق)

بهره متعلقه اوراقی را که سود دوره‌ای می‌پردازند، از تاریخ صدور تا تاریخ معامله محاسبه می‌کند.

جدول خلاصه: کدام تابع اکسل را کی استفاده کنیم؟

تفاوت NPV و IRR در یک نگاه

NPV سودآوری پروژه را به‌صورت مبلغ نشان می‌دهد و IRR به‌صورت درصد. برای مقایسه پروژه‌های هم‌اندازه هر دو نتیجه مشابهی می‌دهند، اما وقتی اندازه یا زمان‌بندی پروژه‌ها فرق دارد، معمولاً NPV معیار مطمئن‌تری است. بهتر است هر دو را کنار هم گزارش کنید.

★ برخی موارد پرکاربرد در اکسل

✓ مرتب سازی (sort)

مرتب سازی داده ها یا به عبارتی سورت کردن در اکسل از مواردی است که زیاد مورد استفاده قرار می‌گیرد. در واقع برای اینکه مثلا داده ها به ترتیب حروف الفبا یا بر اساس اعداد از کوچک به بزرگ یا برعکس مرتب شوند از   استفاده می‌کنیم.

در زمان استفاده از Sort باید دقت کنیم که داده ها درست انتخاب شوند، زیرا در صورتی که Sort و ذخیره کنیم، دیگر قابل بازگرداندن نیست.

✓ ابزار جستجو یا Find

ابزار Find جهت سرچ و پیدا کردن داده ها با ویژگی های مورد نظر استفاده می‌شود.

✓ جداسازی یا filter

یکی از سریع ترین راه ها برای تهیه گزارش ساده از اطلاعات زیاد، استفاده از ابزار فیلتر است. از طریق این ابزار در اکسل میتون از بین داده های زیاد، داده های با مشخصات خاصی رو مشاهده کرد. برای فیلتر یا همان جداسازی، کافیست محدوده اطلاعات رو انتخاب و از تب Data گزینه Filter رو کلیک کنیم. مثلا اگر بخواهیم از میان داده های مربوط به فروش، اطلاعات یک محصول خاص رو ببینیم، از فیلتر استفاده می‌کنیم.

✓ جداول محوری ( Pivot Tables) 

یک ابزار بسیار قدرتمند در تحلیل داده ها، جداول پاشنه ای یا  Pivot Table در اکسل است. در واقع با این ابزار می‌توانید با صرف چند دقیقه و بدون فرمول نویسی‏ پیچیده، یک گزارش کاربردی و جذاب از هزاران داده‌ی خام تهیه کنید.

✓ Conditional Formatting

فرمت دهی شرطی ( Conditional Formatting) در اکسل ابزاری بسیار کاربردی در زمینه بصری سازی داده هاست. بصری سازی، انجام تنظیماتی روی داده هاست برای اینکه تفسیر داده ها و درک آنها راحت تر شود.

• در دوره های آموزشی حصین حاسب استفاده از اکسل هم آموزش داده می شود؟

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

• فرق NPV و XNPV چیست؟

  NPV فاصله زمانی بین جریان‌های نقدی را یکسان فرض می‌کند، اما XNPV تاریخ دقیق هر جریان را می‌گیرد و برای پرداخت‌های نامنظم مناسب‌تر است.

• چرا در توابع اکسل نتیجه PMT منفی می‌شود؟

  چون اکسل پرداخت را خروج پول و عددی منفی می‌داند. اگر مبلغ مثبت می‌خواهید، پیش از آرگومان pv علامت منفی بگذارید یا کل تابع را در 1- ضرب کنید.
  
  

• چطور اقساط وام را در اکسل حساب کنیم؟

  با تابع PMT. نرخ سالانه را بر 12 تقسیم کنید، تعداد اقساط را بر حسب ماه و مبلغ وام را وارد کنید. برای تفکیک اصل و سود هر قسط از PPMT و IPMT کمک بگیرید