Intra-portfolio correlation (IPC)  
 
 
Neither this site, the available materials, nor its contents (including any information concerning 
Intra-portfolio correlation allows us to understand how good our portfolio is diversified any securities mentioned on this site) constitutes  an advertisement of any securities or   
an offer to sell or a solicitation of an offer to purchase any securities.         
The intra-portfolio correlation coefficient (IPC) can be from -1 to +1. The closer it is to -1 the more diversified your portfolio is. The closer IPC is to +1 the less diversified your portfolio is.
So let's create a portfolio of 5 stocks and find its IPC
STEP 1 CREATING A PORTFOLIO  
Traditionally, let's take Apple, Citi, General Electric, Exxon Mobil and Alcoa and upload their weekly market prices for 2010
You should take price intervals (weekly, monthly, quarterly, annually) similar to for what period of time you are planning to create your portfolio
And let's just randomly insert number of shares in our portfolio
Company Ticker Last price (end of 2010), $  Number of shares Position $ Share in portfolio Prices ($) Change (%)
Apple AAPL 322.56 2 645 21.5% AAPL C GE XOM AA S&P AAPL C GE XOM AA S&P
Citi C 4.73 150 710 23.6% 01.01.2010 210.73 3.31 15.13 68.19 16.12 1122.9
General Electric GE 18.29 20 366 12.2% 08.01.2010 211.98 3.59 16.6 69.52 17.02 1148 0.6% 8.5% 9.7% 2.0% 5.6% 2.2%
Exxon Mobil XOM 73.12 7 512 17.1% 15.01.2010 205.93 3.42 16.44 69.11 15.63 1137.6 -2.9% -4.7% -1.0% -0.6% -8.2% -0.9%
Alcoa AA 15.39 50 770 25.6% 22.01.2010 197.75 3.25 16.11 66.1 13.4 1098.7 -4.0% -5.0% -2.0% -4.4% -14.3% -3.4%
Portfolio       3 002 100.0% 29.01.2010 192.06 3.32 16.08 64.43 12.73 1080.2 -2.9% 2.2% -0.2% -2.5% -5.0% -1.7%
05.02.2010 195.46 3.22 15.79 64.8 13.18 1064.5 1.8% -3.0% -1.8% 0.6% 3.5% -1.5%
STEP 2 CALCULATING IPC   12.02.2010 200.38 3.18 15.55 64.8 13.28 1081.2 2.5% -1.2% -1.5% 0.0% 0.8% 1.6%
19.02.2010 201.67 3.42 16.17 65.87 13.53 1112.3 0.6% 7.5% 4.0% 1.7% 1.9% 2.9%
The formula for intra-portfolio correlation coefficient (IPC) is: 26.02.2010 204.62 3.4 16.06 65 13.3 1109.6 1.5% -0.6% -0.7% -1.3% -1.7% -0.2%
05.03.2010 218.95 3.5 16.35 66.47 13.84 1139.2 7.0% 2.9% 1.8% 2.3% 4.1% 2.7%
12.03.2010 226.6 3.97 17.04 66.8 13.6 1147.3 3.5% 13.4% 4.2% 0.5% -1.7% 0.7%
19.03.2010 222.25 3.9 18.07 67.04 14.26 1153.9 -1.9% -1.8% 6.0% 0.4% 4.9% 0.6%
26.03.2010 230.9 4.31 18.34 66.54 14.27 1170.4 3.9% 10.5% 1.5% -0.7% 0.1% 1.4%
02.04.2010 235.97 4.18 18.33 67.61 14.7 1179.9 2.2% -3.0% -0.1% 1.6% 3.0% 0.8%
09.04.2010 241.79 4.55 18.52 68.76 14.39 1195.7 2.5% 8.9% 1.0% 1.7% -2.1% 1.3%
where Xi and Xj are the weights of the assets in the portfolio 16.04.2010 247.4 4.56 18.97 67.93 13.91 1189.3 2.3% 0.2% 2.4% -1.2% -3.3% -0.5%
p - is the correlation coefficient between the assets in the portfolio 23.04.2010 270.83 4.86 19.07 69.24 14.11 1216.8 9.5% 6.6% 0.5% 1.9% 1.4% 2.3%
30.04.2010 261.09 4.37 18.86 67.77 13.43 1191.9 -3.6% -10.1% -1.1% -2.1% -4.8% -2.0%
We will draw 4 matrices to calculate IPC of our portfolio 07.05.2010 235.86 4 16.88 63.7 12 1142.4 -9.7% -8.5% -10.5% -6.0% -10.6% -4.2%
14.05.2010 253.82 3.98 17.64 63.6 12.36 1136.5 7.6% -0.5% 4.5% -0.2% 3.0% -0.5%
First, we need the correlation matrix 21.05.2010 242.32 3.75 16.42 60.88 11.35 1085 -4.5% -5.8% -6.9% -4.3% -8.2% -4.5%
Use CORREL function in excel to calculate correlation between the assets 28.05.2010 256.88 3.96 16.35 60.46 11.64 1085.7 6.0% 5.6% -0.4% -0.7% 2.6% 0.1%
04.06.2010 255.97 3.79 15.71 59.525 10.84 1066.7 -0.4% -4.3% -3.9% -1.5% -6.9% -1.8%
Correlation matrix 11.06.2010 253.51 3.88 15.56 61.86 11.36 1096 -1.0% 2.4% -1.0% 3.9% 4.8% 2.7%
  AAPL C GE XOM AA 18.06.2010 274.07 4.01 15.95 63.1 11.11 1124.5 8.1% 3.4% 2.5% 2.0% -2.2% 2.6%
AAPL 1.000 0.477 0.566 0.587 0.631 25.06.2010 266.7 3.94 14.91 59.1 11.23 1078.2 -2.7% -1.7% -6.5% -6.3% 1.1% -4.1%
C 0.477 1.000 0.665 0.566 0.488 02.07.2010 246.94 3.79 13.88 56.57 10 1030.2 -7.4% -3.8% -6.9% -4.3% -11.0% -4.5%
GE 0.566 0.665 1.000 0.700 0.697 09.07.2010 259.62 4.04 14.95 58.78 10.94 1076.7 5.1% 6.6% 7.7% 3.9% 9.4% 4.5%
XOM 0.587 0.566 0.700 1.000 0.741 16.07.2010 249.9 3.9 14.55 57.96 10.41 1067.5 -3.7% -3.5% -2.7% -1.4% -4.8% -0.9%
AA 0.631 0.488 0.697 0.741 1.000 23.07.2010 259.94 4.02 15.71 59.72 11.05 1103.6 4.0% 3.1% 8.0% 3.0% 6.1% 3.4%
30.07.2010 257.25 4.1 16.12 59.68 11.17 1108.7 -1.0% 2.0% 2.6% -0.1% 1.1% 0.5%
Now we need a simple matrix with the weights of assets in our portfolio 06.08.2010 260.09 4.06 16.45 61.97 11.59 1123.6 1.1% -1.0% 2.0% 3.8% 3.8% 1.3%
13.08.2010 249.1 3.88 15.38 59.91 10.64 1077.5 -4.2% -4.4% -6.5% -3.3% -8.2% -4.1%
Weights matrix 20.08.2010 249.64 3.75 15.03 58.89 10.57 1074.2 0.2% -3.4% -2.3% -1.7% -0.7% -0.3%
AAPL C GE XOM AA 27.08.2010 241.62 3.76 14.71 59.8 10.32 1061.3 -3.2% 0.3% -2.1% 1.5% -2.4% -1.2%
21.5% 23.6% 12.2% 17.1% 25.6% 03.09.2010 258.77 3.91 15.393 61.32 10.88 1101.7 7.1% 4.0% 4.6% 2.5% 5.4% 3.8%
21.5% 23.6% 12.2% 17.1% 25.6% 10.09.2010 263.41 3.91 15.98 61.2 11.17 1113.4 1.8% 0.0% 3.8% -0.2% 2.7% 1.1%
21.5% 23.6% 12.2% 17.1% 25.6% 17.09.2010 275.37 3.95 16.29 60.78 11.172 1127 4.5% 1.0% 1.9% -0.7% 0.0% 1.2%
21.5% 23.6% 12.2% 17.1% 25.6% 24.09.2010 292.32 3.904 16.66 61.75 12.2 1148.6 6.2% -1.2% 2.3% 1.6% 9.2% 1.9%
21.5% 23.6% 12.2% 17.1% 25.6% 01.10.2010 282.52 4.09 16.36 62.54 12.23 1144.6 -3.4% 4.8% -1.8% 1.3% 0.2% -0.4%
08.10.2010 294.07 4.19 17.12 64.38 12.89 1165.9 4.1% 2.4% 4.6% 2.9% 5.4% 1.9%
After that we need to multiply the weights of all the assets in our portfolio (like in the formula for IPC) 15.10.2010 314.74 3.95 16.3 65.19 13.13 1176.8 7.0% -5.7% -4.8% 1.3% 1.9% 0.9%
22.10.2010 307.47 4.11 16.055 66.34 12.72 1186.6 -2.3% 4.1% -1.5% 1.8% -3.1% 0.8%
Weights multiplication matrix 29.10.2010 300.98 4.17 16.02 66.49 13.14 1187 -2.1% 1.5% -0.2% 0.2% 3.3% 0.0%
  AAPL C GE XOM AA 05.11.2010 317.13 4.49 16.73 70 14 1222.6 5.4% 7.7% 4.4% 5.3% 6.5% 3.0%
AAPL 0.046 0.051 0.026 0.037 0.055 12.11.2010 308.03 4.29 16.25 70.99 13.49 1202.4 -2.9% -4.5% -2.9% 1.4% -3.6% -1.7%
C 0.051 0.056 0.029 0.040 0.061 19.11.2010 306.73 4.268 16.22 70.54 13.38 1195.3 -0.4% -0.5% -0.2% -0.6% -0.8% -0.6%
GE 0.026 0.029 0.015 0.021 0.031 26.11.2010 315.76 4.11 15.8 69.23 13.17 1183.8 2.9% -3.7% -2.6% -1.9% -1.6% -1.0%
XOM 0.037 0.040 0.021 0.029 0.044 03.12.2010 317.44 4.45 16.78 71.19 14.23 1223.5 0.5% 8.3% 6.2% 2.8% 8.0% 3.4%
AA 0.055 0.061 0.031 0.044 0.066 10.12.2010 320.56 4.77 17.72 72.18 14.25 1243.4 1.0% 7.2% 5.6% 1.4% 0.1% 1.6%
17.12.2010 320.61 4.7 17.7 72.17 14.56 1246.5 0.0% -1.5% -0.1% 0.0% 2.2% 0.2%
And the last matrix we need is the multiplication of the weights multiplication matrix and correlation matrix 24.12.2010 323.6 4.68 18.04 73.2 15.34 1253.6 0.9% -0.4% 1.9% 1.4% 5.4% 0.6%
31.12.2010 322.56 4.73 18.29 73.12 15.39 1262.9 -0.3% 1.1% 1.4% -0.1% 0.3% 0.7%
Final matrix
  AAPL C GE XOM AA
AAPL 0.0462 0.0242 0.0148 0.0215 0.0348
C 0.0242 0.0559 0.0192 0.0228 0.0296
GE 0.0148 0.0192 0.0149 0.0145 0.0218
XOM 0.0215 0.0228 0.0145 0.0291 0.0324
AA 0.0348 0.0296 0.0218 0.0324 0.0657
By the way I multiply matrices step by step but if you want you can use MMULT function in excel
Now to find IPC we just need to sum up all the values in the final matrix
IPC= 0.6828
Now we need to know which percentage of diversifiable risk we removed. 
It is simple 
Risk removed = -0.5 x IPC + 0.5 =   15.9%
Using the diversification of our portfolio we removed  15.9% of non-systematic risk
That's it. Change the number of shares in your portfolio to experiment with this coefficient
Remember: the lower the correlation between the assets in your portfolio - the better diversified it is.