Payment Terms in Excel

[BryanF]

I was wondering if anyone knew of a way to create a spreadsheet that would allow someone to enter an invoice date and have Excel automatically calculate payment terms of 2% 10th net 25?

Example: Customer is invoiced 7/24. They must pay by 8/10 to get the 2% discount. Payment is due in full on 8/25. But if a customer is invoiced on 7/27 they must pay 9/10 to get a 2% discount and payment is due in full by 9/25.


Can Excel automate this process?

 

[DRSteele]

I think I can help. I believe this works, though others may have a more elegant solution.

Excel 2012
	A	B	C
1	net	25
2	discount	10
3
4	Invoiced	Discount eligible	Payment Due
5	7/24/2015	8/10/2015	8/25/2015
6	7/27/2015	9/10/2015	9/25/2015
7	9/12/2015	10/10/2015	10/25/2015
8	11/26/2015	1/10/2016	1/25/2016
9	12/31/2015	2/10/2016	2/25/2016
Sheet11

Cell Formulas
Range	Formula
B5	=IF(DAY(A5)<=$B$1,DATE(YEAR(EDATE(A5,1)),MONTH(EDATE(A5,1)),$B$2),DATE(YEAR(EDATE(A5,2)),MONTH(EDATE(A5,2)),$B$2))
B6	=IF(DAY(A6)<=$B$1,DATE(YEAR(EDATE(A6,1)),MONTH(EDATE(A6,1)),$B$2),DATE(YEAR(EDATE(A6,2)),MONTH(EDATE(A6,2)),$B$2))
B7	=IF(DAY(A7)<=$B$1,DATE(YEAR(EDATE(A7,1)),MONTH(EDATE(A7,1)),$B$2),DATE(YEAR(EDATE(A7,2)),MONTH(EDATE(A7,2)),$B$2))
B8	=IF(DAY(A8)<=$B$1,DATE(YEAR(EDATE(A8,1)),MONTH(EDATE(A8,1)),$B$2),DATE(YEAR(EDATE(A8,2)),MONTH(EDATE(A8,2)),$B$2))
B9	=IF(DAY(A9)<=$B$1,DATE(YEAR(EDATE(A9,1)),MONTH(EDATE(A9,1)),$B$2),DATE(YEAR(EDATE(A9,2)),MONTH(EDATE(A9,2)),$B$2))
C5	=IF(DAY(A5)<=$B$1,DATE(YEAR(EDATE(A5,1)),MONTH(EDATE(A5,1)),$B$1),DATE(YEAR(EDATE(A5,2)),MONTH(EDATE(A5,2)),$B$1))
C6	=IF(DAY(A6)<=$B$1,DATE(YEAR(EDATE(A6,1)),MONTH(EDATE(A6,1)),$B$1),DATE(YEAR(EDATE(A6,2)),MONTH(EDATE(A6,2)),$B$1))
C7	=IF(DAY(A7)<=$B$1,DATE(YEAR(EDATE(A7,1)),MONTH(EDATE(A7,1)),$B$1),DATE(YEAR(EDATE(A7,2)),MONTH(EDATE(A7,2)),$B$1))
C8	=IF(DAY(A8)<=$B$1,DATE(YEAR(EDATE(A8,1)),MONTH(EDATE(A8,1)),$B$1),DATE(YEAR(EDATE(A8,2)),MONTH(EDATE(A8,2)),$B$1))
C9	=IF(DAY(A9)<=$B$1,DATE(YEAR(EDATE(A9,1)),MONTH(EDATE(A9,1)),$B$1),DATE(YEAR(EDATE(A9,2)),MONTH(EDATE(A9,2)),$B$1))

 

[BryanF]

Thank you so much! I will give this a try.