February 4, 2012 2 Comments
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
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
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
the formula in cell E7 is
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
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.