Loan Deals and partial Payments and identify profits copy paid once to another sheet.

ifraz

New Member
Joined
Apr 26, 2007
Messages
3
Hi Everyone

i am put in a huge in my company. where have to calculate the profits of every settlements done. Data provided to me is huge. but the example provided here to get the idea is simple.

i have the repayment plan of all the customers and in another sheet i have the settlements of it. as we know customers do not settle as per payment plan. settlements are done in part and some time even after maturity date, which is difficult to find for which month the customer has paid.

okay, now, for profit purpose, say i have total paid amount for every deal, now i need is, find the deal in repayment plan sheet deduct every month until paid amount for that specific deal is zero, and for every deal settled and no balance in the repayment plan, then copy the whole row to another sheet, so that i can take their profits.

show i have got this far.

VBA Code:
Sub Balance()
Dim dfinalrow As String
Dim deal As String
Dim i As Integer
Dim j As Integer
Dim k As Range
Dim m As Range

    For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
            For j = 2 To Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
            Set k = Range("A:A").Find(what:=j, LookIn:=xlValues, lookat:=xlWhole)
                If Not k Is Nothing Then
                    Set m = Sheet2.Range("A:A").Find(what:=j, LookIn:=xlValues, lookat:=xlWhole)
                    Sheet2.Range("D" & j).Cells = Sheet1.Range("E" & i).Value - k.Range("C" & j).Value
                End If
            Next
        Sheet1.Rows(i).Copy
        Sheet3.Activate
        dfinalrow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
        Sheet3.Cells(dfinalrow + 1, 1).Select
        Sheet3.Paste
        
    Next
End Sub

the above code just copies, all the data from Sheet1 (Deals) with the deal number available in the Sheet2 (Settlements) to Sheet3 (Copied Data), but, what i want is, for every loop subtract the Paid amount from Total Deal amount of in sequence until the deal amount can accommodate the total paid amount, and only those accommodated deals from Sheet1 (Deal) to be copied to Sheet3 (Copied Data).

please some one kindly help.

thanks alot.
 

Attachments

  • Deal Sheet.JPG
    Deal Sheet.JPG
    81.5 KB · Views: 10
  • Settlement Sheet.JPG
    Settlement Sheet.JPG
    40.7 KB · Views: 9
  • Copiied Data.JPG
    Copiied Data.JPG
    77.6 KB · Views: 10

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

ifraz

New Member
Joined
Apr 26, 2007
Messages
3
hi everyone,

i tried the below code, it nearly worked, but doesn't loop by updating the new balance in sheet2 and when the balance becomes zero, the profit should be zero for relevant rows.



VBA Code:
Sub test3()
Dim rng As Range
Dim i As Integer


Dim rngS As Range
Dim j As Integer


Dim tot As Long
Dim bal As Long
Dim paid As Long


    With ThisWorkbook.Worksheets("Deals")
    lastr = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = Sheet1.Range("A2:A" & lastr)
    End With
    
    With ThisWorkbook.Worksheets("Settlements")
    lastrs = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rngS = Sheet2.Range("A2:A" & lastrs)
    End With


For i = 2 To rng.Rows.Count
    tot = rng.Cells(RowIndex:=i, ColumnIndex:=7).Value
    
    For j = 2 To rngS.Rows.Count
        paid = rngS.Cells(RowIndex:=j, ColumnIndex:=3).Value
        bal = rngS.Cells(RowIndex:=j, ColumnIndex:=4).Value
        bal = bal - tot
    Next
    bal = bal - tot
    Debug.Print bal
    rngS.Offset(0, 3).Value = bal + tot
    If paid > tot Then
    rng.Offset(0, 7).Value = rng.Offset(0, 3).Value
    Else
    rng.Offset(0, 7).Value = 0
    End If
Next


End Sub


Waiting for someone to help.



thanks
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,551
Messages
5,838,055
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top