استفاده از ابزار Solver برای برنامه‌ریزی مالی

27 اردیبهشت 1401

دقیقه

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

استفاده از ابزار Solver برای برنامه‌ریزی مالی

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

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

مثلاً وضعیتی را فرض کنید که در آن می‌خواهید پولی قرض کنید. ازآنجاکه تنها بخش بدون بهره هر پرداخت ماهیانه، مانده وام پرداخت نشده را پایین می‌آورد، می‌دانیم که فرمول زیر (که آن را فرمول 1 می‌نامیم) صحیح می‌باشد.

(مانده پرداخت نشده وام در انتهای هر دوره t)=(مانده پرداخت نشده وام در ابتدای هر دوره t) – (پرداخت ماه t) – (بهره پرداخت شده ماه t)

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

(پس‌انداز بازنشستگی در انتهای سال t+ 1)=(پس‌انداز بازنشستگی در انتهای سال t + واریزی به‌حساب بازنشستگی سال t+1- برداشت حساب بازنشستگی سال t+1)* (سود سرمایه‌گذاری کسب شده در طول سال t+1)

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

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

آیا می‌توان از ابزار Solver برای ارزیابی صحت تابع PMT اکسل یا مشخص‌کردن پرداخت‌های وام مسکنی با نرخ بهره شناور استفاده کرد؟

به‌خاطر آورید که درفصل ده به نام توابع مالی بیشتر، مقدار پرداخت ماهیانه یک وام ‌ده ماهه به مبلغ 800000 دلار را با بهره سالیانه 10 درصد به میزان 1037.03 دلار را به دست آوردیم. آیا می‌توانستیم از ابزار Solver استفاده کنیم تا پرداخت ماهیانه را تعیین کنیم؟ جواب این سؤال را در کاربرگی به نام PMT By Solver در فایلی به نامFinmathosolver.xlsx که در تصویر 1-34 نیز نشان‌داده‌شده پیدا خواهید کرد.

تصویر 1-34 مدل Solver برای محاسبه پرداخت ماهیانه یک وام

تصویر 1-34 مدل Solver برای محاسبه پرداخت ماهیانه یک وام

راه‌حل کلیدی برای این مدل استفاده از معادله 1 برای دنبال‌کردن مانده ابتدای ماه می‌باشد. سلول هدف Solver می‌بایست پرداخت ماهیانه را به حداقل برساند. سلول متغیر پرداخت ماهیانه است. تنها محدودیت آن است که مانده انتهایی ماه دهم (Month 10) برابر با صفر باشد.

مانده ابتدایی را در سلول B5 وارد می‌کنیم. پرداخت ماهیانه آزمایشی را در سلول C5 وارد می‌کنیم، سپس پرداخت ماهیانه را در محدوده C6:C14 وارد می‌کنیم. ازآنجاکه فرض را بر آن گرفته‌ایم که پرداخت‌ها در انتهای هر ماه انجام خواهند شد، بهره در مانده ابتدای ماه وارد خواهد شد. نرخ بهره ماهیانه (نام سلول C1 را rate یا نرخ گذاشته‌ایم) در سلول D1 با تقسیم بهره سالیانه 0.08 بر عدد 12 محاسبه خواهد شد. بهره پرداخت شده در هر ماه با کپی‌کردن فرمول =rate*B5 در محدوده D6:D14 محاسبه خواهد شد (در اینجا rate نام محدوده برای سلول D1 می‌باشد) این فرمول هر ماه بهره را به شکل (مانده اولیه ماه)* 006666. محاسبه می‌نماید. با کپی‌کردن فرمول =(B5–(Payment–D5)) از سلول E5 به محدوده E6:E14 از معادله 1 استفاده می‌کنیم تا مانده انتهایی هر ماه را محاسبه کنیم. (Payment یا پرداخت نام محدوده سلول C5 می‌باشد) از آنجا که (مانده انتهایی ماه t)=(مانده ابتدای 1+ ماه t)، مانده ابتدای هر ماه با کپی‌کردن فرمول=E5 از سلول B6 به محدوده B7:B14 محاسبه می‌شود.

مقاله ی مرتبط:   مسئله فروشنده دوره گرد

اکنون آماده‌ایم تا از ابزار Solver برای مشخص‌کردن پرداخت‌های ماهیانه استفاده کنیم. برای اینکه ببینید چگونه کادر محاوره‌ای Solver Parameters را تنظیم می‌کنیم بهتر است نگاهی به تصویر 2-34 بیندازید. (برای بازکردن کادر محاوره‌ای Solver Parameters بر روی گزینه Solver در گروه گزینه‌های Analysis در تب Data کلیک کنید)

تصویر 2-34 کادر محاوره‌ای Solver Parameters تنظیم شده برای تعیین پرداخت‌های قسطی

تصویر 2-34 کادر محاوره‌ای Solver Parameters تنظیم شده برای تعیین پرداخت‌های قسطی

هدف آن است که پرداخت ماهیانه (سلول C5) را به حداقل برسانیم. توجه کنید که سلول متغیر همان سلول هدف است. تنها محدودیت آن است که مانده انتهایی ماه 10 (Month 10) می‌بایست برابر با صفر باشد. اضافه‌کردن این محدودیت به ما اطمینان می‌دهد که قسط وام پرداخت شده است. بعد از آنکه موتور Simplex LP را انتخاب کرده و همچنین گزینه Make Unconstrained Variables Non- Negative را برگزیدیم، ابزار Solver پرداختی برابر با 1.037.03 دلار را محاسبه می‌کند که با مبلغی که توسط تابع PMT اکسل محاسبه شده بود برابر می‌باشد.

این مدل مدلی خطی است چرا که سلول هدف برابر سلول متغیر است و محدودیت با اضافه‌کردن چندین سلول متغیر ایجاد شده است.

باید اشاره کرد که هنگامی که مدل‌های Solver دارای اعداد بسیار بزرگ/ بسیار کوچک باشند، برخی اوقات ابزار Solver مدل‌هایی را که خطی هستند، غیرخطی در نظر می‌گیرد. برای جلوگیری از این مشکل تمرین خوبی است که از گزینه Automatic Scaling در کادر محاوره‌ای Options استفاده کنید. این کار شما را مطمئن می‌سازد که ابزار Solver همیشه مدل‌های خطی را به‌عنوان مدل خطی در نظر خواهد گرفت.

 

استفاده از ابزار Solver برای مشخص‌کردن پس‌انداز مورد نیاز برای بازنشستگی

آیا می‌توان از ابزار Solver برای مشخص‌کردن میزان پولی که می‌بایست برای بازنشستگی پس‌انداز کرد استفاده نمود؟

با استفاده از معادله 2(Equation 2) که قبلاً در این فصل به آن اشاره کردیم، می‌توان به‌آسانی تعیین کرد که شخص چقدر می‌بایست برای بازنشستگی پس‌انداز نماید. در اینجا مثالی مشاهده می‌کنید:

برنامه پس‌انداز بازنشستگی‌ را برنامه‌ریزی می‌کنم و می‌خواهم در ابتدای این سال و 39 سال آینده مقداری پول در صندوق بازنشستگی پس‌انداز کنم. می‌خواهم هرسال میزان پس‌اندازم را 500 دلار افزایش دهم. چهل سال بعد بازنشسته خواهم شد می‌خواهم در بیست سال بعد از آن (در ابتدای هرسال)10000 دلار از حسابم برداشت کنم. فرض‌های زیر را در باره سود سرمایه‌گذاری بازنشستگی‌ام در نظر گرفته‌ام:

  • در طول بیست سال سرمایه‌گذاری‌ام، این سرمایه‌گذاری هرسال ده درصد بهره خواهد داشت.
  • در طول تمامی سال‌ها، سرمایه‌گذاری‌ام سالیانه 5 درصد بهره خواهد داشت.
مقاله ی مرتبط:   قالب‌بندی شرطی (Conditional Formatting)- بخش 2

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

با وجود تمامی این فرضیات، حداقل پولی که می‌توانم امسال پس‌انداز کرده و هنوز پول کافی برای برداشت‌های حساب بازنشستگی داشته باشم چقدر است؟

می‌توانید حل این مسئله را در کاربرگ Retire در فایلی به نام Finmathsolver.xlsx که در تصویر 3-34 نشان‌داده‌شده پیدا کنید. توجه کنید که در مدل بسیاری از ردیف‌ها را پنهان کرده‌ایم.

تصویر 3-34 داده‌های مربوط به برنامه‌ریزی بازنشستگی را می‌تواند برای تحلیل با ابزار Solver آماده کرد

تصویر 3-34 داده‌های مربوط به برنامه‌ریزی بازنشستگی را می‌تواند برای تحلیل با ابزار Solver آماده کرد

 این کاربرگ به شکلی ساده مانده حساب بازنشستگی را در طول 60 سال آینده دنبال می‌کند. نرخ سود مشخص شده درمانده بازنشستگی را دریافت می‌کنیم. کار خود را با واردکردن مقداری آزمایشی برای پرداخت سال اول در سلول C6 شروع می‌کنیم. کپی‌کردن فرمول =C6+500 در سلول C7 به ما اطمینان می‌دهد که از سال‌های 2 تا 40، هرسال 500 دلار به پرداخت‌های حساب بازنشستگی اضافه می‌شود.

در ستون D سود فرضی برای سرمایه‌گذاری‌ها در هر یک از سال‌های 60 سال آینده را وارد می‌کنیم مقدار سالیانه برداشتی برابر با 100000 دلار را در سلول‌های E46:E65 برای سال‌های 41 تا 60 وارد کرده‌ایم. کپی‌کردن فرمول =(B6+C6–E6)*(1+D6) از سلول F6 در محدوده F7:F65 از معادله 2 استفاده می‌کند تا مانده حساب بازنشستگی پایان هرسال را محاسبه کند. کپی‌کردن فرمول=F6 از سلول B7 به محدوده B8:B65 مانده اول سال 2 تا 60 را محاسبه می‌کند. البته مانده اولیه سال 1 برابر با صفر است. توجه کنید که ارزش 07-E 6.8704 در سلول F65 تقریباً برابر با صفر است، با تفاوتی که نتیجه خطای گرد کردن می‌باشد.

کادر محاوره‌ای Solver Parameters برای این مدل در تصویر 4-34 نشان‌داده‌شده است. قصد داریم میزان پس‌انداز سال اول (سلول C6) را به حداقل برسانیم. سلول متغیر نیز میزان پس‌انداز سال اول (سلول C6) می‌باشد. با اضافه‌کردن محدودیت F46:F65>=0 اطمینان حاصل پیدا می‌کنیم که در طول بازنشستگی پس‌انداز هیچگاه به پایان نخواهد رسید، بنابراین مانده پایان سال 41 تا 60 عددی مثبت می‌باشد.

تصویر 4-34 کادر محاوره‌ای Solver Parameters که برای حل مسئله بازنشستگی تنظیم‌شده است.

تصویر 4-34 کادر محاوره‌ای Solver Parameters که برای حل مسئله بازنشستگی تنظیم‌شده است.

پس از انتخاب موتور Simplex LP و انتخاب گزینه Make Unconstrained Variables Non-Negative در کادر محاوره‌ای Solver Parameters روی دکمه Solver کلیک می‌کنیم و می‌بینیم که میزان پس‌انداز اولین سال می‌بایست برابر با 1.387.87 دلار باشد.

این مدل خطی است چرا که سلول هدف برابر سلول متغیر است و محدودیت با اضافه‌کردن چندین سلول متغیر ایجاد شده است. توجه کنید که ازآنجاکه بازده سرمایه‌گذاری در هرسال یکسان نیست، راه آسانی برای استفاده کردن از توابع مالی اکسل برای حل این مسئله وجود ندارد. ابزار Solver چهارچوبی کلی به ما ارائه می‌دهد تا بتوان از آن برای تحلیل مسئله‌های برنامه‌ریزی مالی به هنگامی که نرخ اقساط با بازده سرمایه‌گذاری ثابت نیستند استفاده کنیم.

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

می‌خواهم 15000 دلار برای خرید اتومبیلی تازه وام بگیرم. قرار است شصت ماه در انتهای هر ماه قسط‌های این وام را پرداخت کنم. نرخ بهره سالیانه این وام ده درصد است. فروشنده اتومبیل دوست من است و به من اجازه می‌دهد که پرداخت‌های ماه اول تا سی‌ام برابر با نصف پرداخت‌های ماه 31 تا 60 باشد. پرداخت ماهیانه این وام چقدر است؟

مقاله ی مرتبط:   مدل سازی ساده کسب و کارها با اکسل

مسئله برنامه‌ریزی پس‌انداز بازنشستگی را با این فرض حل کنید که برداشت‌ها در انتهای هرسال و پس‌اندازها در ابتدای هرسال انجام شوند.

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

در مثال مربوط به برنامه‌ریزی پس‌انداز بازنشستگی، فرض کنید که در طول سال اول حقوق شما 40000 دلار است و هرسال تا هنگام بازنشستگی حقوقتان 5 درصد افزایش پیدا می‌کند. می‌خواهید در هرسال کاری همان درصد از حقوق خود را پس‌انداز کنید. چه درصدی از حقوق شما می‌بایست پس‌انداز شود؟

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

فرض کنید که می‌خواهید 300000 دلار با قسطی 20 ساله که در انتهای هر ماه پرداخت می‌شود وام بگیرید. نرخ بهره 6 درصد است. می‌خواهید بیست سال از حالا پرداخت یکجایی برابر با 40000 دلار انجام دهید چرا که انتظار دارید درآمدتان افزایش پیدا کند. قصد دارید این وام را به شکلی ساختاربندی کنید که در ابتدای هرسال پرداخت ماهیانه‌تان 2 درصد افزایش پیدا کنید. اکنون میزان پرداخت ماهیانه هرسال را تعیین کنید.

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

سرمایه‌گذاری‌های زیر موجود هستند:

  1. امروز، یک سال از هم اکنون، دو سال از هم اکنون، سه سال از هم اکنون و چهار سال از هم اکنون او می‌تواند پولی را برای یک سال سرمایه‌گذاری و 6 درصد سود دریافت کند.
  2. امروز، دو سال از هم اکنون، و چهار سال از هم اکنون، او می‌تواند پولی را برای دو سال سرمایه‌گذاری و 14 درصد سود دریافت کند.
  3. سه سال از هم اکنون او می‌تواند پولی را برای سه سال پس‌انداز و 18 درصد سود دریافت کند.
  4. امروز او می‌تواند پولی را برای هفت سال سرمایه‌گذاری و 65 درصد سود دریافت کند.

حداقل مقداری که مادر بلیر می‌بایست امروز برای دانشگاه بلیر پس‌انداز کند تا اطمینان پیدا کند که می‌تواند صورت‌حساب‌های دانشگاهش را بپردازد چقدر است؟

در یکی از کارت‌های اعتباری‌ام که 18 درصد بهره سالیانه دارد 10000 دلار بدهی و در کارت دیگری که 12 درصد بهره سالیانه دارد 5000 دلار بدهی دارم. بهره هر ماه بر اساس مانده ابتدای ماه حساب می‌شود. می‌توانم ماهیانه یکجا 2000 دلار پرداخت کنم و حداقل پرداخت ماهیانه برای هر کارت 10 درصد از مانده پرداخت نشده هر کارت در ابتدای هر ماه می‌باشد. هدفم آن است که بدهی هر دو کارت را در طول دو سال پرداخت کنم. حداقل میزان بهره‌ای که نیاز به پرداخت آن دارم چقدر است؟

شما 50000 دلار وام‌گرفته‌اید که قرار است در انتهای بیست و چهار ماه بازپرداخت شود. میزان بهره سالیانه ده درصد است. در ماه‌های 7، 13 و 19 میزان پرداخت 500 دلار اضافه می‌شود. هر ماه چقدر می‌بایستی بپردازید؟

 

فایل ها جانبی:
دانلود فایل نمونه
اشتراک گذاری در شبکه های اجتماعی
@

لطفا شکبیا باشید...