Excel Mortgage Amortization Formulas – 5 Useful Formulas
Author: weissheiss
Excel spreadsheets represent a very convenient way for you to calculate important details about your current mortgage. You can also use them to look at different mortgage scenarios, including whether you are considering refinancing your current mortgage or shopping for a new home.
There are a number of different formulas, depending upon which variables you know and which ones you want to find out.
5 Excel Mortgage Amortization Formulas
Here are 5 Excel mortgage amortization formulas. (note: see the Variable Key below to understand the meaning of each variable used in these formulas):
1. To find out how much interest would be paid over a certain number of months, use this formula:
=ISPMT(rate,per,nper,pv)
2. To calculate the mortgage payment amount, use this:
=PMT(rate,nper,pv)
3. To figure out the number of payments that will be made, use this one:
=NPER(rate,pmt,pv)
4. To get the total amount paid in interest between two given months, use:
=CUMIPMT(rate,nper,pv,n1,n2,0)
5. To find out the total amount paid in principal between two given months, try:
=CUMPRINC(rate,nper,pv,n1,n2,0)
Variable Key
rate: interest rate per period (note: multiple the interest rate number by 12 if you are making monthly payments, which is the case for most loans. For example, a 6% interest rate would be expressed as 6%*12 in the formula.)
n1, n2: specific period in terms of ordinal month number; for example, plugging a 1 for n1 and a 15 for n2 into the CUMIPMT formula would tell you the total interest paid during the time between month 1 and month 15
per: specific period of time, usually expressed in terms of number of months
nper: the number of payment periods (note: since most payments are made in terms of months, to calculate nper you will want to multiple the number of years in question by 12)
pv: the present value of the loan (i.e. the loan amount) (note: do NOT use commas in the loan value figure)
pmt: the payment amount per period, which is most commonly the monthly payment (note: express in dollars, but do NOT use commas in this figure)
Running these 6 formulas in Excel should allow you to run pretty much any mortgage scenario you want to consider.
Posted in Amortization Calculator | 
Tags: Amortization Formulas, Excel Spreadsheets, Loan Value, Mortgage Scenarios, N1 N2, Period Of Time, Present Value, Variables