+ Reply to Thread
Results 1 to 4 of 4

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. #1
    Registered User
    Join Date
    01-17-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Unhappy 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

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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
    Last edited by 63falcondude; 01-17-2019 at 04:01 PM. Reason: Welcome

  3. #3
    Registered User
    Join Date
    01-17-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    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

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

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 01-15-2019, 03:58 AM
  2. Replies: 6
    Last Post: 10-08-2015, 09:44 PM
  3. [SOLVED] Userform Trying to Add Find & Edit button same like Excel Find All
    By John Cruz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2014, 11:43 PM
  4. Excel FIND/SEARCH Find Name Hours Worked
    By staceynix in forum Excel General
    Replies: 8
    Last Post: 02-01-2013, 01:39 AM
  5. Replies: 4
    Last Post: 08-27-2012, 11:08 PM
  6. Find Macro With Userform - To find a data across multiple tabs in excel
    By Testify in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-15-2012, 04:49 PM
  7. [SOLVED] Despite data existing in Excel 2002 spreadsheet Find doesn't find
    By AnnieB in forum Excel General
    Replies: 1
    Last Post: 06-15-2006, 09:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1