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

How Tax & Inflation eat my Investments' Return

Hi All,

Hmm.. tough time, specially with these monsters called "Tax" and ever rising "Inflation" around. They resemble "Piranhas" in eating up return on my investments. Here, how they are...

Impact of Tax alone

Considering only "Income Tax" for now, suppose I invest $1000 in 1 year Bank Fixed Deposit fetching around 10% nominal interest rate compounded annually and my little income falls into 10% tax slab.

After 1 year, invested $1000 fetching 10% return becomes (1+0.10)*$1000 = $1100,
making my nominal gain $1100-$1000 = $100

But due to tax, Gain after Tax = $100(1-0.10)= $90
i.e my effective return(after tax) will be $90/$1000 = 9%

So, tax eats up my 1% return for the given scenario.


Impact of Inflation alone

Considering same investment of $1000 in 1 year Bank Fixed Deposit fetching around 10% nominal interest rate compounded annually and assuming annual inflation rate of 10%.

After 1 year, invested $1000 fetching 10% return becomes (1+0.10)*$1000 = $1100

But due to inflation, the purchasing power of $1100 after one year is same as purchasing power of $1100/(1+0.10) = $1000 right now. That means, with 10% inflation and 10% return on investment, my effective return is 0%, yes a big "ZERO" or In other words, my purchasing power remains the same.

So, inflation eats up my whole return for the given scenario.


Combined Impact of Tax and Inflation

Considering same example, investing $1000 in 1 year Bank Fixed Deposit fetching around 10% nominal interest rate compounded annually, my little income falls into 10% tax slab and assuming annual inflation rate of 10%.

After 1 year, invested $1000 fetching 10% return becomes (1+0.10)*$1000 = $1100,
making my nominal gain $1100-$1000 = $100

But due to tax, Gain after Tax = $100(1-0.10)= $90, and after-tax inflow will be $1090.

Now, its reduced purchasing power due to inflation is given by $1090/(1+0.10) = $990.91, i.e net return after tax and inflation will ($990.91-$1000)/$1000 = -0.91%

It means, in actual I am losing my money(by 0.91% a year), if I invest in current scenario.

So, Does it mean that I stop investing ???

No, because if I don't invest, then considering 10% inflation rate, my uninvested amount $1000 becomes $1000/(1+0.10) = $909.09 after 1 year and that means, I'll be losing my money/purchasing power by 9% which is 10 times higher than 0.9%.

I should keep on investing and try to invest in an alternative that can fetch higher return to nullify the combined effect of tax and inflation.

It should fetch return higher than {[(1-tax rate)*nominal interest rate]- inflation rate}/(1+inflation rate), in order to have effective gain over an investment.


Be a smart investor rather than being just an investor... Hope it was worth to read...  !!

Original Article : http://www.qondio.com/how-tax-inflation-eat-my-investments-return

Tricky Average: Mean, Median & Mode

Hi,
Hoping you all are in great health and enjoying prosperous time.

Its been long since I last post my Intel. Hey !! Here I come, this time throwing some light on a tricky statistical/mathematical tool called "Average". Going by definition "An Average is a measure of a central tendency or in other words, it's a value that defines the whole set of values to which it belongs."

Now many of us may believe that "Average" and "Mean" is exactly same... hmm... but that is only 1/3 of the truth. Yes, you heard me right, any one of the terrific trio in stats "Mean", "Median" or "Mode" can be regarded as an "Average" individually.
 
Mean, we know, arithmetic mean i.e sum of values divided by total no. of values. Median is the central value that divides a set of values into 2 halves i.e upper 50% and lower 50% while Mode is the value that appears most in a set of values.
Example : for values 1,2,2,3,4,5,6
Mean = 3.28, Median = 3, Mode = 2
Now, there is a trick/catch, which is being used to fool/dupe customers, don't believe me.... See by yourself...
Let suppose, I have to buy a laptop and I visit some electronic store. Salesman gives me following 3 options with respective ratings* :-
Brand 1 - Average Rating 4 out of 5
Brand 2 - Average Rating 3 out of 5
Brand 3 - Average Rating 5 out of 5
*ratings based on last 10 respective customers reviews

Now, on first look I may go for Brand 3, as it has the best rating among all three, But Hey !! I am not a guy who can be easily fooled or duped. The term "Average" doesn't give me ample information which I would like to have. Rather, I would be interested in knowing whether "Average" here refers to Mean or Median or Mode.

Now, have a look at detailed rating data given by last 10 respective customers below :-
Brand 1 - 4,3,3,4,4,4,4,5,4,5
Brand 2 - 1,2,2,2,3,3,4,4,4,4
Brand 3 - 1,2,2,1,3,4,5,5,5,3

It is clear from above data that,
Brand 1 used "Mean" as an Average
Brand 2 used "Median" as an Average
Brand 3 used "Mode" as an Average

In Brand 2 & Brand 3 there is large variations among their respective customer ratings while Brand 1 seems to have comparatively stable, consistent and high ratings. So, my final decision will definitely be to buy Brand 1.
 
Hope it was interesting and worth enough to read.... always love to have your precious comments/opinions.... Cheers !!

http://www.qondio.com/tricky-average-mean-median-mode

MBA Tutorials: Entity, Attribute and Relationships

Entity


An entity is a business object/real world thing like person, place, event etc. that represents a group, class or category of data.

Entities are represented by database tables in a relational database, with each row of the table representing an instance of that entity.

Like student, customer, school etc.



Attribute


A sub group of information within an entity.

Attribute is a unit fact that describes characteristics or properties of an entity.

Like roll no., age, name, height, weight are attributes that describe student.

Attributes are represented by table columns in database.



Relationships


Relationships are associations among entities.

Relationship lines indicate that each instance of an entity have a relationship with instances of the connected entity, and vice versa.

There are 4 types of relationships:-

One to One Relationship

A one to one relationship is an association between two entities where an instance of one entity can be associated with at most one instance of the other entity and vice versa. Like husband and wife etc.

The relationship is also mandatory in both directions.

One to Many Relationship

A one to many relationship is an association between two entities where an instance of one entity can be associated with more than one instances of the second entity while any instance of the second entity may be associated with at most one instance of the first. Like School and Student etc.

One to many relationships are quite common, in relational database, maximum relationships are in one to many format.

Many to Many Relationship

A many to many relationship is an association between two entities where any instance of the first entity may be associated with more than one instances of the second entity and vice versa. Like Teacher and Student etc.


Recursive Relationship

Instance of an entity may have relationships with other instances of the same entity. Like in an entity Employee, an instance (a particular employee) may be a father/mother/husband/wife/relative of another instance.

Depending upon the mutual relationships between the instances of the same entity, it can be classified into three categories :-

One to One
One to Many
Many to Many

MBA Tutorials: Foreign Direct Investment and Foreign Institutional Investor

Foreign Direct Investment(FDIs)

* FDI occurs when an investor from one country obtains or acquires or invests in an asset in another country.
* Long term Investments
* Focus Long term returns
* In India, growth rate of FDI are more than the GDP growth rate
* In the long run, FDIs are able to provide stable capital inflows
* In the long run, FDIs are proved to be more fruitful and balanced from investment & savings point of view.


Foreign Institutional Investor(FIIs)

* An institution or entity, which invests money in the financial stock markets or securities of another country.
* Invest in Equities & Debts
* Short term Investments
* Highly volatile
* Focus Short term returns
* FIIs have been the key drivers of the stock markets.
* When FIIs withdraw the capital from the domestic market or we can say when they sell their share it creates the demand for US dollar and that time demand for dollar will be more and INR will depreciate.

MBA Tutorials: Economic Growth and Economic Development

Economic Growth

-It is a Narrower Concept.
-It refers to quantitative changes in the economy.
-This term generally signifies developed countries
-It is an increase in a country's quantitative national output.
-It can be measured by an increase in a country's GDP(Gross National Product).


Economic Development

-It is a Broader Concept.
-It refers to both qualitative and quantitative changes in the economy.
-This term generally signifies developing or under-developed countries.
-It is an increase in living standards, improvements in self needs/demands, having more choices/options etc.
-It's measurement depends upon various factors like Life Expectancy Index, Education Index, Adult Literacy Index, Human Development Index etc.


Economic Growth is the necessary but not sufficient for Economic Development.

Speed up your Internet Download

In Windows Xp, by default, 20% bandwidth is reserved by Microsoft for their own purposes (updates, interrogating your machine, etc.)

To eliminate this reserved bandwidth space, perform the following steps :-

1. Type gpedit.msc in Run, this will group policy editor.

2. Navigate to "Local Computer Policy" --> "Computer Configuration" --> "Administrative Templates" --> "Network" --> "QOS Packet Scheduler"

3. Double click "Limit Reservable Bandwidth"

4. Check "Enable" and set "Bandwidth limit[%]" to zero and click OK

Now you 20% more bandwidth for your internet work.....Enjoy Surfing........
_

Free Digital issues of Popular Magazines

eZineMart.com is an online magazine store that offers access to leading magazines in digital format. eZineMart also sells subscription of the print editions and the paid digital magazines as well. The decision to make the digital edition free or paid made by the publisher.

This sites offers free digital issues of magazines like Outlook Business, Business Today, Cricket Today, Steel Insights, Brand Lincencing, Star Dust, The Indian Textile Journal etc.
_

Periodic Table of Elements

Nice colorful display of periodic table of elements.

This chart differentiate elements on the basis of different classifications such as weight, electrons, wide, orbital, isotopes, metals, non-metals, properties etc.

A must visit site for chemistry student........

All Information about Civil Services in India

This site is a complete information portal that provides, list of tools and resources for the prepration for the Civil Service exams conducted by the UPSC India. It says that its advisors list consists of IAS, IFS, IPS and various other officers within India's top civil services.

Information at a Glance :-

• About Civil Services
• IAS Preparation Tips and Subject Resources
  
• UPSC Exam Syllabus, Stratergy Tips, Question Papers
• Information on IAS books, coaching centers, study groups
• Current Affairs
• Miscellaneous for UPSC
• Free UPSC Study Material
• etc.

A must visit site for anyone wanting/preparing for civil services in India.....All The Best
_

Good News Folks : NCERT Textbooks online for free download

Here it goes....Good news for all the student......

Although, NCERT books are not expensive enough, but still, now there is no need to buy them from the market.

In order to impart quality & updated education and to reduce the pressure on the students, NCERT has made its textbooks, for Class 1st to Class 12th, available on its website for free download.

For the list of NCERT Textbooks Classes I to XII Click Here

And to download them free Click Here

Select Class, Subject, Book Title and that's it, you will get the pdf link of your book, in which links for each chapter is given, click them to read or download.

So Happy Learning ....... get good marks and have a great future ahead, ALL THE BEST
_

Good Informative sites about CPUs, Microprocessors, Motherboards, Chips etc.

Here are the list of sites that discuss different CPUs, Processors / Microprocessors, Motherboards, Chips in detail. Their pin diagram, datasheet, architecture, internal circuits, functionality, technical details, characteristics etc.

cpu-world.com : rare microprocessors, co-processors and microcontrollers; microprocessor architectures, socket and core names pages; CPU/MCU families, pinouts pages, support chips, benchmarks, odd looking chips; Online tool - Identify chip by part number; BSP · CPU · FPU · MCU · SoC · Other ICs and lots of other information.



gecko54000.free.fr : virtual CPU museum, the Gecko's CPU Library; Collection is grouped by manufacturer and then by family; large CPU and FPU collection; documentation, ressources, packages, sockets sections; till now 3113 images in 784 albums.



antiquetech.com : vintage chips that is difficult to find; some of the most discussed old chips : Intel 4004, MOS 6502, Motorola 6800, RCA 1802, Rockwell PPS-4, NEC uPD751, National Semiconductor IMP-00A.




Microprocessor Report has been the leading technical publication for the microprocessor industry since it was first introduced by Michael Slater in 1987. Microprocessor Report is exclusively subscriber-supported and dedicated to providing unbiased, in-depth, and critical analysis of new high-performance microprocessor developments. The newsletter covers the microprocessor implications of emerging platforms, emerging personal computer technologies, new memory and system logic chips, mobile computing devices, embedded processors, DSP technology, and intellectual property issues.




cpushack.com : Microprocessor Museum of CPU History for Intel CPUs, AMD Processor History, Cyrix Microprocessors, Microcontrollers and more. Till date : Total CPU's: 2228, Total manufacturers represented: 104, Total EPROM's:560

GetNetWise : Tips n Tools to be safe, constructive, and entertaining online

GetNetWise is a public service brought to you by Internet industry corporations and public interest organizations to help ensure that Internet users have safe, constructive, and educational or entertaining online experiences.

It provides Internet users with valuable information and tools on the following topics.

• Keeping Children Safe Online
• Stopping Unwanted E-mail and Spam
• Protecting Your Computer
• Keeping Your Personal Info Private
• How-To Video Tutorials

_

Instructables.com : Guides, Instructions, Slideshows and much more...

Instructables.com is an amalgam of various guides, instructions, slideshows, videos, suggestions etc.

It is basically an informative portal of how's the things can be done, shared by passionate people & artists who share their skills and knowledge about various fields like art, craft, food, games, life, music, science, sports, tech, etc.

A must visit site for everyone........

_

Nice Sites for Kids to learn Banking, Finance and Money Making Concepts

KidsBank.com is a tutorial website brought to you by Sovereign Bank that explains the fundamentals, importance and procedures of money savings and banking to children. There are five chapters Penny, Dollar Bill, Interest Ray, Checks, and lastly Mr. EFT. There is also a Game Room which offers 10 multiple choice quizzes based on information learned in the chapters. And much more..........






RBI has displayed following comics on its website ( Go to Financial Education ->For School Kids ) in order to educate students about its Financial Functioning in the easiest possible way.

• Basic Banking
• Money Kumar & The Monetary Policy
• Old Man Monetary
• Raju and The Skyladder

YoungInvestor.com explain the investment strategies from the perspective of Kids, Teens, Parents and Teachers. Lots of handy tools, calculators for making calculations easy and simple. Explains concepts like Different Investments for Different Goals etc. This a very big source of useful and well arranged financial information.







The Department of The Treasury, United States offers this great website for kids. Through this website kids can visit and learn about following organizations or entites :-

• White House
• U.S. Mint
• Bureau of Engraving and Printing
• USA.gov
• Savings Bonds
• The Alcohol and Tobacco Tax and Trade Bureau
  

These websites are meant specially for kids but adults should also have a look at these informative reservoirs. There are so many things and so much of vital information in these websites that even a professional doesn't know about it.....a must visit links for everyone...

_

AMFI-Mutual Fund Certification Mock Tests

The Association of Mutual Funds in India (AMFI) is dedicated to developing the Indian Mutual Fund Industry on professional, healthy and ethical lines and to enhance and maintain standards in all areas with a view to protecting and promoting the interests of mutual funds and their unit holders.


Following are the Mock Test sites which offers similar simulated test environment as by AMFI-Mutual Fund Certification Test :-

• HSBC Mutual Fund Simulated AMFI Mock Test


• Kotak Mutual Fund Simulated AMFI Mock Test


• Karvy mfs Simulated AMFI Mock Test

*These are only the practice Mock Tests, by passing this Mock Test, the participants are not certified.

_

Maran.com : Awesome Collection of Free Online ebooks

My bookworm friend had sent me this link which I want to share with you all. Believe me it's truly worth to visit this site.

Great collection of some good titles on Fitness, Yoga, Piano, Guitar, Cooking, Wine, Microsoft Office etc. Checkout it's full catalog Here

Guess what, each title is free for online viewing on scribd.com Click here to read free

You can also order them from Amazon.com, visit this site for details.


_

Project Gutenberg: Huge Free Online Book Catalog

There are over 25,000 free books in the Project Gutenberg Online Book Catalog. in over 30 international languages.

The Project Gutenberg collection was produced by tens of thousands of volunteers.

It includes books written by authors like Charles Dickens, Alexis Thomson, Alexander Miles, Mark Twain, William Shakespeare, Arthur J. Thomson etc.

Titles available are like A Christmas Carol, Manual of Surgery Volume First: General Surgery, The Adventures of Huckleberry Finn, The Adventures of Tom Sawyer, Life on the Mississippi, As You Like It, Hamlet, The Outline of Science, Myths and Legends of Ancient Greece and Rome etc.

Site is hosted by ibiblio.org.

In Short, it's a treat for the people who likes reading but don't have great titles with them. Must visit site for everyone........

_

Reserve Bank of India : Features of Genuine Indian Rupee Notes

The Reserve Bank of India has launched an elucidation drive to aware the public about differentiating between fake and genuine currency notes.

Details of Following Denominations are given :-

10 rupee note
20 rupee note
50 rupee note
100 rupee note
500 rupee note
1000 rupee note

Visit here to know various features of Genuine Indian Currency Notes

There are also few films and games, helping to understand the difference between fake and genuine currency notes.


RBI has also displayed following comics on its website ( Go to Financial Education ->For School Kids ) in order to educate students about its Financial Functioning in the easiest possible way.

Basic Banking
Money Kumar & The Monetary Policy
Old Man Monetary
Raju and The Skyladder

_