Project future revenue

[Len Silva]

Folks, I can't even conceptualize how to do this, any help is appreciated.
This is a donor database which will extend to 10 years (120 months) and 3-400 rows. The goal is to populate the monthly cells with the projected amount. I have manually filled in the first five rows.
So, row 2 is a $4000.00 pledge paid annually in $1000.00 increments beginning 6/18 for 4 years
Row 2 is a $20.00 pledge paid monthly in $5.00 increments, beginning 1/18 for 4 months
Row 4 is a $210.00 pledge paid monthly in $10.00 increments, beginning 8/17 for 21 months

I don't even know where to start or if it is even possible.
Thank you!

[AB33]

How do you know when a project ends? You need to include either the contract period-end or the legnth of the contact from a start date.

[Len Silva]

That would come from the pledge Amt, Frequency, period Amt and start date. For example, Row 2 would be Pledge Amt/Per Amt= 4 payments and freq is annual so 48 months after the start date (Jun-22) with a payment every June. (Last day of the month is assumed).
Row 3 would be 20/5 or 4 payments beginning Jan-18 and ending Apr-18
My problem is how to identify the date cells and how to push the payment amount into the proper cells. That's something I've never tried to do before.

[shg]

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Tot Amt	Freq	Amt/Per	Start	DoNotUse	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18
2	5,000	12	1,000	1/1/2017		1,000												1,000
3	2,600	1	100	3/1/2017				100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
4	4,000	12	1,000	8/14/2017									1,000
5	10,000	12	2,000	11/1/2017												2,000
6	900	12	450	12/18/2017													450
7	40,000	12	10,000	12/30/2017													10,000
8	20	1	5	1/1/2018														5	5	5	5
9	4,000	12	1,000	6/20/2018																			1,000
10	210	1	10	8/5/2018
11	100	12	100	8/5/2018
12	800	3	200	8/17/2018
13	300	12	30	9/1/2018
14	725	1	50	10/5/2018
15	225	1	25	10/25/2018

In I2,

=IFERROR(IF(MOD(DATEDIF(EOMONTH($G2, -1) + 1, I$1, "m"), $E2) <> 0, "", MAX(0, MIN($D2 - SUM($H2:H2), $F2))), "")

[Len Silva]

Thank you so much, I learned a lot. I didn't know about DATEDIF. I don't understand the use of the empty column $H2:H2.
In my file, the formula is placing "0" in all the cells after the last value, can you help me with that?

[shg]

I don't understand the use of the empty column $H2:H2.

It's used to total prior amounts.

In my file, the formula is placing "0" in all the cells after the last value, can you help me with that?

Format those cells as #,##0;-#,##0; to suppress display of zeros.

This is more rigorous; it calculates payments made prior to the first date:

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Tot Amt	Mos Betw	Amt/Per	Start	Prior	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	5,000	12	1,000	1 Jan 2017	1,000											1,000
3	2,600	1	100	1 Mar 2017	1,200	100	100	100	100	100	100	100	100	100	100	100	100	100	100
4	4,000	12	1,000	14 Aug 2017	0						1,000
5	10,000	12	2,000	1 Nov 2017	0									2,000
6	900	12	450	18 Dec 2017	0										450
7	40,000	12	10,000	30 Dec 2017	0										10,000
8	20	1	5	1 Jan 2018	10	5	5
9	4,000	12	1,000	20 Jun 2018	0				1,000
10	210	1	10	5 Aug 2018	0						10	10	10	10	10	10	10	10	10	10
11	100	12	100	5 Aug 2018	0						100
12	800	3	200	17 Aug 2018	0						200			200			200			200
13	300	12	30	1 Sep 2018	0							30
14	725	1	50	5 Oct 2018	0								50	50	50	50	50	50	50	50
15	225	1	25	25 Oct 2018	0								25	25	25	25	25	25	25	25

H2: =IFERROR(MIN($D2, INT(DATEDIF($G2 - DAY($G2) + 1, $I$1, "m") / $E2) * $F2), 0)

I2: =IFERROR(IF(MOD(DATEDIF($G2 - DAY($G2) + 1, I$1, "m"), $E2) <> 0, "", MIN($D2 - SUM($H2:H2), $F2)), "")

[Len Silva]

One more thing if you can. I would like the quarterly payments to only come due in months 3,6,9,12, regardless of the start date.
Thanks again, this will be a big help.

[shg]

No, but you can change the start dates to be in months 3/6/9/12.

[Len Silva]

Thanks again.