1. Registered User
Join Date
Sep 2008
Posts
151

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.

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. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,101
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?

3. Registered User
Join Date
Sep 2008
Posts
151
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. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,101
The interest rate becomes a negative number because 126500/84 > 1000.

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

6. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,101
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.

7. Registered User
Join Date
Sep 2008
Posts
151
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
•