Subscriber numbers with different payment plans

[CTMom]

Am doing a financial projection for app startup. Will be one price for an annual subscription but it can be paid in 4 different ways:
1. $3000 in full upfront
2. $1000 per month for 3 months
3. $500 per month for 6 months

I need to model revenue based on subscriber sign ups for each payment type. There may be say 50% selection Option 1, 25% for Option 2 etc.

I am calculating revenue based on new subscribers for the month but i know this is not accurate as each subscriber pays 3 monthly payments then stops.

Appreciate any help with working out best way to do this.
Thanks
(could not get the add-in to work so just copied and pasted!)

PAY OVER 3 MONTHS		1	2	3	4	5	6	7	8	9	10	11	12
			Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25
Starting number subscribers	0.0	5.0	10.0	15.0	20.0	25.0	30.0	35.0	40.0	45.0	50.0	55.0
New subscribers in mth	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0
Churn	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Closing no	5.0	10.0	15.0	20.0	25.0	30.0	35.0	40.0	45.0	50.0	55.0	60.0
Revenue	$1,000	5,000	10,000	15,000	20,000	25,000	30,000	35,000	40,000	45,000	50,000	55,000	60,000

 

[Jeffrey Mahoney]

I think I understood you question. See if this is the right direction

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			$3,000
2	Up Front:	One Payment	$3,000
3	3 Months:	Three Month Installment	$1,000
4	6 Months:	Six Month Installment	$500
5
6			Jan 2024	Feb 2024	Mar 2024	Apr 2024	May 2024	Jun 2024	Jul 2024	Aug 2024	Sep 2024	Oct 2024	Nov 2024	Dec 2024	Jan 2025	Feb 2025
7		One Payment New Suscribers	1	5	6	7	2	8	3	4	5	10	1	6	9	4
8		One Payment Revenue	$3,000	$15,000	$18,000	$21,000	$6,000	$24,000	$9,000	$12,000	$15,000	$30,000	$3,000	$18,000	$27,000	$12,000
9		Three Month Installment New Suscribers	0	5	6	7	2	8	3	4	5	10	1	6	9	1
10		Three Month Installment Revenue	$0	$5,000	$11,000	$18,000	$15,000	$17,000	$13,000	$15,000	$12,000	$19,000	$16,000	$17,000	$16,000	$16,000
11		Six Month Installment New Suscribers	0	5	6	7	2	8	3	4	5	10	1	6	9	2
12		Six Month Installment Revenue	$0	$2,500	$5,500	$9,000	$10,000	$14,000	$15,500	$15,000	$14,500	$16,000	$15,500	$14,500	$17,500	$16,500
13		Closing no	1	5	6	12	8	15	10	18	15	16	14	19	14	15
14		Revenue	$3,000	$22,500	$34,500	$48,000	$31,000	$55,000	$37,500	$42,000	$41,500	$65,000	$34,500	$49,500	$60,500	$44,500
Sheet1

Cell Formulas
Range	Formula
C2	C2	=C1
C3	C3	=C1/3
C4	C4	=C1/6
D6:P6	D6	=EDATE(C6,1)
C8:P8	C8	=C7*PayType1Cost
C10	C10	=C9*PayType2Cost
D10	D10	=SUM(C9:D9)*PayType2Cost
E10:P10	E10	=SUM(C9:E9)*PayType2Cost
B7	B7	=PayType1Desc&" New Suscribers"
B8	B8	=PayType1Desc&" Revenue"
B9	B9	=PayType2Desc&" New Suscribers"
B10	B10	=PayType2Desc&" Revenue"
B11	B11	=PayType3Desc&" New Suscribers"
B12	B12	=PayType3Desc&" Revenue"
C12	C12	=C11*PayType3Cost
D12	D12	=SUM(C11:D11)*PayType3Cost
E12	E12	=SUM(C11:E11)*PayType3Cost
F12	F12	=SUM(C11:F11)*PayType3Cost
G12	G12	=SUM(C11:G11)*PayType3Cost
H12:P12	H12	=SUM(C11:H11)*PayType3Cost
C13:C14,D14:P14	C13	=SUM(C11,C9,C7)
D13	D13	=SUM(D7)
E13:G13	E13	=SUM(E7,C9)
H13:P13	H13	=SUM(H7,F9,C11)

Named Ranges
Name	Refers To	Cells
PayType1Cost	=Sheet1!$C$2	C8:P8
PayType1Desc	=Sheet1!$B$2	B7:B8
PayType2Cost	=Sheet1!$C$3	C10:P10
PayType2Desc	=Sheet1!$B$3	B9:B10
PayType3Cost	=Sheet1!$C$4	C12:P12
PayType3Desc	=Sheet1!$B$4	B11:B12

 

[CTMom]

Hi Jeffrey, thanks but not sure that's quite there. How do I account for the fact that each new subscriber on a payment plan only pays for either 3 or 6 months then stops? The actual numbers look like this (but need formula to do this properly!):

New subscribers p/mth	5
Payment plan	3	mths
mth number	1	2	3	4	5	6	7	8	9	10	11	12
mth 1	5	5	5
mth 2	5	5	5
mth 3	5	5	5
mth 4	5	5	5
mth 5	5	5	5
mth 6	5	5	5
mth 7	5	5	5
mth 8	5	5	5
mth 9	5	5	5
mth 10	5	5	5
mth 11	5	5
mth 12	5
TOTAL	5	10	15	15	15	15	15	15	15	15	15	15