Hi,
I am working for a company which acquires SME/retail Non Performing Loans (Say Borrower Code B_0001 to B_1000)- of which each Borrower Code may contain multiple Loans (ie B_0001 may have 2 LoanIDs (L_0001, L0002... and the running number goes on with the next Borrower. These are the starting balance from the date of acquisition, of which I would then try to accrue and deduct the respective principal and interest of the respective Loan along the way. Each Payment running number, say PMT0001 will have a LoanID next to it as well- indicating which balance it pays
The loan payment sheet would look something like the below (more or less)
<tbody>
</tbody>
Now, I am trying to build some templates for other users which would make it easier to do but I don’t know how to make the Formula to capture all the PaymentNo for the specified Borrower ID and Loan ID
I would like to be able to type “B_0001” and “L_0001” to vlookup/sumif and have it return “PMT_0001” , “PMT_0004” and “PMT_0005” (so i canlink PMT0001 to lookup for more information such as the date of payment, amount paid in P and I to calculate the balance as of that day.
Is there a way to type a formula to capture the respective PaymentNo as describe above, and so that when I copy down the formula, it would capture these PMT number in the order it finds? (ie if I put the formula in cell A3, it would pull PMT_0001, and when I copy down, A4 and A5 would show PMT_004 and PMT 0005 accordingly?)
Thanks in advance.
I am working for a company which acquires SME/retail Non Performing Loans (Say Borrower Code B_0001 to B_1000)- of which each Borrower Code may contain multiple Loans (ie B_0001 may have 2 LoanIDs (L_0001, L0002... and the running number goes on with the next Borrower. These are the starting balance from the date of acquisition, of which I would then try to accrue and deduct the respective principal and interest of the respective Loan along the way. Each Payment running number, say PMT0001 will have a LoanID next to it as well- indicating which balance it pays
The loan payment sheet would look something like the below (more or less)
PaymentNo | BorrowerID | LoanID | Date | Princ | Int | Total | |
PMT_001 | B_0001 | L_0001 | 1 Sep 2017_ | 2000 | 1000 | 3000 | |
PMT_002 | B_0002 | L_0004 | 1 Sep 2017 | 20 | 50 | 70 | |
PMT_003 | B_0001 | L_0002 | 30 Aug 2017 | 200 | 200 | 400 | |
PMT_004 | B_0001 | L_0001 | 1 Aug 2017 | 500 | 300 | 800 | |
PMT_005 | B_0001 | L_0001 | 31 Aug 2017 | 200 | 200 | 400 |
<tbody>
</tbody>
Now, I am trying to build some templates for other users which would make it easier to do but I don’t know how to make the Formula to capture all the PaymentNo for the specified Borrower ID and Loan ID
I would like to be able to type “B_0001” and “L_0001” to vlookup/sumif and have it return “PMT_0001” , “PMT_0004” and “PMT_0005” (so i canlink PMT0001 to lookup for more information such as the date of payment, amount paid in P and I to calculate the balance as of that day.
Is there a way to type a formula to capture the respective PaymentNo as describe above, and so that when I copy down the formula, it would capture these PMT number in the order it finds? (ie if I put the formula in cell A3, it would pull PMT_0001, and when I copy down, A4 and A5 would show PMT_004 and PMT 0005 accordingly?)
Thanks in advance.