Question about Monthly Mortgage payment and asking price

I am currently reading “the Weekend Millionaire’s Secrets to Investing in Real Estate” (one of many books I am reading). One part of the book has a chart that shows:

Amounts You can Pay Off with a $750 Mortgage Payment

Rate % 15 Year 20 Year 25 Year
3 $108,604 $135,233 $158,157
4 $101,394 $123,766 $142,089
5 $ 94, 841 $113,644 $128,295

I am looking to make this chart in Excel where I can adjust the monthly Mortgage Payment and it would adjust the figures under 15, 20, 25

I am wondering if anyone knows the formula used to get these numbers like
108,604*3% = interest of loan (IOL), IOL+Principle=Total, Total divided by 180 gives monthly payment… but in excel that equals $621.45 and not $750 so I know I am missing something.

Any help would be appreciated.
David

The formula I use is explained at http://www.moneychimp.com/articles/finworks/fmmortgage.htm

basically,

Payment = [M(1 + I/12)^A * I/12] / [(1 + I/12)^A - 1]

M = total mortgage amount
I = interest rate
A = total number of months to amortize over (360 for a 30 year loan)

If you want to know how much you can afford on a specific payment, solve the equation for M and fill in the blanks.

Nice and simple! :smiley:

Funny thing is I found that web site and from it I came up with

Mortgage Amount = [P*(1+I/12)^A-1]/[(1+I/12)^A*I/12

So that way if I want to set my Monthly payments at $750 (like example below) and I know length of time (A) and Interest (I) I can come up with range of Mortgage.

Thank you TJGirl for the assistance ;D