«توابع مالی در اکسل» (Excel Financial Functions) مجموعه‌ای از ابزارهای محاسباتی این نرم افزار هستند که برای تحلیل جریان‌های نقدی پروژه، تحلیل‌های مالی سرمایه‌گذاری‌، ارزیابی ریسک، به‌دست آوردن مبلغ اقساط وام و بهره و استهلاک دارایی‌‌ به‌کار می‌روند. استفاده از این توابع و فرمول‌های اکسل، محاسبات و تصمیم‌گیری‌های مالی شرکت‌ها را به‌خصوص در فعالیت‌های حسابداری و سرمایه‌گذاری سریع‌تر می‌کند. در این مطلب از مجله فرادرس روش فرمول‌نویسی با ۱۸ تابع مالی پرکاربرد در اکسل را همراه مثال‌های ساده یاد می‌گیریم.

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

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

1. توابع تحلیل ارزش سرمایه‌گذاری در پروژه
2. توابع محاسبه اقساط وام و ارزش سرمایه
3. توابع محاسبه استهلاک دارایی‌
4. توابع تحلیل اوراق بهادار

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

در زبانه «Formula» از نوار ابزار اکسل و بخش «Financial» حدود ۵۵ تابع مختلف مالی تعریف شده است که این چهار دسته پرکاربردترین آن‌ها هستند. توابع مالی به‌خصوص در شبیه‌سازی سناریوهای مالی، بودجه‌ریزی‌های سازمانی، ارائه گزارش‌های مالی، تحلیل سودآوری پروژه‌ها یا جذب سرمایه به‌کار می‌روند.

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

۱. توابع تحلیل ارزش سرمایه‌گذاری در پروژه

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

در این بخش ساختار کلی فرمول و روش استفاده پنج مورد از پرکاربردترین توابع مالی در اکسل برای تحلیل ارزش سرمایه‌گذاری پروژه‌ها یعنی تابع NPV، تابع XNPV، تابع IRR، تابعXIRR، تابع MIRR را توضیح می‌دهیم.

تابع NPV در اکسل

تابع NPV در اکسل برای محاسبه «ارزش فعلی خالص» (Net Present Value) یک پروژه یا سرمایه‌گذاری استفاده می‌شود. با این تابع ارزش سرمایه‌گذاری روی یک پروژه را با توجه به جریان‌های نقدی آینده و در نظر گرفتن نرخ بازده مورد انتظار تعیین می‌کنیم. ساختار کلی فرمول تابع NPV به شکل زیر است.

=NPV(rate, value1, [value2], ...)

در این ساختار هر یک از آرگومان‌ها به این صورت تعریف می‌شوند.

• rate : نرخ بازده مورد انتظار
• value1 ، value2 و …: جریان‌های نقدی آینده

در این فرمول بر خلاف سایر فرمول‌های محاسبه نرخ بازده، سرمایه‌گذاری اولیه پروژه به‌عنوان جریان نقدی وارد نمی‌شود.

مثال

فرض می‌کنیم میزان درآمد یا جریان نقدی سه سال آینده حاصل از سرمایه‌گذاری یک شرکت در پروژه‌ای فرضی را مطابق جدول زیر در اختیار داریم. اگر نرخ بازده سرمایه‌گذاری مورد انتظار تامین‌کنندگان مالی پروژه برابر ۲۰ درصد باشد، در این صورت با نوشتن فرمول =NPV(B4,B2:D2) ارزش فعلی خالص سرمایه‌گذاری محاسبه می‌شود.

حال برای مشخص کردن این موضوع که پروژه مورد نظر با این میزان NPV، ارزش سرمایه‌گذاری دارد یا خیر، کافی است مقدار NPV را از مبلغ سرمایه‌گذاری کم کنیم. در این حالت اگر نتیجه محاسبه عدد منفی بود، پروژه ارزش سرمایه‌گذاری ندارد. به‌عنوان مثال اگر میزان سرمایه اولیه ۵۰۰۰۰ میلیون تومان باشد، اختلاف آن با NPV عدد «۱۲۷۴۱-» به‌دست می‌آید که نشان‌دهنده توجیه‌ناپذیر بودن سرمایه‌گذاری در پروژه است.

تابع XNPV در اکسل

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

ساختار کلی فرمول تابع XNPV به شکل زیر است.

=XNPV(discount_rate, cash_flows, dates)

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

• discount_rate : نرخ بازده
• cash_flows : جریان‌های نقدی مثبت یا منفی
• dates : تاریخ‌های متناظر با هر جریان نقدی

مثال

یک شرکت بین‌المللی فرضی در تاریخ ۱۷ آگوست ۲۰۱۷ مبلغ ۵۰۰ میلیون دلار برای خرید تجهیزات مورد نیاز پروژه‌ای سرمایه‌گذاری کرده است. جریان نقدی ورودی حاصل از این سرمایه‌گذاری در سال‌های بعدی نیز مشخص است. حال می‌خواهیم ارزش فعلی خالص این مجموعه جریان نقدی را با نرخ بازده سرمایه‌گذاری ۱۰ درصد به‌دست آوریم.

در این صورت با نوشتن فرمول =XNPV(B5,B2:F2,B3:F3) محاسبه مورد نظر به‌راحتی انجام می‌شود.

در این جدول از آنجا که مبلغ ۵۰۰ میلیون دلار، جریان نقدی خارج شده از شرکت است، عدد مربوط را با علامت منفی می‌نویسیم.

تابع IRR در اکسل

تابع IRR در اکسل برای محاسبه «نرخ بازده داخلی» (Internal Rate of Return) یک جریان نقدی مشخص به‌کار می‌رود. کسب و کارها از این تابع برای مقایسه و تصمیم‌گیری برای سرمایه‌گذاری یا بررسی میزان سودآوری پروژه‌های مختلف استفاده می‌کنند. ساختار کلی این تابع به شکل زیر است.

=IRR(values,[guess])

در این ساختار هر آرگومان به این صورت تعریف می‌شود.

• values : جریان‌های نقدی مثبت یا منفی
• guess (اختیاری): مقدار پیش‌بینی شده نرخ بازده داخلی مورد انتظار

در‌صورتی‌که آرگومان guess را در فرمول وارد نکنیم، تابع به‌صورت پیش‌فرض مقدار ۱۰ درصد را برای آن در نظر می‌گیرد.

مثال

فرض می‌کنیم مقدار سرمایه‌ اولیه یک شرکت برای تامین مالی پروژه‌ای برابر یک میلیون دلار و جریان‌ نقدی ورودی در طی ۱۰ سال مطابق جدول زیر است. در این صورت با نوشتن فرمول =IRR(B2:B12) نرخ بازده داخلی برابر ۱۴ درصد خواهد بود.

برای یادگیری تکمیلی این مبحث پیشنهاد می‌کنیم فیلم آموزش رایگان توابع مالی NPV و IRR در اکسل از فرادرس را مشاهده کنید.

تابع XIRR در اکسل

تابع XIRR در اکسل برای محاسبه نرخ بازده داخلی (IRR) مجموعه‌ای از جریان‌های نقدی ثبت شده در تاریخ یا بازه‌های زمانی متغیر به‌کار می‌رود. ساختار کلی فرمول این تابع به شکل زیر است.

=XIRR(values, dates, [guess])

در این فرمول تعریف هر کدام از آرگومان‌ها به‌صورت زیر است.

• values : جریان‌های نقدی مثبت یا منفی
• dates : تاریخ‌های متناظر با هر جریان نقدی
• guess (اختیاری): حدس اولیه برای نرخ بازده

اگر مقدار guess را در فرمول وارد نکنیم، اکسل به‌صورت خودکار مقداری تصادفی را برای آن در نظر می‌گیرد.

مثال

بار دیگر جدول جریان نقدی ورودی و خروجی شرکت فرضی قبل را در نظر می‌گیریم. برای محاسبه نرخ بازده داخلی این سرمایه‌گذاری، فرمول =XIRR(B2:F2,B3:F3) را می‌نویسیم. همان‌طور که مشخص است در این فرمول مقدار حدسی نرخ بازده یعنی آرگومان guess را در نظر نگرفته‌ایم.

از آنجا که نرخ بازده مقداری بر حسب درصد است، برای نمایش صحیح نتیجه باید فرمت سلول را به حالت درصد یا «Percentage» تغییر دهیم.

تابع MIRR در اکسل

تابع MIRR در اکسل برای محاسبه نرخ بازده داخلی تعدیل شده (Modified Internal Rate of Return) به‌کار می‌رود. این تابع به‌خصوص در زمانی‌که درآمد یا جریان نقدی حاصل از یک فعالیت در پروژه دیگری سرمایه‌گذاری شود، مورد استفاده قرار می‌گیرد. ساختار کلی این تابع به شکل زیر است.

=MIRR(cash flows, finance rate, reinvestment rate)

در این فرمول هر یک از آرگومان‌ها به‌صورت زیر تعریف می‌شوند.

• cash flows : جریان‌های نقدی مثبت و منفی
• finance rate : نرخ بهره پرداخت شده برای سرمایه‌گذاری مجدد جریان نقدی
• reinvestment rate : نرخ بازده سرمایه‌گذاری مجدد

مثال

فرض می‌کنیم جریان نقدی و درآمدهای حاصل از یک کسب و کار خصوصی برای خرید اوراق قرضه دولتی سرمایه‌گذاری می‌شود. مطابق جدول زیر نرخ بازده داخلی (IRR) این کسب و کار در فعالیت‌های عادی خود برابر ۱۸ درصد است. با این حال مدیر شرکت تصمیم دارد جریان‌های نقدی خود را در خرید اوراق قرضه با بازده ۸ درصد سرمایه‌گذاری کند. در این صورت نرخ بازده تعدیل‌شده (MIRR) طبق فرمول =MIRR(B2:F2,B6,B8) برابر ۱۴ درصد خواهد بود.

مسیر آموزش تکمیلی توابع مالی در اکسل با فرادرس

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

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

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

۲. توابع محاسبه اقساط وام و ارزش سرمایه‌

این دسته از توابع مالی ابزاری مناسب برای تصمیم‌گیری سریع‌تر و دقیق‌تر درباره ارزیابی وام‌ها، طرح‌های بازپرداخت، سرمایه‌گذاری‌‌ها و برنامه‌ریزی‌های مالی شرکت هستند. به کمک این توابع می‌توانیم محاسبات مربوط به اقساط وام، ارزش آینده سرمایه، تعیین نرخ بهره یا تعداد دوره‌های بازپرداخت یک سرمایه‌گذاری را انجام دهیم. در نرم‌افزار اکسل شش تابع PMT، تابع IPMT، تابع PPMT، تابع PV، تابع FV و تابع NPER در قالب پرکاربردترین توابع وام و توابع محاسباتی ارزش سرمایه تعریف شده‌اند.

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

تابع PMT در اکسل

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

=PMT(rate, nper, pv, [fv], [type])

در این ساختار تعریف هر آرگومان به‌صورت زیر است.

• rate : نرخ بهره
• nper : تعداد اقساط وام
• pv : مبلغ کل وام
• fv (اختیاری): مقدار مانده نقدی
• type (اختیاری): نوع پرداخت وام (ابتدا یا انتهای دوره)

در صورت وارد نکردن عدد برای آرگومان fv در فرمول، اکسل به‌صورت خودکار مقدار آن را «صفر» در نظر می‌گیرد. همچنین اگر مقداری برای آرگومان type در فرمول وارد نکنیم، محاسبات با توجه روش پرداخت در پایان هر دوره انجام می‌گیرد. اما اگر عدد «۱» را در نظر بگیریم، محاسبات با توجه به پرداخت در ابتدای هر دوره خواهد بود.

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

=PMT(rate / 12, nper * 12, pv, [fv], [type])

مثال

فرض می‌کنیم فردی یک وام برای بازسازی خانه خود به مبلغ ۵۰۰ میلیون تومان با مدت زمان بازپرداخت ۱۰ سال و نرخ بهره ۵ درصد از بانک دریافت کرده است. برای محاسبه مبلغ ماهیانه هر قسط با نوشتن فرمول =PMT(B2,B4,B6) نتیجه به شکل تصویر زیر خواهد بود.

تابع IPMT در اکسل

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

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

=IPMT(rate, per, nper, pv, [fv], [type])

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

مثال

در مثال قبلی مربوط به وام بازسازی خانه، این‌بار می‌خواهیم سهم بهره ماهانه در قسط اول را به‌دست آوریم. در این حالت در ساختار اصلی تابع، مقدار آرگومان per برابر با یک است. بنابراین با نوشتن فرمول =IPMT(B2 / 12, 1, B4 * 12, B6) ابتدا سهم بهره وام در ماه اول را به‌دست می‌آوریم. سپس با کم کردن نتیجه محاسبه تابع PMT (مبلغ کل پرداختی سالانه برای قسط وام) از نتیجه محاسبه تابع IPMT (مبلغ پرداختی سالانه برای بهره) مقدار سهم پرداختی سالانه بابت اصل وام به‌دست خواهد آمد.

تابع PPMT در اکسل

تابع PPMT در اکسل راه میانبر برای محاسبه سهم اصل وام در هر پرداخت دوره‌ای است. در این حالت بدون نیاز به کم کردن نتیجه محاسبه تابع PMT از تابع IPMT می‌توانیم به‌صورت مستقیم مقدار اصل وام را از پرداخت کل اقساط به‌دست آوریم. ساختار کلی فرمول با در نظر گرفتن تعاریف مربوط به تابع IPMT و تابع PMT به شکل زیر است.

=PPMT(rate, per, nper, pv, [fv], [type])

مثال

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

برای یادگیری بیشتر مفهوم این تابع پیشنهاد می‌کنیم فیلم آموزش رایگان محاسبه قسط وام با تابع PPMT در فرادرس را مشاهده کنید.

تابع PV در اکسل

تابع PV در اکسل برای محاسبه ارزش فعلی (Present Value) یک سرمایه‌گذاری یا مبلغ وام در آینده به‌کار می‌رود. به‌عبارتی با کمک این تابع بررسی می‌کنیم که در وضعیت فعلی برای دریافت مبلغ مشخصی در آینده به شکل وام یا سرمایه‌گذاری در یک پروژه با نرخ بهره مشخص چقدر باید پرداخت کنیم. ساختار کلی تابع به شرح زیر است.

=PV(rate, nper, pmt, [fv], [type])

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

• rate : نرخ بهره
• nper : تعداد اقساط وام یا سرمایه‌گذاری
• pmt : مبلغ پرداختی ثابت در طول مدت وام یا سرمایه‌گذاری شامل اصل وام و سود آن بدون در نظر گرفتن مالیات یا هزینه‌های جانبی
• fv :(اختیاری): مقدار مانده نقدی
• type (اختیاری): نوع پرداخت وام یا سرمایه‌گذاری (ابتدا یا انتهای دوره)

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

=PV(rate/12, nper*12, pmt, [fv], [type])

مثال

فرض می‌کنیم طبق برنامه‌ریزی مالی خود برای دریافت وام بازسازی خانه، قادر به پرداخت ماهی ۲۰ میلیون تومان به مدت ۱۰ سال هستیم. حال با در نظر گرفتن نرخ بهره پنج درصد برای محاسبه مقدار مبلغ وام قابل دریافت مطابق جدول زیر فرمول =PV(B2/12,B4*12,B6) را می‌نویسیم.

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

تابع FV در اکسل

تابع FV در اکسل برای محاسبه ارزش آینده (Future Value) یک سرمایه‌گذاری بر اساس یک نرخ بهره ثابت به‌کار می‌رود. این تابع بیشتر برای انجام محاسبات ارزش سرمایه‌گذاری‌ها با پرداخت‌های دوره‌ای و ثابت یا پرداخت یکباره مورد استفاده قرار می‌گیرد. ساختار کلی تابع FV به شکل زیر است.

=FV(rate,nper,pmt,[pv],[type])

در این ساختار آرگومان‌ها نیز به شکل زیر تعریف می‌شوند.

• rate : نرخ بازده سالانه
• nper : تعداد دوره‌های پرداخت در سال
• pmt : مبلغ پرداختی ثابت در طول مدت سرمایه‌گذاری شامل اصل مبلغ و سود آن بدون در نظر گرفتن مالیات یا هزینه‌های جانبی
• pv (اختیاری): ارزش فعلی یا مبلغ پرداختی اولیه در زمان حال
• type (اختیاری): نوع پرداخت مبلغ (ابتدا یا انتهای دوره)

لازم است توجه داشته باشیم که در تابع FV ارزش آینده پولی را که با پرداخت‌های ماهانه سرمایه‌گذاری می‌کنیم، به‌دست می‌آوریم. در حالی‌که تابع PV ارزش فعلی پولی است که در آینده دریافت یا پرداخت خواهد شد.

مثال

فردی قصد دارد برای راه‌اندازی یک پروژه تولیدی مبلغ ۵۰۰ میلیون تومان به‌عنوان سرمایه اولیه و به مدت پنج سال مبلغ ۱۰۰ میلیون تومان را در یک صندوق سرمایه‌گذاری قرار دهد. حال با در نظر گرفتن نرخ بازده سالانه ۲۵ درصد صندوق، می‌خواهیم مجموع سرمایه و سود حاصل از آن را بعد از پنج سال به‌دست آوریم. در این حالت مطابق جدول زیر فرمول =FV(B2,B3,B5,B4) را می‌نویسیم.

طبق محاسبه انجام شده، در پایان سال پنجم میزان سرمایه پروژه حدود دو میلیارد و سیصد و پنجاه میلیون تومان خواهد بود.

برای تکمیل مسیر یادگیری کار با این تابع پیشنهاد می‌کنیم فیلم آموزش رایگان استفاده از تابع مالی FV برای محاسبه ارزش آتی سرمایه‌گذاری در اکسل در فرادرس را مشاهده کنید.

تابع NPER در اکسل

تابع NPER در اکسل برای محاسبه تعداد دوره‌های لازم بازپرداخت یک میزان سرمایه‌گذاری (وام) مشخص بر اساس پرداخت‌ منظم ماهانه یا سالانه معین و نرخ بهره ثابت استفاده می‌شود. ساختار کلی این تابع به شکل زیر است.

=NPER(rate,pmt,pv,[fv],[type])

در این ساختار هر آرگومان به شکل زیر تعریف می‌شود.

• rate : نرخ بهره سالانه
• pmt : مبلغ پرداختی ثابت در طول مدت پرداخت شامل اصل مبلغ و سود آن بدون در نظر گرفتن مالیات یا هزینه‌های جانبی
• pv : ارزش فعلی سرمایه (وام) با توجه به مجموع پرداخت‌های آینده
• fv (اختیاری): ارزش آینده مورد انتظار سرمایه (موجودی نقدی) در پایان دوره پرداخت
• type (اختیاری): نوع پرداخت مبلغ (ابتدا یا انتهای دوره)

در صورت وارد نکردن مقدار برای آرگومان fv ، نرم‌افزار آن را معادل «صفر» در نظر می‌گیرد.

مثال

فردی می‌خواهد وامی به مبلغ پانصد میلیون تومان از بانک بگیرد. او توان پرداخت ۱۰ میلیون تومان در ماه را برای این وام دارد. با فرض در نظر گرفتن نرخ بهره ۱۰ درصد، می‌خواهیم مدت زمان پرداخت کامل مبلغ وام را به‌دست آوریم. در این حالت فرمول را مطابق جدول زیر به شکل =NPER(B2/12,B4,B3) می‌نویسم. همان‌طور که در تصویر مشخص است، مدت زمان تسویه کامل وام ۶۵ ماه یا حدود ۵/۵ سال خواهد بود.

در مطلب زیر آموزش تکمیلی را برای یادگیری بهتر انواع توابع وام در اکسل ارائه کرده‌ایم.

۳. توابع محاسبه استهلاک دارایی‌

توابع استهلاک در اکسل برای ساده کردن محاسبه کاهش تدریجی ارزش دارایی‌های‌های ثابت در طول زمان استفاده می‌شوند. این توابع در حسابداری، برنامه‌ریزی مالیاتی، بودجه‌بندی‌ پروژه و تصمیم‌گیری‌های سرمایه‌گذاری کاربرد دارند.

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

تابع DB در اکسل

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

=DB(cost, salvage, life, period, [month])

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

• cost : هزینه اولیه یا ارزش دارایی
• salvage : ارزش باقی‌مانده دارایی در پایان دوره استهلاک (ارزش اسقاطی)
• life : تعداد دوره استهلاک یا عمر مفید دارایی
• period : دوره‌ مورد نظر برای محاسبه استهلاک
• month (اختیاری): تعداد ماه‌های سال اول

اگر آرگومان month را در فرمول وارد نکنیم، به‌صورت پیش‌فرض عدد «۱۲» در نظر گرفته می‌شود.

مثال

می‌خواهیم میزان استهلاک نزولی سال دوم دارایی به ارزش یک میلیارد تومان با ارزش باقی‌مانده ۱۰۰ میلیون تومان با عمر شش سال را محاسبه کنیم. در این حالت طبق تصویر، فرمول =DB(B2,B3,B4,B5) را می‌نویسیم. مبلغ استهلاک نزولی دارایی در سال دوم حدود دویست و هفده میلیون تومان خواهد بود.

تابع SLN در اکسل

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

=SLN(cost, salvage, life)

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

• cost : هزینه اولیه یا ارزش دارایی
• salvage : ارزش باقی‌مانده دارایی در پایان دوره استهلاک (ارزش اسقاطی)
• life : تعداد دوره استهلاک یا عمر مفید دارایی

مثال

در مثال قبلی، برای محاسبه میزان استهلاک دارایی به روش خط مستقیم فرمول =SLN(B2,B3,B4) را می‌نویسم. این استهلاک بر خلاف نوع نزولی که روند کاهش‌پذیر دارد، هر سال مبلغی ثابتی است.

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

تابع SYD در اکسل

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

=SYD(cost, salvage, life, per)

در این ساختار هر یک از آرگومان‌ها به‌صورت زیر تعریف می‌شوند.

• cost : هزینه اولیه یا ارزش دارایی
• salvage : ارزش باقی‌مانده دارایی در پایان دوره استهلاک (ارزش اسقاطی)
• life : تعداد دوره استهلاک یا عمر مفید دارایی
• per : دوره مورد نظر برای محاسبه استهلاک

مثال

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

در مطلب زیر از مجله فرادرس سایر توابع مربوط به استهلاک را به‌صورت کامل‌ توضیح داده‌ایم.

۴. توابع تحلیل اوراق بهادار

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

در این بخش چهار مورد از پرکاربردترین توابع مالی در اکسل برای تحلیل اوراق بهادار یعنی تابع YIELD، تابع PRICE، تابع DURATION و تابع DISC را توضیح می‌دهیم.

تابع YIELD در اکسل

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

=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

آرگومان‌ها در این ساختار به‌صورت زیر تعریف می‌‌شوند.

• settlement : تاریخ انتقال اوراق از فروشنده به خریدار (تاریخ تسویه)
• maturity : تاریخ پرداخت اصل اوراق (تاریخ سررسید)
• rate : نرخ سود سالانه
• pr : قیمت اوراق (بر حسب درصد ارزش اسمی)
• redemption : مبلغ پرداختی در زمان سررسید (قیمت بازخرید)
• frequency : تعداد دفعات پرداخت سود در سال
• basis (اختیاری): نوع شمارش روزها

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

مثال

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

• تاریخ تسویه: ۰۱-۰۱-۲۰۲۴
• تاریخ سر رسید: ۳۰-۰۶-۲۰۳۰
• نرخ سود سالانه: ۱۰ درصد
• قیمت عرضه در بازار: ۱۰۱ دلار
• قیمت بازخرید: ۱۰۰ دلار
• دفعات پرداخت سود: چهار بار در سال

حال می‌خواهیم بازده یا سود واقعی این اوراق را به‌دست آوریم. همان‌طور که در جدول زیر مشخص است، با نوشتن فرمول =YIELD(DATE(2024,1,1),DATE(2030,6,30),B4,B5,B6,B7,0) بازده سرمایه‌گذاری خرید این اوراق برابر ۹/۷۹ درصدخواهد بود.

تابع PRICE در اکسل

تابع PRICE در اکسل برای محاسبه قیمت یک برگه اوراق قرضه به‌ازای هر ۱۰۰ دلار ارزش اسمی آن بر اساس یک نرخ بهره مشخص استفاده می‌شود. در تحلیل‌های مالی این تابع زمانی کاربرد دارد که بخواهیم منابع مالی خود را در عوض فروش سهام از طریق انتشار اوراق قرضه تامین کنیم. ساختار کلی این تابع به شکل زیر است.

=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

هر یک از آرگومان‌ها در این ساختار به صورت زیر تعریف می‌شوند.

• settlement : تاریخ انتقال اوراق از فروشنده به خریدار (تاریخ تسویه)
• maturity : تاریخ پرداخت اصل اوراق (تاریخ سررسید)
• rate : نرخ سود سالانه
• yld : نرخ بازده مورد انتظار بازار یا سرمایه‌گذار
• redemption : مبلغ پرداختی در زمان سررسید (قیمت بازخرید)
• frequency : تعداد دفعات پرداخت سود در سال
• basis (اختیاری): نوع شمارش روزها

مثال

فرض می‌کنیم فردی در نظر دارد قیمت یک برگه اوراق قرضه شرکت بین‌المللی که در تاریخ ۰۱-۰۱-۲۰۲۴ خریداری کرده است را محاسبه کند. سایر مشخصات خرید این اوراق به شرح زیر است.

• تاریخ سررسید: ۳۰-۰۶-۲۰۳۰
• نرخ سود سالانه: ۱۰ درصد
• نرخ بازده: ۹ درصد
• قیمت بازخرید: ۱۰۰ دلار
• دوره پرداخت: شش ماهه (دو بار پرداخت سود در سال)

بنابراین مطابق تصویر زیر با نوشتن فرمول =PRICE(DATE(2024,1,1),DATE(2030,6,30),B4,B5,B6,B7,0) قیمت اوراق قرضه برابر ۱۰۴/۸۴ دلار محاسبه می‌شود.

تابع DURATION در اکسل

تابع DURATION در تحلیل‌های مالی اکسل برای محاسبه مدت زمان مکالی (Macauley Duration) اوراق بهادار یا مدت زمان باقی‌مانده تا تاریخ سرررسید استفاده می‌شود. این شاخص نشان می‌دهد که ارزش اوراق به چه اندازه نسبت به تغییرات نرخ بهره حساس است. هر اندازه مقدار مکالی برای یک اوراق قرضه بیشتر باشد، حساسیت آن نسبت به تغییرات نیز بالاتر خواهد بود. ساختار کلی تابع به شکل زیر است.

=DURATION(settlement, maturity, coupon, yld, frequency, [basis])

در این ساختار هر یک از آرگومان‌ها به صورت زیر تعریف می‌شوند.

• settlement : تاریخ انتقال اوراق از فروشنده به خریدار (تاریخ تسویه)
• maturity : تاریخ پرداخت اصل اوراق (تاریخ سررسید)
• coupon : نرخ بهره سالیانه اوراق
• yld : نرخ بازده مورد انتظار بازار یا سرمایه‌گذار
• redemption : مبلغ پرداختی در زمان سررسید (قیمت بازخرید)
• frequency : تعداد دفعات پرداخت سود در سال
• basis (اختیاری): نوع شمارش روزها

مثال

می‌خواهیم مدت زمان اوراق قرضه‌ خریداری شده در تاریخ ۰۱-۰۱-۲۰۲۴ مربوط به یک شرکت فرضی بین‌المللی با سایر مشخصات زیر را محاسبه کنیم.

• تاریخ سررسید: ۳۰-۰۶-۲۰۳۰
• نرخ بهره سالانه: ۶ درصد
• نرخ بازده: ۵ درصد
• تعداد دفعات پرداخت:‌ فصلی (چهار بار در سال)

در این حالت با نوشتن فرمول =DURATION(DATE(2024,1,1),DATE(2030,6,30),B4,B5,B6) مطابق تصویر زیر مدت زمان برابر ۵/۵ سال خواهد بود.

تابع DISC در اکسل

تابع DISC در اکسل برای محاسبه نرخ تنزیل (Discount Rate) اوراق بهادار بر حسب درصد استفاده می‌شود. این شاخص به‌خصوص در مقایسه سوددهی اوراق قرضه کوتاه مدت کاربرد دارد. ساختار کلی تابع به شکل زیر است.

=DISC(settlement, maturity, pr, redemption, [basis])

در این ساختار هر یک از آرگومان‌ها به شکل زیر تعریف می‌شوند.

• settlement : تاریخ انتقال اوراق از فروشنده به خریدار (تاریخ تسویه)
• maturity : تاریخ پرداخت اصل اوراق (تاریخ سررسید)
• pr : قیمت ورق بهادار
• redemption : مبلغ پرداختی در زمان سررسید (قیمت بازخرید)
• basis (اختیاری): نوع شمارش روزها

مثال

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

• تاریخ تسویه: ۰۱-۱۲-۲۰۱۷
• تاریخ سررسید: ۰۱-۱۲-۲۰۲۰
• قیمت اوراق: ۹۰ دلار
• قیمت بازخرید: ۱۰۰ دلار

برای محاسبه نرخ تنزیل این اوراق مطابق تصویر زیر فرمول=DISC(DATE(2017,12,1),DATE(2020,12,1),B4,B5) را می‌نویسیم. در این حالت نرخ تنزیل برابر ۳ درصد خواهد بود.

جمع‌بندی پایانی

توابع مالی در اکسل محاسبات مربوط به اقساط وام، تحلیل سرمایه‌گذاری‌های پروژه و فعالیت‌های مهم حسابداری را ساده‌تر می‌کنند. کار با این توابع نیاز به یادگیری ترفندهای فرمول‌نویسی اکسل دارد تا با کمترین میزان درگیر شدن با انواع پیام‌های خطا در این نرم‌افزار، خروجی مورد نظر را به‌دست آوریم. در این مطلب از مجله فرادرس آموزش مقدماتی را برای کار با ۱۸ تابع پرکاربرد مالی در اکسل ارائه کردیم. آشنایی با فرمول‌نویسی هر تابع در چهار دسته اصلی توابع تحلیل سرمایه‌گذاری، توابع استهلاک دارایی، توابع محاسبه اقساط وام و توابع تحلیل اوراق بهادار نقطه شروعی برای یادگیری‌های تکمیلی علاقه‌مندان در آینده خواهد بود. زیرا انجام محاسبات تخصصی و پیچیده در دنیای واقعی امور مالی و حسابداری نیازمند یادگیری پیشرفته فرمول‌نویسی با این توابع در اکسل است.