Excel Formula to Calculate Payback Period

[acool]

Hi Everyone,

I am currently trying to create an excel formula which calculates the Payback Period (Months) based on a a Total Purchase Amount of $4.8M using the inputs A7:C7. While I would normally use a sum product to multiply monthly unit sales by Contribution margin divide that by the total purchase amount to get to the payback period , this differs in the fact that their is a limited number of months on hand for each of the products. For Example, Product #1 only has 8 months of units on hand, and can therefore only have contribution margin dollars for 8 Months. I have exemplified this idea in the table below which breaks down at which month CM for each of the products. I am curious if there is a way to incorporate this idea into a simplified excel formula. Based on the more manual table created below, the payback period should be 10 months. Any help would be greatly appreciated. Thank You!

 

[StephenCrump]

One option would be a simple Goal Seek:

Book6
	A	B	C
1
2	6,300	8	$23.15
3	5,200	9	$5.55
4	4,800	13	$36.90
5	5,500	14	$6.65
6	2,100	24	$39.65
7	3,250	25	$14.90
8
9		Months	1.0
10		Amount	520,090
Sheet1

Cell Formulas
Range	Formula
C10	C10	=SUMPRODUCT(IF(B2:B7<N,B2:B7,N),A2:A7,C2:C7)

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$C$9	C10

--->

6,300	8	$23.15
5,200	9	$5.55
4,800	13	$36.90
5,500	14	$6.65
2,100	24	$39.65
3,250	25	$14.90
	Months	9.9
	Amount	4,833,000

 

[felixstraube]

How about?:

=LET(
s;A2:A7*C2:C7;
m;B2:B7;
ms;SEQUENCE(;MAX(m));
res;s*(ms<=m);
VSTACK(ms;IF(res<>0;res;""))
)

 

[acool]

How about?:

=LET(
s;A2:A7*C2:C7;
m;B2:B7;
ms;SEQUENCE(;MAX(m));
res;s*(ms<=m);
VSTACK(ms;IF(res<>0;res;""))
)

@felixstrabue I am getting an error code. Is this excel VBA code?

 

[acool]

One option would be a simple Goal Seek:

Book6
	A	B	C
1
2	6,300	8	$23.15
3	5,200	9	$5.55
4	4,800	13	$36.90
5	5,500	14	$6.65
6	2,100	24	$39.65
7	3,250	25	$14.90
8
9		Months	1.0
10		Amount	520,090
Sheet1

Cell Formulas
Range	Formula
C10	C10	=SUMPRODUCT(IF(B2:B7<N,B2:B7,N),A2:A7,C2:C7)

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$C$9	C10

View attachment 103549
--->

6,300	8	$23.15
5,200	9	$5.55
4,800	13	$36.90
5,500	14	$6.65
2,100	24	$39.65
3,250	25	$14.90
	Months	9.9
	Amount	4,833,000

@StephenCrump Thank you this works! However, do you know if it is possible to divide the total purchase value by your sumproduct formula to get the desired payback months. I would like to have this automatically update if the purchase value in H1 changes. Thank you both for your help so far!

 

[felixstraube]

It is just a excel formula. but sorry the ";" should be ",". This should work, but i'm not sure that it is what you wan't:

=LET(s, A2:A7 * C2:C7, m, B2:B7, ms, SEQUENCE(, MAX(m)), res, s * (ms <= m), VSTACK(ms, IF(res <> 0, res, "")))

 

[acool]

It is just a excel formula. but sorry the ";" should be ",". This should work, but i'm not sure that it is what you wan't:

=LET(s, A2:A7 * C2:C7, m, B2:B7, ms, SEQUENCE(, MAX(m)), res, s * (ms <= m), VSTACK(ms, IF(res <> 0, res, "")))

This is great too! This helps to autopopulate the table at the bottom which I had to create manually. This was actually my next question, so thanks for providing this. However, I am currently trying to determine the number of months achieve the total purchase price with the knowledge that some of the products have a limited number of months on hand. For example, you will only be able to recognize contribution margin dollars for the first 8 months. After those 8 months I would no longer like that product to be included in the calculation. So G1/(Sumproduct(A2:A7,C2:C7) BUT only for the max number of Months on hand

 

[StephenCrump]

I would like to have this automatically update if the purchase value in H1 changes. Thank you both for your help so far!

You could trap the value change in H1 with Worksheet_Change code, and run the Goal Seek from VBA.

Or continuing with @felixstraube's approach, and using the layout in Post #2, use a formula:

=XMATCH(C11,SCAN(0,BYCOL((B2:B7>=SEQUENCE(,MAX(B2:B7)))*(A2:A7*C2:C7),LAMBDA(c,SUM(c))),LAMBDA(a,b,a+b)),1)

 

[acool]

Thank You Both for your Help!