Use Excel to solve a complex algebraic equation?

[Mr. Snrub]

I am working on calculating money-weighted rate of return for my portfolio. I have to solve the following equation for r:

$298,082 = ($250,000 * (1 + r)) + ($25,000 * (1 + r)^((365 - 258)/365)))

...which simplifies to:

$298,082 = ($250,000 * (1 + r)) + ($25,000 * (1 + r)^(0.293150685))

Is there an easy way to do this in Excel? I am using Excel 2016.

 

[BadgerRoad]

I am working on calculating money-weighted rate of return for my portfolio. I have to solve the following equation for r:

$298,082 = ($250,000 * (1 + r)) + ($25,000 * (1 + r)^((365 - 258)/365)))

...which simplifies to:

$298,082 = ($250,000 * (1 + r)) + ($25,000 * (1 + r)^(0.293150685))

Is there an easy way to do this in Excel? I am using Excel 2016.

One easy way is to use Goal Seek.

 

[Dave Patton]

Goal Seek would work.

 

[Mr. Snrub]

Um, okay. Could I have more details, please?

 

[oldbrewer]

r=	1						$298,082 = ($250,000 * (1 + r)) + ($25,000 * (1 + r)^(0.293150685))
					looking for 0 in this column
			r plus	0	232550.8
				1	482550.8
				5	1482551
				10	2732551
				20	5232551
				50	12732551
				100	25232551
				500	1.25E+08
				1000	2.5E+08
				5000	1.25E+09
				20000	5E+09
				50000	1.25E+10
			this first guess shows r is less than 0
			so plug in a guess into B1

<colgroup><col span="14"></colgroup><tbody>
</tbody>

 

[oldbrewer]

r=	-50000						$298,082 = ($250,000 * (1 + r)) + ($25,000 * (1 + r)^(0.293150685))
					looking for 0 in this column
			r plus	0	#NUM!
				1	#NUM!
				5	#NUM!
				10	#NUM!
				20	#NUM!
				50	#NUM!
				100	#NUM!
				500	#NUM!
				1000	#NUM!
				5000	#NUM!
				20000	#NUM!
				50000	#NUM!
			so plug in a guess into B1
			ooops maybe the formula logic is wrong

<colgroup><col span="15"></colgroup><tbody>
</tbody>

 

[Dave Patton]

What did you try?
Make A5 to 298020 by changing A2

See result in adjacent column

Excel 2010
	A	B	C	D
1	250,000.00			250,000.00
2	5.00%			8.98%
3	25,000.00			25,000.00
4	0.293150685			0.293150685
5	287,860.14			298,082.00
6
2a

Cell Formulas
Range	Formula
A5	=A1*(1+A2)+A3*(1+A2)^A4
D5	=D1*(1+D2)+D3*(1+D2)^D4

 

[shg]

Another way, without Goal Seek:

	A​	B​	C​	D​	E​
1​	1/1/2017​	($250,000)​		A1: Input	B1: Input
2​	9/16/2017​	($25,000)​		A2: =A1 + 258	B2: Input
3​	1/1/2018​	$298,082​		A3: =A1 + 365	B3: Input
4​	rate​	8.98%​		A4: Input	B4: =XIRR(B1:B3, A1:A3)

 

[shg]

Maybe more clearly,

	A​	B​	C​
1​	0​	($250,000)​
2​	258​	($25,000)​
3​	365​	$298,082​
4​	rate​	8.98%​	B4: =XIRR(B1:B3, A1:A3)