Forum Discussion
Interest Calculation based on FIFO clearing
Interest calculation issue, deriving the desired result interest calculation via excel formulae or any other best way.
| Posting Date | Customer | Doc Type | Invoice No. | Amount (Rs.) | Due Date | Desired Result Interest Amount | Remarks | ||||||
| 01-Jan-25 | A | RV | 101 | 100 | 03-Jan-25 | 0.043836 | Rs. 40 Adjusted against FIFO basis Against RV 101 with 1 day delay & Rs. 60 Adjusted against FIFO basis against RV 101 & balance Re 1 out of Rs. 61 payment adjusted against Next RV 102 Rs. 200 | ||||||
| 02-Jan-25 | A | RV | 102 | 200 | 04-Jan-25 | 0.109315 | Re 1 balance out of Rs 61 payment adjusted against Rs. 200 RV 102 and next out of Rs. 300 payment only 199 rupees to be adjusted based on FIFO basis. | ||||||
| 04-Jan-25 | A | DZ | - | -40 | 04-Jan-25 | - | Only in front of RV/Billing interest need to be caculated. | ||||||
| 05-Jan-25 | A | DZ | - | -61 | 05-Jan-25 | - | |||||||
| 06-Jan-25 | A | DZ | - | -300 | 06-Jan-25 | - | |||||||
| 03-Jan-25 | B | RV | 201 | 150 | 05-Jan-25 | 0.780822 | Customer B Rs. 100 received adjusted against RV 201 based on FIFO with 1 day delay & balance Rs. 50 remains outstanding till today calculated accordingly. | ||||||
| 06-Jan-25 | B | DG | - | -100 | 06-Jan-25 | - | Only in front of RV/Billing interest need to be caculated. | ||||||
| Note | |||||||||||||
| 1. Positive Balance is Billing to Customer. (Denoted via RV) | |||||||||||||
| 2. Negative Balance is Amount/Payment received from Customer. (Denoted via DZ or DG or TE) | |||||||||||||
| 3. Billing (RV) sorted based on Due Date (If 2nd RV due date was before 1st RV due date then RV 2 adjusted against first payment received from customer. | |||||||||||||
| 4. Payment received from customer sorted based on posting date. | |||||||||||||
| 5. Payment received from Customer is not tagged with RV, they just send the payment and based on FIFO and Due date of RV it got adjusted by us. | |||||||||||||
| 6. Interest Rate 10% considered for interest caculation. | |||||||||||||
| 7. Payment adjustment done on customer wise only means Customer A payment adjusted against Customer A billing only. | |||||||||||||
| 8. If partial payment received from customer then based on FIFO first RV settled first and till exhaust of first RV or balance left after first RV adjustment, secound RV comes into picture and balance left if any from 1st RV excess payment adjusted against secound RV, like/eg Re 1 excess received on dated 05 Jan 2025 above to be adjusted agaisnt next RV. | |||||||||||||
1 Reply
Does this exist as an actual spreadsheet? As it's presented here it's more confusing that necessary; you'd help us help you by greater clarity, and the underlying spreadsheet would be a big step in that direction. Please attach it to your reply.
