Help needed

SAJAD M K

New Member
Joined
Dec 24, 2019
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hello Sir,
Can you please help to solve my doubt. My doubt is nothing but regarding to invoice and payment settlement using excel. If we have issued two invoices of amount 20000/- and 30000/- and we are getting a payment of 40000/-. Then the balance amount 10000/- must show as the outstanding in the second invoice row. Payment must be cleared on each invoices and also excel should work out if a payment is getting after the credit period with an interest rate of whatever we want. Actually I've done that but failed in interest calculation. Waiting for your valuable reply
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Following is My invoice details range and include settlement formula too.
Book1
ABCDEF
1Inv noInv date30 Day Credit periodInv amntPaid amntDue amnt
2101-04-201901-05-201930000300000
3215-04-201915-05-2019250001500010000
4301-05-201931-05-201933000033000
5430-05-201929-06-201940000040000
Experiment
Cell Formulas
RangeFormula
E2:E5E2=MIN(SUM(K:K)-SUM(OFFSET($E$1,,,ROW(E2)-ROW($E$1))),D2)
F2:F5F2=D2-E2
C2:C5C2=B2+30


And Following is my payment area.

Book1
JK
1DatePayment
210-05-201915000
316-05-201930000
402-06-2019
510-06-2019
Experiment
 

Attachments

  • Settlement Repot.jpg
    Settlement Repot.jpg
    41 KB · Views: 3
Upvote 0
My question is how to write a formula using the logic...if a payment is getting after the credit period then an interest of whatever the rate should be calculated on full invoice amount ie for an invoice amount 50000 we are getting 25000 after the credit period then a 20% of interest on 50000 should be calculated. and next payment of 15000 is getting after some days then an interest on balance amount of 25000 should be calculated using date difference of the last payment and original invoice date..
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top