For a lot of homebuyers, calculating a prospective mortgage payment is an online experience.  For example, a search on Google for “mortgage calculator” returns 39 million options.

Some people, however, prefer to plan on their local hard drive using spreadsheets.  For these people, the hardest part is often figuring out what formulas to use.

Interest Only Payments

Home loans with interest only payments are much more simple to calculate than amortizing loans.

Using the graphic at right as a guide, enter your loan size and your interest rate into two separate spreadsheet cells.

Then, create a third cell and input the following formula that calculates the “Monthly Payment”.  The formula is:

= (Loan Size) * (Interest Rate) / 12

Principal + Interest Payments

For a home loan with (principal + interest) payments, the formula is a little bit more complicated than with an interest only home loan.

Using the graphic at right as a guide, enter your loan size, your interest rate and the duration of your home loan into three separate spreadsheet cells.

Then, create a fourth cell and input the following formula that calculates the “Monthly Payment”.  The formula is:

= – PMT(Interest Rate/12, Loan Term in Months, Loan Size)

For additional spreadsheet formulas and more in-depth reporting, explore your software’s “Help” feature to see what you can find.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s