說明
Use this function to calculate the payment for a loan based on constant payments and a constant interest rate. Supported in Chains.
PMT is particularly useful for calculating monthly mortgage payments, car loan payments, or any fixed-term loan with regular payments.
Note: By default, the PMT function assumes that payments are made at the end of each period. For payments made at the beginning of the period, use the type argument to change this.
語法
PMT(rate, nper, pv, [fv], [type])
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
評分 |
是 | The interest rate per period. | A decimal number, a reference to a cell containing a decimal number, or a formula which results in either of these. |
nper |
是 | The total number of payment periods in the loan. | A positive integer, a reference to a cell containing a positive integer, or a formula which results in either of these. |
pv |
是 | The present value (or principal) of the loan. | A positive number, a reference to a cell containing a positive number, or a formula which results in either of these. |
fv |
沒有 | The future value or cash balance after the last payment. | A number, a reference to a cell containing a number, a cell range,or a formula which results in any of these. If omitted, this is assumed assumed to be 0. |
類型 |
沒有 | Indicates when payments are due. | 0 (end of period) or 1 (beginning of period). If omitted, it's assumed to be 0. |
範例
範例資料
| A | B |
|---|---|
| Loan Amount | 200000 |
| Annual Interest Rate | 5% |
| Loan Term (years) | 30 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=PMT(B2/12, B3*12, B1) |
Calculates the monthly payment for the loan. | -$1073.64 |
=PMT(B2/12, B3*12, B1, 0, 1) |
Calculates the monthly payment with payments at the beginning of each period. | -$1069.19 |
=PMT(B2/4, B3*4, B1) |
Calculates the quarterly payment for the loan. | -$3234.81 |
=PMT(B2/12, B3*12, B1, 10000) |
Calculates the monthly payment with a future value of 10000. | -$1093.51 |
=ABS(PMT(B2/12, B3*12, B1))*12 |
Calculates the total annual payment for the loan. | $12883.68 |
附註
- The PMT function returns a negative number by default, indicating an outgoing payment.
- For monthly payments, divide the annual interest rate by 12 and multiply the number of years by 12.
- The
rateandnperarguments must use the same time unit. Ifrateis per year and payments are monthly,rateshould be divided by 12 and nper multiplied by 12. -
fv(future value) is optional. If omitted, it's assumed to be 0 (the loan is fully paid off). - The type argument is also optional. Use 1 for payments at the beginning of the period, or 0 (or omit) for end of period.
提示
- Use PMT in combination with other financial functions such as FV, NPV, XNPV, IRR, or XIRR, for more comprehensive financial analysis.
- Always ensure your rate is in the correct periodic form (e.g., monthly rate for monthly payments).
- Use the ABS function with PMT if you need the payment amount as a positive number.