MS Excel's Solver - Really Solves Real World Financial Problems

Greetings !!

Today I'll be throwing light over some important applications of Excel's Solver Tool with respect to real life financial problems. Here, we will see that how this magical tool executes following "Monsters" before they can even make note of that.

*Pls Note: This Intel is devoted to Solver tool only, detailed explanation of various calculations(like NPV etc.) is beyond the scope of this Intel.

Monster 1 - Complex Mathematical Problem.

Suppose, mathematical formula given by

Y = R*(N+R)+N*(1+R/X)*(X+N/X)+(N/X)*(1+R/N)/(N+4)

Subject to constraints :-

X greater than or equal to 5
R = 15
N = (1+R/X) and N smaller than or equal to 1.5

We have to find the value of X for which Y is minimum.

Killing Monster 1 - Refer to Figure 1

Variables Y, X, R, N are entered in cells A1 to A4
Enter formula as shown in the figure in B1 to calculate Y
Enter any arbitrary values in cells B2 & B3
Enter formula =(1+B3/B2) in cell B4

Go To Tools - Solver, It will open solver window as shown, Enter Solver parameters as shown in the figure and click Solve.

We will find that for value X = 30, Y is minimum i.e. 315
------

------
Monster 2 - Cost of Capital (Cost of Debt Borrowing)

Simplest Formula for calculating Pre-Tax Cost of Debt is as Shown in Figure 2, Kd represents the pre-tax cost of Debt Borrowing.

Now I want to know minimum rate of return that Firm must earn from the raised capital through debt/bond whose current value is 1200 Euro, Maturity Value 2000 Euro, Annual Coupon rate is 10% and maturity period is 10 years.

Killing Monster 2 - Refer again to Figure 2

We can see the formula entered in cell B1, Enter solver parameter as shown and click solve.

We will find that, Firm must earn atleast 14% (i.e after-tax cost of debt) to maintain its value.
------

------
Monster 3 - Capital Budgeting (Buy Vs Leasing)

Lessee has 2 Options, either to purchase an equipment or lease it. (Terms of above options are as given in the figure 3). Now it is required for lessor to find the maximum lease rate that will be accepted by Lessee.

Killing Monster 3- Refer to Figure 3

Firstly it is required to calculate the Net present Value (NPV) of cashflows from the two options. Lessee will accept any lease rate which is less than or equal to the rate at which NPV from both the options is equal.

So from figure, by using help of Solver Tool, we can find that Lessee will accept lease rate smaller than or equal to 10%, because at this lease rate, NPV from both options will be equal, so he/she will be interested in opting Lease option, in which he/she has to pay equal or less lease interest payments than 10%.
------

------
List is very long, this can go on and on....

I think these three examples have given a good idea about Excel Solver tool.

See ya.... Don't forget to comment... Cheers !!

Original Article: http://www.qondio.com/ms-excels-solver-really-solves-real-world-financial-problems

Analysis of World Stock Markets

Greetings !!

Hope everybody is fine, and enjoying life at its fullest. As, I am still searching a good job, last week I had full time to spend in analyzing World Stock Markets and their performance over last 10 years. It's an ongoing process but I would like to share some of the findings till now, one can consider it as a detailed excerpt.

Data used : From Jan 01, 2001 to Dec 31, 2011
*For S&P Global 100 and S&P Global 1200, only last 5 years data was available.

Market Indices taken in consider : S&P 500, NASDAQ, DOW JONES, NIKKEI 225, IBOVESPA, RTSI, NIFTY, BSE SENSEX, SHANGHAI, HANG SENG, KOSPI, STRAIT TIMES, DAX, FTSE 100, CAC 40, S&P GLOBAL 100, S&P GLOBAL 1200

Economic Factors considered : GDP Growth Rate, Investment(as %GDP), Gross National Savings (as %GDP), Average Inflation(CPI YOY Basis), Change in Import volume of goods and services, Change in Export volume of goods and services, Unemployment rate, General Government Expenditure(as %GDP), General Government Revenue(as %GDP), General Government Net Lending/Borrowing(as %GDP), Current account balance(as %GDP), Real Interest Rate

Tools Used : Microsoft Excel 2003, SPSS Statistics 17.0

This excerpt be will limited to below attached 10 images/figures.

Figure 1

It depicts absolute 10 year return of Major Stock Markets in the world. Russian RTSI has clearly outperformed other indices while Indian, Brazil & South Korean Indices have managed to give decent return as compared to others over last 10 years. It is interesting to know that US & European Markets have failed to give any significant return during the same period. Clearly Emerging Markets have outperformed others in terms of 10 year return.

Figure 2

It depicts yearly return at a point of time. Russian RTSI, China SHANGHAI, Brazil IBOVESPA & Indian BSE Sensex have been most active indices, both on upside and downside of the return scale. Russian RTSI seen to be most volatile index among all.

Figure 3

This is simple and quite interesting depiction of Return against its Variability/Volatility of major stock indices. SHANGHAI has large difference between its mean & median return over last 10 years while US & European Markets have comparatively less standard deviation(i.e they are comparatively more stable) but they failed to give any significant magnitude of return to investors.

KOSPI, BSE Sensex, Nifty 50, IBOVESPA & RTSI have clearly emerged out as favorite as their mean & median return are almost similar and not very far from the standard deviation line with comparatively decent returns.

Here nature and behavior of S&P Global 100 & S&P Global 1200 indices are not so Global. They still resembles majorly developed markets only, emerging markets seems to have very little space in their global indices composition. That's why I choose not to analysis these so called Global Indices further.

Figure 4

It shows correlation among World Stock Markets. It can easily be seen that all these indices' return are highly & positively correlated with each other. HANGSENG & Strait Times are two mostly correlated indices with rest of the world indices(specially SHANGHAI).

Figure 5a & 5b

These 2D figures shows behavior of US Market Indices and various economic factors/variables over last 10 years. Among all, Unemployment Rate and Government Revenue seems to be two most correlated economic factors with US Market Indices' return.

Figure 6a & 6b

These 2D figures shows behavior of Indian Market Indices and various economic factors/variables over last 10 years. These factors individually are not highly correlated with the Indian Market Indices' return. GDP Growth Rate is most positively correlated factor among all.

Figure 7

It shows the regression equation of US S&P Index Return with respect to various economic factors affecting it. It is interesting to note that out of considered economic factors, Index return is increasing with increase in Unemployment Rate and Average Inflation(CPI). This needs further research.

Figure 8

It shows the regression equation of Indian Nifty 50 Index Return with respect to various economic factors affecting it. It is interesting to note that constant value is negative and Index return decreases with increase in exports. This needs further research.

Not sure, how accurately & rationally I made my effort, but it surely gives a brief insight about World Stock Indices and their respective behavior over last 10 years.

Hoping it was worth to read... Always Love to have comments/corrections/opinions... Have A Great Day... Cheers !!

Original Article : http://www.qondio.com/world-stock-markets-from-my-eyes