Amount of loan overdue



I am posting here for the first time. Thanks for inviting me. My problem: in the attached excel sheet I want a formula which will calculate the amount of loan which is overdue. I have elaborated more in the attached sheet.

You might find the approach that I have used in the following formula useful. But the formula is not working: =IF(AND(TODAY()>EDATE(D6,MAX(INDEX((H$6:H6)*(C$6:C6=C6)*(E$6:E6="loan"),)))),LOOKUP(2,1/((E$6:E6="repayment")*(C$6:C6=C6)),G$6:G6),"")

Let me beef my question up, following some of suggestions made by Variatus (received with appreciation. I am working on other suggestions:

- Yes it is unrealistic for someone to make a deposit (savings) and take a loan on the same date. I have attached a sheet in which dates are different.

- In the original excel sheet which I am already using to administor a small serving and credit scheme, I have used data validation to restrict someone from taking a loan while they have an outstanding loan. Hence the overdue loan should be the last loan taken by that person.  

- Variatus you have suggested that I have designed this sheet in a way which makes it difficult to calculate outstanding loan without VBA. I thank you in advance on ideas on redesigining the sheet.

- Please note that my final problem has not been solved yet

Again thank you Variatus and other people who are working on this. I am an excel novice. Your ideas and support are very encouraging and highly appreciated.



This thread has run its course. It has an answer and points have been awarded. Thank you. To get at your real problem I recommend a new question. For this you have three options.
(1) Ask how to re-design your worksheet so that the currently overdue amount for each loan can be calculated more easily.
(2) Do the re-design yourself and ask the same question as you asked here but based on the new design.
(3) Ask for any solution, VBA or not, based on your current design. Even this can't be handled within this thread because you have to decide which of the changes I have suggested you want to implement. The result will be a different worksheet, meaning a new task, meaning a new question.
Variatus (rep: 803) Sep 3, '17 at 8:57 pm
Add to Discussion


Selected Answer

Please find attached a revised copy of your workbook in which I made the following modifications.

  1. I created a column "DR/CR" with the formula
    =IF($D6="Loan",-1,IF($D6="Repayment", 1, 0))
    and the custom cell formatting +1;-1;;
    The purpose of this column is to avoid the ubiquitous requirement to check the text in column D. The added advantage is in that you can use the value in this column to create positive and negative amounts by simple multiplication. The cell format serves to suppress the display of 0. However, I would recommend to hide the entire column. It doesn't need to be shown because it's only used by the formulas in other columns.
  2. I have introduced a column for "Interest". The way you have calculated interest in every formula is simply not workable. You need to show the chargeable rate somewhere. I did so at the top of the column but I think you will need an extra column for that (in case of changing rates), and even the method of calculation I introduced with this formula in the interest column is probably inadequte.
    In general, interest is charged per annum or per month. This calculation just allows a flat charge - one rate fits all - for any period of time (not to mention extra interest for overdue payments). Not practical.
  3. I have changed the formula for the Debt columns. Now it is
    . That is much simpler than what you had but it is still twice as long as it shoud be. The Interest charge should be in a separate row in the Amount column. It could then be included in the total debt with the first half of the formula alone.
  4. Despite these improvements I failed to answer your question. It isn't possible with the current structure of your sheet unless you use VBA. Here is the problem:-
    Boniface took out a loan on July 1. The repayments he made are shown under the same date. That is unlikely to be the case in real life.
    The loan period was 3 months which is recorded under the date of July 1. So, to find the duration we need to find "Boniface" and "Loan" and "July 1" in row 9 (or 13) so that we can determine if the balance of 99,500 is overdue or not.
    In row 9 we have "Boniface" and we have "Loan" (implicitly because "repayment" is linked to "Loan") but we don't have "July 1".
    We can find the "3" using a SUMPRODUCT() function, but the effort will fail if Boniface takes out a second loan later.
  5. Anyway, we don't want the duration. We want the due date. So, the first step toward a solution would be to introduce a column with "Due date". You might add that on to the existing and automatically calculate the due date by entering the duration.
    Then the due date must be made available in rows 9 and 13. I feel that the solution would be in having a unique loan number to which all transactions are linked. A loan number would be more unique than "Boniface" and could be used to exclude "Savings" (which don't have a loan number). However, even that would be difficult because you would need to find the first and last entries pertaining to each number, the frist to find the start date, the last to find the overdue amount. Consider writing the due date in every line.

    Anyway, I think your question has been answered as good as was possible. If you agree, please award points (accept the answer). You can then lodge another question to solve the problem that you will have after you re-design your worksheet. Good luck!


Answer the Question

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