نحوه ایجاد جدول استهلاک با نرخ متغیر در مایکروسافت اکسل

جدول استهلاک با نرخ متغیر به شما امکان می دهد وام های خود را از قبل برنامه ریزی کنید و تغییرات نرخ بهره را تنظیم کنید. در اینجا نحوه ایجاد یک در اکسل آورده شده است.

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

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

ایجاد جدول استهلاک وام با نرخ متغیر

قبل از ساخت جدول، باید اطلاعات زیر را در دسترس داشته باشید:

کل مبلغ وام
مدت وام (تعداد سالهای پرداخت)
تعداد پرداخت ها در هر ترم (تعداد دوره های پرداخت در یک سال)

همچنین باید با فرمول زیر آشنا باشید:

-PMT(RATE,NPER,PV)

RATE به نرخ بهره سالانه اعمال شده برای آن پرداخت، NPER به تعداد پرداخت‌های باقی‌مانده، و PV به مانده وام باقی‌مانده قبل از پرداخت برای دوره مذکور اشاره دارد.

هنگامی که این اطلاعات را دارید، آنها را در فایل اکسل خود وارد کنید. برای این مثال، ما از وام مسکن 20 ساله و 5,000,000 دلاری استفاده خواهیم کرد که ماهانه پرداخت می شود.

پس از اضافه کردن داده های اولیه، می توانید شروع به ایجاد جدول استهلاک کنید.

ایجاد ستون ها برای جدول استهلاک شما

شما باید ستون های زیر را برای جدول خود ایجاد کنید:

دوره پرداخت: دوره پرداخت را نشان می دهد.
مبلغ پرداخت: کل مبلغی که باید برای دوره پرداخت شود.
بهره پرداختی: مبلغ پرداختی که به سود می رود.
اصل پرداخت شده: مبلغ پرداختی برای کاهش موجودی اصلی.
موجودی وام: کل مبلغ وام باقی مانده برای پرداخت.
نرخ بهره سالانه: نرخ بهره سالانه برای آن دوره پرداخت.
پرداخت یکجا: پیش پرداخت ها را در صورت وجود ثبت می کند.

مطلب مرتبط: اجاره در مقابل خرید خانه: بیش از 5 راهنما و برنامه آنلاین برای محاسبه بهترین موارد برای شما

پر کردن داده های ثابت

هنگامی که ستون های خود را ایجاد کردید، وقت آن است که اطلاعات از پیش تعیین شده را پر کنید. زیر ستون دوره پرداخت، 0 را برای داده های اولیه، 1 در ردیف بعدی برای نشان دادن اولین دوره پرداخت و 2 را در ردیف بعد از آن بنویسید. پس از انجام این کار، هر سه سلول را انتخاب کنید.

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

باید ببینید یک کادر کوچک در سمت راست مکان نما ظاهر می شود. این عدد آخرین عددی را که اکسل پر می‌کند نشان می‌دهد – وقتی تعداد کل دوره‌های پرداخت وام خود را زدید (در مثال ما 240) دکمه ماوس را رها کنید.

در زیر LoanBalance، کل مبلغ وام در دوره 0 را به مبلغ کل وام اصلی پیوند دهید. برای مثال ما از فرمول =C1 استفاده کنید. بقیه ردیف ها را خالی بگذارید.

در زیر نرخ بهره سالانه، نرخ بهره را برای دوره پرداخت مذکور وارد کنید. اگر 5/1 ARM (وام مسکن با نرخ قابل تنظیم) دارید، پنج سال اول وام شما دارای نرخ ثابت است. سپس پس از آن دوره، سالانه بر اساس بازار تعدیل خواهد کرد.

برای این مثال، ما از نرخ بهره 3.57% برای پنج سال اول استفاده می‌کنیم و سپس از نرخ‌های متغیر بر اساس نرخ‌های تاریخی استفاده می‌کنیم.

افزودن فرمول ها

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

=-PMT(RATE,NPER,PV)

در زیر RATE، سلولی را انتخاب کنید که نرخ بهره سالانه جاری را فهرست می کند (سلول F6 در مثال ما) و سپس آن را بر تعداد پرداخت ها در هر دوره تقسیم کنید. علامت $ را قبل از نشانگر ستون و ردیف (سلول $C$3 در مثال ما) اضافه کنید تا مطمئن شوید که وقتی فرمول را بعداً روی سلول‌های دیگر اعمال می‌کنید، تغییر نمی‌کند.

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

برای NPER، تایپ کنید COUNT(. سپس، اولین سلول دوره را انتخاب کنید، shift را روی صفحه کلید خود فشار دهید، سپس آخرین سلول دوره (سلول فعلی) را انتخاب کنید. باز هم فراموش نکنید که علامت $ را قبل از شماره ستون و ردیف اضافه کنید. در فرمولی که آخرین سلول را نشان می دهد (در مثال ما 245$ A$) تا اطمینان حاصل شود که بعداً تغییر نمی کند.

برای وارد کردن PV، سلول حاوی کل مبلغ وام در دوره 0 را انتخاب کنید (سلول E6 در مثال ما). همچنین توجه داشته باشید که فرمول PMT قبل از آن علامت منفی دارد، بنابراین در جدول خود یک مقدار مثبت دریافت می کنید. اگر این را اضافه نکنید، یک مقدار منفی دریافت خواهید کرد.

در ستون بهره پرداخت شده، مبلغی را که به سود پرداختی ماهانه شما می رود محاسبه کنید. از فرمول زیر استفاده کنید:

=Balance Remaining*(Annual Interest Rate/Payments Per Period)

برای مثال ما، فرمول به این صورت است:

=E6*(F7/$C$3)

در جایی که E6 باقیمانده موجودی است که باید پرداخت کنید، F7 نرخ بهره سالانه قابل اجرا فعلی است و 3 دلار کانادا تعداد پرداخت هایی است که در هر ترم انجام می دهید.

هنگامی که میزان بهره پرداختی را تعیین کردید، آن را از مبلغ پرداخت کم کنید تا اصل پرداخت شده را دریافت کنید.

در مثال ما، فرمول این است:

=B7-C7

با کم کردن مبلغ پرداخت فعلی و پرداخت یکجا از مانده وام از آخرین دوره (دوره 0) می توانید مانده وام را از آنجا دریافت کنید.

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

=E6-B7-G7

هنگامی که همه فرمول ها را اضافه کردید، باید تمام داده های ماه اول خود را آماده کنید.

پر کردن بقیه جدول استهلاک

با تمام فرمول های اولیه آماده، اکنون می توانید بقیه جدول را فقط با کشیدن و رها کردن پر کنید. ابتدا سلول های دارای فرمول (مبلغ پرداخت، بهره پرداختی، اصل پرداخت شده و موجودی وام در دوره 1) را انتخاب کنید.

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

مطلب مرتبط: چگونه خطای Microsoft Excel "Document Not Saved" را در ویندوز برطرف کنیم

همچنین، می‌توانید سلول‌ها را با فرمول‌ها کپی کنید، سپس تمام ردیف‌های دوره پرداخت را در زیر آنها انتخاب کنید. از آنجا، Paste را انتخاب کنید یا Ctrl + V را فشار دهید و اکسل به طور خودکار جدول شما را با مقادیر صحیح پر می کند.

برای جلوگیری از تغییر تصادفی فرمول ها، تمام سلول های دارای فرمول (سلول A6 تا سلول E246) را انتخاب کنید و سپس آن را با هر رنگی پر کنید. همچنین می توانید از قالب بندی مشروط برای تغییر رنگ سلول های خود پس از پرداخت کامل موجودی وام خود استفاده کنید.

آزمایش با داده های شما

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

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

دانستن می تواند به شما در برنامه ریزی مالی کمک کند

با برنامه استهلاک با نرخ متغیر، می توانید وام و پرداخت های خود را برنامه ریزی کنید. به این ترتیب، حتی اگر نرخ بهره تغییر کند، می توانید خود را آماده کنید. و با ستون یکجا، حتی می توانید ببینید که چگونه پرداخت های پیش پرداخت کوچک می تواند کمک زیادی به پرداخت وام شما کند.