10Aug2013
Dan Green
Author
Dan Green
Filed Under
Essential Mortgage Miscellany

Make Your Own Mortgage Payment Calculators Using Spreadsheet Formulas In Microsoft Excel, Mac Numbers, and Google Docs

Mortgage rates and markets change constantly. Stay 100% current by taking The Mortgage Reports by email each day. Click here to get free email alerts, or subscribe to the RSS feed in your browser.

Mortgage payment formulas for popular spreadsheet programs

Are you the "spreadsheet type"? If you are, do your own mortgage math within Microsoft Excel, Mac Numbers and/or Google Docs. All you need are some formulas and mortgage rate to add to the mix.

Click for a personalized mortgage rate to use in your formulas.

Mortgage Calculator Formula : Principal + Interest Calculation

Mortgage payment spreadsheet formula : Principal + Interest

A standard, amortizing mortgage payment is comprised of two parts -- principal reduction and interest repayment. Amortizing loans are the most common loan type, and apply to 30-year fixed loans, 15-year fixed loans, and home equity loans.

Finding the monthly (principal + interest) payment on an amortizing loan is among the simplest of the spreadsheet mortgage formulas. You'll need to know 3 pieces of information about your loan, and how to assign those values to the formula's variables.

The formula's variables are :

  • Periodic Rate : Your mortgage rate divided by 12
  • Number of Periods : Your loan term (in months)
  • Present Value : Your starting loan size

The standard mortgage payment formula also contains two extra variables, you'll notice -- Future Value and When Due. Both should be equal to 0. This tells the spreadsheet that the home will eventually be paid off to $0, and that interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell into which you're trying to solve, or (2) in the formula bar at the top of your spreadsheet.

Also, make sure to add a negative (-) in front of the formula.

Get a personalized mortgage rate to use in your formulas.

Mortgage Calculator Formula : Principal Paid In A Given Month

Mortgage payment formula - Find principal paid in a given month

With an amortizing loan, the amount of principal included in your payment starts off small, then gets bigger over time. If you've ever noticed how your loan balance is barely dented after 5 years of payments, this is why. It's amortization at work.

Whether your mortgage is VA loan, conforming loan, an FHA loan or USDA, amortization schedules are decidedly bank-friendly. This is just the way that mortgages work.

Take the 30-year fixed rate mortgage, for example. With the 30-year fixed, it takes 18 years before your mortgage payment pays more principal than interest. Prior to that 18th year, your monthly payment is more than 50% interest payments.

To calculate how much principal you're paying in any given month, you'll need to know 4 pieces of information about your loan, and how to assign those values to the formula's variables.

The formula's variables are :

  • Periodic Rate : Your mortgage rate divided by 12
  • Period : The month for which you are solving
  • Number of Periods : Your loan term (in months)
  • Present Value : Your starting loan size

The principal payment formula also contains two extra variables -- Future Value and When Due. Both should be equal to 0. This tells the spreadsheet that the home will eventually be paid off to $0, and that interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell into which you're trying to solve, or (2) in the formula bar at the top of your spreadsheet, and make sure to add a negative (-) in front of the formula.

In the example shown, the homeowner's first mortgage payment contains $395.06 of principal paydown.

Get a personalized mortgage rate to use in your formulas.

Mortgage Calculator Formula : Interest Paid In A Given Month

Mortgage Formula : Calculate Interest Paid In A Given Month

Interest charges are the "other half" of your monthly mortgage payment; the portion not covered by principal. But unlike principal payments which increase over time, interest charges fall over time.

You pay less interest toward the end of a loan than you do at its start.

To calculate how much interest you're paying in any given month, you'll need to know 4 pieces of information about your loan, and how to assign those values to the formula's variables.

The formula's variables are :

  • Periodic Rate : Your mortgage rate divided by 12
  • Period : The month for which you are solving
  • Number of Periods : Your loan term (in months)
  • Present Value : Your starting loan size

The interest payment formula contains the same two extra variables -- Future Value and When Due. Both should be equal to 0. This tells the spreadsheet that the home will eventually be paid off to $0, and that interest on your mortgage will be paid in arrears.

Place this formula either (1) in the cell into which you're trying to solve, or (2) in the formula bar at the top of your spreadsheet, and make sure to add a negative (-) in front of the formula.

In the example shown, the homeowner's first mortgage payment contains $1125.00 in interest charges. If we add this to the principal payment of $395.06 solved for in Formula #2, we're left with a payment of $1,520.06 -- the exact figure solved for at the top of the page.

Find More Mortgage Calculator Spreadsheet Formulas

Whether your spreadsheet of choice is Microsoft Excel, Mac Numbers, or Google Docs, you can always find mortgage formulas to help you "do the math".

And, because most spreadsheets have a built-in formula browser, it's easy to plug-and-play to solve for what you need. You'll just need a live mortgage rate to plug into your math. You can get that online, for free.

Get a personalized mortgage rate to use in your formulas.

About the Author

Dan Green is a mortgage market expert, providing over 10 years of direct-to-consumer advice. NMLS #1019791. You can also connect with Dan on Twitter and on Google+.

The information contained on The Mortgage Reports website is for informational purposes only and is not an advertisement for products. The views and opinions expressed herein are those of the respective authors and do not reflect the policy or position of Full Beaker, its officers, parent, or affiliates.