2017 S02 P03 EXCEL no excel part 3


NB. The following PCB scenario is a continuation of the question 3 scenario.
PCB is a registered credit provider and obtained its banking license a few years ago. One of the business objectives of PCB is to empower women through financial inclusion. PCB uses a group lending methodology to approve and disburse (pay) loans to its women clients.
For your clarification
A group lending methodology is a lending mechanism, which allows a group of individuals, often called solidarity (unity) group, to provide collateral or a loan guarantee through a group repayment pledge. The incentive to repay the loan is based on peer pressure. If one group member defaults (fails to pay), the other group members pay up the outstanding repayment amount.
Mr. Taylor asked you as his consultant to help him with calculations of loan product pricing and loan amounts to be disbursed (paid) to groups of women clients who applied for loans.
Mr. Taylor provided you with the following rules for the women group loans

1. A group is made up of five (5) women (refer to cell A19 of the worksheet attached below)

2. Women qualify to form a group iF they live in the same suburb (area) (refer to cell range D14:D18 of the worksheet attached below)

3. A group number is only allocated to a group of women after meeting rules (1) and (2) above (refer to cell range H14 H18 of the worksheet attached below)

4. Each group member must have a valid PCB Client Number Refer to cell range (A14:A18). A valid PCB client number consists of the following

1 2 3 4 5 6 7 8 9 10 11
M P 4 3 1 - 1 0 0 P Q


• The first six digits (YYMMDD) represent an individual's date of birth, in this instance 890518 which represents 18 May 1989
• The next four digits (GGGG) define an individual’s gender Women are assigned numbers ranging between 0000-4999 and males between 5000-9999

5. Each group member must have an active Savings Bank Account with PCB with a bank balance of more than 20% of the loan amount Refer to ceil range (C6:C10).

6. Each group member's savings balance at the time of loan application should be at least 20% or more of the required loan amount, otherwise, the loan is rejected.

7. After approval of loans, PCB transfers the clients required savings ie 20% of the approved loan (refer to cell C2) amount out of their savings account into a separate short-term investment account, which earns interest at 6% (refer to cell C3) per annum. Clients are required to grow this short-term investment account by investing a fixed amount of R500 (refer to cell C4) on monthly basis. Clients can only withdraw or access this investment account at the end of the loan term. Interest accrues at the end of each month and deposits are due at the end of each month

8. All group members must apply for the equal/same loan amounts payable over the similar repayment term (eg. Each group member applies for a loan of R5. 000 payable over 6 months)

9. PCB only has five (5) loan products as follows


Loan amount of R5 000 over 6, 12 or 18 months term.

Loan amount of R10 000 over 6 or 12 months term.

Loan products are coded. For example loan product 1 is PC001 and loan product 2 is PC002. Interest rates are charged at 3% per month for a.6 months term. 4% per month for a 12 months term and 5% per month for an 18 months term (Refer to Cell range C22:F26)

10. Loans are payable in equal installments at the end of each month. There are no other charges on the group loans.
Refer to the next page for the worksheet you prepared for Mr. Taylor. The worksheet includes the following:


Cell Range A1 C4 - Loan application table

Cell Range A12 H19 - Extract from Client List (client personal information)

Cell Range A22 F28 - Loan pricing table

Please login to get access to this quiz