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

H

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
 
B

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?
 
V

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
 
V

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top