Results 1 to 7 of 7

Thread: Goal Seek Issue

  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Goal Seek Issue

    Hello,

    I am wondering if someone can see what the heck I am not doing correctly using the Goal Seek to find out how low the interest rate has to be for a monthly payment to be $1,000.

    As you can see, the interest rate in the “Before Goal Seek” table is 4.99% and after I performed a Goal Seek function to change my monthly payment to $1,000 the interest rate becomes a negative number.

    I have tried everything and I don’t know what I am doing wrong. Please help.

    Before Goal Seek

    selling price: $156,500
    down payment: $30,000
    loan amount: $126,500
    interest rate: 4.99%
    terms in years: 7
    length of loan in years: 15
    monthy payment: $1,787.35

    After Goal Seek

    selling price: $156,500
    down payment: $30,000
    loan amount: $126,500
    interest rate: -10.83%
    terms in years: 7
    length of loan in years: 15
    monthy payment: $1,000.00


    FORMULA VIEW

    selling price: 156500
    down payment: 30000
    loan amount: =B7-C7
    interest rate: -0.108318983764876
    terms in years: 7
    length of loan in years: 15
    monthy payment: =PMT(E7/12,84,-D7)

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Just a thought, never having used Goal Seek or the PMT function, but what happens if you remove the negative symbol in front of D7 within the function?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Hi Weejas,

    I tried that and it turns the interest rate to 10.83% (which is too high) and changes the monthly payment to $2,154.82. I am trying to find a monthly payment of $1,000 by changing getting a low interest rate. :-(

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The interest rate becomes a negative number because 126500/84 > 1000.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Sep 2008
    Posts
    150
    Where am I going wrong? I thought I calculated the PMT correctly and the goal seek also.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The point is that you want to repay $126,500 in 84 installments of $1,000. 84*$1,000 = $84,000, which is less than the amount you want to repay. Therefore, the only interest rate that meets your needs is a negative one - one that will also work to reduce the amount being repaid.
    If you want to repay $1,000 a month, you need to increase the number of months to at least 127.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Sep 2008
    Posts
    150
    Awwwww. Got you. Thank you soooo much for the explanation. Now I have something to re-examine. :-D

Posting Permissions

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