Goal Seek on PMT function give different answer than a mortgage ca

[headly]

Calculating mortgage payments in Excel using the PMT function, is this wrong?
When I use the calculator on any mortgage broker site I get a different
answer to a simple question: What amount could I borrow to result in a $1,000
monthly payment. Excel's goal seek give $190K and others say $160K, why such
a big discrepency? TIA

 

[Bernard Liengme]

Show us the formulas and values you used.

 

[bpeltzer]

It's tough to offer a definitive answer given the limited info (what formula
did you use, for instance), but I'm going to venture that any broker would
consider your payment to include 'P&I,T&I': principal, interest, taxes, and
insurance, plus any home-owner association dues. The PMT function calculates
only the loan payment (principal and interest). Since the PMT function
calculates only a portion of the total monthly payment, it would suggest that
you can afford to borrow more.
Does the broker's site break down that $1000 payment to show the components?

 

[vandenberg p]

: Calculating mortgage payments in Excel using the PMT function, is this wrong?
: When I use the calculator on any mortgage broker site I get a different
: answer to a simple question: What amount could I borrow to result in a $1,000
: monthly payment. Excel's goal seek give $190K and others say $160K, why such
: a big discrepency? TIA

 

[vandenberg p]

Hello:

Sorry, pushed the wrong key, at the wrong time.

For this problem you don't Goal Seek, just use the Present Value function.
The Present value of the payments is equal to amount you owe, it is a "law
of finance." So in A1 to B5 enter:
(the numbers are in B1 to B5 if it does not line up)

Payment 1000
Annual Interest Rate 0.05
Years 30
Frequency of Payment 12
Present Value ($186,281.62) <---=PV(B2/B4,B3*B4,B1)

Pieter Vandenberg

: : Calculating mortgage payments in Excel using the PMT function, is this wrong?
: : When I use the calculator on any mortgage broker site I get a different
: : answer to a simple question: What amount could I borrow to result in a $1,000
: : monthly payment. Excel's goal seek give $190K and others say $160K, why such
: : a big discrepency? TIA