PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

EXCEL FUNCTIONS-PMT

INTRODUCTION

In our lives, we always come through ups and downs mentally, physically and financially too.

When it comes to the finances, we might need to apply for a loan. The loan is an amount borrowed from any financial institution on a fixed rate of interest and for a fixed term . We pay the emi at regular intervals.

The function which we are going to discuss helps us find out the payment to be made for the loan taken easily.

PMT function comes under the FINANCIAL FUNCTIONS category in Excel.

PMT FUNCTION returns the value of the payment to be made against a loan with the fixed interest and for a fixed tunure [ duration ].

PURPOSE OF PMT IN EXCEL

PMT FUNCTION returns the PAYMENT TO BE MADE for a loan with a fixed interest and fixed installments .

When we have taken a loan , it is important to find out the installments.

This function returns installment for a fixed number of terms at fixed rate of interest.

PREREQUISITES TO LEARN PMT

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

Basic understanding of how to use a formula or function.
Basic understanding of rows and columns in Excel.
Some information about the financial terms is an advantage for the use of such formulas.
Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

SYNTAX: PMT FUNCTION

The Syntax for the function is

=CUMIPMT(INTEREST RATE, NUMBER OF PAYMENT, CURRENT VALUE [PRINCIPAL] , FINAL FUTURE VALUE [ OPTIONAL ], TYPE)

INTEREST RATE is the rate of interest of the loan. [Rate needs to be divided as per the number of payments]

PERIOD OF PAYMENT is the total period of the payment for which the payment will be done.

CURRENT VALUE [ PRINCIPLE ] is the current principal value. We can call it as total loan amount after which we want to calculate the installments.

FINAL FUTURE VALUE [OPTIONAL DEFAULT=0] is the amount which we want at the end of the loan repayment. It is 0 by default, if no value is given.

TYPE [OPTIONAL DEFAULT IS 0] is the type determining the time of payment.

0 Payment at the end of period

1 Payment at the starting of the period.

EXAMPLE: PMT FUNCTION IN EXCEL

DATA SAMPLE

Let us try an example for the calculating the interest for a particular period.

Suppose we have the loan of 10000 dollars.

We took the loan for one year and will be paying monthly.

Rate of interest is 10% per annum.

Let us calculate the accumulated interest for the complete period.

Let us finalize the arguments for our formula

INTEREST RATE =10/12% as monthly payment so interest rate also converted to monthly.

NUMBER OF PAYMENT PERIODS 12 as payment is monthly for one year.

CURRENT VALUE is 10000 dollars.

FUTURE VALUE is 0. The value at the end of the tenure.

TYPE is 0

STEPS TO USE PMT FUNCTION

The data is put in the cells from F5 TO G9.

For the result

we put the formula in H11 as

=PMT(G5,G6,G7,G8,G9)

As we paid the interest monthly, the rate of interest was divided by 12.

The result comes out to be 878.97 which is to be paid monthly so that our loan becomes zero after 12 months.

CONFUSION CLARIFICATIONS

TOTAL INTEREST IN LOANS

Many people get confused in this after finding out the interest for a year and comparing it with the interest found by these formulas.

For example, the 10% interest for 100000 should be 10000 but it comes out to be around 5500.

So , we should keep in mind that we are paying the interest and principal back every month and interest is calculated only on the left out amount and not on the complete amount.

That is why this logic won’t work here.

CONTENTS