Forum Discussion
Excel invoce aging
You've had a fair number of views but no replies to date. I suspect that people are passing for the same reason: your request is too vague.
Is it possible for you to spell out some rules for the situation you're describing. You give a couple instances, but no framework for actually constructing the formula. So here are some questions for you to answer that might start pointing a potential helper in the right direction.
- Are you the recipient of the invoice, or the creator of it..sending it to someone else? Said another way, are you trying to figure out how much YOU have to pay to satisfy a contract, or are you creating an invoice telling somebody else?
- Is it a monthly invoice, so you want to track history of payments, balance still due? (Which again, raises the importance of knowing whether you're the one doing the paying or the one being paid.)
- Is there any interest charged? Does the interest change if there's non-payment, or less-than-a-minimum paid?
It's entirely possible that if you define your situation more clearly you will be able to write the formula (or formulas) yourself.
But the main point: the more clearly and precisely you can describe the situation, the more clearly and precisely somebody else can resolve it.
Hello mathetes and thanks for reply.
I'm really sorry for vague request.
I'm creator of invoice, and i want to send mail to customer if he is late with payment.
For example: i sell TV to customer and i give him to pay that TV for 5 months with equal installments, and there is no interest charged.
I know how to calculate without installments with today, if(and)...but i need formula for aging when i have information like in picture below with installment amount and number of installments, and all that in one row, without making rows as much as i have number of installments.
Is it possible for you to post the actual spreadsheet (with dummy names such as you did in that image)?
And if I may ask yet another question or two, now that I know you are generating the invoice based on your spreadsheet:
- Do you generate an actual Invoice document (perhaps in PDF) from your spreadsheet?
- Or do you just write an email that says, in effect, please pay XXX.XX by DD/MM/YYYY?
- How do you track the history of what payments have been made and when?
At one point my wife was doing consulting with a couple organizations, and we had to prepare monthly invoices for her clients. I created a database into which she entered hours worked each day and some brief description of the nature of the work, so that we could generate an itemized invoice. All this from within Excel.
Your need is different in some ways, but also similar in some ways. What you've shown me is just one row of a spreadsheet; I'd be interested in what the rest of it looks like, whether you're making optimal use of Excel--for tracking the history of a customer's account over time--or just using it to do some relatively simple calculations growing out of a single transaction.