Chester Tugwell is a freelance Microsoft Office trainer and owner of Blue Pecan Computer Training based in Sussex, UK. He provides a comprehensive set of Excel training courses as well as other Microsoft Office training options. More free computer training materials are available via the Blue Pecan website.
1. Mortgage repayment calculations are possible is Excel using the PMT function. The PMT function has the following arguments:
Rate – this is interest rate on the mortgage loan divided by 12
Nper – this is the term of the mortgage or the number of monthly repayments you will make. For example with a 25 year mortgage you would make 12 multipled by 25 monthly repayments.
PV (present value) - is the mortage amount – the amount you have borrowed, expressed as a negative value.
FV - you can leave blank. FV stands for future value. As the future of the loan when it has be repaid will be zero and zero is the default for this argument it can be left empty.
Type – here you state whether you will make the payment at the beginning or at the end of each month, type 1 if at the beginning or 0 if at the end. Sorry no option for halfway through the month.
2. An example. John takes out a £250,000 mortgage over 25 years with an annual interest rate of 4.5%. He will make his mortgage payment on the 1st of each month.
Rate would be 4.5%/12
Nper would be 25*12
PV would be -250000
Type would be 1
3. To practice this example in Excel, in a blank spreadsheet enter the following data starting in A1.
i) Rate goes in A1, 4.5% goes in B1 and so on for each row.
Rate 4.5%
Term 25
Mortgage 250000
Repayment
ii) Click into cell B4 – this is where we will calculate the monthly repayment
iii) Now click on the fx button on the Excel formula bar just above the spreadsheet’s column headers. This will open the Insert Function dialogue box. In the search box type PMT and then click Go. Select PMT from the results list below and then click OK
In the Rate box type B1/12
In the Nper box type B2*12
In the Pv box type –B3
Leave the FV box empty
In the Type box type 1
iv) Click OK Your answer should be 1384.39
- Related Videos
- Related Articles
- Ask / Related Q&A
- Secure Your Property By Regular Mortgage Payments
- The No Chance For Foreclosure Method to Calculate a Mortgage Payment
- Changing Monthly Mortgage Payments
- Absolute Best Way to Calculate a Mortgage Payment
- Should You Buy Mortgage Payment Protection From Your Lender?
- Lowering Your Monthly Mortgage Payments
- Mortgage Payment Protection and More
- Mortgage Payment Behind? Some Things To Consider
Global Product Data Management (PDM) Software Market
By: Bharat Book Bureau | 02/01/2010Bharatbook.com added a new report on "Global Product Data Management (PDM) Software Market - 2008-2012" which create a single repository for all the information about a company's products and services.
Software Development at Its Real Best
By: Arun Kumar | 02/01/2010The demand for software has increased in such a manner that if the developers stop working for a day, the entire world of business would surely come to a cease. That’s where the present day software development solutions providers strike the chord playing the tunes of their profit.
Why Ares is better choice for download music, movie, DVD?
By: Ares | 02/01/2010Ares download is becoming one of the good choice for downloading movie, music and DVD movie today. The reason is this because it is very user friendly. User can easily download without any spyware and adware. No third site service required to download. User can categorized their downloading files easily.
Easy Option to Convert Contacts from .CSV to PST
By: Sys Tools | 02/01/2010There are many people who make use of MS Excel to store their contacts and MS Outlook email client to send/receive emails. SysTools Excel to Outlook Software can transfer contacts from Excel to Outlook. The software provides a unique feature – to export contacts from .CSV to Outlook PST. Now, if you make use of .CSV format in Excel to store your email contacts, then you can now very easily import CSV contacts to Outlook with the help of this software.
Career Development Centre: Guiding you towards a right career track
By: HCL Career Development Centre | 02/01/2010Career Development has become a primary responsibility and an essential requirement for every individual in any organization. A profound career development skill can help an individual to meet the constantly changing needs of the market. For more information visit: www.hclcdc.in
What RFID Healthcare Software Can Do For Your Office
By: Tom Gruich | 01/01/2010Whether your office is large and busy or small and growing, making use of RFID tracking and the latest healthcare software can improve your office manager’s ability to track assets, ensure patient safety and control access to patient records.
Satellite Direct TV Software Review - TV On The Computer
By: Rose Daly | 01/01/2010Watching satellite TV on your personal computer has truly come a long way over the past year. There’s no wires, no additional hardware and to top it all you don’t have to be a computer expert, all you need is software and you can now enjoy watching your favorite shows. One of the best TV to PC software available online is Satellite Direct TV Software that will allow you to watch all your favorite TV channels such as sports, premium movie channels, international TV shows and many other genres.
Free Live TV On Internet - Satellite Direct TV Software
By: Rose Daly | 01/01/2010Free live TV on Internet has a lot of benefits and advantages. You don’t have to bother purchasing expensive flat screen TV, LCD or Plasma just to watch your favorite shows. Now it is possible to enjoy your favorite TV shows plus so much more via the Internet and over your very own personal computer.
Creating a User Defined Function in Excel using VBA
By: Chester Tugwell | 10/07/2009 | SoftwareExcel includes many different functions that help you complete calculations, but have you ever wished for a function that that doesn’t exist in Excel? If you have, this tutorial will explain how to create a function DIY style.
Excel SUMIFS Function
By: Chester Tugwell | 03/07/2009 | SoftwareSUMIFS is a function that first appears in Excel 2007. You may have previously used SUMIF to sum up values in a list based on criteria in another column. SUMIFS works the same way but allows you to use multiple criteria either in different columns or the same column.
Create A Drop Down List In Excel
By: Chester Tugwell | 03/07/2009 | SoftwareDrop down lists are a great addition to a worksheet making data more efficient and also restricting what values can be entered into a cell.
Excel Mortgage Payment Formula
By: Chester Tugwell | 22/06/2009 | SoftwareA walk through of the PMT function in Excel which allows you to calculate monthly repayments on a mortgage.
Tips and Tricks for working with Microsoft Access Reports
By: Chester Tugwell | 18/06/2009 | DatabasesA selection of tips and tricks for working in Microsoft Access reports. Not all tips will be relevant to all versions of Access
Tips and Tricks for working with Microsoft Access Forms
By: Chester Tugwell | 18/06/2009 | DatabasesA selection of tips for working with Microsoft Access Forms. Not all tips will be relevant to all versions of Access.
Displaying Parameter Values in a Microsoft Access Report
By: Chester Tugwell | 18/06/2009 | DatabasesIf you use parameter queries and then base reports on those queries this tutorial is for you. It will show you to display the parameter values that have been used in the heading of the report.