Payment Plan

[Blake0920]

Trying to figure out a way where I can create a table that is approx 18 months in length that will calculate payment plans. The customer has either 3,6,9, or 12 months to pay.

I wanted to make a formula that would allow for the sale price to be divided by the payment plan and then added to Feb-22, Mar-22, Apr-22 and so on.

How do I have that payment replicate 12/9/6/3 times once it has been entered?

 

[etaf]

very simple solution below, something like

But you would need to put your spreadsheet on here using XL2BB - if you need something specific to your layout

also a lot of templates online free for excel

Month-pay-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Amount	Pay No	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23
2	123000	3	41000	41000	41000	0	0	0	0	0	0	0	0	0	0
3	123000	6	20500	20500	20500	20500	20500	20500	0	0	0	0	0	0	0
4	123000	9	13666.6667	13666.6667	13666.6667	13666.6667	13666.6667	13666.6667	13666.6667	13666.6667	13666.6667	0	0	0	0
5	123000	12	10250	10250	10250	10250	10250	10250	10250	10250	10250	10250	10250	10250	0
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=A2/B2
D2:O2	D2	=IF(COUNTA($C$2:C2)+1<=$B2,$A2/$B2,0)
D3:O3	D3	=IF(COUNTA($C$3:C3)+1<=$B3,$A3/$B3,0)
D4:O4	D4	=IF(COUNTA($C$4:C4)+1<=$B4,$A4/$B4,0)
D5:O5	D5	=IF(COUNTA($C$5:C5)+1<=$B5,$A5/$B5,0)

 

[Blake0920]

Okay that makes a lot more sense now. I am going to work on this for a bit.

My next question is that I am trying to figure out how may people are on each payment plan but I am getting 1 extra value each time I create the formula. For example for the month of Feb-22 there are 3 people on the 3mo payment plan BUT this formula is calculating as 4 people. =COUNT((Orders!C4:C71)*(Orders!I4:I71="3M"))

 

[Blake0920]

I have also tried using this formula but it doesn't seem to like it. =SUBTOTAL(3,Orders!C4:C71)+COUNT(Orders!I4:I71="3M")

 

[etaf]

ok, not easy to see in a photo attachment , or use - can you give a sample sheet with examples and expected results using XL2BB - see menu or my signature
or on a share like onedrive/dropbox

 

[Blake0920]

For whatever reason my excel program is not allowing for the XL2BB

 

[etaf]

or on a share like onedrive/dropbox

 

[Blake0920]

Here is a better screenshot.

Don't want to share on onedrive/dropbox - personal information of clients on it

 

[etaf]

take all the private info out , so you can share - XL2BB would also have private info
- its still difficult with that picture and also means i have to type all that into a sheet to then help , quite time consuming
It does not explain what result you are expecting or why
Sorry if i'm coming across difficult , just trying to help as effectively as i can

 

[Blake0920]

No i appreciate your help.

I found the solution. =SUMPRODUCT(--ISTEXT(I4:I71)*(Orders!A4:A71>="02")*(Orders!A4:A71<="03")*(I4:I71="3M"))