Avoiding a Circular Reference

**robotlust** · 05-06-2019, 06:45 AM

Hi,
I often run a calculaton on a loan structure that calculates all the fees associated with the transaction. When we lend money we provide an Advance to somebody and then all the fees are capitalized into the Facility.
The current process is: take a guess at the Facility, enter the fees, then go to the resultant Advance and goal seek that field to the $$ being advanced to the borrower. This is backwards.
I want to enter the Advance, then the fees are worked out from there. The issue with this is that all the fees are calculated from the Facility.

I've atttached a spreadsheet where it's pretty clear how I want to calculate it. Currently we're doing in the Backwards format and I want to reverse this.
I dont know VBA, but from what I do know is that a forumla cannot handle this.

**MrShorty** · 05-06-2019, 10:27 AM

A formula will do this just fine, and VBA should not be necessary. Before this is a programming problem, it needs to be an algebra problem. If y is facility and x is advance, then it looks like the problem is to solve for y given:

y=x+y*0.02+y*0.0175+y*0.0153+3300 -> combine all the y's together
y-y*0.02-y*0.0175-y*0.0153=x+3300 -> solve for y

Once you have solved for y, the desired Excel formula should be pretty simple. If you need an algebra refresher: https://www.purplemath.com/modules/solvelin3.htm
Your algebra might be rusty, but let us know how you get on.