Can you calculate mortgage amortization tabes?

So I’m (hopefully) buying a house and I’m confused by the amortization tables. Is there a simple equation to calculate what the monthly payment would be for a 30yr fixed loan? As opposed to having to look it up for every case in a table. I want to make an excel sheet that will calculate payments depending on amount of loan, interest rate, and points, but I don’t know how they calculate the required amount of principle that’s paid on the first months payment.

Is there a straightforward calculation?


It is rather complex.

Actually I wrote 2 calculators for my website that will do it for you: and

The first one gives a yearly summary and the second gives a month by month detail.
Hey. it’s free.

Okay, if you just want to know the monthly payment then go here:

Want to see the formula with a worked out example?
Go here:

Excel has those formulas built-in. They’re FV, PV, IPMT, NPER, and PMT.

I’m not sure what you’re asking with the “required amount of principle that’s paid on the first month’s payment.”

There are dozens of free sites online that will easily and quickly calculate a mortgage payment.

Here’s one site Some lender ads pop up along with the calculation and amort table but nothing too obnoxious.

If you want to do it the hard way

How to calculate amortization tables by hand

Probably best to use someone else’s calculator - IMHO, the terminology used by financial types tends to get pretty obscure. Still, if you have too much time on your hands, I think the correct Excel formula is:


Check the Excel docs for more details. Warning - if you’re using monthly periods, the interest per period is AnnualRate / 12. Otherwise, you get really strange results. Which is to say - use someone else’s calculator.

Is PosterChild maybe trying to figure out the down payment to achieve a given monthly payment?

Thanks for the replies. I’d found the online calculators, but I wanted to have all the other numbers I’m playing with calculated, too (points, remaining savings) and have the different percentage loan results all visible on one sheet.

wolf_meister: your equation was very helpful. I came across a more detailed version here that works out to be the same as yours.

CurtC: I’d tried looking up amortization and mortgage in Excel help and couldn’t find those, but they also look like the same equation- thanks.

Civil Guy: That’s basically it- trying to play with the downpayment and other variables to see what my monthly would be- and looks I may now be able to do it. :slight_smile:


Here is a good site with multiple calculators (and the main site is also quite helpful):