Excel Pmt Function Examples Example 1
In the following spreadsheet, the Excel Pmt function is used to calculate the monthly payments on a loan of $50,000 which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.
Formula:
|
A
|
1
|
Monthly payments on a loan of
$50,000 that is to be paid off in
full over 5 years, with an interest
rate of 5% per year (payment
made at end of each mth):
|
2
|
=PMT( 5%/12, 60, 50000 )
|
|
Result:
|
A
|
1
|
Monthly payments on a loan of
$50,000 that is to be paid off in
full over 5 years, with an interest
rate of 5% per year (payment
made at end of each mth):
|
2
|
-943.56
|
|
Note that in this example:
-
The payments are made monthly, so the annual interest rate of 5% has been converted into the monthly rate (=5%/12), and the period of 5 years is expressed in months (=5*12).
-
As the future value is zero, and the payment is to be made at the end of the month, the [fv] and [type] arguments can be omitted from the above function.
-
The returned payments are negative values, as these represent outgoing payments (for the individual taking out the loan).
Example 2
In the spreadsheet below, the Excel Pmt function is used to calculate the quarterly payments required to increase an investment from $0 to $5,000 over a period of 2 years. Interest is paid at a rate of 3.5% per year and the payment into the investment is to be made at the beginning of each quarter.
Formula:
|
A
|
1
|
Quarterly payments into an investment
with current value $0, which is
required to reach $5,000 over 2 yrs.
The interest rate is 3.5% per year
(payment made at start of each qtr):
|
2
|
=PMT( 3.5%/4, 8, 0, 5000, 1 )
|
|
Result:
|
A
|
1
|
Quarterly payments into an investment
with current value $0, which is
required to reach $5,000 over 2 yrs.
The interest rate is 3.5% per year
(payment made at start of each qtr):
|
2
|
-600.85
|
|
Note that, in this example:
-
The payments into the investment are made quarterly, so the annual interest rate of 3.5% is converted into a quarterly rate (3.5%/4), and the number of years is converted into quarters (=2*4).
-
The [type] argument has been set to 1, to indicate that the payment into the investment is to be made at the beginning of each quarter.
-
The returned payment is a negative value, as this represents an outgoing payment.
he Excel PPMT function calculates the payment on the principal, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate.
The syntax of the function is:
PPMT( rate, per, nper, pv, [fv], [type] )
Where the arguments are as follows:
rate
|
-
|
The interest rate, per period.
|
per
|
-
|
The period for which the payment on the principal is to be calculated (must be an integer between 1 and nper).
|
nper
|
-
|
The number of periods over which the loan or investment is to be paid.
|
pv
|
-
|
The present value of the loan / investment.
|
[fv]
|
-
|
An optional argument that specifies the future value of the loan / investment, at the end of nper payments.
If omitted, [fv] takes on the default value of 0.
|
[type]
|
-
|
An optional argument that defines whether the payment is made at the start or the end of the period.
The [type] argument can have the value 0 or 1, meaning:
0 - the payment is made at the end of the period;
1 - the payment is made at the beginning of the period.
If the [type] argument is omitted, it takes on the default value of 0 (denoting payments made at the end of the period).
|
Cash Flow Convention:
Note that, in line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers. This is seen in the examples below.
Dostları ilə paylaş: |
