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