Saturday, 8 April 2017

How To Calculate Loan Payment Using The Pmt Function In Ms Excel

The PMT function is one of the MS Excel’s built in function used to compute the periodic amount that will be used to pay off a loan either on daily, weekly, monthly or yearly basis. The PMT function is used in Loan Amortization or payment analysis and helps to know the total amount you have to pay back when you borrow a loan from any financial institution. The PMT function syntax is:
=PMT(Rate, NPer, PV, FV, Type) then press the Enter Key
learn how to calculate loan amortization using the MS Excel's PMT function
The PMT function takes five argument of which the last two are optional.
Where:
Rate is the percentage interest charged on the principal or amount you borrowed,
NPer is the number of period or duration of the loan,
PV is the present value, that is the principal amount you planned to borrow,
FV is the future value and it is optional likewise the Type argument.

This is part 1 of chapter 6 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to calculate loan payment using the PMT function in a spreadsheet software like Microsoft Excel 2016, 2013 and other lower versions.

In the previous chapter, I explained How To Calculate Depreciation Charges Using The 3 Most Common Methods in MS Excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series.

WHAT IS LOAN AMORTIZATION OR PAYMENT?

Loan Amortization is the act of paying a certain amount of money regularly in order to offset a loan over a period of time (daily, weekly, monthly or yearly basis). The PMT function is used to calculate the exact amount of money to be paid regularly in order to amortize or pay back a loan including the interest and other accrued charges within that period. 
You can also adjust the PMT function to suite your payment interval which can be daily, weekly, monthly or even yearly.

For example, to calculate the monthly payment that will amortize a 25 years mortgage of $500000 with an interest of 21%:

LOAN PAYMENT CALCULATION STEPS USING THE PMT FUNCTION IN MS EXCEL

These are the steps you need to take n order to calculate the monthly pay, Total pay and Bank Profit on a loan uing the PMT function before you conclude borrowing money from any financial institution.

Step 1:

You will first determine the monthly interest by dividing the 21% interest rate by 12 (21 / 12) which is the number of months in a year. It will you a monthly rate of 1.75%.

Step 2:

Next step is to convert the number of periods into months by multiplying the 25 years by 12. It gives you 300 months.

Step 3:

Finally, you input the values into the PMT function as follows:
PMT(1.75%, 300, 500000) then press the Enter key.

It returns a value of $8798.31. This means that you will be paying $8798.31 at the end of every month for 300 months (25 years).

USING MS EXCEL'S WORKSHEET FOR THE CALCULATION:



In Microsoft Excel worksheet, you can arrange the data in a worksheet and then further calculate the Total pay and Bank profit. Enter the data as seen in the excel worksheet shown below.
Loan amortization worksheet in excel
Enter the following formulas in the appropriate cells as specified below:

For the Monthly pay; in cell B4, type: =PMT(B2/12, B3, B1) then press the Enter key.

For the Total pay; in cell B5, type: =B4*B3 then press the Enter key.

For Bank profit; in cell B6, type: =B5-B1 then press the Enter key.

Your worksheet should now look like the one shown below.
Final loan amortization worksheet in excel

NOTE:

I did not provide values for the last two arguments in the PMT function formula. The values are not necessarily needed in this example and also they are optional arguments.

This is the end of part 1 of chapter 6 of this online tutorial series. 

Recommended MS Excel Textbook

Click Here to know more about the book.

In the next part (part 2), I will explain What If Analysis and Excel Powerful tools – Scenario Manager, Data tables and Goal Seek in MS Excel.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

google classroom and zoom video online conferencing guide by buzzer joseph

Google Classroom and Zoom Meeting for Beginners: Learn the Step by Step Guide with Screenshots on How to Use Google Classroom as a Business Owner, Teacher or a Student. Also Learn how to Use Zoom for Online Meeting, Web Conferencing, Video Conferencing and Webinars in Mobile Devices, PC and Mac. Make Distant Teaching and Learning a Fun! Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

No comments:

Post a comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!