Solving for IRR without using a column

[Davidns]

Is there a way to use a formula, rather than a column of data, to solve IRR wherein the variables are something like:

Payment Amount, # of occurrences
Payment Amount, # of occurrences
ie:
$200 for 5 months
$300 for 7 months
225 for 4 months

Thanks

 

[Eric W]

IRR requires at least 1 negative and 1 positive value. The negative usually is first, with the following values being the income. So you could do something like this:

Book1
	A	B	C	D
1	Cost	Income	# of months	IRR
2	$ (500.00)	$ 200.00	5	29%
3	$(1,000.00)	$ 300.00	7	23%
4	$ (750.00)	$ 225.00	4	8%
Sheet6

Cell Formulas
Range	Formula
D2:D4	D2	=IRR(IF(SEQUENCE(C2+1)=1,A2,B2))

 

[Davidns]

IRR requires at least 1 negative and 1 positive value. The negative usually is first, with the following values being the income. So you could do something like this:

Book1
	A	B	C	D
1	Cost	Income	# of months	IRR
2	$ (500.00)	$ 200.00	5	29%
3	$(1,000.00)	$ 300.00	7	23%
4	$ (750.00)	$ 225.00	4	8%
Sheet6

Cell Formulas
Range	Formula
D2:D4	D2	=IRR(IF(SEQUENCE(C2+1)=1,A2,B2))

Thanks but not sure I am following you. In my use case, the sequence will always be an initial negative number (my loan amount), followed by a series of payments, such as:

-3000 loan amount
200 7 times
300. 5 times
250. 4 times

How would your formula work in this case?
Thanks!

 

[StephenCrump]

Like this perhaps?

	A	B	C
1		Amount	No
2	Principal	-3,000	1
3	Repayments	200	7
4		300	5
5		250	4
6
7	IRR	3.1%	per month
Sheet1

Cell Formulas
Range	Formula
B7	B7	=IRR(TOCOL(IFS(SEQUENCE(,MAX(C2:C5))<=C2:C5,B2:B5),2))

 

[Eric W]

I came up with the formula below, but Stephen's is much better! Go with that one.

Book1
	A	B	C
1	Amount	Count	IRR
2	$(3,000.00)	1	3.06%
3	$ 200.00	7
4	$ 300.00	5
5	$ 250.00	4
6
Sheet6

Cell Formulas
Range	Formula
C2	C2	=LET(r,COUNT(A2:A20),s,SUM(B2:B20),sq,SEQUENCE(r,,0),lu,SUBTOTAL(9,OFFSET(B1,0,0,sq+1)),sl,SEQUENCE(s,,0),si,XLOOKUP(sl,lu,OFFSET(A2,0,0,r),,-1),IRR(si))

 

[Davidns]

wow, you guys are really good. Could never have done this on my own (and even Perplexity said it is not possible). Will play around with it later today. Thank you very much. Truly appreciated!