My assignment requires me to find return on capital, industry wise for the loan portfolio classified into various industry/sector, the data fields are : ... Name of account|industry|Outstanding Amount|Return on capital|....
for example: XYZ Ltd.|Automobile|30 million|34% PQR Ltd.|Infrastructure|100 milllion|18% DCR Ltd| Retail| 8 million|-128%
I have data of 3500 accounts classified into 28 industries, now the problem I am facing is I am confused as to what kind of mean or average function to use that will give me the most relevant result for the particular sector. Say in Retail sector there are 70 accounts and majority of them have returns ranging from 20% to 48% but for the few defaulted accounts in this sector returns range from -50% to -500% so how do I tell what is the average return that a particuar account from this sector will give. I am not totally convinced on using Simple average for particular sector because few outliers make the average value too different from observed or logical results, I am not using geometric mean because the returns for each account are independent of each other for one year period and are not multiplicative in any way, and, finally for the defaulted accounts returns are negative so I am not able to use harmonic mean.
Please help and guide me as to How and what average value should I take that would best represent return on capital from an account for a particular sector. Thanks in advance
If you want to avoid the effect of outliers, you can try with the median, that is, the value that leaves 50% of your observations below it and 50% of observations above it