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

آخرین به‌روزرسانی: 27 دی 1401

در فصل گذشته به بررسی توابع  XIRR, IRRو MIRR پرداختیم, در این فصل به بررسی توابع مالی اکسل به همراه مثال های متعدد می پردازیم.

سؤال‌هایی که در این فصل به آن‌ها پاسخ‌خواهیم داد:

• می‌خواهید یک دستگاه کپی بخرید. آیا ترجیح می‌دهید همین امروز 11000 دلار برای آن بپردازید یا اینکه به مدت پنج سال هرسال 3000 دلار برای آن پرداخت کنید؟
• اگر شخصی در انتهای 40 سال آینده، هرسال 2000 دلار برای بازنشستگی سرمایه‌گذاری کرده باشد و از این سرمایه‌گذاری هشت درصد سود کسب کند، به هنگام بازنشستگی چقدر به دست خواهد آورد؟
• شخصی 10000 دلار برای 10 ماه با نرخ بهره سالیانه 8 درصد وام گرفته است. پرداخت ماهیانه این شخص چقدر است؟ هرماه چقدر اصل و چقدر سود پرداخت خواهد کرد؟
• می‌خواهم 8000 دلار وام بگیرم و ماهیانه در طول ده سال آن را پرداخت کنم. حداکثر مبلغی که می‌توانم ماهیانه بپردازم 1000 دلار است. حداکثر بهره‌ای که می‌توانم بپردازم چقدر است؟
• اگر 100000 دلار وام با بهره 8 درصد گرفته‌شده و هرسال 10000 دلار پرداخته شود چند سال طول می‌کشد تا تمام بدهی پرداخت شود؟
• حسابدار دارای مدرک CPA هستم و اغلب از روش‌های پیچیده‌ای برای به دست آوردن هزینه استهلاک ماشین‌ آلات استفاده می‌کنم. آیا اکسل توابعی دارد که بتوانم این استهلاک‌ها را محاسبه کنم؟

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

پاسخ به سؤالات این فصل:

می‌خواهید یک دستگاه کپی بخرید. آیا ترجیح می‌دهید همین امروز 11000 دلار برای آن بپردازید یا اینکه به مدت پنج سال هرسال 3000 دلار برای آن پرداخت کنید؟

راه‌حل پاسخ به این سؤال آن است که بتوانید ارزش پرداخت‌های 3000 دلاری سالیانه را پیدا کنید. فرض کنید که هزینه سرمایه در هرسال 12 درصد است. در این صورت می‌توانید از تابع NPV برای پاسخ دادن به این سؤال استفاده کنید، اما تابع PV اکسل راه سریع‌تری برای حل این مشکل ارائه می‌دهد. به جریانی نقدی که شامل همان مقدار وجوه نقد ناشی از هزینه (ناشی از درآمد) در هر دوره زمانی است مبلغ سالیانه (Annuity) گفته می‌شود. فرض کنید که نرخ بهره هر دوره زمانی یکسان باشد، آن‌وقت می‌توان میزان مبلغ سالیانه را با استفاده از تابع PV اکسل محاسبه کرد. تابع PV ارزش نقدی مجموعه‌ای از پرداخت‌های آینده را با فرض پرداخت‌های دوره‌ای ثابت و نرخ بهره ثابت را محاسبه و نشان می‌دهد. دستور زبان تابع PV عبارت است از: PV(rate,#per,[pmt],[fv],[type]) به شیوه‌ای که آرگومان‌های pmt ،fv و type آرگومان‌هایی اختیاری می‌باشند.

توجه: هنگام کار با توابع مالی نرم‌افزار اکسل، اغلب از نمادهای قراردادی زیر برای علائم pmt (پرداخت) و fv (ارزش آتی) استفاده می‌شود. پول دریافت شده علامت مثبت و پول پرداخت‌شده علامت منفی می‌گیرد.

• Rate نرخ بهره در هر دوره زمانی است. مثلاً اگر پولی را با بهره 6 درصد در سال قرض گرفته باشید و دوره زمانی یک سال باشد بنابراین rate=0.06 است، اگر دوره زمانی یک ماه باشد بنابراین rate=0.06/12=0.005 است.
• #per عدد دوره‌های موجود در مبلغ سالیانه است. مثلاً در مورد مثال خرید دستگاه کپی،#per=5 است. اگر قرار باشد پرداخت‌ها به مدت پنج سال، ماهیانه انجام شوند آنگاه#per=60 می‌شود. البته نرخ بهره نیز می‌بایست در موارد استفاده از#per موردتوجه قرار گیرد؛ یعنی اگر دوره زمانی #per ماهیانه است می‌بایست از نرخ بهره ماهیانه استفاده شود و اگر دوره زمانی #per سالیانه است می‌بایست از نرخ بهره سالیانه استفاده شود.
• Pmt میزان پرداخت در هر دوره است. در مثال خرید دستگاه کپی pmt=$-3000 است. مبالغ پرداختی با علامت منفی نشان داده می‌شوند درحالی‌که مبالغ دریافتی علامت مثبت دارند. حداقل یک مورد از pmt یا fv‌ می‌بایست لحاظ شود.
• Fv تراز نقدی (ارزش آتی) است که می‌خواهید در آخرین پرداخت کسب شود. در مثال خرید دستگاه کپی fv=0 است. مثلاً اگر می‌خواهید بعد از آخرین پرداخت 500 دلار موجودی داشته باشید بنابراین fv=500 می‌شود. اگر می‌خواهید در انتهای مسئله 500 دلار اضافی بپردازید بنابراین fv=$-500‌ می‌شود. اگر fv حذف شود، مقدار آن برابر با صفر در نظر گرفته می‌شود.
• مقدار type معمولاً یا صفر است و یا یک و مشخص‌کننده زمانی است که پرداخت‌ها انجام‌شده‌اند. وقتی‌که type حذف یا مقدار آن صفر در نظر گرفته شود نشان می‌دهد که پرداخت‌ها در انتهای هر دوره انجام‌شده‌اند. وقتی‌که type=1 باشد نشان می‌دهد که پرداخت‌ها در ابتدای هر دوره زمانی انجام‌شده‌اند. توجه کنید که می‌توانید در تمام توابع این فصل به‌جای 1 از عبارت True و به‌جای صفر از عبارت False استفاده کنید.

تصویر 1-10 کاربرگ PV از فایلی بنام Excelfindunctions.xlsx و راه‌حل مشکل خرید دستگاه کپی را به نمایش می‌گذارد.

تصویر 1-10 مثالی از تابع PV

در سلول B3 ارزش فعلی پرداخت مبالغ 3000 دلاری در انتهای هرسال به مدت پنج سال با دوازده درصد هزینه سرمایه با استفاده از فرمول =PV(0.12,5,–3000,0,0) محاسبه‌شده است. تابع

NPV اکسل برای این مسئله مقدار برابر با 10,814.33‌دلار را به ما می‌دهد. همان جواب با حذف دو آرگومان آخر با فرمول:=PV(0.12,5,–3000) به دست می‌آید؛ بنابراین اگر پرداخت‌ها را در آخر هرسال انجام دهیم معامله بهتری خواهیم داشت تا اینکه همین امروز 11000 دلار را بپردازیم.

اگر پرداخت‌های 3000 دلاری دستگاه کپی در ابتدای هرسال به مدت پنج سال انجام شوند،NPV پرداخت‌ها در سلول B4 با فرمول: =PV(0.12,5,– 3000,0,1) محاسبه می‌شود. توجه کنید که تغییر آخرین آرگومان از 0 به 1، دوره محاسبه را از انتهای سال به ابتدای سال تغییر می‌دهد. می‌بینیم که ارزش فعلی پرداخت‌ها 12.112.50 دلار است؛ بنابراین بهتر است همین امروز 11000 دلار پرداخته شود تا اینکه پرداخت‌ها در ابتدای سال انجام شود.

فرض کنید که هرسال 3000 دلار در پایان سال می‌پردازید که شامل 500 دلار پرداخت اضافه در پایان سال پنجم نیز می‌شود. می‌توانید ارزش فعلی تمامی پرداخت‌ها را در سلول B5 با تعیین ارزش آتی 500 دلاری با استفاده از فرمول =PV(0.12,5,–3000,–500,0) محاسبه کنید. توجه کنید که جریان نقدی 3000 دلاری و 500 دلاری نشان منفی دارند چراکه پرداخت می‌شوند. ارزش فعلی تمامی این پرداخت‌ها برابر با 11.098.40 دلار است.

اگر شخصی در انتهای هر یک از 40 سال آینده، 2000 دلار برای بازنشستگی سرمایه‌گذاری کرده باشد و هرسال از این سرمایه‌گذاری هشت درصد سود کسب کند، به هنگام بازنشستگی چقدر به دست خواهد آورد؟

در این وضعیت می‌خواهیم ارزش مبلغ سالیانه را به نرخ دلار آتی (40 سال پس از الآن) محاسبه کنیم و نه دلارهای امروز. این محاسبه توسط تابع FV (ارزش آتی) نرم‌افزار اکسل انجام می‌شود. تابع FV ارزش آتی یک سرمایه‌گذاری را با فرض پرداخت‌های ثابت دوره‌ای و نرخ بهره ثابت محاسبه می‌کند. دستور زبان این تابع عبارت است از FV(rate,#per,[pmt],[pv],[type]) که در اینجا pmt، pv و type آرگومان‌های اختیاری می‌باشند.

• Rate نرخ بهره در هر دوره زمانی است. در این مورد میزان rate‌ برابر با 0.08 است.
• #per تعداد دوره‌های زمانی در آینده است که می‌خواهید ارزش آتی آن‌ها را محاسبه کنید. #per‌همچنین تعداد دوره‌هایی است که در طی آن‌ها مبلغ سالیانه دریافت شده است. در این مورد #per برابر با 40 است.
• Pmt میزان پرداختی است که در هر دوره انجام‌شده. در این مورد pmt برابر با 2000-دلار است. علامت منفی مشخص کرده که پول به‌حساب پرداخت‌شده است. حداقل یک مورد از pmt یا pv می‌بایست در اینجا لحاظ شود.
• Pv مقدار پول (به نرخ امروز) است که در حال حاضر مدیون هستیم. در این مورد pv برابر با صفر است. اگر امروز 10000 دلار مدیون باشیم بنابراین pv برابر می‌شود با 10000 دلار، چراکه وام‌دهنده ده هزار دلار به ما داده و آن را دریافت کرده‌ایم. اگر امروز 10000 دلار در بانک داشته باشیم بنابراین pv برابر است با 10000- دلار، چراکه ما 10000 دلار به‌حساب بانکی‌مان پرداخت کرده‌ایم. اگر Pv‌حذف شود، مقدار آن برابر صفر در نظر گرفته می‌شود.
• مقدار type صفر و یا یک است و مشخص‌کننده زمانی است که پرداخت‌ها باید انجام‌شده و یا پول به‌حساب ریخته شود. اگر type برابر صفر باشد یا حذف شود، پول در پایان دوره به بانک سپرده‌شده است. در این مورد type صفر است یا حذف‌شده است. اگر type برابر یک باشد بنابراین پرداخت و یا سپردن پول به بانک در ابتدای دوره زمانی انجام‌شده است.

در کاربرگ FV در فایلی بنام Excelfinfunctions.xlsx (تصویر 2-10) ‌در سلول B3 فرمول

=FV(0.08,40,–2000,0,0) را وارد کرده‌ایم تا محاسبه کنیم که پس‌انداز ما در 40 سال برابر با 518.113.04 دلار خواهد بود. توجه شود که یک مقدار منفی برای پرداخت سالانه اضافه‌شده است. این کار به خاطر آن انجام‌شده که 2000 دلار به‌حساب پرداخت‌شده است.

اگر سپرده‌ها به مدت 40 سال در ابتدای هرسال پرداخت شوند فرمول (واردشده در سلول B4) یعنی =FV(0.08,40,–2000,0,1)، ارزش پس‌انداز چهل سال آینده یعنی 559,562.08 دلار را به دست می‌آورد.

بالاخره فرض کنید که شما به‌اضافه 2000 دلاری که در چهل سال آینده در انتهای هرسال سرمایه‌گذاری می‌کنید 30000 دلار هم سرمایه اولیه دارید. اگر هرسال از سرمایه‌گذاری خود 8 درصد سود به دست آورید، آنگاه وقتی در انتهای 40 سال بازنشسته می‌شوید چقدر سرمایه خواهید داشت؟ با قرار دادن مقدار Pv به 30000- دلار در تابع FV می‌توان به این سؤال پاسخ داد. در اینجا از علامت منفی استفاده‌شده چراکه شما 30000 دلار پرداخت و یا به ‌حسابتان ریخته‌اید. فرمول =FV(0.08,40,–2000,–30000,0) در سلول B5 از کاربرگ FV

ارزش آتی 1,169,848.68 دلار را به دست می‌آورد.

تصویر 2-10 مثالی از تابع FV

شخصی 10000 دلار برای 10 ماه با نرخ بهره سالیانه 8 درصد وام گرفته. پرداخت ماهیانه این شخص چقدر است؟ هرماه چقدر اصل و چقدر بهره پرداخت خواهد کرد؟

تابع PMT اکسل پرداخت‌های دوره‌ای یک وام را با فرض ثابت بودن پرداخت‌ها و نرخ بهره محاسبه می‌کند. دستور زبان این تابع عبارت است از: PMT(rate,#per,pv,[fv],[type]) درحالی‌که fv‌ و type آرگومان‌های اختیاری می‌باشند.

• Rate نرخ بهره پیش از دوره پرداخت وام است. در این مثال از یک ماه دوره پرداخت استفاده‌شده بنابراین Rate برابر است با 08/12=0.006666667.
• #per تعداد پرداخت‌های انجام‌شده است. در این مورد #per برابر است با 10.
• Pv ارزش فعلی تمام‌پرداخت‌ها است. این یعنی pv برابر است با میزان وام گرفته‌شده. در این مورد pv برابر است با 10000 دلار. Pv در اینجا مثبت است چراکه ما 10000 دلار دریافت می‌کنیم.
• Fv مشخص‌کننده ارزش آتی مانده نهایی وام است که می‌خواهید بعد از آخرین پرداخت داشته باشید. در این مورد fv برابر با صفر است. اگر fv‌حذف شود، اکسل فرض را بر آن می‌گیرد که مقدارش برابر با صفر است. فرض کنید که وام غیر اقساطی گرفته‌اید که می‌خواهید آن را در انتهای هرماه پرداخت کنید اما در خاتمه وام کل میزان وام را بازپرداخت یکجای 1000 دلاری می‌پردازید؛ بنابراین در اینجا fv برابر 1000-دلار است. مبلغ هزار دلار منفی است چراکه شما آن را پرداخت می‌کنید.
• مقدار type صفر و یا یک است و زمان پرداخت را مشخص می‌کند. اگر type‌برابر صفر یا حذف‌شده باشد، پرداخت‌ها در انتهای دوره انجام‌شده‌اند. در اینجا پرداخت‌ها در انتهای ماه فرض شده‌اند بنابراین مقدار type صفر است یا حذف‌شده است. اگر type برابر با یک باشد، پرداخت‌ها و یا ریخته شدن پول‌ها به‌حساب در آغاز دوره انجام‌شده است.

در سلول G1 از کاربرگ PMT در فایلی بنام Excelfinfunctions.xlsx(تصویر 3-10) پرداخت ماهیانه یک وام ده‌ماهه به میزان 10000 دلار با فرض اینکه نرخ بهره سالانه 8 درصد و پرداخت‌ها در انتهای ماه صورت می‌گیرند با فرمول =–PMT (0.08/12,10,10000,0,0) محاسبه‌شده است. پرداخت ماهیانه 1,037.03 دلار است. تابع PMT به‌تنهایی مقداری منفی برمی‌گرداند چراکه در اینجا به شرکتی که وام داده است پول پرداخت می‌شود.

چنانکه دوست داشته باشید می‌توانید از توابع IPMT‌ و PPMT اکسل نیز برای محاسبه میزان بهره پرداخت‌شده برای وام در هرماه و مبلغ موجودی پرداخت‌شده در هرماه استفاده کنید. این را پرداخت اصل مبلغ می‌گویند.

تصویر 3-10 مثال‌هایی از توابع PMT، PPMT، CUMPRINC، CUMIPMT و IPMT

برای مشخص کردن سود پرداخت‌شده در هرماه از تابع IPMT(پرداخت بهره) استفاده کنید. دستور زبان تابع IPMT عبارت است از IPMT(rate,per,#per,pv,[fv],[type]) که در اینجا fv و type آرگومان‌های اختیاری هستند. آرگومان per تعداد دوره‌هایی را که بهره آن‌ها محاسبه می‌شوند را مشخص می‌کند. سایر آرگومان‌ها هم همان معنای خود در تابع PMT رادارند. درست همانند همان مثال برای مشخص کردن ما به ازای پرداخت‌شده در هرماه نسبت به اصل مبلغ می‌توانید از تابع PPMT (پرداخت اصل) استفاده کنید. دستور زبان تابع PPMT‌عبارت است از PPMT(rate,per,#per,pv,[fv],[type]). معنای هر یک از آرگومان‌های این دستور زبان درست مثل تابع IPMT است. با کپی کردن فرمول

PPMT (0.08/12,C6,10,10000,0,0)-= از سلول F6 به محدوده F7:F16 می‌توانید میزان پرداخت هرماه که از اصل مبلغ کم می‌شود را حساب کنید. مثلاً در ماه اول شما تنها 970.37 دلار از اصل مبلغ را می‌پردازید. همان‌طور که انتظار می‌رود عدد اصل مبلغ پرداخت ، هرماه افزایش پیدا می‌کند. علامت منفی (پیش از PPMT) علامتی لازم است چراکه اصل مبلغ به خاطر وامی که گرفته‌اید به شرکت پرداخت‌شده است، بنابراین PPMT مبلغی منفی را به ما می‌دهد. با کپی کردن فرمول

=–IPMT(0.08/12,C6,10,10000,0,0) از سلول G6 به محدوده G7:G16 می‌توان میزان بهره پرداخت‌شده در هرماه را حساب کرد. مثلاً در ماه اول 66.67 دلار بهره پرداخت‌شده است. البته میزان بهره پرداخت‌شده در هرماه روندی کاهشی دارد.

توجه کنید که در هرماه: (بهره پرداخت‌شده)+(پرداخت اصل مبلغ)=(مبلغ کل). برخی اوقات مبلغ کل به خاطر گرد کردن مقدار بسیار اندکی متفاوت خواهد بود.

می‌توان تراز نهایی را برای هرماه در ستون H با استفاده از رابطه:

(تراز نهایی ماه اول)=( تراز ابتدایی ماه اول)- (پرداخت اصل مبلغ ماه اول) ایجاد کرد. توجه کنید که در ماه اول تراز ابتدایی برابر با 10000 دلار است. در ستونD  تراز ابتدایی شروع هرماه را با استفاده از رابطه:

(برای t=2,3…10) (تراز t ابتدای هرماه) =( تراز t-1 انتهای هرماه) ایجاد کرده‌ایم.

البته تراز انتهایی ماه دهم برابر صفر است (در سلول H15) که باید هم همین‌طور باشد.

بهره هرماه را می‌توان با رابطه: (بهره ماه اول)= (نرخ بهره) *( تراز t ابتدای هرماه) محاسبه کرد. مثلاً بهره ماه سوم را می‌توان به این شکل محاسبه کرد=(0.0066667)*($8,052.80) که برابر با 53.69 دلار است.

البته میزان NPV تمامی پرداخت‌ها برابر با 10000 دلار است. این مسئله را با فرمول NPV(0.08/12,E6:E15) در سلول D17 امتحان می‌کنیم. توجه کنید که فرمول سلول D17

=NPV(E1,E6:E15) است و فرمول سلول E1 برابر =0.08/12 ‌ است. (تصویر 3-10 را ببینید)

چنانچه پرداخت‌ها در ابتدای هرماه انجام شوند، میزان هر پرداخت را می‌تواند در سلول D19 با استفاده از فرمول =–PMT(0.08/12,10,10000,0,1) محاسبه کرد. تغییر آرگومان آخر به عدد یک، دوره هر پرداخت را به ابتدای ماه تغییر می‌دهد. چراکه وام‌دهنده پولش را زودتر دریافت می‌کند، به همین دلیل پرداخت‌های ماهیانه ابتدای ماه کمتر از پرداخت‌هایی هستند که در موارد انتهای ماه انجام می‌شوند. اگر پرداخت در ابتدای ماه انجام شود، پرداخت ماهیانه 1,030,16‌دلار است.

در انتها فرض کنید که می‌خواهید در انتهای ده ماه ، یکجا مبلغ 1000 دلار پرداخت کنید. اگر قرار بوده پرداخت‌های ماهیانه را در انتهای ماه انجام دهید، فرمول موردنظر PMT(0.08/12,10,10000, –1000,0)-= ‌در سلول D20 پرداخت ماهیانه شمارا حساب خواهد کرد. این مبلغ ماهیانه 940 دلار خواهد بود. ازآنجاکه 1000 دلار از وام از طریق پرداخت‌های ماهیانه پرداخت‌ نشده است، اینکه پرداخت ماهیانه جدید کمتر از پرداخت ماهیانه آخر ماه یعنی 1,037.03 ‌دلار باشد عاقلانه به نظر می‌رسد.

توابع  CUMPRINC و CUMIPMT

اغلب پیش می‌آید که بخواهید بهره و اصل را در طی چند دوره پرداخت باهم جمع بزنید. توابع CUMPRINC و CUMIPMT این کار را در یک‌چشم به هم زدن انجام می‌دهند.

تابع CUMPRINC (جمع پرداخت اصل) مبلغ اصل پرداخت‌شده بین دو دوره (به‌صورت تجمعی) را محاسبه می‌کند. دستور زبان تابع CUMPRINC عبارت است از:

CUMPRINC(rate,#per,pv,start period,end period,type). آرگومان‌های Rate، #per، pv‌ و type همان معانی پیشین را دارند

تابع CUMIPMT (جمع بهره ) بهره‌های پرداخت‌شده در میان دو دوره را محاسبه می‌کند (به‌صورت تجمعی) دستور زبان این تابع عبارت است از:

CUMIPMT(rate,#nper,pv,start period,end period,type). آرگومان‌های Rate، #per، pv‌ و type همان معانی پیشین رادارند. مثلاً در سلول F19‌ از کاربرگ PMT میزان بهره پرداخت‌شده در طی ماه‌های دو تا چهار (1616.01 دلار) با استفاده از فرمول:

=CUMIPMT(0.08/12,10,10000,2,4,0) محاسبه‌شده است. در سلول G19 پرداخت اصل در طی ماه دو تا چهار (2.950.08 دلار) ‌با استفاده از فرمول =CUMPRINC(0.08/12,10,10000,2,4,0)‌ محاسبه‌شده است.

می‌خواهم 80000 دلار وام بگیرم و ماهیانه در طول ده سال آن را پرداخت کنم. حداکثر مبلغی که می‌توانم ماهیانه بپردازم 1000 دلار است. حداکثر بهره‌ای که می‌توانم بپردازم چقدر است؟

تابع Rate با توجه به میزان وامی که گرفته‌شده، مدت‌زمان پرداخت آن و پرداخت در هر دوره به شما نرخ بهره وام را ارائه می‌دهد. دستور زبان تابع Rate عبارت است از:

RATE(#per,pmt,pv,[fv],[type],[guess]) به شکلی که Fv، type و guess‌ آرگومان‌های انتخابی می‌باشند. آرگومان‌های #per، pmt،pv،fv‌ و type همان معانی پیشین مطرح‌شده در سؤالات قبلی رادارند. آرگومان guess‌ به‌سادگی نرخ بهره وام را حدس می‌زند. ‌معمولاً می‌توان guess را حذف کرد. واردکردن فرمول =RATE(120,–1000,80000,0,0,) در سلول D9 از کار برگ Rate (در فایل Excelfinfunctions.xlsx) مقدار 0.7241  درصد را به‌عنوان بهره ماهیانه به ما می‌دهد. (تصویر 4-10)

تصویر 4-10 مثالی از تابع Rate

تابع Rate در سلول D15 ‌برای محاسبه بکار گرفته‌شده است. فرمول

=PV(.007241,120,–1000.000) مقدار $80,000.08 را به ما می‌دهد. این مورد نشان می‌دهد که پرداخت‌های 1000 دلاری در انتهای ماه به مدت 120 ماه ارزش فعلی‌ای برابر با 80.000.8‌ دلار دارند.

اگر بتوانید در آخر120 ماه 10.000 دلار بپردازید، حداکثر بهره‌ای که می‌توانید از پس آن برآیید را با فرمول =RATE(120,–1000,80000,–10000,0,0) محاسبه می‌کنیم. واردکردن این فرمول در سلول D12 بهره ماهیانه 0.818  درصد را به ما می‌دهد.

اگر 100000 دلار وام با بهره 8 درصد گرفته‌شده و هرسال 10000 دلار پرداخته شود چند سال طول می‌کشد تا تمام بدهی پرداخت شود؟

تابع NPER (تعداد دوره‌های پرداخت) با در نظر گرفتن مقدار یک وام، پرداخت‌های هر دوره و نرخ بهره وام به شما نشان می‌دهد که چند قسط یا دوره پرداخت طول می‌کشد تا وام به‌طور کامل پرداخت شود. دستور زبان این تابع عبارت است از NPER(rate,pmt,pv,[fv],[type]) توجه کنید که آرگومان‌های fv و type آرگومان‌هایی اختیاری هستند.

در سلول D7 از کار برگ Nper (درفایل excelfinfunttionc.xlsx) فرمول

=NPER(0.08,–10000,100000,0,0) با در نظر گرفتن دوره پرداخت آخر سال 20.91  سال را به دست می‌آورد (تصویر 5-10) بنابراین بیست سال پرداخت برای پرداخت کامل قسط مناسب نیست و 21 سال هم باعث ایجاد اضافه پرداخت می‌شود. برای تائید این محاسبه در سلول D10 و D11 از تابع PV استفاده می‌کنیم تا نشان دهیم که پرداخت 10000 دلار در هرسال برای مدت 20 سال به پرداخت کلی $98,181.47 دلار ختم می‌شود و پرداخت 10000 دلار به مدت 21 سال به پرداخت کلی $100,168.03 دلار ختم می‌شود.

تصویر 5-10 مثالی از تابع NPER

فرض کنید که می‌خواهید در آخرین دوره پرداخت، 40000 دلار بپردازید. چند سال طول می‌کشد تا تمامی وام را بازپرداخت کنید؟ در سلول D14 فرمول

=NPER(0.08,–10000,100000, –40000,0) را وارد کرده‌ایم که نشان می‌دهد که این زمان بازپرداخت وام 15.90  سال خواهد بود؛ بنابراین نتیجه می‌گیریم 15 سال زمان کافی برای بازپرداخت وام نیست و 16 سال نیز کمی باعث اضافه پرداخت خواهد شد.

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

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

• روش محاسبه مستقیم استهلاک (SLN)
• روش مجموع ارقام سنوات (SYD)
• روش استهلاک مانده نزولی مضاعف (DDB)

بیائید ماشینی را در نظر بگیریم که 15000 دلار می‌ارزد و در نهایت پس ازپنج سال استهلاک، ارزش 3000 دلار (ارزش اسقاطی) پیدا می‌کند. سؤال این است که روش‌های متفاوت محاسبه استهلاک چگونه استهلاک $15,000 – $3000 = $12,000 را در طول پنج سال اختصاص می‌دهند؟

• روش محاسبه مستقیم (SLN) به‌سادگی ارزش ماشین را در هرسال به صورت مساوی‌ کم می‌کند. (در این مورد 12000 دلار تقسیم ‌بر 5 سال مساوی 2400 دلار در هر سال است)
• وقتی‌که تعداد کل سال‌ها N است، روش مجموع ارقام سنوات (SYD) در طی سال اول کسری (N-I+1)/(N*(N+1)/2) از ارزش هزینه‌ – اسقاط کم می‌کند. (N*(N+1)/2) جمع متغیرهای 1, 2, [el],N است. در این مثال 15/5 کل استهلاکی است که در سال اول رخ می‌دهد 15/4 استهلاکی است که در سال دوم رخ می‌دهد و به همین روال ادامه دارد.
• چنانچه ارزش دفتری (Book value) را برابر با هزینه منهای جمع استهلاک بدانیم، آن‌وقت برای محاسبه استهلاک N سال‌، روش استهلاک مانده نزولی مضاعف (DDB) میزان استهلاک را در طی یک سال از طریق فرمول (2*Book Value)/N محاسبه می‌کند. در مثال موردنظر ما N=5‌ است. در طول هرسال استهلاک می‌بایست برابر 40 درصد ارزش دفتری باشد. بدبختانه روش DDB، آن‌گونه که توصیف شد میزان دقیقی برابر با ارزش هزینه‌ – اسقاط را به استهلاک تخصیص نمی‌دهد. در چنین مواردی متد DDB استهلاک را در طی آخرین سال‌ها مورد تسویه قرار می‌دهند بنابراین استهلاک نهایی برابر با ارزش هزینه‌ – اسقاط خواهد بود. در مثال موردنظر ما DDB در سال اول میزان

0.4*$15,000 = $6000، در سال دوم 0.4*$9000 = $3600 و در سال سوم 0.4*($5400) = $2160 را تخصیص می‌دهد. چنانچه میزان 0.4*($3240) = $1296 را به استهلاک سال چهارم تخصیص دهیم آن‌وقت کل استهلاک ما 12000 دلار خواهد بود؛ بنابراین

استهلاک سال چهارم برابر است با = $12,000 – ($6000+ $3600 + $2160) = $240 و استهلاک سال پنجم برابر با صفر است.

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

• برای روش استهلاک مستقیم تابع SLN(Cost,Salvage_value,Years) استهلاک هرسال را محاسبه می‌کند.
• برای روش مجموع ارقام سنوات تابع SYD(Cost,Salvage_value,Years,i) استهلاک سال i را محاسبه می‌کند.
• برای روش استهلاک مانده نزولی مضاعف تابع DDB(Cost,Salvage_value,Years,i) استهلاک سال i را محاسبه می‌کند.

بنابراین بعد از ایجاد محدوده نام‌ها برای سلول‌ها C2:C4 بر اساس محدوده B4:B4(همان‌طور که در تصویر 6-10 نشان داده‌شده و فایل Depreciationexamplesxlsx) می‌توان میزان استهلاک را با هریک از سه روش به این شکل محاسبه کرد:

• با کپی کردن فرمول =SLN(Cost,Salvage_Value,Years) از سلول E8‌به سلول‌های F8:J8 استهلاک را به روش مستقیم محاسبه می‌کنیم.
• با کپی کردن فرمول =SYD(Cost.Salvage_Value.Years.E7) از سلول E9‌ به محدوده سلول‌های F9:J9 استهلاک را به روش مجموع ارقام سنوات محاسبه می‌کنیم
• با کپی کردن فرمول DDB(Cost,Salvage_value,Years,E7) از سلول E10‌به محدوده F9:J9 استهلاک را به روش مانده نزولی مضاعف محاسبه می‌کنیم.

توجه کنید که هردو توابع SYD و DDB بیشترین میزان استهلاک را در سالهای ابتدایی می گنجانند.

تصویر 6-10 مثال‌هایی از توابع محاسبه استهلاک در اکسل

مسئله‌های این فصل:

تمامی پرداخت‌ها در این مسئله‌ها در انتهای دوره انجام می‌شوند، مگر آنکه خلاف آن ذکر شود.

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

اقساط مادام‌العمر سالانه‌هایی هستند که به‌طور دائم دریافت می‌شوند. اگر خانه‌ای را اجاره دهیم و در پایان سال 14000 دلار پول دریافت کرده باشیم آن‌وقت ارزش این ملک چقدر است؟ فرض کنید هزینه سرمایه سالیانه ده درصد است. راهنمایی: از تابع PV استفاده کرده و تعداد دوره‌های زیادی را به آن اختصاص دهید.

هم‌اکنون 250000 دلار در بانک دارم. در پایان هرسال تا 20 سال آینده 15000 دلار از آن برداشت می‌کنم. اگر سالیانه هشت درصد سود برای سرمایه‌ام کسب کنم، در پایان 20 سال چقدر پول خواهم داشت؟

در طی ده سال آینده، ماهیانه (در پایان هرماه) 2000 دلار به‌حساب واریز می‌کنم سود سرمایه‌ام ماهیانه 0.8 درصد است. دوست دارم در پایان ده سال صاحب 1 میلیون دلار شوم. برای رسیدن به این هدف چقدر پول باید به‌حساب بریزم؟

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

در پایان هریک از بیست سال آینده مقادیر زیر را دریافت خواهم کرد:

از تابع PV برای پیدا کردن ارزش فعلی این جریان‌های نقدی استفاده کنید در حالیکه هزینه سرمایه 10 درصد است. راهنمایی: با محاسبه ارزش 400 دلار دریافتی سالیانه به مدت 20 سال کار را شروع کنید و بعد ارزش 100 دلار دریافتی سالیانه به مدت ده سال را از آن کم کنید و به همین روال ادامه دهید.

200000 دلار را با اقساط 30 ساله وام گرفته‌اید که نرخ بهره آن 10 درصد است. با فرض اینکه پرداخت‌ها در انتهای ماه می‌باشند، میزان پرداخت ماهیانه، میزان بهره پرداختی ماهیانه و ما به ازای پرداخت‌شده نسبت به مبلغ اصل در هرماه را پیدا کنید.

هریک از سؤال‌های مسئله هفتم را با فرض پرداخت در ابتدای ماه پاسخ دهید.

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

یک میلیون دلار بدهی دارید که می‌بایست در طی ده سال آن را پرداخت کنید. هزینه سرمایه ده درصد در سال است. در طی ده سال آینده در انتهای هرسال چقدر پول می‌بایست کنار بگذارید تا این بدهی‌ را تسویه کنید؟

می‌خواهید ماشین جدیدی بخرید. قیمت ماشین 50000 دلار است. به شما دو روش پرداخت پیشنهادشده است:

• ده درصد تخفیف در قیمت خرید ماشین و شصت ماه پرداخت قسطی با 9 درصد بهره در هرسال.
• شصت ماه پرداخت قسطی با دو درصد بهره در هرسال بدون هیچ تخفیف اعمال‌شده بر قیمت اصلی ماشین.

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

در حال حاضر 10000 دلار در بانک پس‌انداز دارم. می‌خواهم در ابتدای هریک از 20 سال آینده 4000 دلار سرمایه‌گذاری کنم و انتظار دارم هرسال از سرمایه‌گذاری‌ام 6 درصد سود به دست آورم. در طول 20 سال چقدر پول خواهم داشت؟

وام بالونی نیازمند آن است که شما بدهی خود را در طی دوره زمانی خاصی پرداخته و سپس باقی قسط را به‌صورت یکجا بپردازید. فرض کنید که 400000 دلار برای یک دوره 20 ساله و با نرخ بهره 0.5 درصد در هرماه به‌صورت غیر قسطی وام گرفته‌اید. شما در طی این 20 ماه با پرداخت آخر ماه خود 300000 دلار از این وام را پرداخته‌اید و حالا می‌بایست باقیمانده 100000 دلار از وام را یکجا پرداخت نمایید. میزان پرداخت ماهیانه خود برای این وام را مشخص کنید.

وام با بهره قابل‌تغییر (ARM) پرداخت‌های ماهیانه را با شاخص نرخ بهره‌ای (مثلاً نرخ بهره خزانه‌داری آمریکا) حساب می‌کند. فرض کنید 60000 دلار وام با بهره قابل‌تغییر به مدت سی سال (360 پرداخت ماهیانه) دریافت کرده‌اید. دوازده پرداخت اول بر اساس نرخ بهره 8 درصدی تعیین‌شده توسط خزانه‌داری انجام می‌شوند. در سال‌های دو تا پنج پرداخت‌های ماهیانه شامل پرداخت‌های اول ماه بر اساس نرخ خزانه‌داری به اضافه دو درصد می‌شوند. فرض کنید که نرخ بهره خزانه‌داری اول سال سال‌های 2 تا 5 مقادیر زیر ‌باشند:

مقدار پرداخت‌های ماهیانه در سال‌های یک تا پنج و مانده پایان سال را محاسبه کنید.

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

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

می‌خواهید یک موتورسیکلت بخرید. قرار است امروز 1500 دلار بپردازید و به مدت سه سال هرماه 182.50 دلار قسط بدهید. اگر نرخ بهره سالیانه 18 درصد باشد قیمت اصلی موتورسیکلت چقدر است؟

فرض کنید که نرخ بهره سالیانه ده درصد است. شما دو سال ماهیانه 200 دلار، یک سال ماهیانه 300 دلار و دو سال دیگر ماهیانه 400 دلار می‌پردازید. ارزش فعلی تمامی پرداخت‌های شما چقدر است؟

می‌توانید به مدت 5 سال در انتهای هر شش ماه 500 دلار سرمایه‌گذاری کنید. اگر بخواهید پس از پنج سال 6000 دلار داشته باشید نرخ بهره بازده ای که برای سرمایه گزاری خود نیاز دارید چقدر است؟

2000 دلار وام گرفته‌ام و دو سال است که هر چهار ماه یک‌بار قسط‌ها را پرداخت می‌کنم. نرخ بهره 24 درصد است. هریک از پرداخت‌های من چقدر است؟

15000 دلار وام گرفته‌ام. باید آن را در 48 ماه پرداخت کنم و نرخ بهره سالیانه 9 درصد است. کل بهره‌ای که در طول مدت پرداخت وام می‌پردازم چقدر است؟

5000 دلار قرض کرده‌ام و قرار است این قرض را در طی 36 ماه پرداخت کنم. نرخ بهره سالیانه 16.5 درصد است. بعد از یک سال من 500 دلار اضافه می‌پردازم و دوره پرداخت وام را به دو سال کاهش می‌دهم. پرداخت ماهیانه من در طی سال دوم چقدر خواهد بود؟

وامی با بهره قابل‌تغییر گرفته‌اید و ماهیانه بر اساس نرخ بهره آغاز هرسال قسط‌های آن را پرداخت می‌کنید. شما 60000 دلار وام با بازپرداخت سی‌ساله گرفته است. پرداخت‌های ماهیانه سال اول بر اساس نرخ بهره سالیانه خزانه‌داری 9 درصد است. در سال‌های دو تا پنج، پرداخت‌های ماهیانه بر اساس نرخ‌های بهره سالیانه خزانه‌داری زیر به‌اضافه دو درصد است:

• سال دوم ده درصد
• سال سوم 13 درصد
• سال چهارم 15 درصد
• سال پنجم 10 درصد

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

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

بلیت بخت‌آزمایی شما برنده‌شده و قرار است در طی 20 سال 50000 دلار دریافت کنید. چه نرخ بهره‌ای مقدار این پرداخت‌ها را برابر با دریافت 500000 دلار در همین امروز خواهد کرد؟

اوراق قرضه ای ملزم به پرداخت 50 دلار کوپن در انتهای هرسال از سی سال آینده و 1000 دلار ارزش اسمی (Face value) در سی سال است. اگر جریان نقدی به نرخ بهره 6 درصد را از آن کم کنید، ارزش عادلانه این اوراق قرضه چقدر است؟

100000 دلار با بازپرداخت 40 ساله به شکل پرداخت ماهیانه وام گرفته‌اید. نرخ بهره سالیانه 16 درصد است. در طول دوره پرداخت چه مقدار پول خواهید پرداخت؟ اگر چهار سال از موعد پرداخت باقی‌مانده باشد در آن زمان هنوز چقدر بدهکار خواهید بود؟

بایستی 12000 دلار وام بگیرم. می‌توانم از پس پرداخت 500 دلار در ماه با نرخ بهره 4.5 درصد برآیم. چند ماه طول می‌کشد تا تمامی وام را بازپرداخت کنم؟

وامی به مبلغ 50000 دلار با بازپرداخت 180 ماهه را در نظر گرفته‌اید. نرخ بهره سالیانه وام بستگی به سوابق اعتباری دارد که در تصویر 7-10 نشان داده‌شده:

تصویر 7-10 وابستگی پرداخت‌های وام به سوابق اعتبار بانکی

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

می‌خواهید 40000 دلار برای خرید اتومبیلی جدید وام بگیرید. پرداخت‌های ماهیانه و کل بهره پرداختی را برای وضعیت‌های زیر مشخص کنید:

• وام 48 ماهه، بهره سالیانه 6.85 درصدی
• وام 60 ماهه، بهره سالیانه 6.59 درصدی

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

با گروه مالی زاویه همراه باشید.