Powered By Blogger

Monday, November 13, 2017

A Model for Analysis of Bond Valuation By Using Microsoft Excel plus VBA







A bond is a type of asset in which a government or a company issues these securities as a long – term debt to borrow money from institutional investors (Banks) or public sector. Each bond has a time to maturity which is usually 5, 10, and 20 or 30 years. Initial value of bond is named par value or face value equal to $1000 with a coupon interest rate on the bond which is the percentage of par value and it will be paid annually or semiannually (two times in one year). In fact, the government or the company is committed regularly and continuously to pay these payments and also repayment of initial value (par value) at maturity time.
The purpose of this article is to present a model for analysis of bond value where there are seven independent variables including current bond price (bond value), YTM, coupon rate, purchased year, purchased month, purchased day and time period (n). This model simultaneously solves an equation with three independent variables accompanied by generating maximum and minimum of this function for given domain and range and also non simultaneously analyzes seven independent variables. One of the most crucial applications of this model is to obtain YTM (return rate) for current price equal to bond value without using any trial and error.

A coupon interest rate always stay the constant while the purchasers of bonds strongly look at and compare it with premium risk of market which is named the return rate or required rate on the capital or yield to maturity (YTM). This is why the price of bond varied with bond value. Of course, there are two factors for this discrepancy: (1) the difference between coupon interest rate and return rate (YTM) and (2) entering time (the time of purchasing). Below diagrams as well as show us the impact of these factors for time periods of 30, 20 and 10 years:




Above diagrams say to us, when YTM (rd) is greater than the coupon rate, the bond value will be less than its par value (Discount Bond), when YTM (rd) is less than the coupon rate, the bond value will be greater than its par value and when YTM (rd) is equal to coupon rate, the bond value will be equal to par value.
Generally the basic valuation model for any asset can be made by using below equation:

Where:

V0 = value of the asset at time zero

CFt = cash flow expected at the end of year t

r = appropriate required return (discount rate)

n = relevant time period

But for each specific asset such as Bonds, Stocks, Real estate and so on, we have to change a little bit above basic equation. For instance, the formula to evaluate the bond value can be as follows:



B0 = value of the bond at time zero

I = annual interest paid in dollars

r = appropriate required return (discount rate)

n = number of years to maturity

M = par value in dollars

rd = required return on a bond

I also used above equation to make this model for analysis of the bonds. Below figure as well as shows the features of this model:



As you can see in the figure above, there are seven independent variables (Inputs) which have been highlighted by red color. First, we enter the period of maturity which is “n”. Then, according to the issue date and the maturity date, we enter Year, Month and Day as current date. After that, we choose a range for Current price, YTM and Coupon rate (Low and High) and next we consider a specific current bond price which is into the range of current price. Finally, this model gives us the outputs which are the minimum and maximum bond value with  the appropriate  YTM and Coupon rate. In the meanwhile, you can see that specific bond price (input) is approximately equal to specific bond value (output) that it says to us about the appropriate YTM and Coupon rate for the specific bond price where we do not need to use any trial and error to obtain YTM for a specific bond price.
You can see below clips as the examples for this model:

The model for n = 30



The model for n = 10



All researchers, investors and individual people who are interested in having this model, don’t hesitate to send their request to below addresses:

Sunday, October 22, 2017

The Power of Analysis Is Still in Microsoft Hands

In the reference with article of “A case of Accounting Control System Solved by a New Idea” posted on link:  http://www.emfps.org/2015/10/a-case-of-accounting-control-system.html?m=1, there is another option for this model. If you only replace one number in column of Debit instead of 10 numbers and type randomly 10 numbers in column of Credit, you will solve a linear equation with maximum 10 independent variables. Of course, I have developed both columns by 20 numbers in this article.
The purpose of this article is to show a model which solves an equation with 20 independent variables. It is clear, normal people don’t need to solve an equation with 20 independent variables but this is a promotional model in which it will present the power of analysis by Microsoft and it will be a challenge for its competitors.

 According to a report posted on website of Forbes in 2012, Tim Worstall stated that Microsoft's Market Share Drops From 97% to 20% In Just over A Decade. (Below link)



But I can anticipate that Microsoft will come back to market and will gain its previous market share where the share price of Microsoft will significantly surge upward in the near future. But when? The time is, when everybody will be easily able to connect his/her spreadsheets to his/her website. In this case, the people will be able to have the data or system analysis simply on their smart phones.

Model (6): The solve an linear equation with 20 variables by click

Assume we have function of “w” with 20 independent variables as follows:

w = f (x,y,z,s,d,f,g,h,j,k,l,i,u,t,r,e,q,n,m,v) =
 x + y + z+ s+ d + f + g + h + j + k + l + i + u + t + r + e + q + n + m+ v

Now, this model will find many answers for all independent variables for a specific result of “c” just like below equation:


x + y + z+ s+ d + f + g + h + j + k + l + i + u + t + r + e + q + n + m+ v = c

Definitely there are infinity answers for all independent variables for a specific result of “c”.
Blow clip shows you some examples which are generated by click:




As you can see on above clip, all variables are changed by each click while amount of “c” as total sum of variables always stays the constant. When I change “c”, we will have a new equation and definitely new answers for this equation. At the first, "c" is equal to 63031280 then it is equal to 77699560 and finally it is equal to 14667470





This is the reason why I say to you: "Many thanks to Bill Gates and Microsoft who gifted and donated us the great opportunity to discover the ways to reach the gates of new worlds. There will be the big changes in engineering fields and finally big changes in educational systems throughout the world in the near future."

Sunday, October 8, 2017

مدل 1-5) کنترل سیستم های مالی با یک ایده جدید

اکسل برای مدیران, مهندسان, حسابداران
 وتولید ایده های نو

مدل های قبلی را میتوانید روی لینک های زیر مطالعه بفرمایید:

شماره 1) مدل H – V:     http://www.emfps.org/2017/09/h-v.html

مدل شماره 2) تولید انبوه پسورد (Password):  http://www.emfps.org/2017/09/2-password.html

مدل شماره 3) دینامیک و حرکت در اکسل:    http://www.emfps.org/2017/09/3.html

مدل 4) آنالیز سه بعدی با کلیک:   http://www.emfps.org/2017/10/4.html



مدل1 -5) کنترل سیستم های مالی با یک ایده جدید


فرض کنید دو ستون دارید که یکی مربوط به متغیر x و ستون بعدی مربوط به متغیر y هست. حالا زیر ستون x تعدادی عدد تایپ میکنید در این مدل من 10 عدد انتخاب کردم (x1, x2, x3, x4, x5, x6, x7, x8, x9, x10). در زیرستون y هم ده عدد دیگه تایپ میکنید (y1, y2, y3, y4, y5, y6, y7, y8, y9, y10). حالا سوال اینست که کدام سری ریاضی از ستون x برابر میشود با یک سری ریاضی در ستون y؟ یا کدام سری ریاضی از ستون x منهای یک سری ریاضی در ستون y , برابر یک عدد خاص یا ایندکس که در اینجا به اسم مغایرت در حسابداری نام دارد, میشود؟. بطورمثال برای ستون x ما حالتهای زیررا داریم:

x1+ x2+x10+x5

x3+x7+x1

x4 + x3 + x 6 + x 7 + x8 + x9

بطورمثال برای ستون y ما حالتهای زیررا داریم:

y2 + y4 + y6 + y8 + y10

y1 + y3 + y5 + y7

y2 + y5 + y9

همانطور که شما می دانید بر اساس روابط جایگشت ها در آمار و احتمالات ریاضی, ما می توانیم 2^10 معادل 1024 معادله برای ستون x ها و 1024 معادله برای ستون y ها بنویسیم. در حقیقت این مدل توانایی حل همزمان 1024 معادله را دارد بطوریکه در قسمت Outputs نتیجه حل معادلات را که همان جواب به سوالات فوق می باشد را دارد. بستگی به مشخصات فنی کامپیوترو قدرت و سرعت پردازش آن, شما می توانید تعداد اعداد هر ستون را کم و یا زیاد کنید. بطور مثال من برای هر ستون تا 20 عدد یک مدل ساخته ام.

همانطور که در فیلم آموزشی زیر می بینید, شما در قسمت Inputs برای هر ستون اعداد تصادفی وارد میکنید که در این مدل یک ستون مربوط به حساب بستانکاری و یک ستون مربوط به حساب بدهکاری است. سپس در قسمت Outputs , روی هر ردیف بستانکاری اعدادی حاصل میشود که اگر با هم جمع شوند برابر جمع اعداد همردیف در قسمت بدهکاری (ردیف های سمت راست) است. اگر در قسمت Inputs به جای بالانس, مغایرت را جایگزین کنید, اعداد روی ردیف ها در Outputs ودرهر ردیف بستانکاری اعدادی حاصل میشود که اگر با هم جمع شوند و منهای جمع اعداد همردیف در قسمت بدهکاری (ردیف های سمت راست) شوند همگی مساوی عدد مغایرت خواهند بود.








همانطور که در فیلم آموزشی فوق می بینیم, من در ستون بستانکار اعداد 1 تا 10 و در ستون بدهکار اعداد 10 تا 100 را جایگزین کردم. سپس در قسمت نتیجه نهایی (Outputs) , پنج جواب حاصل میشود. بطور مثال: در ردیف 20 شیت اکسل و جواب شماره 3 ,برای ستون بستانکار اعداد 2 و 4و 7 و 8 و 9 و 10 بدست آمد که جمع این اعداد 40 میشود از طرفی در قسمت بدهکار فقط یک عدد 40 بدست آمد یعنی مجموع اعداد سل ها روی هر ردیف در سمت چپ با مجموع اعداد سل ها روی همان ردیف در سمت راست برابر شد. یا در ردیف 22 شیت اکسل و جواب شماره 5, برای ستون بستانکار اعداد 2 و 3و 5 و 6 و 7 و 8 و 9 و 10 بدست آمد که جمع این اعداد 50 میشود از طرفی در قسمت بدهکار فقط دو عدد 20 و 30 بدست آمد یعنی مجموع اعداد سل ها روی هر ردیف در سمت چپ با مجموع اعداد سل ها روی همان ردیف در سمت راست برابر شد. حالا من در سل B14 و در قسمت بالانس عدد مغایرت بین دو ستون بدهکار و بستانکار را جایگزین کردم و شما می بینید که در این حالت 6 جواب حاصل شد که با جواب های قبلی کاملا فرق دارد. بطور مثال: در ردیف 20 شیت اکسل و جواب شماره 3 ,برای ستون بستانکار اعداد 2 و 4و 5 و 7 و 8 و 9 بدست آمد که جمع این اعداد 35 میشود از طرفی در قسمت بدهکار اعداد 10 و 30 و 40و 50و 60 و 70 و 80 و 90 و 100 بدست آمد که جمع این اعداد 530 میشود.  یعنی مجموع اعداد سل ها روی هر ردیف در سمت چپ منهای مجموع اعداد سل ها روی همان ردیف در سمت راست برابربا عدد مغایرت میشود (530 – 35 = 495 -). یا در ردیف 22 شیت اکسل و جواب شماره 5, برای ستون بستانکار اعداد 2 و 3  بدست آمد که جمع این اعداد 5 میشود از طرفی در قسمت بدهکار اعداد 20 و 30 و 50و 60 و 70 و 80 و 90 و 100 بدست آمد که جمع این اعداد 500 میشود. یعنی مجموع اعداد سل ها روی هر ردیف در سمت چپ منهای مجموع اعداد سل ها روی همان ردیف در سمت راست برابربا عدد مغایرت میشود (500 –5 = 495 -).

نکته جالب اینجاست که گاهی اوقات تعداد جواب های حاصل شده در قسمت Outputs بسیار زیاد میشود بطور مثال در فیلم آموزشی فوق وقتی من اعداد تصادفی در هر دو ستون جایگزین میکنم ,بطوریکه برای ستون بستانکاراعداد 1 و 2 و 3و23و 5 و 6 و 7 و 78 و 9 و 10 و برای ستون بدهکار اعداد 10 و20و 30 و 40و 12و 60 و 34 و 80 و 90 و 6 جایگزین میکنم, تعداد 62 جواب در قسمت   Outputs حاصل میشود.

کنترل سیستم های مالی


این مدل کاربردهای زیادی دارد که در مقالات بعدی من کاربرد این مدل را در برخی علوم مهندسی به شما نشان خواهم داد. اما در این مقاله به یکی از کاربردهای مهم این مدل در کنترل سیستم های مالی اشاره کرده ام.
پیشنهاد من به کلیه دوستانی که در علوم مالی و حسابداری فعالیت دارند اینست که قبل از استفاده از این مدل و جهت بهینه سازی ارقام ورودی در قسمت Inputs , حتما مراحل زیر را انجام دهند:

1) قبل از شروع کنترل با این مدل, یک محدوده زمانی که می خواهید حساب ها را کنترل کنند, در نظر بگیرید که به نام Time Benchmarking میباشد.

2) با توجه به اینکه در برخی تاریخ ها مثلا در یک روز چندین پرداخت و یا دریافت داشته اید, جهت جمع مبالغ روی یک روز از دستور SUMIF همانند فیلم آموزشی زیر استفاده کنید.






3) با توجه به اینکه تاریخ برخی پرداخت ها مثل پیش پرداخت یک قرارداد بسیار زود تر از اسناد دریافتی, پرداخت میشود و یا برخی دریافت ها در تاریخ های مختلف پرداخت میگردد, جهت نشاندن هر رقم پرداختی یا دریافتی در محل تاریخ خود, از دستور IFERROR بهمراه INDEX همانند فیلم آموزشی زیراستفاده نمایید.

ضمنا در لینک زیر شرح جزییات کامل موارد فوق الذکر را همراه با مثال توضیح داده ام:

http://www.emfps.org/2015/10/a-case-of-accounting-control-system.html






در فیلم آموزشی زیر یک مثال از کنترل مالی و روش مغایرت گیری با استفاده از این مدل مشاهده می فرمایید:









Monday, October 2, 2017

مدل 4) آنالیز سه بعدی با کلیک

اکسل برای مدیران, مهندسان, حسابداران

 وتولید ایده های نو

مدل های قبلی را میتوانید روی لینک های زیر مطالعه بفرمایید:

شماره 1) مدل H – V:     http://www.emfps.org/2017/09/h-v.html

مدل شماره 2) تولید انبوه پسورد (Password):  http://www.emfps.org/2017/09/2-password.html

مدل شماره 3) دینامیک و حرکت در اکسل:    http://www.emfps.org/2017/09/3.html

مدل 4) آنالیز سه بعدی با کلیک

یکی از بهترین ابزارها جهت آنالیز در اکسل, استفاده از Data Table بوده که جهت بررسی و آنالیز مربوط به تاثیر یک یا دو متغیر همزمان بر روی یک الگوریتم بکار میرود. اسم این آنالیز Sensitivity Analysis میباشد. 
همانطور که گفتم حداکثر تعداد متغیر ها جهت آنالیز با این روش, دو متغیراست اما در این مقاله با استفاده از روش قید شده در مدل 3 (لینک: http://www.emfps.org/2017/09/3.html), من یک مثال در زمینه حسابداری برای آنالیز سه طرفه و یا سه بعدی آورده ام.

قبل ازآن دو مثال بصورت فیلم آموزشی در رشته مهند سی برق و حسابداری برای روش آنالیزدو بعدی که با اکسل قابل اجراست بشرح زیر توضیح داده شده است:

مثال در زمینه مهندسی برق (طراحی یا دستیابی به حداقل افت ولتاژ هدف)

همانطورکه در مدل شماره 1و 3 قید شد, میتوان یک الگوریتم در اکسل برای محاسبه افت ولتاژ نوشت و در این مدل با یک کلیک بر اساس تغییرات سطح مقطع و مقاومت AC یا DC , میزان افت ولتاژ را محاسبه کرد.حال در آنالیز دو بعدی برای دو متغیر دیگر یعنی شدت جریان و فاصله روی شیت اکسل دامنه ای تعریف میکنیم. دامنه برای فاصله روی یک ردیف و شدت جریان روی یک ستون. روش گام به گام آنالیز دو بعدی طبق فیلم آموزشی زیر بشرح زیر است:
- در صد افت ولتاژ را به جدول بین دو متغیر شدت جریان و فاصله منتقل میکنیم.
- برای هر متغیر مذکور یک رنج یا دامنه روی شیت اکسل تایپ میکنیم.
- روی کل اعداد و سل های خالی جدول با موس میکشیم و از نوار بالا ی اکسل روی DATA و
 What – If – Analysis و سپس Data Table کلیک میکنیم.
- در محل Row input cell , مقدار فاصله در الگوریتم اولیه را کلیک میکنیم.
- در محل Column input cell , مقدار شدت جریان در الگوریتم اولیه را کلیک کرده و OK میکنیم.
- حال کل جدول مقدار محاسبه شده افت ولتاژ را به ما میدهد.
- یک سل انتخاب کرده و مقدار حداکثر افت ولتاژطراحی را به عنوان در صد افت ولتاژ هدف تایپ میکنیم.
- روی کل جدول با موس میکشیم و سپس در نوار بالای اکسل روی HOME و Conditional Formatting و
Highlight Cells Rules وسپس Less than کلیک میکنیم.
- داخل محل ورودی اطلاعات در Less than , روی مقدار در صد افت ولتاژ هدف کلیک میکنیم.
حالا با هر کلیک روی نوار سبز و یا قرمزدر انتهای الگوریتم, قسمتی از جدول که به رنگ قرمز های لایت میشود, همگی دارای افت ولتاژ کمتر از افت ولتاژ هدف است بنابراین از مقادیر فاصله و شدت جریان مرتبط با قسمت رنگ قرمز میتوان برای ادامه طراحی استفاده کرد.






 مثال در زمینه حسابداری (آنالیز رسیدن به سود خالص هدف در سال آتی)

فرض کنیم یک خلاصه صورت سود و زیان سال 1396همانگونه که در فیلم آموزشی زیرمی بینید در دسترس دارید. در این الگوریتم ما سه متغییر بشرح زیرداریم که بعنوان فرضیات در این مدل درنظرمیگیریم:
1) در صد رشد فروش خالص
2) درصد رشد بهای کالای فروش رفته
3) در صد رشد هزینه های فروش, اداری وعمومی
از آنجایی که این مدل فقط برای آنالیزدو بعدی است, من متغیر های (1) و (2) بالا یعنی در صد رشد فروش خالص و ودرصد رشد بهای کالای فروش رفته را بعنوان متغیرهای جدول آنالیز در نظر گرفته و متغیر (3) را ثابت فرض میکنم.
حال می خواهیم بدانیم برای رسیدن به سود خالص هدف در سال 1397, متغیرهای ما چگونه باید حرکت کنند که همان مفهوم آنالیزرسیدن به سود هدف است. فرق این مثال با مثال مهندسی برق که در بالا قید شد, تعریف یک رنج و محدوده برای هدف می باشد که در اینجا افزایش تعداد سود خالص هدف است که من آنرا با لیست سود خالص هدف در شیت اکسل نمایش داده ام.
با توجه به فیلم آموزشی زیر, مراحل گام به گام این آنالیز بشرح زیر می باشد:

- مقدارسود خالص پیش بینی شده در سال 1397(Cell C14) را به جدول بین دو متغیردرصد رشد بهای کالای فروش رفته و در صد رشد فروش خالص منتقل میکنیم.
- برای هر متغیر مذکور یک رنج یا دامنه روی شیت اکسل تایپ میکنیم.
- روی کل اعداد و سل های خالی جدول با موس میکشیم و از نوار بالا ی اکسل روی DATA و
 What – If – Analysis و سپس Data Table کلیک میکنیم.
- در محل Row input cell , درصد رشد بهای کالای فروش رفته Cell B3)) در الگوریتم اولیه را کلیک میکنیم.
- در محل Column input cell , در صد رشد فروش خالص Cell B2)) در الگوریتم اولیه را کلیک کرده و OK میکنیم.
- حال کل جدول مقدارسود خالص پیش بینی شده در سال 1397 را به ما میدهد.
- یک سل انتخاب کرده و یک مقداردلخواه بعنوان سود خالص هدف تایپ میکنیم (Cell B16).
- یک ستون انتخاب کرده و یک رنج و محدوده برای سود خالص هدف تایپ میکنیم. در اینجا دامنه مذکور بین سل D8 و D19 تایپ شده است.
- روی کل جدول با موس میکشیم و سپس در نوار بالای اکسل روی HOME و Conditional Formatting و
Highlight Cells Rules وسپس Great than کلیک میکنیم.
- داخل محل ورودی اطلاعات در Great than , روی مقداردلخواه بعنوان سود خالص هدف (Cell B16) کلیک کرده و در قسمت سمت راست رنگ (های لایت) را سبز انتخاب میکنیم.
- روی سل D9 کلیک کرده و سپس از نوار بالای صفحه اکسل روی DATA وData Validation  و در قسمت Allow کلیک کرده و List را انتخاب می کنیم و نهایتا در محل Source محدوده برای سود خالص هدف (D10:D19) را وارد کرده وOK می کنیم.
- حال   D9را به B16 منتقل میکنیم.
حالا با هر کلیک روی سل   D9ومقادیر مربوط به سود خالص هدف,  قسمتی از جدول که به رنگ سبز های لایت میشود, همگی دارای مقادیربیش ازسود خالص هدف است بنابراین از دو متغیردرصد رشد بهای کالای فروش رفته و در صد رشد فروش خالص مرتبط با قسمت رنگ سبز میتوان برای آنالیزوپیش بینی سود خالص سال آتی استفاده کرد.






مثال در زمینه حسابداری (آنالیز سه بعدی با کلیک)

همانگونه که در فیلم آموزشی زیر می بینید, در این مدل علاوه بر درصد رشد فروش خالص و همچنین درصد رشد قیمت تمام شده کالا به عنوان دو متغیر تاثیر گذار بر الگوریتم تعیین سود خالص سالیانه شرکت, تاثیرهمزمان متغیر سوم که درصد رشد هزینه های فروش, اداری و عمومی نیز بر آنالیز تعیین مقدار سود خالص سالیانه شرکت لحاظ شده است. کاربرد مدل شماره 3 (http://www.emfps.org/2017/09/3.html) که مربوط به دینامیک و حرکت در اکسل می باشد, هنگام شروع فیلم آموزشی مجددا نشان داده شده است بطوریکه شما فقط با یک کلیک روی نوار قرمز سمت چپ (سل A15) , همزمان چندین سل متن دار را به هر جایی که میخواهید میتوانید انتقال دهید.
در این مدل, متغیر سوم که درصد رشد هزینه های فروش, اداری و عمومی می باشد, روی یک ستون (H3:H11) تایپ میشود و سپس با استفاده از روش قید شده در مدل شماره 3 به سل B4 انتقال می یابد جایی که با هرکلیک روی نوار سبز(سل های  B15 و (B16 , مقادیر سود خالص در جدول بر اساس سه متغیربدست می آید. در اینجا محل ورودی اطلاعات ((Inputs شامل: درصد رشد فروش خالص و همچنین درصد رشد قیمت تمام شده کالا در سل های C2 و C3 بترتیب به سل های F14 و G13 لینک بوده و سل های D2 و D3 بترتیب برای کنترل تغییرات دامنه F15:F24 و H13:N13 می باشد. همچنین سل   C4مربوط به درصد رشد هزینه های فروش, اداری و عمومی به سل H4 لینک بوده و سل D4 برای کنترل تغییرات دامنه H4:H11 می باشد.