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.

Input (optimal ratio)	1
Debt	1
Equity	2
Debt-to-equity Ratio	.5

<tbody>
</tbody>

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!!