Tuesday, November 8, 2016

PMT Function in Microsoft Excel

PMT function in Excel:

These functions are widely used in financial
transaction to find the present value and monthly payments.

Syntax for PMT function:

PMT (rate, nper, pv, [fv], [type])

Note: For a more complete description of the arguments in PMT, see
the PV function.

The PMT
function syntax has the following arguments:

·
Rate Required. The interest rate for the loan.

·
Nper Required. The total number of payments for the
loan.

·
Pv Required. The present value, or the total
amount that a series of future payments is worth now; also known as the
principal.

·
Fv Optional. The future value, or a cash
balance you want to attain after the last payment is made. If fv is omitted, it
is assumed to be 0 (zero), that is, the future value of a loan is 0.

·
Type Optional. The number 0 (zero) or 1 and
indicates when payments are due.

Consider a
loan amount consider a loan with monthly payments, an
annual interest rate of 6%, a 5-year duration, a present value of $150,000
(amount borrowed) and a future value of 0 (that's what you hope to achieve when
you pay off a loan).

5 years or total number of payments 5 x 12 = 60.

Monthly payments at 5% so use 6%12
that is 0.5%

Here it produces a negative value but treat it as positive value.

Therefore, monthly payment is 193.33.

Also we could find Nper, Rate, PV, and Fv.

To find out the Nper: