Using MMULT to find optimal Debt-to-Equity Ratio

[ThatExcelLife]

Hey guys!

I am trying to build out a MMULT + MINVERSE formula to automatically solve for an optimal debt to equity ratio.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Input (optimal ratio)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debt[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Equity[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Debt-to-equity Ratio[/TD]
[TD].5[/TD]
[/TR]
</tbody>[/TABLE]


Currently, my debt to equity ratio is .5, or 1/2. What I am trying to do is get the ratio to 1 (this would be the input that I can adjust to whatever I want).

There are two ways this can be done. I can increase the debt by 1, so it would be 2/2 = 1. The other option is I can add .5 to debt and minus .5 to equity, or 1.5/1.5 = 1. I am trying to find a formula that can solve the latter automatically. This is easy to do with goalseek but I am trying to do it without always going back to goalseek constantly. Its an issue of having two unknowns. Is there I can work MMULT (MINVERSE....) but utilizing the .5 and the 1?

Thanks, everyone!

 

[Oaktree]

I don't think you need anything that fancy... just some algebra.

Let x = the amount you move from equity to debt, d = debt, e = equity, and r = ratio:

(d+x)/(e-x) = r
(d+x) = r(e-x)
d+x = re - rx
d+x + rx = re
x + rx = re - d
x(1+r) = re - d
x = (re - d)/(1 + r)

In your example, x (the amount to add to debt and subtract from equity) = (1*2 - 1)/(1+1) = 1/2 = 0.5.

 

[ThatExcelLife]

Ugh you are so right. I dont know what I am thinking this was a difficult problem that only an advanced formula can solve.

I followed your theory and it worked! I feel like a fool. Thanks for responding, @Oaktree!!