Convert a comma separated string of values to an array of values for MIRR function

[K Sandman]

Greetings Excel Wizards,
Long-time (years) lurker here and am finally stumped with a question I can't find a solution for anywhere in the forum.

I am trying to utilize the MIRR() function with a dynamic number of periods without building a values table. For the 'values' argument in the function I want to be able to repeat the value in B2 for a specified number of years. My thought was to use the Rept() function, but it returns a string of comma separated values instead of an array of values. This results in a #Value ! error.

Here is the bugger I've been trying to make work: =MIRR((B1&REPT(","&B2,B4-1)&","&B3),B5,B6).

Seems like it should be simple enough, but I'm at a wall. Below is a demonstration table with all the inputs I'll be using in my workbook. I'll clarify that this formula will be filled-right for similar inputs in columns C,D,etc so it needs to be able to work with relative references.

Any help is much appreciated!

[TABLE="class: grid, width: 191"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Initial Investment[/TD]
[TD="align: right"]-10000[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Period Returns[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Exit Period[/TD]
[TD="align: right"]9000[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]# years[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Discount Rate[/TD]
[TD="align: right"]5.50%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Reinvest Rate[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]MIRR[/TD]
[TD="align: center"]#Value ![/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the posting side of the forum

I don't know if it's the best way, but if I've understood correctly then here is one possible way.

See here for a description of the technique used to parse the comma separated list.

http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

Excel 2013/2016
	A	B
1	Initial Investment	-10000
2	Period Returns	1000
3	Exit Period	9000
4	# years	5
5	Discount Rate	6%
6	Reinvest Rate	5%
7	MIRR	6%
Sheet1

Cell Formulas
Range	Formula
B7	=MIRR(0+MID(SUBSTITUTE(B1&REPT(","&B2,B4-1)&","&B3,",",REPT(" ",99)),ROW(INDEX(B:B,1):INDEX(B:B,B4+1))*99-98,99),B5,B6)

 

[K Sandman]

@FormR - I have verified that your solution works exactly as I had intended. Many thanks good sir! I have to say even after reading the explanation and slowly stepping through & evaluating the formula, it's going to take me a bit before I can implement that solution on my own. Here's to you.

 

[FormR]

Hi, I thought about this a bit more and I think this is better (more robust & more efficient) . Pay attention to the array formulas note.

Excel 2013/2016
	A	B
1	Initial Investment	-10000
2	Period Returns	1000
3	Exit Period	9000
4	# years	5
5	Discount Rate	6%
6	Reinvest Rate	5%
7	MIRR	6.23%
Sheet1

Cell Formulas
Range	Formula
B7	{=MIRR(CHOOSE(ROUNDUP((ROW(INDEX(B:B,1):INDEX(B:B,B4+1))-1)/(B4-1),0)+1,B1,B2,B3),B5,B6)}
Press CTRL+SHIFT+ENTER to enter array formulas.