Spreadsheet Formulas: Calculating Home Payments
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.
Start the discussion by leaving a comment