What Function Returns The Total Present Value Of An Investment With A Fixed Rate

PMT Function

Calculates the payment for a loan or investment with constant payments and a fixed involvement charge per unit

What is the PMT Function?

The PMT Function[i] is categorized under financial Excel functions. The office helps calculate the total payment (primary and interest) required to settle a loan or an investment with a fixed interest rate over a specific time menses.

Formula

=PMT(charge per unit, nper, pv, [fv], [type])

The PMT office uses the following arguments:

Charge per unit (required argument) – The interest rate of the loan.
Nper (required argument) – Total number of payments for the loan taken.
Pv (required statement) – The nowadays value or total amount that a series of futurity payments is worth now. It is also termed every bit the main of a loan.
Fv (optional argument) – This is the future value or a cash balance we desire 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 argument) – The blazon of twenty-four hour period count basis to use. The possible values of the basis are:

PMT Function

How to employ the PMT Role in Excel?

As a worksheet part, the PMT part can be entered as office of a formula in a jail cell of a worksheet. To understand the uses of PMT, let us consider an example:

Example one

Let'due south assume that we demand to invest in such a fashion that, after ii years, we'll receive $75,000. The rate of interest is three.five% per twelvemonth and the payment will be fabricated at the start of each month. The details are:

PMT Function - Example 1

The formula used is:

PMT Function - Example 1a

We get the results below:

PMT Function - Example 1b

The in a higher place function returns PMT as $3,240.20. This is the monthly cash outflow required to realize $75,000 in two years. In this instance:

The payments into the investment are on a monthly ground. Hence, the annual involvement rate is converted to a monthly rate. Also, nosotros converted the years into months: ii*12 = 24.
The [blazon] argument is set to 1 to indicate that the payment of the investment will be fabricated at the commencement of each period.
As per the general cash menstruation convention, approachable payments are represented by negative numbers and incoming payments are represented by positive numbers.
Equally the value returned is negative, information technology indicates an outgoing payment is to exist made.
The value $three,240.20 includes the main and involvement but no taxes, reserve payments, or fees.

A few things to call back near the PMT Function:

#NUM! error – Occurs when:
- The given rate value is less than or equal to -1.
- The given nper value is equal to 0.
#VALUE! error – Occurs when whatever of the arguments provided are non-numeric.
When calculating monthly or quarterly payments, we demand to convert annual interest rates or the number of periods to months or quarters.
If we wish to find out the full amount that was paid for the duration of the loan, we demand to multiply the PMT as calculated past nper.

Click hither to download the sample Excel file