MS Excel: Goal Seek or Algebra

Let's start with a question. I want to pay a business through a payment processor such as PayPal. I want to send it $120 but I know the processor will charge it both a percentage fee as well as a fixed fee. So, the question becomes what amount should I have the processor charge me so that the recipient receives $120. Mathematically, what the recipient gets is

Paid_to_Recipient = Amount_charged*(1-Percent_fee_rate)-Fixed_fee        

and, I want to know what should be the Amount_charged.

We can use MS Excel's Goal Seek utility to calculate it.

One weakness of a utility such as Goal Seek is that it is dependent on the initial approximation provided by the user. A poor initial approximation for a complex problem could lead to an suboptimal result or even a failure to find a solution (*)

Of course, if the relationship between the goal and the 'by changing' cell is one that we can invert through algebra, we should do so and directly answer our question. The above formula, once rewritten becomes

Amount_charged = (Pay_To_Recipient + Fixed_fee)/(1-Percent_fee_rate)        

Now, change the value in cell C12 and immediately see the result in cell C13.

Goal Seek is a powerful utility particularly useful when a relationship is not "invertible." One example of this is in calculating the interest rate for a loan with fixed recurring payments (say, monthly payments). Excel's RATE function is similar to Goal Seek when it comes to computing the interest rate - in particular, the last argument to the function is a 'guess' value.

Do you use Goal Seek? Where do you find use for it? Would you like to learn more about it? Please share your ideas and comments in the discussion of this article.

(*) Goal Seek falls into the category of analytical tools called Numerical Analysis. The basic idea is to reach a numerical solution to a problem starting with an initial approximation and then iteratively improving the approximation until there is no further improvement. For more see the Newton–Raphson and associated methods: Newton's method - Wikipedia

My previous article: Calendar for the New Year | LinkedIn