فایل اکسل بازگشت سرمایه گذاری، کاربران را قادر می سازد تا بازده سرمایه گذاری سالانه حاصل از سرمایه گذاری سالیانه ماهانه را محاسبه کنند. کاربران موظفند مبالغ مستمری ماهانه و ارزش گذاری سرمایه گذاری مناسب را برای محاسبه بازده سرمایه گذاری سالانه وارد کنند.

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

برگه های زیر در فایل اکسل بازگشت سرمایه گذاری موجود است:

ROI – مبالغ مستمری ماهانه و ارزش گذاری سرمایه گذاری را در این برگه وارد کنید تا به طور خودکار بازده سرمایه گذاری سالانه محاسبه شود. دوره های ماهانه و سالانه با تاریخ شروعی که در سلول D2 وارد می شود تعیین می شود و مبلغ یکجا در ابتدای دوره سرمایه گذاری می تواند در سلول G2 وارد شود. از بخش پیش بینی می توان برای محاسبه پیش بینی ارزش گذاری سرمایه گذاری استفاده کرد.

ترازها – این برگه شامل محاسبه دقیق ارزش گذاری سرمایه گذاری و بازده سرمایه گذاری سالانه (ستون A تا G) بر اساس مقادیر درج شده در برگه بازگشت سرمایه است. از محاسبات ستون های I تا L می توان برای محاسبه بازده سرمایه گذاری سالانه تجمعی استفاده کرد. ویژگی Goal Seek باید برای محاسبه دقیق بازده سرمایه گذاری کلی استفاده شود.

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

محاسبه بازده سرمایه گذاری سالانه

ورودی کاربر

تمام محاسبات بازگشت سرمایه در این الگو بر اساس مبالغ مستمری ماهانه است که در محدوده سلولی از سلول B6 تا U17 در برگه بازگشت سرمایه وارد می شود. کاربران همچنین ملزم به وارد کردن ارزش گذاری برای سرمایه گذاری مستمری در ردیف 20 هستند. همه سلول های دارای پس زمینه سلول زرد نیاز به ورودی کاربر دارند و سلول های با پس زمینه سلول آبی روشن حاوی فرمول هستند.

تاریخ شروعی که در سلول D2 مشخص شده است، دوره‌های ماهانه در ستون A و دوره‌های سالانه در ردیف 5 را تعیین می‌کند. همه محاسبات بازده سرمایه‌گذاری بر اساس این فرض است که مبالغ مستمری مناسب در پایان سال پرداخت می‌شود. هر دوره ماهانه – تاریخ شروعی که در سلول D2 مشخص شده است، بنابراین نباید تاریخ اولین پرداخت باشد، بلکه باید تاریخ یک ماه قبل از پرداخت اولین مبلغ سالیانه باشد.

توجه: تاریخ شروع باید هنگام شروع استفاده از الگو وارد شود و نباید متعاقباً اصلاح شود. اگر تاریخ شروع را اصلاح کنید، مبالغ مستمری که در محدوده سلول B6 تا U17 وارد شده است ممکن است با دوره های ماهانه صحیح مطابقت نداشته باشد.

مبلغ یکجا که در سلول G2 مشخص شده است در ابتدای دوره سرمایه گذاری برای اهداف محاسبه بازگشت سرمایه سالانه لحاظ می شود. اگر مبلغ یکجا علاوه بر مبالغ مستمری ماهانه که در سلول‌های B6 تا U17 مشخص شده‌اند، باید در این سلول مقداری وارد شود. اگر در ابتدای دوره سرمایه گذاری سرمایه گذاری یکجا وجود نداشته باشد، مقدار صفر باید در سلول G2 وارد شود.

تمام مبالغ مستمری ماهانه باید در محدوده سلولی از سلول B6 تا U17 وارد شود و باید با دوره های ماهانه مناسب که در ستون A و دوره های سالانه در ردیف 5 گنجانده شده است مطابقت داشته باشد. همچنین توجه به این نکته مهم است که مقادیر مستمری باید با مقادیر ارزش گذاری وارد شده در ردیف 20 مطابقت داشته باشد.

به عنوان مثال: اگر مبالغ مستمری تا دوره ماهانه دسامبر 2010 وارد شده باشد، ارزش گذاری در ردیف 20 باید ارزش گذاری سرمایه گذاری در پایان دسامبر 2010 باشد. اگر مبالغ مستمری با تاریخ ارزیابی مطابقت نداشته باشد، ارزش سالانه محاسبات بازگشت سرمایه دقیق نخواهد بود.

توجه: برای یک دوره کامل 12 ماهه مجبور نیستید مبالغ مستمری را وارد کنید. تا زمانی که تاریخ ارزیابی با مبالغ سالیانه وارد شده مطابقت داشته باشد، بازده سرمایه گذاری سالانه به طور دقیق محاسبه می شود. بنابراین، می‌توانید بازده سرمایه‌گذاری سالانه مناسب را به صورت مستمر محاسبه کنید – اگر فقط 3 مبلغ سالیانه را وارد کرده‌اید و ارزش‌گذاری سرمایه‌گذاری را در پایان ماه سوم وارد کرده‌اید، محاسبه بازگشت سرمایه سالانه برای دوره‌ای محاسبه می‌شود که بعد از 3 ماه به پایان می رسد.

ارزش گذاری های سرمایه گذاری که در ردیف 20 درج می شود را می توان از صورت های ماهیانه ای که از موسسه مالی که سالیانه با آن سرمایه گذاری می شود دریافت می شود. به منظور محاسبه و نظارت بر بازده سرمایه‌گذاری حاصل از سرمایه‌گذاری مستمری، ضروری است که صورت‌های ماهانه منظم از مؤسسه مالی مناسب دریافت شود و این صورت‌ها منعکس‌کننده ارزیابی سرمایه‌گذاری باشند.

فایل اکسل بازگشت سرمایه گذاری – Annuity Investment Return (ROI)

محاسبات بازده سرمایه گذاری

برگه ROI شامل محاسبات مشارکت‌های انباشته، مازاد یا کسری سرمایه‌گذاری، درصد مازاد یا کسری سرمایه‌گذاری، درصد بازگشت سرمایه سالانه و درصد بازده سرمایه‌گذاری تجمعی سالانه است. همه این محاسبات به‌جز درصد سرمایه‌گذاری سالانه تجمعی خودکار هستند – برای اطلاعات بیشتر در مورد نحوه محاسبه بازده سرمایه‌گذاری تجمعی، به بخش محاسبه بازگشت سرمایه سالانه تجمعی این دستورالعمل‌ها مراجعه کنید.

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

مبالغ مازاد یا کسری ردیف 23 با کسر مبلغ مشارکت انباشته مناسب از مبالغ ارزشیابی وارد شده در ردیف 20 محاسبه می شود. درصد مازاد یا کسری ردیف 24 با تقسیم مبلغ مازاد یا کسری بر مبالغ مشارکت انباشته محاسبه می شود. . بنابراین این مقادیر نشان می دهد که آیا ارزش گذاری سرمایه گذاری از وجوهی که به سرمایه گذاری کمک شده است بیشتر است یا خیر – اگر مقادیر منفی باشند، نشان می دهد که ارزش سرمایه گذاری کمتر از وجوهی است که سرمایه گذاری شده است.

درصد بازده سالانه که در ردیف 25 محاسبه می شود بر اساس مبالغ مستمری درج شده در ردیف 6 تا 17 و رشد سرمایه گذاری است که با احتساب ارزش گذاری سال قبل به عنوان ارزش فعلی (PV) و سال جاری تعیین می شود. ارزش گذاری به عنوان ارزش آتی (FV) در محاسبه. میانگین مبلغ مستمری ماهانه محاسبه و به عنوان مبلغ پرداختی ماهانه در محاسبه لحاظ می شود.

توجه: پرداخت‌های ماهانه‌ای که با اکثر سرمایه‌گذاری‌های نوع مستمری مرتبط است، معمولاً مشمول یک درصد تشدید سالانه می‌شوند، به این معنی که پرداخت‌های مستمری برای هر دوره ۱۲ ماهه که بخشی از دوره سرمایه‌گذاری را تشکیل می‌دهد ثابت می‌ماند. بنابراین محاسبه ما از بازده سرمایه گذاری سالانه بر این فرض استوار است که پرداخت های ماهانه در هر دوره 12 ماهه ثابت می ماند.

توجه: اگر مبالغ مستمری ماهانه ثابت نباشد، بازده سرمایه گذاری سالانه محاسبه شده 100% دقیق نخواهد بود، اما چون بازده را بر اساس میانگین پرداخت ماهانه 12 ماهه محاسبه می کنیم، بازده واقعی سرمایه گذاری تفاوت قابل توجهی با محاسبه ما نخواهد داشت. . بنابراین این الگو می تواند برای مستمری ثابت و متغیر ماهانه استفاده شود.

پیش بینی سرمایه گذاری

پیش‌بینی سرمایه‌گذاری سالیانه در ردیف‌های 29 تا 33 در برگه ROI گنجانده شده است. کاربران ملزم به وارد کردن مبلغ سالانه اولیه ماهانه، درصد افزایش سالانه و بازده سرمایه‌گذاری سالانه پیش‌بینی‌شده هستند تا پیش‌بینی ارزیابی سرمایه‌گذاری را تهیه کنند.

درصد افزایش سنوات سالانه که در ردیف 30 وارد می شود، به منظور محاسبه مبالغ مستمری ماهانه در ردیف 32، به مبلغ مقرری اولیه وارد شده در سلول B29 اعمال می شود. این مبالغ در محاسبه پیش بینی ارزش گذاری سرمایه گذاری به عنوان پرداخت ماهانه لحاظ می شود. مقادیر؛ بازده سرمایه گذاری سالانه پیش بینی شده (ردیف 31) به عنوان نرخ بازده و ارزش گذاری دوره قبل به عنوان ارزش فعلی (PV) لحاظ می شود.

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

فایل اکسل بازگشت سرمایه گذاری – Annuity Investment Return (ROI)

مانده های سرمایه گذاری سالانه

محاسبات ستون A تا G در ترازنامه به منظور ارائه تجزیه و تحلیل دقیق ترازهای سرمایه گذاری مستمری ماهانه در الگو گنجانده شده است. تمام این محاسبات بر اساس مقادیری است که در برگه ROI وارد شده است. اگر مبالغ مستمری ماهانه برای هر دوره 12 ماهه ثابت باشد، مانده‌هایی که در ستون F محاسبه می‌شوند باید با مبالغ ارزیابی که در ردیف 20 در برگه بازگشت سرمایه درج شده‌اند، مطابقت داشته باشند.

اگر مبالغ مستمری ماهانه برای هر دوره 12 ماهه ثابت نباشد، ممکن است متوجه شوید که مانده های سرمایه گذاری که در ستون F محاسبه می شود با مبالغ ارزش گذاری مناسب متفاوت است، اما این تفاوت ها قابل توجه نخواهد بود و تأثیر قابل توجهی بر سالانه نخواهد داشت. بازده سرمایه گذاری که محاسبه می شود.

محاسبات ستون I تا L در ترازنامه در قالب گنجانده شده است تا کاربران بتوانند بازگشت سرمایه گذاری سالانه تجمعی را محاسبه کنند که 100٪ دقیق است.

محاسبه بازده سرمایه گذاری سالانه تجمعی

روش محاسبه

محاسبه بازده سرمایه گذاری انباشته سالانه برای سرمایه گذاری های سالیانه بسیار پیچیده است زیرا مبالغ مستمری ماهانه معمولاً در یک دوره سرمایه گذاری بیش از 12 ماه ثابت نیست و مبالغ مستمری ماهانه باید به دوره سرمایه گذاری ماهانه صحیح تخصیص داده شود. بازگشت کلی سرمایه گذاری سالانه 100٪ دقیق را محاسبه کنید.

همانطور که قبلا ذکر کردیم، بازده سرمایه گذاری سالانه بر اساس این فرض محاسبه می شود که مبالغ مستمری ماهانه که در دوره 12 ماهه مناسب لحاظ می شود ثابت بماند. این فرضی است که برای اکثر سرمایه گذاری های مستمری صادق است و حتی اگر مبالغ مستمری ماهانه ثابت نباشد، بازگشت سرمایه گذاری که در دوره 12 ماهه مناسب بر اساس میانگین مبلغ سالیانه ماهانه محاسبه می شود، نباید تفاوت قابل توجهی با بازده سرمایه گذاری واقعی داشته باشد. .

فرض ثابت بودن مبلغ مستمری ماهانه نباید برای مدت سرمایه گذاری بیش از 12 ماه اعمال شود. مبالغ مستمری ماهانه یک سرمایه گذاری مستمری معمولی در دوره های بیش از 12 ماه متفاوت خواهد بود و اگر از میانگین مبلغ مستمری ماهانه برای محاسبه بازده کلی سرمایه گذاری سالانه استفاده شود، نتیجه محاسبه نادرست تضمین می شود.

توجه: بازده سرمایه گذاری سالانه مستمری در این الگو (ردیف 25 در برگه ROI) با استفاده از تابع RATE و بر اساس محاسبه میانگین مبلغ مستمری ماهانه برای دوره 12 ماهه مناسب محاسبه می شود. همانطور که توضیح دادیم، بنابراین نمی توان از این تابع برای محاسبه بازده سرمایه گذاری سالانه تجمعی استفاده کرد زیرا نتیجه محاسبه دقیق نخواهد بود.

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

هنگام استفاده از تابع IRR، جریان‌های نقدی سرمایه‌گذاری سالیانه با دوره‌های ماهانه مناسب مطابقت ندارد و بنابراین تضمین می‌شود که نتیجه محاسبه نادرست است. این یک نقص در همه محاسبات مبتنی بر IRR است و یک ویژگی مهم است که کاربران باید هر زمان که با محاسبه بازگشت سرمایه گذاری سالیانه بر اساس تابع IRR روبرو می شوند آن را در نظر بگیرند.

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

بنابراین، برای محاسبه بازده سرمایه گذاری سالانه تجمعی دقیق، سطحی از محاسبه دستی لازم است. ما این محاسبه را در ستون‌های I تا L در ترازنامه تسهیل کرده‌ایم و اکنون توضیح می‌دهیم که چگونه می‌توانید از ویژگی Goal Seek Excel برای محاسبه بازگشت سرمایه‌گذاری سالانه تجمعی دقیق برای سرمایه‌گذاری سالیانه در هر دوره سرمایه‌گذاری استفاده کنید.

محاسبه دستی

محاسبه دستی ما از بازگشت سرمایه گذاری تجمعی سالانه در ستون های I تا L در ترازنامه شامل تراز افتتاحیه، درصد بازگشت سرمایه، مقدار رشد سرمایه گذاری و تراز پایانی است. مبالغ مستمری ماهانه که در برگه بازگشت سرمایه درج می شود و در ستون D در ترازنامه گنجانده می شود نیز در محاسبه بازگشت سرمایه سالانه تجمعی ما در نظر گرفته می شود.

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

ارزش گذاری سرمایه گذاری در پایان دوره سرمایه گذاری که بازده انباشته سرمایه گذاری سالانه برای آن محاسبه می شود باید در این ستون مشخص شود. از کادر فهرست در سلول J3 می توان برای برجسته کردن تراز پایانی ماهانه مناسب استفاده کرد – پس از انتخاب ماه مناسب از این کادر فهترست، سلولی که دارای مانده پایانی مناسب (مقدار ارزش گذاری سرمایه گذاری) در ستون L است برجسته می شود. مقدار موجود در این سلول باید با مقدار ارزش گذاری مناسب جایگزین شود تا بازگشت سرمایه گذاری انباشته سالانه محاسبه شود.

با این حال، بحث صرفاً جایگزینی تراز پایانی محاسبه‌شده با مقدار ارزش‌گذاری مناسب نیست – ما در واقع باید تعیین کنیم که کدام درصد بازگشت سرمایه‌گذاری سالانه منجر به تراز پایانی در ستون L با مقدار ارزش‌گذاری مناسب سرمایه‌گذاری می‌شود. این را می توان بر اساس آزمون و خطا با وارد کردن تعدادی درصد بازگشت سرمایه گذاری مختلف در سلول L3 (که مقادیر درصد بازگشت سرمایه گذاری در ستون J را تعیین می کند) تا زمانی که مانده پایانی در ستون L برابر با ارزش گذاری مناسب سرمایه گذاری شود، به دست آورد. مقدار یا می توانیم از ویژگی Goal Seek Excel برای تعیین آنی این مقدار استفاده کنیم.

دستورالعمل های گام به گام – جستجوی هدف

فایل اکسل بازگشت سرمایه گذاری – Annuity Investment Return (ROI)

برای محاسبه بازگشت سرمایه گذاری انباشته سالانه مراحل زیر باید طی شود.

مرحله 1 – ما فرض می کنیم که پرداخت های مستمری ماهانه را در برگه ROI وارد کرده اید و ارزش سرمایه گذاری مناسب را در ردیف 20 در برگه ROI وارد کرده اید. اکنون باید تراز پایانی مناسب را در ستون L در ترازنامه پیدا کنید و این مقدار را با مقدار ارزش گذاری مناسب جایگزین کنید. این را می توان به سادگی با انتخاب ماه مناسب از کادر فهرست موجود در سلول J3 در ترازنامه انجام داد. پس از انتخاب ماه مناسب از این کادر فهرست، موجودی نهایی مناسب با رنگ نارنجی برجسته می شود. سپس باید سلولی که هایلایت شده است را انتخاب کنید.

مرحله 2 – با انتخاب ویژگی What-If Analysis از بخش Data Tools در زبانه Data روبان، به ویژگی Goal Seek Excel دسترسی پیدا کنید. ویژگی Goal Seek را می توان از منوی نمایش داده شده پس از دسترسی به ویژگی What-If Analysis انتخاب کرد.

مرحله 3 – کادر گفتگوی Goal Seek شامل 3 فیلد ورودی است. اولین قسمت فیلد ورودی “تنظیم سلول” است – اگر قبل از دسترسی به ویژگی جستجوی هدف، سلولی را که با رنگ نارنجی برجسته شده انتخاب کرده باشید، مرجع سلول از قبل در فیلد ورودی گنجانده شده است. اگر سلول صحیح در فیلد ورودی گنجانده نشده است، کافی است با کلیک بر روی نماد کنار فیلد ورودی، سلول صحیح را انتخاب کنید.

مرحله 4 – مقدار ارزش گذاری سرمایه گذاری مناسب را در قسمت ورودی “To value” وارد کنید. این مقدار باید همان مقدار ارزش گذاری باشد که در برگه بازگشت سرمایه وارد شده است.

توجه: اگر مبالغ مستمری که در برگه ROI درج شده است برای هر دوره 12 ماهه ثابت باشد، مانده پایانی ستون F در ترازنامه باید با مقدار ارزشیابی که باید در این قسمت ورودی وارد کنید، موافقت کند. اگر مبالغ مستمری ماهانه ثابت نباشد، مانده پایانی ستون F ممکن است با مقدار ارزش گذاری متفاوت باشد اما تفاوت قابل توجهی نخواهد بود.

مرحله 5 – فیلد ورودی “By change cell” را انتخاب کرده و سلول L3 را انتخاب کنید. همانطور که قبلاً ذکر کردیم، این سلول درصدهای بازگشت سرمایه سالانه را که در ستون J در ترازنامه گنجانده شده است را تعیین می کند و بنابراین همیشه باید در قسمت ورودی سوم تابع Goal Seek انتخاب شود.

مرحله 6 – روی دکمه OK کلیک کنید. سپس باید پیامی نمایش داده شود که نشان دهد مقدار مناسب محاسبه شده است و مقدار سلول L3 باید به طور خودکار به درصد بازگشت سرمایه سالانه تجمعی مناسب تغییر کند. این مقدار در ستون J در ترازنامه نیز نمایش داده خواهد شد.

مرحله 7 – مقدار سلول L3 را کپی کنید و آن را به عنوان یک مقدار در سلول مناسب در ردیف 26 در برگه ROI قرار دهید. این تضمین می‌کند که تمام درصدهای بازگشت سرمایه‌گذاری سالانه انباشته مناسبی که محاسبه شده‌اند را ثبت می‌کنید و نمودار روی برگه نمودار بر این اساس به‌روزرسانی می‌شود.

نکته: قرار نیست مانده های پایانی ستون های F و L در ترازنامه یکسان باشد زیرا مبالغ این ستون ها بر اساس یکسان محاسبه نمی شود. تنها مقادیری که باید توافق کنند، مانده های پایانی دوره است که در محاسبه بازگشت سرمایه گذاری تجمعی لحاظ شده است. مانده های این دوره نیز تنها در صورتی دقیقاً یکسان خواهد بود که مبالغ مستمری ماهانه که در هر دوره سالانه لحاظ می شود ثابت باشد.

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

نمودار روی برگه نمودار بازگشت سرمایه گذاری برای هر دوره سالانه (ردیف 25 در برگه ROI) و بازده سرمایه گذاری تجمعی را که به صورت دستی محاسبه شده و در ردیف 26 برای هر دوره سالانه کپی شده است را نشان می دهد. بنابراین نمودار مشاهده بازگشت سرمایه گذاری که در هر دوره سرمایه گذاری سالانه به دست می آید و تأثیری که بازگشت سرمایه گذاری سالانه بر بازده سرمایه گذاری سالانه تجمعی دارد را تسهیل می کند.

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