Ots 24 > 24071 > Presenta > Copyofpa
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Ots 24 > 24071 > Presenta > Copyofpa as PDF for free.
More details
- Words: 7,881
- Pages: 105
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
480800
VO 1 VO 2 VO 3 Contribution from members Net Amount received
109800 45000 326000 36200 444600 Table 3: CIF disbursement details
Village Organisation 1
Village Organisation 2
Purpose Number applied Number of members Amount given / member Total amount 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%
Purpose Number applied Number of members given Flower Garden 1 1 Guava garden 3 1 Kirana stores 4 2 Cloth shop 2 1 Sewing Machine 24 1 34 6 17.6%
3.46% 13.66% 10.02%
45.54%
9.11%
Cattle Clothes shop Kirana Stores Wood business Borewell Sewing Machines
11.11% 22.22% 22.22%
22.22%
18.21%
22.22%
Flower G Guava ga Kirana sto Cloth sho Sewing M
Village Organisation 3 Amount / member Total amount 5000 5000 10000 10000 5000 10000 10000 10000 10000 10000 45000
Purpose Number appliedmembers givenAmount / member Total amount Cattle 9 8 10000 80000 Borewell 15 15 2000 30000 Vegetable shop 28 28 5000 140000 Mirchi powder 2 2 8000 16000 Sewing Machine 5 2 5000 10000 Foot Mart 2 1 10000 10000 STD booth 3 2 7500 15000 64 58 301000 90.6%
.22%
22.22%
Flower Garden Guava garden Kirana stores Cloth shop Sewing Machine
26.58%
4.98% 3.32% 3.32% 5.32%
9.97% 46.51%
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
Economic sustainability of VO
Repayment rate Total CIF received
100% 434400
90% 434400
80% 434400
70% 434400
61% 434400
6% 26064
6% 23457.6
6% 20851.2
6% 18244.8
6% 15899.04
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
Total expenditure
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
Net Loss/Profit
1704
-902.4
-3508.8
-6115.2
-8460.96
-153%
289%
74%
38%
Rate of interest per annum Income from interest
Stationary expenses Conveyance allowance to EC Miscellaneous expenses
60% 434400
50% 434400
6% 15638.4
6% 13032
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
500 240 100 840 24360
500 240 100 840 24360
-8721.6
-11328
43%
30%
Table 9 Economic sustainability of VO2 at different repayment rates
Repayment rate Total CIF received Rate of interest per annum Income from interest
100% 832925 6% 49975.5
90% 815667 6% 44046.02
80% 815667 6% 39152.02
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
500 240 100 840 24360
500 240 100 840 24360
500 240 100 840 24360
25615.5
19686.02
14792.02
-23%
-25%
Stationary expenses Conveyance allowance to EC Miscellaneous expenses Total expenditure
Net Loss/Profit
70% 815667 6% 34258.01
63% 815667 6% 30832.21
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
500 240 100 840 24360
500 240 100 840 24360
9898.01
6472.21
-33%
-56%
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 At Different rates of idle funds Percentage of idle funds 10% 15% 20%
Avg Group size
25%
15
15
15
15
Monthly savings per member 30 Total group savings 450
30 450
30 450
30 450
5400
5400
5400
5400
540
810
1080
1350
4860
4590
4320
4050
Interest on internal lendings 1166.4 per annum 1101.6
1036.8
972
400 39%
400 41%
Per Annum Savings Idle funds Net funds for lendings
Book keeper salary per annum400 In percentage terms for the34% groups
400 36%
Note: income from External funds is not being considered here
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
Table No:10 Estimated Economic sustainability of VO Number of Groups Average Group size Total individual members Individual share capital Membership fee per individual Compulsory savings at VO per member
25 15 375 100 15 20
25 15 375 100 15 10
25 15 375 100 15 10
37500 5625 7500 90000 140625 459375 600000
37500 5625 3750 45000 91875 508125 600000
37500 5625 3750 45000 91875 508125 600000
Income To VO Interest on external funds Interest income from external loans Interest income from Internal funds Total Income
15% 68906.25 21093.75 90000
18% 91462.5 13781.25 105243.75
24% 121950 13781.25 135731.25
Expenditure To VO's Interest expense on Savings Interest expense on external loans(9%) *Salaries to CC's Sitting charges to BOD Registeration and other legal expenses Membership+share cap to MS Expenditure on conveyance Stationary expenses Miscellaneous expenses Total Expenditure
5400 41343.75 9000 3600 1000 500 360 500 500 62203.75
2700 45731.25 9000 3600
2700 45731.25 9000 3600
500 360 500 500 62891.25
500 360 500 500 62891.25
Net Profit/loss
27796.25
42352.5
72840
Share Capital from members Fee from memebrship Funds from members savings per month Funds from members per annum Internal funds for lending External loans per annum Total Funds for lending
* A CC is responsible for eight village organisations. The columns indicate various rates of interest.
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
480800
VO 1 VO 2 VO 3 Contribution from members Net Amount received
109800 45000 326000 36200 444600 Table 3: CIF disbursement details
Village Organisation 1
Village Organisation 2
Purpose Number applied Number of members Amount given / member Total amount 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%
Purpose Number applied Number of members given Flower Garden 1 1 Guava garden 3 1 Kirana stores 4 2 Cloth shop 2 1 Sewing Machine 24 1 34 6 17.6%
3.46% 13.66% 10.02%
45.54%
9.11%
Cattle Clothes shop Kirana Stores Wood business Borewell Sewing Machines
11.11% 22.22% 22.22%
22.22%
18.21%
22.22%
Flower G Guava ga Kirana sto Cloth sho Sewing M
Village Organisation 3 Amount / member Total amount 5000 5000 10000 10000 5000 10000 10000 10000 10000 10000 45000
Purpose Number appliedmembers givenAmount / member Total amount Cattle 9 8 10000 80000 Borewell 15 15 2000 30000 Vegetable shop 28 28 5000 140000 Mirchi powder 2 2 8000 16000 Sewing Machine 5 2 5000 10000 Foot Mart 2 1 10000 10000 STD booth 3 2 7500 15000 64 58 301000 90.6%
.22%
22.22%
Flower Garden Guava garden Kirana stores Cloth shop Sewing Machine
26.58%
4.98% 3.32% 3.32% 5.32%
9.97% 46.51%
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
Economic sustainability of VO
Repayment rate Total CIF received
100% 434400
90% 434400
80% 434400
70% 434400
61% 434400
6% 26064
6% 23457.6
6% 20851.2
6% 18244.8
6% 15899.04
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
Total expenditure
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
Net Loss/Profit
1704
-902.4
-3508.8
-6115.2
-8460.96
-153%
289%
74%
38%
Rate of interest per annum Income from interest
Stationary expenses Conveyance allowance to EC Miscellaneous expenses
60% 434400
50% 434400
6% 15638.4
6% 13032
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
500 240 100 840 24360
500 240 100 840 24360
-8721.6
-11328
43%
30%
Table 9 Economic sustainability of VO2 at different repayment rates
Repayment rate Total CIF received Rate of interest per annum Income from interest
100% 832925 6% 49975.5
90% 815667 6% 44046.02
80% 815667 6% 39152.02
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
500 240 100 840 24360
500 240 100 840 24360
500 240 100 840 24360
25615.5
19686.02
14792.02
-23%
-25%
Stationary expenses Conveyance allowance to EC Miscellaneous expenses Total expenditure
Net Loss/Profit
70% 815667 6% 34258.01
63% 815667 6% 30832.21
750 9000 520 6240 690 8280 23520
750 9000 520 6240 690 8280 23520
500 240 100 840 24360
500 240 100 840 24360
9898.01
6472.21
-33%
-56%
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 At Different rates of idle funds Percentage of idle funds 10% 15% 20%
Avg Group size
25%
15
15
15
15
Monthly savings per member 30 Total group savings 450
30 450
30 450
30 450
5400
5400
5400
5400
540
810
1080
1350
4860
4590
4320
4050
Interest on internal lendings 1166.4 per annum 1101.6
1036.8
972
400 39%
400 41%
Per Annum Savings Idle funds Net funds for lendings
Book keeper salary per annum400 In percentage terms for the34% groups
400 36%
Note: income from External funds is not being considered here
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
Table No:10 Estimated Economic sustainability of VO Number of Groups Average Group size Total individual members Individual share capital Membership fee per individual Compulsory savings at VO per member
25 15 375 100 15 20
25 15 375 100 15 10
25 15 375 100 15 10
37500 5625 7500 90000 140625 459375 600000
37500 5625 3750 45000 91875 508125 600000
37500 5625 3750 45000 91875 508125 600000
Income To VO Interest on external funds Interest income from external loans Interest income from Internal funds Total Income
15% 68906.25 21093.75 90000
18% 91462.5 13781.25 105243.75
24% 121950 13781.25 135731.25
Expenditure To VO's Interest expense on Savings Interest expense on external loans(9%) *Salaries to CC's Sitting charges to BOD Registeration and other legal expenses Membership+share cap to MS Expenditure on conveyance Stationary expenses Miscellaneous expenses Total Expenditure
5400 41343.75 9000 3600 1000 500 360 500 500 62203.75
2700 45731.25 9000 3600
2700 45731.25 9000 3600
500 360 500 500 62891.25
500 360 500 500 62891.25
Net Profit/loss
27796.25
42352.5
72840
Share Capital from members Fee from memebrship Funds from members savings per month Funds from members per annum Internal funds for lending External loans per annum Total Funds for lending
* A CC is responsible for eight village organisations. The columns indicate various rates of interest.
Related Documents
Ots 24 > 24071 > Presenta > Copyofpa
November 2019 7
Ots 24 > 24071 > Presenta > Paramete
November 2019 8
Ots 24 > 24071 > Questinn
November 2019 6
Ots 24 > 24071 > Execsumm
November 2019 8
Ots 24 > 24071 > Tablesan
November 2019 12