Calculate payment months covered

[tyty123]

hey,

I have a forecast of payments to be made, with the totals to be paid for the months listed in row 11, the dates are in row 2 (=EDATE(AM2,1)) formatted (mmm-yy)

I currently have the balance in B16

The following formula calculates the total to be paid in the current month.

=HLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),AJ2:PU11,10,FALSE)

What I would like is the formula to tell me how many months i can cover based on the value in B16 (after every payment the balance is reduced)

it would have to find the current month and then subtract it from the balance, then take the new balance and subtract it from the next month until there is insufficient balance then return the number of full payments that can be made.

This is as close as i got, but it only returns the value not the number of payments. its also not dynamic, i've input +1 to account for future dates but id like it to keep searching until insufficient thus not limiting it to 3 payment cycles:

=B16-HLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),AJ2:PU11,10,FALSE)-HLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),AJ2:PU11,10,FALSE)-HLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())+3,1),AJ2:PU11,10,FALSE)



any ideas?

Thanks

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[tyty123]

forecast

i cannot download the XL2BB but this is my formula, id like to know how many months can be paid with b16 or b17 (prefer b16)

 

[Fluff]

Thanks for that, how about

forecast.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				Jan-1	Feb-1	Mar-1	Apr-1	May-1	Jun-1	Jul-1	Aug-1	Sep-1	Oct-1	Nov-1	Dec-1	Jan-1
2					$16,729.10			$16,729.10			$16,729.10			$16,729.10
3												$27,916.91
4				$1,227.01			$1,227.01			$1,227.01			$1,227.01			1227.0102
5				$11,698.31			$11,698.31			$11,698.31			$11,698.31			11698.3137
6				$94.50			$94.50			$94.50			$94.50			94.5
7						$10,000.00
8								$10,000.00
9											$7,000.00
10			Totals for month	$13,020	$16,729	$10,000	$13,020	$26,729	$0	$13,020	$23,729	$27,917	$13,020	$16,729	$0	$13,020
11
12	Balance	$32,000.00
13	Projected Soft required for april	13,020.00
14	surplus	$18,980.00
15	Months we are able to cover:	2.00
Sheet1

Cell Formulas
Range	Formula
D1	D1	=DATE(2023,1,1)
E1:P1	E1	=EDATE(D1,1)
B13	B13	=HLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),D1:AA10,10,FALSE)
B14	B14	=B12-B13
B15	B15	=XMATCH(0,SCAN(B12,D10:AA10,LAMBDA(a,b,a-b)),1)

 

[tyty123]

wow this is amazing! but is there a way to make it for only the current month and going forward? or a way to make the array dynamic?

 

[tyty123]

wow this is amazing! but is there a way to make it for only the current month and going forward? or a way to make the array dynamic?

sorted now! his is how i did it =XMATCH(0,SCAN($B$16,INDEX($AJ$11:$BG$11,1,MATCH(TODAY(),$AJ$2:$BG$2,1)):BG11,LAMBDA(a,b,a-b)),1)


the range/array are different from the sheet but thank you very much

 

[Fluff]

If you only want it from this month onwards, you could also use

=XMATCH(0,SCAN(B12,FILTER(D10:AA10,D1:AA1>EOMONTH(TODAY(),-1)),LAMBDA(a,b,a-b)),1)