Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Loan overdue & table redesigning

0

Hello excel experts,

 I need a formula which will calculate the amount of loan which is overdue (take note of the loan duration column) as follows:

1)    If 99,500 is overdue I want the amount to be displayed against the cell with loan in the debt column. If you have a better idea on redesigning the excel sheet attached, so as to make it easier to solve  this problem please go ahead and redesign the table. Thanks in advance

2)    As Boniface continues to repay his loan, is there a way to create a formula such that the remaining overdue amount (59,500) will be displayed and the overdue amount which was displayed before (99,500) will disappear? I think conditional formatting might help but I cannot figure out how

Few explanations

I am using this excel sheet for managing a small serving and credit scheme. The interest rate is flat at 5% per month. Loan duration is between 1 to 6 months. We are still figuring out how the interest rate should change if the loan is overdue. Once we have figured that out I will fix the interest rate accordingly

In my original excel sheet I have restricted issuing a loan to someone who has an outstanding loan

Thank you Variatus (in this forum) for your great ideas on the attached excel sheet. I have incorporated your ideas into my original excel sheet. This has reduced calculation time, and the formulas are less complicated. 

excelfinance
posted by: kindi1 (rep: 4) on Wed Sep 6, 2017 at 1:23 pm
Answer
Discuss

Answers

0
Selected Answer

I messed up your beautiful workbook. This is what else I did.

  1. I created lots of named ranges. Please look at the Name Manager (Formulas tab) to get the hang of them.
  2. I added a worksheet "Params" (which is hidden). It has two overlapping named ranges. One lists transactions by name, the other associates them with either debit or credit. This arrangement replaces the DR/CR column I recommended last time.
  3. The list in named range "Transactions" feeds the dropdown in column D on the Ledger sheet.
  4. In the same way the "Customers' named range feeds the validation in Ledger!Column A, and the "Loans" named range feeds the dropdown in Ledger!Column E.
  5. The "Customers" worksheet has overlapping named ranges "Customers" and "Interest". You can set the interest separately for each customer, for normal and overdue accounts. But you have to set up a customer before you can make an entry in the ledger for him.
  6. The "Loans" sheet gives a number of each loan. Observe to enter only the number (1, 2 or 3 etc) and not "ABC-001". Set the number format to match your numbering requirement. Note that the Ledger sheet needs an integer although it displays the way you format the cell.
  7. For each loan you set a customer, the day it was granted and teh duration. The due date is calculated by a formula.
  8. On the Ledger sheet there is a formula in column F which has no output until you select the transaction type of "interest" and have specified a loan. It will then calculate the interest for one month, for the balance of the indicated loan, using the customer's normal or overdue rate depending upon the entry date and the loan maturity. If the entry isn't for interest, just over-write the formula with whatever.

So, the work flow is to create a formatted, blank row in the Ledger worksheet, select a customer from the dropdown in column A (set up the customer first if new), enter the date, select the transaction type from the dropdown (set up all transactions on the Params sheet beforehand), Select a loan number from the dropdown if the transaction concerns a loan (else leave blank. Loans must be set up before you can refer to them), enter the transaction amount. If the entry refers to a loan the balance of that lown, after the new entry, will be shown in column H.

You will not see the column G which is hidden. It converts the absolute amount of the transaction into a positive or negative number depending upon the setting for the entry type on the Parms sheet.

The Customers sheet will show each customer's balance. The formula shows the total balance (Savings + Loans).

The Loans sheet will show the balance of each loan. If it is overdue it will be shown red and bold (conditional formatting).

It isn't exactly what you wanted, I know. But after you pretty things up a little it will do the job for which it was designed, I hope :-)

posted by: Variatus (rep: 4889) on Thu Sep 7, 2017 at 1:16 am
Discuss

Discussion

Thanks a lot Variatus. 2 small questions:
- On my opinion, the Loan sheet that you have designed has made it very easy to calculate overdue loan. If someone is taking a loan data should be entered in the two sheets (Leja ya kila siku and Loan). Is that right? I would suggest designing the Leja ya kila siku so that everything is entered there, and use formulas to extract entries to the Loan sheet(id, debtor and date of loan). I think this will make it easy for the recorder because they will not need to enter data twice. +minimizing errors. What do you think?
-I do not understand why you have put -1 for savings and repayment and 1 for loan; instead of the other way round.

Again I appreciate your help and wish you a good day. I am still studying your great suggestions
kindi1 (rep: 4) Sep 7, '17 at 4:39 am
I'm glad you like some of it, at least.
I would prefer to take a slightly different view (same effect): the Loans sheet give a number. It is a loan register. Only the loan data are there: debtor name, date, duration/maturity and interest rates, but not even the loan amount. The Leja sheet shows all transactions. No transactions are entered twice. No transactions are recorded or visible anywhere else. When you allocate a loan number the balance of that loan is zero (taken from the Leja sheet). Once entries are made under that number in the Leja sheet the current balance is shown in the Loans sheet, marked overdue if it is overdue. If you want transaction details regarding any loan you can filter on the Leja sheet.
 =SUMIFS(Ledger,L_Customers,$B4,L_Loans,$A4). "Ledger" is a named range. Within the SUMIFS() formula it is the "sum range". The next four entries are two pairs of "criteria range" and "criterion" (with the equal sign the default): If the cell in (L_Customers = B4) AND the cell in (L_Loans = A4 ) then add the value in Ledger to the return total.
Variatus (rep: 4889) Sep 7, '17 at 5:08 am
I seem to have overlooked one of your questions: DR/CR.
When you give somebody money you "debit" his account. The debit side of the ledger is the left of two columns, digitally positive. Hence the word "Debtor". That leaves only the right column for "Creditors", to whom you owe money, and the minus sign to make the digital difference. That's banking and accounting. Don't become confused by the fact that when you deposit money in the bank you debit the bank, but the bank credits your account because they received money from you. The same transaction, seen from opposite sides, is reflected on opposite sides in the books.
For consumption in a small lender it might make sense that when you grant a loan that is "positive" money and repayments must be negative because they are opposite.
However, from Excel's point of view it makes no difference at all if you change those values to the opposite of what I set them so long as the applied logic is consistent with itself.
Variatus (rep: 4889) Sep 7, '17 at 8:39 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login