SNo Shg Fed's Sneha MACSParameters Maheswaram DB MACSYacharamGangavaram MS Gandlapenta MS 1 Formed On Oct-01 Feb-98 2 Structure 2-Tier 2-Tier 3-Tier 3-Tier 3 No of Members 4 Membership inc/dec 5 Villages covered 6 Outreach 7 Financial Intermediation Yes Yes No Yes 8 Agency of FI MS MS VO's MS 9 Sources of funds DRDA, BC, SC corporations ASP DPIP Velugu UNDP, Velugu 10 Grants/Funds received 11 Bank Linkage No No No No 12 Basis of lending Group Group Group/Individual Group 13 Avg Loan size 14 Loans accessed 15 Interest rate 15% 24% 6% 3% 16 Days taken to finalize loans 30 45 30 45 17 Regularity of repayments Interest Only Interest Only Interest Only Principal + Interest 18 Defaulters Yes Yes Yes yes 19 Default ctrl Mechanisms No No No No 20 Profit/loss 21 Decision Makers BOD 22 Power centers 23 Influencing parties DRDA Staff Staff CC's /MBK's CC's 24 Strenght Of BOD/EC 15 13 52(15) 15 25 Elections Yes Yes No No 26 Term of BOD 3 3 3 3 27 Changes in Board No No No Yes 28 By law compliance Poor Good Good 29 Awareness of BOD Poor Good Poor Good 30 Staff Strengths 31 Recruiting Agency 32 Pay Scales 33 Performance Appraisals 34 Trainings 35 Membership 36 Records 37 Conflict Resolutions 38 FSS 39 OSS 40 PAR 90-180 41 Age of External Support 14 3 3 12 42 Type of Agency Govt Agency NGO Govt Project Govt Project 43 44
KunikinapaduMaa MACS Dec-00 2-Tier
Yes MS Sanga Mitra No Group
24% 7 Principal + interest Yes No
SangaMitra 9 No 3 No Good Good
15 NGO
Table 2. Details of CIF disbursement Total amount of CIF received VO 1 VO 2 VO 3 Contribution from members Net Amount received Village Organisation 1
480800 109800 45000 326000 36200 444600 Village Organisation 2
Purpose Number applied Number of members Amount forgiven Total each amount member Cattle 5 5 10000 50000 Clothes shop 5 2 10000 20000 Kirana Stores 2 2 5000 10000 Wood business 2 2 5500 11000 Borewell 5 3 5000 15000 Sewing Machines 3 1 3800 3800 22 15 109800 percentage catered to 68.2%
Village Organisation 3
Purpose Numbers applied Number of members Amount for Total each amount member Purpose Numbers applied Number of members Amount for each m Flower Garden 1 1 5000 5000 Cattle 9 8 10000 Guava garden 3 1 10000 10000 Borewell 15 15 2000 Kirana stores 4 2 5000 10000 Vegetable shop 28 28 5000 Cloth shop 2 1 10000 10000 Mirchi powder 2 2 8000 Sewing Machine 24 1 10000 10000 Sewing Machine 5 2 5000 34 6 45000 Foot Mart 2 1 10000 17.6% STD booth 3 2 7500 64 58 90.6% 11.11%
3.46% 13.66% 10.02%
45.54%
9.11% 18.21%
22.22%
Cattle
Borewell
Clothes shop Kirana Stores Wood business
Sewing Machines
22.22%
22.22% 22.22%
Flower Garden Guava garden Kirana stores Cloth shop Sewing Machine
26.58%
9.97%
4.98% 3.32% 3.32% 5.32%
46.51%
Cattle Borewell Vegetable shop Mirchi powder Sewing Machine
Total amount 80000 30000 140000 16000 10000 10000 15000 301000
Cattle Borewell Vegetable shop Mirchi powder Sewing Machine
Foot Mart STD booth
Loan Analysis of Loans given to member VOs/SHGs Enter date on which you want to do loan analysis: ----->
Please insert rows if required, before the row # 12, else all formulas will change. Then copy the for S#
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
VO Name
Day
Bogaram 10 Nagasanpally 10 Chandravancha 10 Mukthipad 5 Nacharam 5 Gundmal 5 Amlikuntla 25 Chennaram 21 Mushrifa 8 Bijjaram 20 Mushrifa 19 P.c.tanda 25 Chandravancha 3 Sarjakhanpet 25 Chennaram 10 Bolovanpally 26 Yellamma (individual) 12 Mogulamma (individual)25 Amlikuntla 22 Amlikuntla 10 Bakthimalla 27
Month
4 4 4 3 3 3 7 6 8 1 6 6 7 8 2 6 10 11 1 2 1
Year
2001 2001 2001 2002 2002 2002 2002 2003 2003 2004 2000 2000 2000 2000 2001 2001 2001 2001 2002 2002 2003
Loan date Loan amount
Loan outstanding
225,000 295,000 334,972 150,000 204,972 500,000 50,000 80,000 160,000 200,000 100,000 60,000 10,500 50,000 60,000 60,000 5,000 5,000 20,000 80,000 130,000
109,485 263,000 167,472 150,000 143,472 308,250 80,000 160,000 200,000 6,707 41,000 2,000 20,000 80,000 124,000
10/Apr/01 10/Apr/01 10/Apr/01 5/Mar/02 5/Mar/02 5/Mar/02 25/Jul/02 21/Jun/03 8/Aug/03 20/Jan/04 19/Jun/00 25/Jun/00 3/Jul/00 25/Aug/00 10/Feb/01 26/Jun/01 12/Oct/01 25/Nov/01 22/Jan/02 10/Feb/02 27/Jan/03
2,780,444
Code:
## ^^ NA
1,855,386 A = Agriculture; H = Animal Hu Y=Yearly; H=Half yearly; Q=Q Not Available
Borrowings (External loans taken by Federation/VO)
Please insert rows if required, before the row # 6, else all formulas will change. Then change the S
S#
Name of the bank/fed.
Loan date Loan amount
Loan outstanding
30/Nov/99 30/Nov/99 30/Nov/99 30/Nov/99 30/Nov/99
###
Total
-
-
Portfolio Analysis 1 CRR OTRR PIA (>1day) PAR (>1days) PIA (1-30 days) PAR (1-30 days) PIA (31-60 days) PAR (31-60 days) PIA (61-90days) PAR (61-90 days) PIA (91-180 days) PAR (91-180days) PIA (181-365 days) PAR (181-365 days) PIA (>365 days) PAR (>365 days) CRR = Cumulative Repayment Rate OTRR = On-time Repayment Rate PIA = Portfolio in Arrears PAR = Portfolio at Risk PIA & PAR PIA >1day PAR >1days PIA > 30 days PAR > 30 days PIA > 60 days PAR >60 days PIA > 90 days
PAR >90 days PIA >180 days PAR >180 days PIA > 365 days PAR > 365 days
Checks: 1. Did you enter the date on which you w 2. Did you enter the maximum loan o/s w 3. Check Ratios of PIA & PAR (PIA% sh > 1day > 30 days > 60 days > 90 days > 180 days > 365 days
4. Loan Purpose List Please insert a new purpose between th Agriculture A Buffalo H Ceremonies S Education C Health C House construction R House repair R Kirana M Land purchase R Loan Repayment L Others O Poultry H Sheep H Small Business M Tea shop/Hotel M Wedding S
10-Jun-04
ange. Then copy the formulas (column R to column AU) in the inserted rows. Also change the S#. Loan Purpose
Loan Purpose Code##
H S R R M #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Buffalo Ceremonies House construction House repair Kirana
Peri odic No. ity Instalm of ents Inst. ^^
H H H H H H H H H H H H H H H H M M H H H
10 10 10 10 10 10 4 10 10 10 10 10 10 10 10 10 3 3 10 10 4
Instalment Size
22,500 29,500 33,497 15,000 20,497 50,000 12,500 8,000 16,000 20,000 10,000 6,000 1,050 5,000 6,000 6,000 1,666 1,666 2,000 8,000 32,500
Cum. Prin. Cum. Prin. Paid Due (schedule to pay)
135,000 177,000 200,982 60,000 81,988 200,000 37,500 8,000 16,000 80,000 42,000 7,350 35,000 36,000 30,000 4,998 4,998 8,000 32,000 65,000 1,261,816
115,515 32,000 167,500 61,500 191,750 50,000 100,000 60,000 3,793 50,000 60,000 19,000 5,000 3,000 6,000 925,058
Interest Paid
-
= Agriculture; H = Animal Husbandry; M = Microenterprises; C = Consumption; S = Social ceremonies; R = House Construction Yearly; H=Half yearly; Q=Quaterly; M=Monthly; F=Four monthly
nge. Then change the S#, also
Loan Purpose
Loan Purpose Code##
Peri odic No. ity Instalm of ents Inst. ^^
Instalment Size
Cum. Prin. Due (schedule to pay) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Cum. Prin. Paid
-
Interest Paid
-
Internal external 66.22% #DIV/0! na #DIV/0! Loan outstanding 1,855,386 - Portfolio Distribution 22.97% #DIV/0! >1 day Principal O/D 426,260 #DIV/0! Agriculture 89.22% #DIV/0! >1 day Principal1,655,386 Outstanding of O/D #DIV/0! Loans Animal & Husbandry 0.00% #DIV/0! 1-30 days Principal O/D#DIV/0! Micro Enterprises 0.00% #DIV/0! 1-30 days Principal Outstanding of#DIV/0! O/D Loans Consumption (health & education) 0.00% #DIV/0! 31-60 days Principal O/D #DIV/0! Social ceremonies 0.00% #DIV/0! 31-60 days Principal Outstanding #DIV/0! of O/D Loans House/Land construction/repair 2.85% #DIV/0! 61-90 days Principal 52,967 O/D #DIV/0! Loan Repayment 14.93% #DIV/0! 61-90 days Principal 276,957 Outstanding #DIV/0! of O/D Loans Others 2.84% #DIV/0! 91-180days Principal 52,738 O/D #DIV/0! Total Max. exposure 37.28% #DIV/0! 91-180 days Principal 691,722 Outstanding#DIV/0! of O/D Loans 3.77% #DIV/0! 181-365 days Principal 70,000 O/D #DIV/0! Total Production Loan 8.89% #DIV/0! 181-365 days Principal 165,000 Outstanding #DIV/0! of O/D Max Loans outstanding against one SHG/VO 13.50% #DIV/0! >365 days Principal 250,555 O/D #DIV/0! Loan distribution among SHG/VO 28.12% #DIV/0! >365 days Principal 521,707 Outstanding of #DIV/0! O/D Loans Avg. Loan size ve Repayment Rate Current Loans 200,000 Avg. Loan amount per SHG/VO Repayment Rate 3 Avg. number of loans per SHG/VO No. loan given last 12 months No.of loans outstanding 15 (end of period) Total loan disbursed during year 440,000
22.97% 89.22% 22.97% 89.22% 22.97% 89.22% 20.12%
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Loan outstanding >1 day Principal O/D 426,260 #DIV/0! >1 day Principal1,655,386 Outstanding of O/D #DIV/0! Loans > 30 days Principal 426,260 O/D #DIV/0! > 30 days Principal 1,655,386 Outstanding of#DIV/0! O/D Loans > 60 days Principal 426,260 O/D #DIV/0! > 60 days Principal 1,655,386 Outstanding of#DIV/0! O/D Loans > 90 days Principal 373,293 O/D #DIV/0!
Loan Loss Provision Days Principal o/s of o/d loans 1-30 31-60 61-90 91-180
74.29% 17.28% 37.01% 13.50% 28.12%
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
> 90 days Principal 1,378,429 Outstanding of#DIV/0! O/D Loans > 180days Principal 320,555 O/D #DIV/0! > 180 days Principal 686,707 Outstanding #DIV/0! of O/D Loans > 365 days Principal 250,555 O/D #DIV/0! > 365 days Principal 521,707 Outstanding #DIV/0! of O/D Loans
181-365 > 365 Total
the date on which you want the analysis --> cell # is G5 the maximum loan o/s with an VO/SHG (only in case of more than 1 loan to the same VO/SHG) ? of PIA & PAR (PIA% should be less than or equal to PAR%, and not more) Correct #DIV/0! Correct #DIV/0! Correct #DIV/0! Correct #DIV/0! Correct #DIV/0! Correct #DIV/0!
new purpose between the first and last row (in this case Agriculture & Wedding), data sort "Column H" in asc
nge the S#. Cum prin Paid Ontime
-
Prepayment
12,500 20,000 18,000 15,000 24,000 2 89,502
No.of months since loan given
37 37 37 26 26 26 22 11 9 4 46 46 46 44 39 34 31 29 28 27 16
No. of Inst. elpsed
6 6 6 4 4 4 3 1 1 0 8 7 7 7 6 5 31 29 4 4 2
s; R = House Construction/repairs; R = Land purchase; O = Other
6 6 6 4 4 4 3 1 1 0 8 7 7 7 6 5 3 3 4 4 2
No. of days overdue
71 795 71 647 104 104 0 174 126 0 0 0 714 0 0 356 0 866 689 670 319 5,706
Principal O/D
19,485 145,000 33,482 60,000 20,488 8,250 8,000 16,000 3,557 11,000 1,998 8,000 32,000 59,000 426,260
Cum prin Paid Ontime
-
Prepayment
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
923,000
health & education)
onstruction/repair
1,647,000 842,000 1,813,000
5,225,000
No.of months since loan given 53 53 53 53 53
No. of No. of Inst. days elpsed overdue 53 53 53 53 53
265
0 0 0 0 0 265
-
Principal O/D
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
% 17.7% 0.0% 31.5% 16.1% 0.0% 34.7% 0.0% 0.0% 100% 35%
ng against one SHG/VO tion among SHG/VO
mount per SHG/VO of loans per SHG/VO
49% 308,250 <--- Please enter this figure, in case of 16.6% more than one loan being given to the 248,810 VO/SHG 14,315 0.10
oan Loss Provision % 0.00% 0.00% 2.50% 10.00%
Amount 276,957 691,722
LLP Amount 6,924 69,172
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
20.00% 50.00% Total
165,000 521,707
VO/SHG) ?
sort "Column H" in ascending order
33,000 260,854 369,950 19.94%
Principal Outstandin g of O/D Loans
1-30 days Principal O/D
109,485 263,000 167,472 150,000 143,472 308,250 80,000 160,000 6,707 41,000 2,000 20,000 80,000 124,000 1,655,386
No. of loans
1-30 days 31-60 days 61-90 days Principal 31-60 Principal 61-90 Principal Outstandin days Outstanding days Outstanding g of O/D Principal of O/D Principal of O/D Loans O/D Loans O/D Loans
-
-
7 No. of loans
-
19,485 33,482 52,967
-
No. of loans
109,485 167,472 276,957 -
Principal 1-30 days 31-60 days 61-90 days Outstandin Principal 31-60 Principal 61-90 Principal g of O/D 1-30 days Outstandin days Outstanding days Outstanding Loans Principal g of O/D Principal of O/D Principal of O/D O/D Loans O/D Loans O/D Loans #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
181-365 91-180 days days 91-180 Principal 181-365 Principal days Outstanding days Outstanding Principal of O/D Principal of O/D O/D Loans O/D Loans
20,488 8,250 8,000 16,000 52,738 No. of loans
143,472 308,250 80,000 160,000 691,722
11,000 59,000 70,000
2 No. of loans
41,000 124,000 165,000
>365 days >365days Principal No. of Principal Outstanding days since O/D of O/D Loans loan given
145,000 60,000 3,557 1,998 8,000 32,000 250,555
4 No. of loans
263,000 150,000 6,707 2,000 20,000 80,000 521,707
1,157 1,157 1,157 828 828 828 686 355 307 142 1,452 1,446 1,438 1,385 1,216 1,080 972 928 870 851 500
2 No. of loans
181-365 91-180 days days 91-180 Principal 181-365 Principal >365 days days Outstanding days Outstanding >365days Principal No. of Principal of O/D Principal of O/D Principal Outstanding days since O/D Loans O/D Loans O/D of O/D Loans loan given #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 8,270
Sector wise loan disbursement
Loan disbursemen t last year
80,000 160,000 200,000 440,000 6
A
H
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
225,000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
M
204,972 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
C
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
S
295,000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Loan disbursemen t last year -
ursement
R
334,972 150,000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
L
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
O
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Table No: 5 Economic sustainability of VO(Mammadugu)
Repayment rate Total CIF received
100% 434400
90% 434400
80% 434400
70% 434400
6% 26064
6% 23457.6
6% 20851.2
6% 18244.8
Expenditures Salaries of Sanga Mitra per month 750 Per annum salary 9000 MBK salary per month per VO 520 Per annum salary of MBK 6240 CC salary per VO 690 Per annum salary of CC 8280 Salary expenses per annum 23520
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
Total expenditure
500 240 100 840 24360
500 240 100 840 24360
500 240 100 840 24360
500 240 100 840 24360
Net Loss/Profit
1704
-902.4
-3508.8
-6115.2
-153%
289%
74%
Rate of interest per annum Income from interest
Stationary expenses Conveyance allowance to EC Miscellaneous expenses
61% 434400 6% 15899.04
750 9000 520 6240 690 8280 23520 500 240 100 840 24360
-8460.96 38%
Table 6: Economic sustainability of VO 2 at different repayment rates
Repayment rate Total CIF received Rate of interest per annum Income from interest
100% 832925 6% 49975.5
90% 832925 6% 44977.95
80% 832925 6% 39980.4
70% 832925 6% 34982.85
63% 832925 6% 31484.57
Expenditures Salaries of Sanga Mitra per month 750 Per annum salary 9000 MBK salary per month per VO 520 Per annum salary of MBK 6240 CC salary per VO 690 Per annum salary of CC 8280 Salary expenses per annum 23520
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
500 240 100 840 24360
500 240 100 840 24360
500 240 100 840 24360
500 240 100 840 24360
500 240 100 840 24360
25615.5
20617.95
15620.4
10622.85
7124.57
11640
-20% 8040%
-24% 4440
-32% 840
-54% -1680
Stationary expenses Conveyance allowance to EC Miscellaneous expenses Total expenditure
Net Loss/Profit at an average CIF amount
60% 600000 6% 21600
50% 600000 6% 18000
750 9000 520 6240 690 8280 23520
750 9000 670 8040 920 11040 28080
500 240 100 840 24360
500 240 100 840 28920
-2760
-10920
-139% -2760
296% -10920
Economic sustainability of MS
Repayment rate 100% Total CIF received 15000000 Rate of interest per annum 6% Income from interest
900000
Expenditures Salaries of Sanga Mitra per month 19500 Per annum salary 234000 MBK salary per month 12300 Per annum salary of MBK 147600 CC salary per month 16500 Per annum salary of CC 198000 APM salary 4500 APM salary per annum 54000 Salary expenses per annum633600 Phone Bill 7200 Electricity bill 3600 Office rent 6000 Stationary expenses 13000 Conveyance allowance to EC 240 Miscellaneous expenses 2600 32640 Total expenditure 666240
Net Loss/Profit
233760 19480
90% 15000000 6%
80% 15000000 6%
70% 15000000 6%
63% 15000000 6%
60% 15000000 6%
810000
720000
630000
567000
540000
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
146260
56260
-33740
-96740
-123740
-37%
-62%
-160%
-272%
28%
Repayment rate Total CIF receivedRate of interest per annum Income from interest 63% 15000000 6% 567000 63% 15000000 9% 850500 63% 15000000 12% 1134000 63% 15000000 15% 1417500
Economic sustainability of MS
50% 15000000 6%
Repayment rate 100% Total CIF received 15000000 Rate of interest per annum 12%
90% 15000000 12%
80% 15000000 12%
70% 15000000 12%
450000
Income from interest 1800000
1620000
1440000
1260000
19500 234000 12300 147600 16500 198000 4500 54000 633600
Expenditures Salaries of Sanga Mitra19500 per month Per annum salary 234000 MBK salary per month 12300 Per annum salary of MBK 147600 CC salary per month 16500 Per annum salary of CC 198000 APM salary 4500 APM salary per annum 54000 Salary expenses per 633600 annum
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
7200 3600 6000 13000 240 100 30140 663740
Phone Bill 7200 Electricity bill 3600 Office rent 6000 Stationary expenses 13000 Conveyance allowance to240 EC Miscellaneous expenses2600 32640 Total expenditure 666240
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
-213740
Net Loss/Profit 1133760
956260
776260
596260
#DIV/0!
94480
-16%
-19%
-23%
Per annum salaryMBK salary per month Per annum salary CC of salary MBKper month Per annum salary APM of salary CC PER Mth APMper salary MS per annum 234000 12300 147600 16500 198000 4500 54000 234000 12300 147600 16500 198000 4500 54000 234000 12300 147600 16500 198000 4500 54000 234000 12300 147600 16500 198000 4500 54000
Economic sustainability of MS
63% 60% 15000000 15000000 12% 12% 1134000
1080000
Repayment rate 100% Total CIF received 15000000 Rate of interest per annum 15%
90% 15000000 15%
80% 15000000 15%
Income from interest 2250000
2025000
1800000
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
Expenditures Salaries of Sanga Mitra19500 per month Per annum salary 234000 MBK salary per month 12300 Per annum salary of MBK 147600 CC salary per month 16500 Per annum salary of CC 198000 APM salary 4500 APM salary per annum54000 Salary expenses per633600 annum
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
Phone Bill 7200 Electricity bill 3600 Office rent 6000 Stationary expenses 13000 Conveyance allowance to240 EC Miscellaneous expenses2600 32640 Total expenditure 666240
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
470260
416260
Net Loss/Profit 1583760
1361260
1136260
-39%
-11%
-14%
-17%
Salary expenses per annum Electricity bill Office rent Stationary expenses Conveyance allowance Miscellaneous to EC expenses 633600 3600 6000 13000 240 100 633600 3600 6000 13000 240 100 633600 3600 6000 13000 240 100 633600 3600 6000 13000 240 100
70% 15000000 15%
63% 15000000 15%
60% 15000000 15%
50% 15000000 15%
50% 815667 6%
1575000
1417500
1350000
1125000
24470.01
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
19500 234000 12300 147600 16500 198000 4500 54000 633600
750 9000 670 8040 920 11040
28080
19500 234000 12300 147600 16500 198000 4500 54000 633600
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
7200 3600 6000 13000 240 100 30140 663740
500 240 100 840 28920
7200 3600 6000 13000 240 100 30140 663740
911260
753760
686260
1096080
-639269.99
-20%
-34%
-9%
scellaneous expenses Total expenditure 30140 663740 30140 663740 30140 663740 30140 663740
Net Loss/Profit -96740 186760 283,500 470260 567,000 753760 850,500
Loan Analysis of Loans given to member VOs/SHGs Enter date on which you want to do loan analysis: ----->
Please insert rows if required, before the row # 12, else all formulas will change. Then copy the for S#
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
VO Name
Day
Bogaram 10 Nagasanpally 10 Chandravancha 10 Mukthipad 5 Nacharam 5 Gundmal 5 Amlikuntla 25 Chennaram 21 Mushrifa 8 Bijjaram 20 Mushrifa 19 P.c.tanda 25 Chandravancha 3 Sarjakhanpet 25 Chennaram 10 Bolovanpally 26 Yellamma (individual) 12 Mogulamma (individual)25 Amlikuntla 22 Amlikuntla 10 Bakthimalla 27
Month
4 4 4 3 3 3 7 6 8 1 6 6 7 8 2 6 10 11 1 2 1
Year
2001 2001 2001 2002 2002 2002 2002 2003 2003 2004 2000 2000 2000 2000 2001 2001 2001 2001 2002 2002 2003
Loan date Loan amount
Loan outstanding
225,000 295,000 334,972 150,000 204,972 500,000 50,000 80,000 160,000 200,000 100,000 60,000 10,500 50,000 60,000 60,000 5,000 5,000 20,000 80,000 130,000
109,485 263,000 167,472 150,000 143,472 308,250 80,000 160,000 200,000 6,707 41,000 2,000 20,000 80,000 124,000
10/Apr/01 10/Apr/01 10/Apr/01 5/Mar/02 5/Mar/02 5/Mar/02 25/Jul/02 21/Jun/03 8/Aug/03 20/Jan/04 19/Jun/00 25/Jun/00 3/Jul/00 25/Aug/00 10/Feb/01 26/Jun/01 12/Oct/01 25/Nov/01 22/Jan/02 10/Feb/02 27/Jan/03
2,780,444
Code:
## ^^ NA
1,855,386 A = Agriculture; H = Animal Hu Y=Yearly; H=Half yearly; Q=Q Not Available
Borrowings (External loans taken by Federation/VO)
Please insert rows if required, before the row # 6, else all formulas will change. Then change the S
S#
Name of the bank/fed.
Loan date Loan amount
Loan outstanding
30/Nov/99 30/Nov/99 30/Nov/99 30/Nov/99 30/Nov/99
###
Total
-
-
Portfolio Analysis 1 CRR OTRR PIA (>1day) PAR (>1days) PIA (1-30 days) PAR (1-30 days) PIA (31-60 days) PAR (31-60 days) PIA (61-90days) PAR (61-90 days) PIA (91-180 days) PAR (91-180days) PIA (181-365 days) PAR (181-365 days) PIA (>365 days) PAR (>365 days) CRR = Cumulative Repayment Rate OTRR = On-time Repayment Rate PIA = Portfolio in Arrears PAR = Portfolio at Risk PIA & PAR PIA >1day PAR >1days PIA > 30 days PAR > 30 days PIA > 60 days PAR >60 days PIA > 90 days
PAR >90 days PIA >180 days PAR >180 days PIA > 365 days PAR > 365 days
Checks: 1. Did you enter the date on which you w 2. Did you enter the maximum loan o/s w 3. Check Ratios of PIA & PAR (PIA% sh > 1day > 30 days > 60 days > 90 days > 180 days > 365 days
4. Loan Purpose List Please insert a new purpose between th Agriculture A Buffalo H Ceremonies S Education C Health C House construction R House repair R Kirana M Land purchase R Loan Repayment L Others O Poultry H Sheep H Small Business M Tea shop/Hotel M Wedding S
10-Jun-04
ange. Then copy the formulas (column R to column AU) in the inserted rows. Also change the S#. Loan Purpose
Loan Purpose Code##
H S R R M #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Buffalo Ceremonies House construction House repair Kirana
Peri odic No. ity Instalm of ents Inst. ^^
H H H H H H H H H H H H H H H H M M H H H
10 10 10 10 10 10 4 10 10 10 10 10 10 10 10 10 3 3 10 10 4
Instalment Size
22,500 29,500 33,497 15,000 20,497 50,000 12,500 8,000 16,000 20,000 10,000 6,000 1,050 5,000 6,000 6,000 1,666 1,666 2,000 8,000 32,500
Cum. Prin. Cum. Prin. Paid Due (schedule to pay)
135,000 177,000 200,982 60,000 81,988 200,000 37,500 8,000 16,000 80,000 42,000 7,350 35,000 36,000 30,000 4,998 4,998 8,000 32,000 65,000 1,261,816
115,515 32,000 167,500 61,500 191,750 50,000 100,000 60,000 3,793 50,000 60,000 19,000 5,000 3,000 6,000 925,058
Interest Paid
-
= Agriculture; H = Animal Husbandry; M = Microenterprises; C = Consumption; S = Social ceremonies; R = House Construction Yearly; H=Half yearly; Q=Quaterly; M=Monthly; F=Four monthly
nge. Then change the S#, also
Loan Purpose
Loan Purpose Code##
Peri odic No. ity Instalm of ents Inst. ^^
Instalment Size
Cum. Prin. Due (schedule to pay) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Cum. Prin. Paid
-
Interest Paid
-
Internal external 66.22% #DIV/0! na #DIV/0! Loan outstanding 1,855,386 - Portfolio Distribution 22.97% #DIV/0! >1 day Principal O/D 426,260 #DIV/0! Agriculture 89.22% #DIV/0! >1 day Principal1,655,386 Outstanding of O/D #DIV/0! Loans Animal & Husbandry 0.00% #DIV/0! 1-30 days Principal O/D#DIV/0! Micro Enterprises 0.00% #DIV/0! 1-30 days Principal Outstanding of#DIV/0! O/D Loans Consumption (health & education) 0.00% #DIV/0! 31-60 days Principal O/D #DIV/0! Social ceremonies 0.00% #DIV/0! 31-60 days Principal Outstanding #DIV/0! of O/D Loans House/Land construction/repair 2.85% #DIV/0! 61-90 days Principal 52,967 O/D #DIV/0! Loan Repayment 14.93% #DIV/0! 61-90 days Principal 276,957 Outstanding #DIV/0! of O/D Loans Others 2.84% #DIV/0! 91-180days Principal 52,738 O/D #DIV/0! Total Max. exposure 37.28% #DIV/0! 91-180 days Principal 691,722 Outstanding#DIV/0! of O/D Loans 3.77% #DIV/0! 181-365 days Principal 70,000 O/D #DIV/0! Total Production Loan 8.89% #DIV/0! 181-365 days Principal 165,000 Outstanding #DIV/0! of O/D Max Loans outstanding against one SHG/VO 13.50% #DIV/0! >365 days Principal 250,555 O/D #DIV/0! Loan distribution among SHG/VO 28.12% #DIV/0! >365 days Principal 521,707 Outstanding of #DIV/0! O/D Loans Avg. Loan size ve Repayment Rate Current Loans 200,000 Avg. Loan amount per SHG/VO Repayment Rate 3 Avg. number of loans per SHG/VO No. loan given last 12 months No.of loans outstanding 15 (end of period) Total loan disbursed during year 440,000
22.97% 89.22% 22.97% 89.22% 22.97% 89.22% 20.12%
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Loan outstanding >1 day Principal O/D 426,260 #DIV/0! >1 day Principal1,655,386 Outstanding of O/D #DIV/0! Loans > 30 days Principal 426,260 O/D #DIV/0! > 30 days Principal 1,655,386 Outstanding of#DIV/0! O/D Loans > 60 days Principal 426,260 O/D #DIV/0! > 60 days Principal 1,655,386 Outstanding of#DIV/0! O/D Loans > 90 days Principal 373,293 O/D #DIV/0!
Loan Loss Provision Days Principal o/s of o/d loans 1-30 31-60 61-90 91-180
74.29% 17.28% 37.01% 13.50% 28.12%
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
> 90 days Principal 1,378,429 Outstanding of#DIV/0! O/D Loans > 180days Principal 320,555 O/D #DIV/0! > 180 days Principal 686,707 Outstanding #DIV/0! of O/D Loans > 365 days Principal 250,555 O/D #DIV/0! > 365 days Principal 521,707 Outstanding #DIV/0! of O/D Loans
181-365 > 365 Total
the date on which you want the analysis --> cell # is G5 the maximum loan o/s with an VO/SHG (only in case of more than 1 loan to the same VO/SHG) ? of PIA & PAR (PIA% should be less than or equal to PAR%, and not more) Correct #DIV/0! Correct #DIV/0! Correct #DIV/0! Correct #DIV/0! Correct #DIV/0! Correct #DIV/0!
new purpose between the first and last row (in this case Agriculture & Wedding), data sort "Column H" in asc
nge the S#. Cum prin Paid Ontime
-
Prepayment
12,500 20,000 18,000 15,000 24,000 2 89,502
No.of months since loan given
37 37 37 26 26 26 22 11 9 4 46 46 46 44 39 34 31 29 28 27 16
No. of Inst. elpsed
6 6 6 4 4 4 3 1 1 0 8 7 7 7 6 5 31 29 4 4 2
s; R = House Construction/repairs; R = Land purchase; O = Other
6 6 6 4 4 4 3 1 1 0 8 7 7 7 6 5 3 3 4 4 2
No. of days overdue
71 795 71 647 104 104 0 174 126 0 0 0 714 0 0 356 0 866 689 670 319 5,706
Principal O/D
19,485 145,000 33,482 60,000 20,488 8,250 8,000 16,000 3,557 11,000 1,998 8,000 32,000 59,000 426,260
Cum prin Paid Ontime
-
Prepayment
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
923,000
health & education)
onstruction/repair
1,647,000 842,000 1,813,000
5,225,000
No.of months since loan given 53 53 53 53 53
No. of No. of Inst. days elpsed overdue 53 53 53 53 53
265
0 0 0 0 0 265
-
Principal O/D
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
% 17.7% 0.0% 31.5% 16.1% 0.0% 34.7% 0.0% 0.0% 100% 35%
ng against one SHG/VO tion among SHG/VO
mount per SHG/VO of loans per SHG/VO
49% 308,250 <--- Please enter this figure, in case of 16.6% more than one loan being given to the 248,810 VO/SHG 14,315 0.10
oan Loss Provision % 0.00% 0.00% 2.50% 10.00%
Amount 276,957 691,722
LLP Amount 6,924 69,172
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
20.00% 50.00% Total
165,000 521,707
VO/SHG) ?
sort "Column H" in ascending order
33,000 260,854 369,950 19.94%
Principal Outstandin g of O/D Loans
1-30 days Principal O/D
109,485 263,000 167,472 150,000 143,472 308,250 80,000 160,000 6,707 41,000 2,000 20,000 80,000 124,000 1,655,386
No. of loans
1-30 days 31-60 days 61-90 days Principal 31-60 Principal 61-90 Principal Outstandin days Outstanding days Outstanding g of O/D Principal of O/D Principal of O/D Loans O/D Loans O/D Loans
-
-
7 No. of loans
-
19,485 33,482 52,967
-
No. of loans
109,485 167,472 276,957 -
Principal 1-30 days 31-60 days 61-90 days Outstandin Principal 31-60 Principal 61-90 Principal g of O/D 1-30 days Outstandin days Outstanding days Outstanding Loans Principal g of O/D Principal of O/D Principal of O/D O/D Loans O/D Loans O/D Loans #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
181-365 91-180 days days 91-180 Principal 181-365 Principal days Outstanding days Outstanding Principal of O/D Principal of O/D O/D Loans O/D Loans
20,488 8,250 8,000 16,000 52,738 No. of loans
143,472 308,250 80,000 160,000 691,722
11,000 59,000 70,000
2 No. of loans
41,000 124,000 165,000
>365 days >365days Principal No. of Principal Outstanding days since O/D of O/D Loans loan given
145,000 60,000 3,557 1,998 8,000 32,000 250,555
4 No. of loans
263,000 150,000 6,707 2,000 20,000 80,000 521,707
1,157 1,157 1,157 828 828 828 686 355 307 142 1,452 1,446 1,438 1,385 1,216 1,080 972 928 870 851 500
2 No. of loans
181-365 91-180 days days 91-180 Principal 181-365 Principal >365 days days Outstanding days Outstanding >365days Principal No. of Principal of O/D Principal of O/D Principal Outstanding days since O/D Loans O/D Loans O/D of O/D Loans loan given #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,654 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 8,270
Sector wise loan disbursement
Loan disbursemen t last year
80,000 160,000 200,000 440,000 6
A
H
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
225,000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
M
204,972 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
C
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
S
295,000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Loan disbursemen t last year -
ursement
R
334,972 150,000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
L
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
O
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Table No: 9 Expenditure for the Groups on Book Keepers
Percentage of idle funds
10%
15%
20%
25%
15 12 Monthly savings per member 30 Total group savings 450 360 Per Annum Savings 5400 4320 Idle funds 540 432 Net funds for lendings 4860 3888 Interest on internal lendings per annum 1166.4 933 Book keeper salary per annum 400 In percentage terms 34% In percentage terms for group size of 43% 12
15 12 30 450 360 5400 4320 810 648 4590 3672 1101.6 881 400 36% 45%
15 12 30 450 360 5400 4320 1080 864 4320 3456 1036.8 829 400 39% 48%
15 12 30 450 360 5400 4320 1350 1080 4050 3240 972 778 400 41% 51%
Avg Group size
Loan Analysis of Kunikinapaadu MAA society Loans given to Group members Enter date on which you want to do loan analysis: ----->
Please insert rows if required, before the row # 12, else all formulas will change. Then copy the for S#
Members Name
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
shakuntala Seetharamamma Saraswathi Yellamma Kanthamma Sujatha Venkatamma Seethamma tirupathamma Nagamma laxmi Jayama Savithri Baayama Mariamma Rama maha laxmi Raani Narsamma Naramma Koteshwari Ananthamma Kalavathi Peddamma
Day
31 31 31 24 24 24 24 24 24 24 24 24 24 24 24 10 10 10 10 10 10 10 10 10
Month
1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3
Year
2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004
Loan date Loan amount
Loan outstanding
2,000 3,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000
996 527 1,124 1,240 1,240 1,400 1,200 1,200 1,200 1,200 1,200 1,200 1,200 1,200 1,200 1,400 1,200 1,200 1,200 1,200 1,600 1,200 1,200 1,200
31/Jan/04 31/Jan/04 31/Jan/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 24/Feb/04 10/Mar/04 10/Mar/04 10/Mar/04 10/Mar/04 10/Mar/04 10/Mar/04 10/Mar/04 10/Mar/04 10/Mar/04
Code:
Borrowings (External loans taken by Federation/VO)
Please insert rows if required, before the row # 6, else all formulas will change. Then change the S
S#
Name of the bank/fed.
Loan date Loan amount
Loan outstanding
30/Nov/99 30/Nov/99 30/Nov/99 30/Nov/99 30/Nov/99
###
Total
-
-
Portfolio Analysis 1 CRR OTRR PIA (>1day) PAR (>1days) PIA (1-30 days) PAR (1-30 days) PIA (31-60 days) PAR (31-60 days) PIA (61-90days) PAR (61-90 days) PIA (91-180 days) PAR (91-180days) PIA (181-365 days) PAR (181-365 days) PIA (>365 days) PAR (>365 days) CRR = Cumulative Repayment Rate OTRR = On-time Repayment Rate PIA = Portfolio in Arrears PAR = Portfolio at Risk PIA & PAR PIA >1day PAR >1days PIA > 30 days PAR > 30 days PIA > 60 days PAR >60 days PIA > 90 days
PAR >90 days PIA >180 days PAR >180 days PIA > 365 days PAR > 365 days
Checks: 1. Did you enter the date on which you w 2. Did you enter the maximum loan o/s w 3. Check Ratios of PIA & PAR (PIA% sh > 1day > 30 days > 60 days > 90 days > 180 days > 365 days
4. Loan Purpose List Please insert a new purpose between th Agriculture A Buffalo H Ceremonies S Education C Health C House construction R House repair R Kirana M Land purchase R Loan Repayment L Others O Poultry H Sheep H Small Business M Tea shop/Hotel M Wedding S
22-Jul-04
ange. Then copy the formulas (column R to column AU) in the inserted rows. Also change the S#. Loan Purpose
Loan Purpose Code##
House construction R House construction R House construction R House construction R House construction R House construction R House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A House construction#N/A
nge. Then change the S#, also
Peri odic No. ity Instalm of ents Inst. ^^
M M M M M M M M M M M M M M M M M M M M M M M M
10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Instalment Size
200 300 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200 200
Cum. Prin. Cum. Prin. Paid Due (schedule to pay)
1,000 1,500 1,000 800 800 800 800 800 800 800 800 800 800 800 800 800 800 800 800 800 800 800 800 800 17,900
1,004 2,473 876 760 760 600 800 800 800 800 800 800 800 800 800 600 800 800 800 800 400 800 800 800 17,873
Interest Paid
-
Loan Purpose
Loan Purpose Code##
Peri odic No. ity Instalm of ents Inst. ^^
Instalment Size
Cum. Prin. Due (schedule to pay) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Cum. Prin. Paid
-
Interest Paid
-
Internal external 94.39% #DIV/0! na #DIV/0! Loan outstanding - Portfolio Distribution #DIV/0! #DIV/0! >1 day Principal O/D1,004 #DIV/0! Agriculture #DIV/0! #DIV/0! >1 day Principal Outstanding 8,004 of O/D #DIV/0! Loans Animal & Husbandry #DIV/0! #DIV/0! 1-30 days Principal O/D 604 #DIV/0! Micro Enterprises #DIV/0! #DIV/0! 1-30 days Principal Outstanding 6,404 of#DIV/0! O/D Loans Consumption (health & education) #DIV/0! #DIV/0! 31-60 days Principal O/D 400 #DIV/0! Social ceremonies #DIV/0! #DIV/0! 31-60 days Principal1,600 Outstanding #DIV/0! of O/D Loans House/Land construction/repair #DIV/0! #DIV/0! 61-90 days Principal O/D #DIV/0! Loan Repayment #DIV/0! #DIV/0! 61-90 days Principal Outstanding #DIV/0! of O/D Loans Others #DIV/0! #DIV/0! 91-180days Principal O/D #DIV/0! Total Max. exposure #DIV/0! #DIV/0! 91-180 days Principal Outstanding #DIV/0! of O/D Loans #DIV/0! #DIV/0! 181-365 days Principal O/D #DIV/0! Total Production Loan #DIV/0! #DIV/0! 181-365 days Principal Outstanding #DIV/0! of O/D Max Loans outstanding against one SHG/VO #DIV/0! #DIV/0! >365 days Principal O/D#DIV/0! Loan distribution among SHG/VO #DIV/0! #DIV/0! >365 days Principal Outstanding of #DIV/0! O/D Loans Avg. Loan size ve Repayment Rate Current Loans (8,004) Avg. Loan amount per SHG/VO Repayment Rate 21 Avg. number of loans per SHG/VO No. loan given last 12 months No.of loans outstanding 21 (end of period) Total loan disbursed during year 43,000
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Loan outstanding >1 day Principal O/D1,004 #DIV/0! >1 day Principal Outstanding 8,004 of O/D #DIV/0! Loans > 30 days Principal O/D 400 #DIV/0! > 30 days Principal Outstanding 1,600 of#DIV/0! O/D Loans > 60 days Principal O/D #DIV/0! > 60 days Principal Outstanding of#DIV/0! O/D Loans > 90 days Principal O/D #DIV/0!
Loan Loss Provision Days Principal o/s of o/d loans 1-30 31-60 61-90 91-180
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
> 90 days Principal Outstanding of#DIV/0! O/D Loans > 180days Principal O/D #DIV/0! > 180 days Principal Outstanding #DIV/0! of O/D Loans > 365 days Principal O/D#DIV/0! > 365 days Principal Outstanding #DIV/0! of O/D Loans
181-365 > 365 Total
the date on which you want the analysis --> cell # is G5 the maximum loan o/s with an VO/SHG (only in case of more than 1 loan to the same VO/SHG) ? of PIA & PAR (PIA% should be less than or equal to PAR%, and not more) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
new purpose between the first and last row (in this case Agriculture & Wedding), data sort "Column H" in asc
nge the S#. Cum prin Paid Ontime
-
Prepayment
4 973 977
No.of months since loan given
5 5 5 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
No. of Inst. elpsed
5 5 5 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
5 5 5 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
No. of days overdue
0 0 18 25 25 25 0 0 0 0 0 0 0 0 0 10 0 0 0 0 41 0 0 0 144
Principal O/D
124 40 40 200 200 400 1,004
Cum prin Paid Ontime
-
Prepayment
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
923,000
health & education)
onstruction/repair
1,647,000 842,000 1,813,000
5,225,000
No.of months since loan given 54 54 54 54 54
No. of No. of Inst. days elpsed overdue 54 54 54 54 54
270
0 0 0 0 0 270
-
Principal O/D
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
% 17.7% 0.0% 31.5% 16.1% 0.0% 34.7% 0.0% 0.0% 100% 35%
ng against one SHG/VO tion among SHG/VO
mount per SHG/VO of loans per SHG/VO
49% 1,600 <--- Please enter this figure, in case of #DIV/0! more than one loan being given to the 248,810 VO/SHG 14,315 0.10
oan Loss Provision % 0.00% 0.00% 2.50% 10.00%
Amount 6,404 1,600 -
LLP Amount -
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
20.00% 50.00% Total
-
VO/SHG) ?
sort "Column H" in ascending order
#DIV/0!
Principal Outstandin g of O/D Loans
1,124 1,240 1,240 1,400 1,400 1,600 8,004
1-30 days Principal O/D
124 40 40 200 200 604 No. of loans
1-30 days 31-60 days 61-90 days Principal 31-60 Principal 61-90 Principal Outstandin days Outstanding days Outstanding g of O/D Principal of O/D Principal of O/D Loans O/D Loans O/D Loans
1,124 1,240 1,240 1,400 1,400 6,404
400 400
15 No. of loans
1,600 1,600
-
5 No. of loans
1
Principal 1-30 days 31-60 days 61-90 days Outstandin Principal 31-60 Principal 61-90 Principal g of O/D 1-30 days Outstandin days Outstanding days Outstanding Loans Principal g of O/D Principal of O/D Principal of O/D O/D Loans O/D Loans O/D Loans #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
181-365 91-180 days days 91-180 Principal 181-365 Principal days Outstanding days Outstanding Principal of O/D Principal of O/D O/D Loans O/D Loans
No. of loans
-
No. of loans
-
>365 days >365days Principal No. of Principal Outstanding days since O/D of O/D Loans loan given
No. of loans
-
173 173 173 149 149 149 149 149 149 149 149 149 149 149 149 134 134 134 134 134 134 134 134 134
No. of loans
181-365 91-180 days days 91-180 Principal 181-365 Principal >365 days days Outstanding days Outstanding >365days Principal No. of Principal of O/D Principal of O/D Principal Outstanding days since O/D Loans O/D Loans O/D of O/D Loans loan given #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,696 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,696 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,696 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,696 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,696 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 8,480
Sector wise loan disbursement
Loan disbursemen t last year
2,000 3,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 43,000 -
A
H
M
C
S
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Loan disbursemen t last year -
ursement
R
L
O
2,000 3,000 2,000 2,000 2,000 2,000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A
#N/A
#N/A