I have a calculation to work out the monthly rental for a vehicle based on the amount borrowed from the lender, and the price of the car after a certain mileage and period.
I cant figure out to put all the logic into this question as its based in a spreadsheet, so I have posted it here....
https://docs.google.com/spreadsheets/d/1xm1tH0DHrBA06NZhcd2rFQ78IT4Q0Wjpr6ltYfYh4Gk/edit?usp=sharing
The problem is....
The Total Rental is currently edited manually up and down until the Monthly Surplus reaches £50.
What we want, is to get the Total Rental automatically, by manually editing the Monthly Surplus field. The problem with this, is that the Total to Be Funded is based on a subtraction of 3 times the Monthly Rental. And the total to be funded is needed to work out the Monthly Rental, so creating a circular dependency.
Can anyone figure out a way around this?
Update:
In a separate sheet (now editable by anyone), we have the monthly rental being automatically calculated (massive thanks to @tilper), but we find ourselves at another circular dependency once we need to take away the deposit from the total amount funded.
My client does not want to borrow the deposit from the lender if the customer is paying it upfront anyway. This means the client can borrow less from the lender, and pass the savings onto the customer.
The remaining issue is that in this sheet....
B7 needs to take away D25 , but cant because the result in D25 is calculated (way up the chain) from B7.
We can just reverse engineer it based on the formulas for the cells.
The formula for cell $B30$ (Monthly Surplus) is $B30 = B29/24$, where cell $B29$ is the Total Surplus. This equation can be rewritten as $B29 = B30 \cdot 24$. This tells us how to find the Total Surplus from the Monthly Surplus.
Note the current Total Surplus formula in cell $B29$. It's $B29 = B28 - B27$. Let's look at the formulas for cells $B27$ and $B28$.
Cell $B25$ is the Total Rental. This is the one you want to generate automatically. Yes, I see you also want to generate $D25$ automatically but that will happen once we get $B25$, because the formula for $D25$ already depends on $B25$.
Anyway, from the second bullet point above we get $B25 = B28 / 29$. So this tells us how to find $B25$ using $B28$. But how do we get $B28$?
Well, we know that $B29 = B28 - B27$. Therefore, $B28 = B29 + B27$.
And that's it!
To summarize with step-by-step instructions:
We now have a circular dependency with $B25$. We can resolve this by changing the formula in $B7$. Unravel it by following the formulas:
\begin{align} B7 &= B1 - B3 - B5 - \color{red}{B25} \cdot 6\\ \color{red}{B25} &= \color{blue}{B28}/29\\ \color{blue}{B28} &= B29 + \color{green}{B27}\\ \color{green}{B27} &= \color{orange}{B17} \cdot 29 + B19 + B20 + B21 + B22\\ \color{orange}{B17} &= B13 + B15\\ &= B11 / 0.03608 + B9/0.00514\\ &= (B7 - B9) / 0.03608 + B9/0.00514 \end{align}
Here are the major equalities from the above unraveling:
\begin{align} B7 &= B1 - B3 - B5 - B25 \cdot 6\\ B25 &= B28/29\\ B28 &= B29 + B27\\ B27 &= B17 \cdot 29 + B19 + B20 + B21 + B22\\ B17 &= (B7 - B9) / 0.03608 + B9/0.00514 \end{align}
Now just do a lot of messy back-substitution: \begin{align} B17 &= (B7 - B9) / 0.03608 + B9/0.00514\\ B27 &= \color{orange}{B17} \cdot 29 + B19 + B20 + B21 + B22\\ &= \color{orange}{\left((B7 - B9) / 0.03608 + B9/0.00514\right)} \cdot 29 + B19 + B20 + B21 + B22\\ &= 803.76940133 (B7 - B9) + 5642.0233463 B9 + B19 + B20 + B21 + B22\\ &= 803.76940133 B7 + 4838.25394497 B9 + B19 + B20 + B21 + B22 \end{align}
That was one step of the back-substitution. The next step is: \begin{align} B27 &= 803.76940133 B7 + 4838.25394497 B9 + B19 + B20 + B21 + B22\\ B28 &= B29 + \color{green}{B27}\\ &= B29 + \color{green}{803.76940133 B7 + 4838.25394497 B9 + B19 + B20 + B21 + B22}\\ &= B29 + B19 + B20 + B21 + B22 + 803.76940133 B7 + 4838.25394497 B9\\ \end{align}
Luckily that wasn't as messy. The rest aren't that bad also. Next: \begin{align} B28 &= B29 + B19 + B20 + B21 + B22 + 803.76940133 B7 + 4838.25394497 B9\\ B25 &= \color{blue}{B28}/29\\ &= \color{blue}{(B29 + B19 + B20 + B21 + B22 + 803.76940133 B7 + 4838.25394497 B9)}/29\\ &= \frac{1}{29}(B29 + B19 + B20 + B21 + B22) + 27.71618625 B7 + 166.83634293 B9 \end{align}
And finally: \begin{align} B25 &= \frac{1}{29}(B29 + B19 + B20 + B21 + B22) + 27.71618625 B7 + 166.83634293 B9\\ B7 &= B1 - B3 - B5 - \color{red}{B25} \cdot 6\\ &= B1 - B3 - B5 - \color{red}{\left[\frac{1}{29}(\cdots) + 27.71618625 B7 + 166.83634293 B9\right]} \cdot 6\\ &= B1 - B3 - B5 - \frac{6}{29}(\cdots) + 27.71618625 B7 + 166.83634293 B9 \end{align} I'm using the $\cdots$ in the parentheses above instead of fully writing out $B29 + B19 + B20 + B21 + B22$ because otherwise the equations would wrap to the next line and be harder to read. Anyway, now we have the following equation: $$ B7 = B1 - B3 - B5 - \frac{6}{29}(B29 + B19 + B20 + B21 + B22) + 27.71618625 B7 + 166.83634293 B9 $$
Solve this for $B7$ just like you'd solve any other linear equation in one variable. Then you'll have a new formula you can put in cell $B7$ and it won't have circular dependencies. I should point out that I haven't really verified that I didn't make any arithmetic mistakes. There's a chance the numbers may be off, but this is the process you'll want to follow to remove the dependency.
A couple things I wanted to note:
=SUM(B1-B3-B5-(B25*6)). The "SUM" here makes no sense. It's better to just say=B1-B3-B5-(B25*6). Note that the parentheses around $B25*6$ are also not needed but aren't really harmful or a bad idea.=B33/1.2*0.9. This gets interpreted as $\dfrac{B33}{1.2} \cdot 0.9$, and not $\dfrac{B33}{1.2 \cdot 0.9}$, because of order of operations. Please verify this is the intended result. I point this out only because I've seen this error more times than I can count.