PMT

PMT function

The PMT function returns the fixed periodic payment for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.

the syntax of PMT function is as follows:
PMT(rate, NPER, PV, FV, type)

rate:  The interest rate per period. periodic-rate is a number value and is either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). If the value given is in 8% per annual to convert this into monthly rate we have divide this value by 12

NPER:  The number of periods. NPER is a number value and must be
greater than or equal to 0.

PV:  The value of the initial investment, or the amount of the loan or annuity. present-value is a number value. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).

Note: Suppose if we are calculating the EMI (equated monthly installment) for the amount borrowed. the PMT function will spit out a negative value (-ve) because this much amount is going out of our pocket to the lender. To avoid this native values in PMT function we may enter this value as minus followed by value

FV:  An optional argument that represents the value of the investment or remaining cash value of the annuity (positive amount), or the remaining loan balance (negative amount), after the final payment. future-value is a number value. At the end of the investment period, an amount received is a positive amount and an amount invested is a negative amount. For example, It could be the balloon payment due on a loan (negative) or the remaining value of an annuity contract (positive). If omitted, it is assumed to be 0.

Note: while calculative the EMI normally we ignore the this value.

type:  An optional argument that specifies whether payments are due at the beginning or end of each period. Most mortgage and other loans require the first payment at the end of the first period (0), which is the default. Most lease and rent payments, and some other types of payments, are due at the beginning of each period (1).

end (0 or omitted):  Payments are due at the end of each period.
beginning (1):  Payments are due at the beginning of each period.

Now lets look at an example:
We have borrowed $1,00,000 at an interest rate of 12% p.a and repayable in 2 years the amortisation table as mentioned below:

lets look at the formulas:
in cell  C7
=PMT($B$2/12,$B$3*12,-$B$1)  drag it down till cell C30 or
use the google docs single  formula to populate to cell C7 TO C30
=ArrayFormula(if(len(A7:A30);ROUND(PMT($B$2/12,$B$3*12,-$B$1),2);iferror(1/0)))

Now we breakup the formula
The first argument is rate we have used $B$2/12 since our rate of interest is give in annual we have to convert this to  monthly rate.

The second argument is  NPER which is number of periods, here in this example we are repaying the principal within the 2 years so we are multiplying the 2 with number of period which 24

The third argument is PV the present value of the loan is 100000 we have to precede this with minus symbol so that the PMT function will spit the positive value

The fourth argument is FV we have ignored this value

The fifth argument is TYPE this arguments also we have ignored this means we are making the payment at the end of the month

to avoid more than two decimals in the value we can warp it with round function as mentioned below
=ROUND(PMT($B$2/12,$B$3*12,-$B$1),2)

the formula in cell D7 is
=ROUND($B7*$B$2/12,2) and drag it down  to calculated interest in the EMI or
use the google docs array formula
=ArrayFormula(if(len(A7:A30);ROUND(B7:B30*$B$2/12,2);iferror(1/0)))

the formula in  cell E7 is
=ROUND(B7-C7+D7,2)
to calculate the closing principal outstanding

To calculate the interest in the first 12 months without preparing the table we can use the following formula

=ROUND(ArrayFormula(SUM(IPMT($B$2/12,ROW(1:12),24,-B1))),2)
which will give you the result of 9469.72
you can verify the same by adding interest column first 12 rows from D7 TO D18
1000 + 962.93 + 925.48 +887.66 + 849.47 + 810.89 + 771.92 + 732.57 + 692.82 + 652.68 + 612.13 + 571.18 = 9469.72

similar for next 12 months the formula will be
=ROUND(ArrayFormula(SUM(IPMT($B$2/12,ROW(13:24),24,-B1))),2) the result will be 3506.61

Similarly to calculate the Principal repayment for the first 12 months the formula will be
=ROUND(ArrayFormula(SUM(PPMT($B$2/12,ROW(1:12),24,-B1))),2) the result will be 47018.44

for the second 12 month i.e. from month 13 to 24 the principal repayment will be
=ROUND(ArrayFormula(SUM(PPMT($B$2/12,ROW(13:24),24,-B1))),2) the result will be 52981.56

we can verify this by adding the above two (i.e.47018.44 + 52981.56) we will get 100000 which is same as principal what we have borrowed from lender.

Example data


2 Responses to PMT

  1. i love your blog, i have it in my rss reader and always like new things coming up from it.

  2. Pingback: IRR | anandexcels

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: