# How do you ask excel to find x such that f(x,y,z)=b where y,z and b are set by you

1. ## How do you ask excel to find x such that f(x,y,z)=b where y,z and b are set by you

Hi all, am new here so forgive if I'm posting in the wrong section of website !

How could you ask excel to find x such that f(x, y) = b

I mean this for a fixed b and y
A simple example of this would be if you were using the multiplication function:
f(x, y) = x*y =b
And you set inputs
Y = 5
b =40

So you want to ask excel what value x has to be to make this true (in this case the answer is x = 8 )
i.e Find x such that x*5 = 40

My question is quite general but specifically I came across this problem when using the PMT function
For those of you who don't know, PMT tells you things like what your monthly payment would have to be to payback a loan over a given amount of time periods with a specific interest rate

For example, if you borrowed 1000 from the bank at a 4.5% annual interest rate, and wished to pay this back monthly over a 20 year period.
Then in this case the monthly interest rate is 0.375%, the number of periods is 240 months, and the present value is £1000

so pmt(0.375%,240,1000) returns -6.33 which means you'd have to pay back £6.33 each month for 20 years to pay back the loan at a 4.5% annual rate

BUT what if I wanted to know what the interest rate would have to be in order to allow me to pay back £6.33 a month for 20 years on a £1000 loan

Here I'd want to ask excel:
Find x such that PMT(x,240,1000)= -6.33

which would return 0.375%

Is there a way to do this please? My question is not necessarily specific to this function but I'd just like to know how to solve problems like this.

Thank you for your help   Register To Reply

2. ## Re: How do you ask excel to find x such that f(x,y,z)=b where y,z and b are set by you

Hello and welcome to the forum.

Try using Goal Seek.

A2: blank
B2: 240
C2: 1000

E2: =PMT(A2,B2,C2)

Now select E2 > Data > What-IF Analysis > Goal Seek
Set cell: E2
To value: -6.33
By changing cell: A2
OK  Register To Reply

3. ## Re: How do you ask excel to find x such that f(x,y,z)=b where y,z and b are set by you

I'm no longer on Excel computer but will try this tomorrow. Thanks for your reply have a nice day  Register To Reply

4. ## Re: How do you ask excel to find x such that f(x,y,z)=b where y,z and b are set by you

Solver and/or goal seek are fairly general, numerical algorithms for this kind of problem that can work in most cases. As with any numerical algorithm there will be scenarios where the algorithm fails or finds the wrong answer.

My preference when possible is to solve the problem more directly. In your first case, I would prefer to solve the problem algebraically. b=x*y solve for x is x=b/y. In the case of the PV/FV type problems, use the appropriate function for the desired unknown. Rather than use goal seek on the PMT() function, I would use the RATE() function to find the interest rate. For your example =RATE(240,-6.33,1000) would return 0.376%.

I guess I don't have a single strategy for solving all problems. I take what I know about the problem and my programming environment and figure out a suitable strategy on a case by case basis. Usually, numeric algorithms like goal seek/Solver are last on the list.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 