تابع نرخ اکسل: مثال هایی از فرمول برای محاسبه نرخ بهره

توسط سوتلانا چوشوا، به روز شده در 26 اکتبر 2022

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

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

تابع RATE اکسل

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

این تابع در تمام نسخه های Excel 365 - 2007 موجود است.

نحو به شرح زیر است:

• Nper (الزامی) - تعداد کل دوره های پرداخت مانند سال، ماه، سه ماهه و غیره.
• Pmt (الزامی) - مبلغ پرداخت ثابت در هر دوره که در طول عمر مستمری قابل تغییر نیست. معمولاً شامل اصل و بهره است، اما بدون مالیات.
• Pv (الزامی) - ارزش فعلی، یعنی ارزش فعلی وام یا سرمایه گذاری.
• Fv (اختیاری) - ارزش آتی، یعنی موجودی نقدی که می خواهید پس از آخرین پرداخت داشته باشید. اگر حذف شود، پیش فرض 0 است.
• نوع (اختیاری) - نشان می دهد که چه زمانی پرداخت ها انجام می شود:
  • 0 یا حذف شده (پیش فرض) - پرداخت در پایان دوره انجام می شود
  • 1 - پرداخت در ابتدای دوره پرداخت می شود

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

  برای استفاده موثر از فرمول های RATE در کاربرگ های خود، لطفاً به این نکات استفاده توجه کنید:

  1. تابع RATE از طریق آزمون و خطا محاسبه می شود. اگر بعد از 20 تکرار نتواند به یک راه حل همگرا شود، یک #NUM! خطا برگردانده می شود.
  2. به طور پیش فرض، نرخ بهره در هر دوره پرداخت محاسبه می شود. اما همانطور که در این مثال نشان داده شده است، می توانید نرخ بهره سالانه را با ضرب بدست آورید.
  3. از اعداد مثبت برای نشان دادن پول نقدی که دریافت می کنید (ورودی ها) و از اعداد منفی برای نشان دادن پول نقدی که پرداخت می کنید (خروجی ها) استفاده کنید.
  4. اگرچه نحو RATE pv را به عنوان آرگومان مورد نیاز توصیف می کند، اما اگر آرگومان fv را وارد کنید، در واقع می توان آن را حذف کرد. چنین نحوی معمولاً برای محاسبه نرخ بهره در حساب پس انداز استفاده می شود.
  5. آرگومان حدس زدن را می توان در اکثر موارد حذف کرد زیرا فقط یک مقدار شروع برای یک رویه تکراری است.
  6. هنگام محاسبه نرخ برای دوره های مختلف ، اطمینان حاصل کنید که با مقادیر تهیه شده برای NPER و حدس می زنید. به عنوان مثال ، اگر می خواهید سالانه وام 3 ساله را با 8 ٪ سود سالانه پرداخت کنید ، از 3 برای NPER و 8 ٪ برای حدس استفاده کنید. اگر می خواهید پرداخت ماهانه را در همان وام انجام دهید ، برای حدس زدن 3*12 برای NPER و 8 ٪/12 استفاده کنید.
  7. وقتی همه جریان های نقدی یکسان هستند و از طریق فواصل مساوی از زمان استفاده می شوند ، از نرخ استفاده استفاده کنید. اگر مبلغ جریان نقدی تغییر می کند اما در فواصل منظم اتفاق می افتد ، سپس نرخ بازده داخلی را با استفاده از عملکرد IRR محاسبه کنید. در صورت بروز تغییر جریان نقدی در فواصل نامنظم ، از عملکرد XIRR برای دریافت نرخ بازده داخلی برای جریان نقدی غیر دوره استفاده کنید.

  فرمول نرخ پایه در اکسل

  در این مثال ، ما به چگونگی ساخت فرمول نرخ در ساده ترین فرم خود برای محاسبه نرخ بهره در اکسل خواهیم پرداخت.

  بیایید بگوییم 10،000 دلار وام گرفته اید که باید طی سه سال آینده به طور کامل پرداخت شود. شما قصد دارید 3 قسط سالانه 3800 دلار را بپردازید. نرخ بهره سالانه چقدر خواهد بود؟

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

  • NPER در C2 (تعداد پرداخت ها): 3
  • PMT در C3 (مبلغ پرداخت): -3،800
  • PV در C4 (مبلغ وام): 10،000

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

  فرض بر این است که پرداخت در پایان هر سال انجام می شود ، بنابراین می توانیم آرگومان [نوع] را حذف کنیم یا آن را بر روی مقدار پیش فرض (0) تنظیم کنیم. دو آرگومان اختیاری دیگر [FV] و [حدس] نیز حذف شده اند.

  در نتیجه ، ما این فرمول ساده را دریافت می کنیم:

  

  = نرخ (C2 ، C3 ، C4)

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

  

  = نرخ (C2 ، -C3 ، C4)

  نحوه محاسبه نرخ بهره در اکسل - نمونه های فرمول

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

  نحوه محاسبه نرخ بهره ماهانه در وام

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

  فرض کنید وام بیش از 3 سال در اقساط ماهانه پرداخت می شود. برای به دست آوردن تعداد کل پرداخت ها ، ما 3 سال را در 12 ماه ضرب می کنیم (3*12 = 36).

  پارامترهای دیگر در زیر نشان داده شده است:

  • NPER در C2 (تعداد دوره ها): 36
  • PMT در C3 (پرداخت ماهانه): -300
  • PV در C4 (مبلغ وام): 10،000

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

  

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

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

  

  = نرخ (C2*12 ، C3 ، C4)

  نحوه محاسبه نرخ بهره سالانه در اکسل

  با توجه به مثال ما ، چگونه می توانید نرخ سود سالانه را برای پرداخت ماهانه پیدا کنید؟به سادگی با ضرب نتیجه نرخ توسط تعداد دوره های در سال ، که در مورد ما 12 است:

  = نرخ (C2 ، C3 ، C4) * 12

  

  تصویر زیر به شما امکان می دهد نرخ بهره ماهانه را در C7 و نرخ بهره سالانه در C9 مقایسه کنید:

  اگر قرار باشد در پایان هر سه ماهه پرداخت ها انجام شود ، چه می شود؟

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

  nper: 3 (سال) * 4 (چهارم در سال) = 12

  سپس از عملکرد نرخ برای محاسبه نرخ بهره سه ماهه (C7) استفاده کنید:

  و نتیجه را با 4 ضرب کنید تا نرخ بهره سالانه (C9) را بدست آورید:

  

  = نرخ (C2 ، C3 ، C4) * 4

  نحوه یافتن نرخ بهره در حساب پس انداز

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

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

  به عنوان نمونه ، بیایید نرخ بهره مورد نیاز برای صرفه جویی در 100000 دلار در 5 سال را محاسبه کنیم ، مشروط بر اینکه 1500 دلار پرداخت را در پایان هر ماه با سرمایه گذاری اولیه صفر انجام دهید.

  برای انجام آن ، متغیرهای زیر را تعریف می کنیم:

  • NPER در C2 (تعداد کل پرداخت ها): 5*12
  • PMT در C3 (پرداخت ماهانه): -1،500
  • FV در C4 (ارزش آینده مطلوب): 100،000

  برای محاسبه نرخ بهره ماهانه ، فرمول در C6:

  لطفاً توجه داشته باشید که C2 شامل تعداد سالها است. برای به دست آوردن تعداد کل دوره های پرداخت ، ما آن را با 12 ضرب می کنیم.

  برای به دست آوردن نرخ بهره سالانه ، نرخ ماهانه را با 12 ضرب می کنیم. بنابراین ، فرمول در C8 است:

  

  = نرخ (C2 * 12 ، C3 ، ، C4) * 12

  نحوه یافتن نرخ رشد سالانه مرکب در سرمایه گذاری

  عملکرد نرخ در اکسل همچنین می تواند برای محاسبه نرخ رشد سالانه مرکب (CAGR) در یک سرمایه گذاری در یک دوره زمانی معین استفاده شود.

  با فرض اینکه می خواهید به مدت 5 سال 100000 دلار سرمایه گذاری کنید و در پایان 200،000 دلار دریافت کنید. سرمایه گذاری شما از نظر CAGR چگونه رشد خواهد کرد؟برای یافتن این موضوع ، آرگومان های زیر را برای عملکرد نرخ تنظیم کردید:

  لطفاً توجه داشته باشید که از استدلال PMT در این مورد استفاده نمی شود ، بنابراین ما آن را در فرمول خالی می گذاریم:

  

  در نتیجه ، عملکرد نرخ اکسل به ما می گوید که سرمایه گذاری ما نرخ رشد سالانه 14. 87 ٪ را در طی 5 سال به دست آورده است.

  در اکسل ماشین حساب نرخ بهره ایجاد کنید

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

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

  • تعداد کل پرداخت ها (NPER) - C2
  • مبلغ پرداخت (PMT) - C3
  • ارزش فعلی سالیانه (PV) - C4
  • ارزش آینده سالیانه (FV) - C5
  • نوع سالیانه (نوع) - C6
  • نرخ سود تخمین زده شده (حدس) - C7
  • تعداد دوره در سال - C8

  برای آزمایش ماشین حساب ما در عمل ، بیایید سعی کنیم یک ماهانه و سالانه در یک حساب پس انداز پیدا کنیم که 100000 دلار در پایان 5 سال با پرداخت ماهانه 1500 دلار در ابتدای هر دوره تضمین می کند.

  ما متغیرها را در سلولهای مربوطه مانند تصویر زیر وارد می کنیم و فرمول های زیر را وارد می کنیم:

  در C10 ، نرخ بهره دوره ای را برگردانید:

  = نرخ (C2 ، C3 ، C4 ، C5 ، C6 ، C7)

  در C11 ، نرخ بهره سالانه:

  = نرخ (C2 ، C3 ، C4 ، C5 ، C6 ، C7) * C8

  

  برای داده های نمونه ما ، نتایج به شرح زیر است:

  لطفا توجه داشته باشید که:

  • برای NPER ، ما 60 (5 سال * 12 ماه = 60 دوره پرداخت) را وارد می کنیم.
  • برای نوع ، ما 1 را وارد می کنیم (پرداخت در ابتدای دوره انجام می شود). برای جلوگیری از اشتباهات ، ایجاد یک لیست کشویی در C6 منطقی است که فقط 0 و 1 مقادیر را برای آرگومان نوع اجازه می دهد.
  • اگر PV 0 است یا تعریف نشده است (مانند این مثال) ، حتماً آرگومان FV را مشخص کنید.

  عملکرد نرخ اکسل کار نمی کند

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

  #NUM! خطا

  دلیل: هنگامی اتفاق می افتد که عملکرد رتبه نتواند راه حلی پیدا کند.

  

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

  

  در برخی موارد، ممکن است لازم باشد با ارائه یک حدس اولیه به تابع RANK کمک کنید تا به یک راه حل همگرا شود:

  

  هنگام محاسبه نرخ بهره با ارزش فعلی تعریف نشده یا صفر (pv)، حتماً مقدار آتی (fv) را مشخص کنید:

  #ارزش! خطا

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

  برای رفع خطا، مقادیر استفاده شده برای آرگومان های RANK را دوباره بررسی کنید و مطمئن شوید که اعداد شما به صورت متنی قالب بندی نشده اند.

  تابع RATE نتیجه نادرست را برمی گرداند

  علامت: نتیجه فرمول RANK شما یک درصد منفی یا بسیار کمتر یا بیشتر از حد انتظار است.

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

  برای حل این مشکل، از محاسبات زیر برای بیان آرگومان nper در واحدهای مناسب استفاده کنید:

  پرداخت های ماهانه: nper = سال * 12

  پرداخت های سه ماهه: nper = سال * 4

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

  پرداخت های ماهانه: نرخ بهره سالانه = RATE() * 12

  

  پرداخت های سه ماهه: نرخ بهره سالانه = RATE() * 4

  فرمول RATE صفر درصد را برمی گرداند

  علامت: نتیجه فرمول به صورت درصد صفر و بدون ارقام اعشاری (0%) ظاهر می شود.

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

  

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

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