|
|
|
|
|
|
|
|
|
|
Excel Mortgage Payment FormulaA walk through for the PMT function in Excel which allows you to calculate monthly repayments on a mortgage. 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
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 Source: Free Articles from ArticlesFactory.com
ABOUT THE AUTHORChester 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.
|
|||||||||||||||||||||||||||||||||||||||||
Partners
|
