Excel Mortgage Amortization Formulas

 

5 Excel Mortgage Amortization Formulas

 

 

 

Article by Jed Jones

 


  

 

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.

That's it! Download a FREE Excel cheat sheet at: www.Untangle-Your-Thoughts.com

Grab a Copy of My Excel - Hands On Guide!

Microsoft Excel Guide

Microsoft Excel Functions And Formulas Guide

Discover more about using Functions and Formulas - CLICK HERE!

 

-----------------------------------------------------------------------------------------------------------

Trouble Free Computing?




"Discover The Simple But Powerful Secrets To Keeping Out Viruses, Hackers, Trojans, Keyloggers And Many Other Online Security Threats"

Expert PC security advice from a long standing Microsoft MVP!


CLICK HERE - For More Information Now!

-----------------------------------------------------------------------------------------------------------

>>> My FREE Windows Newsletter! >>>

 

Free-Report

Claim YOUR Fortnightly copy of my FREE Windows Newsletter covering:

Windows XP, Windows Vista, Windows 7 , Microsoft Office and Windows Live Services - Sign-up TODAY!!!

 
Name:
Email:
 

Your Details are secure - we never pass them on to anyone else!

Privacy Policy

Enjoy!

Kind Regards

Marc Liron

Marc Liron - Microsoft MVP (2004 - 2010)
www.marcliron.com
www.twitter.com/marcliron
http://blog.marcliron.com

 

Links

Trouble Free Computing?

 

PC Security Bootcamp

"Discover The Simple But Powerful Secrets To Keeping Out Viruses, Hackers, Trojans, Keyloggers And Many Other Online Security Threats"

Expert PC security advice from a long standing Microsoft MVP!

Get YOUR Copy NOW!

--------------------

 

 

Related Articles:

XLS Value Error

Spreadsheet Value Error

Excel If Value

Understanding Excel Dates and Times

Excel 2003 Vs Excel 2007 - What's New in Excel 2007

Calculate the Coefficient of Variation in Excel

How to Wipe and Clean Excel Spreadsheets Thoroughly

Calculate Weighted Average in Excel