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

معرفی تابع PRICE

تابع PRICE در اکسل برای محاسبه قیمت (Price) یک اوراق بهادار با ارزش اسمی ۱۰۰ استفاده می‌شود؛ یعنی خروجی به شما می‌گوید اگر ارزش اسمی (Par) اوراق ۱۰۰ واحد باشد، با توجه به نرخ بهره/کوپن، بازده مورد انتظار و تعداد دفعات پرداخت، قیمت تئوریک آن چقدر می‌شود.

این تابع در تحلیل اوراق قرضه (Bond)، اسناد خزانه، و هر نوع ابزار بدهی با پرداخت بهره دوره‌ای کاربرد دارد؛ مخصوصاً وقتی می‌خواهید قیمت را از روی «بازده تا سررسید» (Yield) و «نرخ کوپن» (Rate) به‌دست آورید.

مثال ساده فارسی: فرض کنید یک اوراق داریم که تاریخ تسویه 1403/01/15 و تاریخ سررسید 1406/01/15 است، نرخ کوپن سالانه 20%، بازده مورد انتظار 22%، ارزش بازخرید 100 و پرداخت بهره سالی 2 بار انجام می‌شود. اکسل می‌تواند قیمت این اوراق را محاسبه کند.

=PRICE("1403/01/15";"1406/01/15";0.2;0.22;100;2;0)

کاربردهای اصلی تابع PRICE

• محاسبه قیمت تئوریک اوراق قرضه/ابزار بدهی از روی نرخ کوپن و بازده
• مقایسه قیمت چند اوراق مختلف با شرایط پرداخت متفاوت (سالانه، شش‌ماهه، فصلی)
• تحلیل حساسیت قیمت نسبت به تغییرات Yield (بازده)
• کنترل و اعتبارسنجی قیمت‌های ارائه‌شده توسط کارگزاری/سامانه‌ها
• ساخت داشبوردهای مدیریت پرتفوی درآمد ثابت

ساختار (Syntax)

ساختار انگلیسی:

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

ساختار با توضیح فارسی آرگومان‌ها:

=PRICE(تاریخ_تسویه; تاریخ_سررسید; نرخ_کوپن; بازده; ارزش_بازخرید; دفعات_پرداخت; [مبنای_روزشماری])

آرگومان‌ها

settlement / تاریخ تسویه

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

maturity / تاریخ سررسید

تاریخ پایان عمر اوراق و بازپرداخت اصل مبلغ. باید بعد از تاریخ تسویه باشد.

rate / نرخ کوپن

نرخ بهره سالانه کوپن (مثلاً 0.18 یعنی 18%). این نرخ مبنای محاسبه پرداخت‌های بهره است.

yld / بازده (Yield)

بازده سالانه مورد انتظار بازار (مثلاً 0.22 یعنی 22%). این نرخ برای تنزیل جریان‌های نقدی استفاده می‌شود.

redemption / ارزش بازخرید

مبلغی که در سررسید به ازای هر 100 ارزش اسمی بازپرداخت می‌شود. معمولاً 100 است، اما ممکن است متفاوت باشد.

frequency / دفعات پرداخت

تعداد پرداخت‌های کوپن در سال:

• 1 = سالانه
• 2 = شش‌ماهه
• 4 = فصلی

basis / مبنای روزشماری (اختیاری)

روش محاسبه تعداد روزها در سال/ماه. رایج‌ترین‌ها:

• 0 = US (NASD) 30/360
• 1 = Actual/Actual
• 2 = Actual/360
• 3 = Actual/365
• 4 = European 30/360

اگر وارد نشود، اکسل معمولاً مقدار 0 را در نظر می‌گیرد.

مثال‌های ساده و پایه

مثال 1: محاسبه قیمت اوراق با داده‌های داخل سلول‌ها

فرض کنید در یک شیت این مقادیر را دارید:

• A2: تاریخ تسویه = 1403/01/15
• B2: تاریخ سررسید = 1406/01/15
• C2: نرخ کوپن = 0.20
• D2: بازده = 0.22
• E2: ارزش بازخرید = 100
• F2: دفعات پرداخت = 2
• G2: مبنا = 0

=PRICE(A2;B2;C2;D2;E2;F2;G2)

نتیجه یک عدد است که قیمت را «به ازای 100 ارزش اسمی» نشان می‌دهد. اگر خروجی مثلاً 97.5 باشد یعنی قیمت تئوریک این اوراق 97.5 است.

مثال 2: استفاده از basis پیش‌فرض

اگر مبنای روزشماری برای شما مهم نیست یا همان پیش‌فرض کافی است، می‌توانید آرگومان basis را حذف کنید.

=PRICE(A2;B2;C2;D2;E2;F2)

مثال‌های کاربردی و واقعی

مثال 1: محاسبه مجموع ارزش بازار پرتفوی با SUM

فرض کنید در ستون H قیمت هر اوراق (به ازای 100) با PRICE محاسبه می‌شود و در ستون I ارزش اسمی خریداری‌شده (مثلاً 500000) دارید. برای تبدیل قیمتِ «به ازای 100» به مبلغ واقعی، معمولاً قیمت را در (ارزش اسمی/100) ضرب می‌کنیم. سپس مجموع کل را می‌گیریم.

=SUMPRODUCT(H2:H10;I2:I10/100)

مثال 2: شرط‌گذاری برای محاسبه فقط اوراق معتبر با AND/OR

اگر بخواهید فقط وقتی محاسبه انجام شود که تاریخ‌ها معتبر باشند و نرخ‌ها منفی نباشند:

=IF(AND(A2=0;D2>=0;OR(F2=1;F2=2;F2=4));PRICE(A2;B2;C2;D2;E2;F2;G2);"ورودی نامعتبر")

مثال 3: پیدا کردن بازده از روی قیمت بازار (ترکیب با XLOOKUP و استفاده از PRICE برای سناریو)

فرض کنید یک جدول سناریو از Yieldها در K2:K20 دارید و می‌خواهید برای هر Yield قیمت را حساب کنید، سپس نزدیک‌ترین قیمت به قیمت بازار را پیدا کنید. (برای سادگی، ابتدا قیمت‌ها را محاسبه می‌کنیم و بعد با XLOOKUP جستجو می‌کنیم.)

در L2 قیمت سناریویی:

=PRICE($A$2;$B$2;$C$2;K2;$E$2;$F$2;$G$2)

و اگر قیمت بازار در M2 باشد و جدول قیمت‌های سناریویی در L2:L20:

=XLOOKUP(M2;L2:L20;K2:K20;;-1)

این کار به شما یک Yield تقریبی می‌دهد که قیمت محاسباتی‌اش نزدیک به قیمت بازار است (روش دقیق‌تر معمولاً با YIELD انجام می‌شود، اما این روش برای تحلیل سریع سناریوها مفید است).

مثال 4: شمارش اوراقی که قیمتشان کمتر از 100 شده (COUNTIF)

اگر در H2:H100 قیمت‌های محاسبه‌شده را دارید:

=COUNTIF(H2:H100;"<100")

ترکیب تابع PRICE با فرمول‌های دیگر

• PRICE + IF برای جلوگیری از محاسبه روی داده‌های ناقص

  =IF(OR(A2="";B2="";C2="";D2="");"";PRICE(A2;B2;C2;D2;E2;F2;G2))

• PRICE + ROUND برای گرد کردن قیمت به تعداد اعشار دلخواه (مثلاً 2 رقم)

  =ROUND(PRICE(A2;B2;C2;D2;E2;F2;G2);2)

• PRICE + SUMPRODUCT برای محاسبه ارزش کل پرتفوی

  =SUMPRODUCT(ROUND(H2:H10;2);I2:I10/100)

• PRICE + XLOOKUP برای خواندن مشخصات اوراق از جدول و سپس قیمت‌گذاری

  =PRICE(XLOOKUP(N2;Table1[نماد];Table1[تسویه]);XLOOKUP(N2;Table1[نماد];Table1[سررسید]);XLOOKUP(N2;Table1[نماد];Table1[کوپن]);XLOOKUP(N2;Table1[نماد];Table1[بازده]);100;2;0)

خطاهای رایج و روش رفع آنها

1) خطای #VALUE!

معمولاً وقتی رخ می‌دهد که تاریخ‌ها به‌صورت متن (Text) ذخیره شده باشند یا یکی از ورودی‌ها غیرعددی باشد.

• تاریخ‌ها را در سلول با فرمت Date واقعی وارد کنید، نه متن.
• اگر تاریخ از سیستم/فایل وارد شده، با ابزار Text to Columns یا DATEVALUE تبدیلش کنید.

2) خطای #NUM!

وقتی ورودی‌ها از نظر منطقی یا محدوده مجاز درست نباشند، مثل:

• settlement ≥ maturity
• frequency چیزی غیر از 1 یا 2 یا 4 باشد
• rate یا yld مقدار نامعتبر (مثلاً بسیار بزرگ یا منفی در سناریوهای ناسازگار) داشته باشد
• redemption ≤ 0

راه‌حل: ترتیب تاریخ‌ها را چک کنید، frequency را فقط 1/2/4 بگذارید، و مطمئن شوید redemption مثبت است.

3) نتیجه غیرواقعی (خیلی بزرگ/خیلی کوچک)

اغلب به خاطر اشتباه در درصدها رخ می‌دهد؛ مثلاً وارد کردن 20 به جای 0.20.

• اگر درصد را به شکل 20 وارد می‌کنید، فرمت را Percentage کنید یا مقدار را به 0.20 تبدیل کنید.
• بررسی کنید rate و yld هر دو «سالانه» هستند و با frequency تناقض ندارند.

4) اختلاف با قیمت بازار

PRICE یک قیمت تئوریک بر اساس مدل تنزیل و مبنای روزشماری است. اختلاف می‌تواند به خاطر basis متفاوت، روش محاسبه کوپن، یا کارمزد/مالیات/شرایط بازار باشد.

• basis را با استاندارد بازار خود هماهنگ کنید (مثلاً Actual/365 یا Actual/Actual).
• فرض‌های مدل (کوپن ثابت، زمان‌بندی پرداخت) را با واقعیت اوراق تطبیق دهید.

نکات حرفه‌ای و ترفندهای مهم

• ورودی تاریخ را حتماً استاندارد کنید: بهترین حالت این است که تاریخ در سلول به‌صورت Date واقعی باشد و فقط نمایش آن شمسی/فارسی باشد (در صورت استفاده از افزونه‌ها).
• برای جلوگیری از خطا، اعتبارسنجی داده (Data Validation) بگذارید تا frequency فقط 1، 2 یا 4 باشد.
• از جدول (Excel Table) استفاده کنید تا فرمول PRICE به‌صورت خودکار برای ردیف‌های جدید کپی شود.
• basis را جدی بگیرید: در بازارهای مختلف، روزشماری متفاوت است و می‌تواند روی قیمت اثر بگذارد.
• خوانایی فرمول: اگر فرمول طولانی شد، ورودی‌ها را در سلول‌های جدا نگه دارید یا از نام‌گذاری (Name Manager) برای محدوده‌ها استفاده کنید.

تفاوت تابع PRICE با توابع مشابه

• PRICE vs YIELD: تابع PRICE «قیمت» را از روی «بازده» می‌دهد؛ ولی YIELD برعکس، «بازده» را از روی «قیمت» محاسبه می‌کند.
• PRICE vs PRICEDISC: PRICEDISC برای اوراق بدون کوپن (Discount Securities) است؛ اما PRICE برای اوراق با پرداخت بهره دوره‌ای (کوپن‌دار) کاربرد دارد.
• PRICE vs PRICEMAT: PRICEMAT برای اوراقی است که بهره در سررسید پرداخت می‌شود یا ساختار خاص «at maturity» دارد؛ اما PRICE برای ساختار رایج پرداخت کوپن دوره‌ای مناسب است.

سازگاری با نسخه‌های مختلف اکسل

تابع PRICE از توابع مالی قدیمی و استاندارد اکسل است و در اغلب نسخه‌ها وجود دارد، از جمله:

• Excel 2007، 2010، 2013، 2016، 2019
• Microsoft 365 (ویندوز و مک)
• Excel for the web (در بسیاری از سناریوها قابل استفاده است، اما در برخی محیط‌ها ممکن است محدودیت‌هایی در توابع مالی وجود داشته باشد)

نکته مهم: تفاوت اصلی معمولاً در تنظیمات ناحیه‌ای (جداکننده ; یا ,) و سیستم تاریخ (1900/1904) است، نه خود تابع.

سؤالات پرتکرار درباره تابع PRICE

PRICE خروجی را بر چه اساسی برمی‌گرداند؟

قیمت «به ازای 100 ارزش اسمی» را برمی‌گرداند، نه قیمت برای کل حجم سرمایه‌گذاری شما.

اگر قیمت کل برای ارزش اسمی 500,000 بخواهم چه کنم؟

قیمت PRICE را در (ارزش اسمی/100) ضرب کنید.

frequency را اشتباه وارد کنم چه می‌شود؟

اگر غیر از 1 یا 2 یا 4 باشد معمولاً #NUM! می‌گیرید.

برای اوراق بدون کوپن هم می‌توانم از PRICE استفاده کنم؟

بهتر است از PRICEDISC استفاده کنید؛ PRICE برای اوراق کوپن‌دار طراحی شده است.

جمع‌بندی و پیشنهاد یادگیری بعدی

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

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

• YIELD (محاسبه بازده از روی قیمت)
• DURATION و MDURATION (ریسک نرخ بهره و حساسیت)
• PV و NPV (ارزش فعلی جریان‌های نقدی)
• XLOOKUP و SUMPRODUCT (مدل‌سازی و ساخت گزارش/داشبورد پرتفوی)