Pmt 函数Pmt function

返回 Double,以指定基于定期、定额支付和固定利率的年金支付。Returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate.


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

Pmt 函数包含以下命名参数The Pmt function has these named arguments:

PartPart 说明Description
rate 必需。Required. 指定每个周期的利率的 DoubleDouble specifying interest rate per period. 例如,如果您获得了年利率 (APR) 为 10% 的汽车贷款并进行月供,则每期利率为 0.1/12 或 0.0083。For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
npernper 必需。Required. 指定年金付款期的总数的 IntegerInteger specifying total number of payment periods in the annuity. 例如,如果您每月偿还为期 4 年的汽车贷款,则您的贷款期总数为 4 * 12(或 48)。For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pvpv 必需。Required. Double 指定现值(或一次付清额),是未来一系列付款在目前的价值。Double specifying present value (or lump sum) that a series of payments to be paid in the future is worth now. 例如,您借钱购买了一辆车,贷款额就是您按月还给贷款人的现值。For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
省略fv 可选。Optional. 指定在完成最后付款后所需的未来值或现金余额的 VariantVariant specifying future value or cash balance you want after you've made the final payment. 例如,贷款的未来值为 $0,因为这是完成最后付款后的值。For example, the future value of a loan is $0 because that's its value after the final payment. 但是, 如果您想要在18年的时间内为孩子的教育版节省 $50000, 则 $50000 是未来的价值。However, if you want to save $50,000 over 18 years for your child's education, $50,000 is the future value. 如果省略了,便假设为 0。If omitted, 0 is assumed.
typetype 可选。Optional. 指定付款的到期时间的 VariantVariant specifying when payments are due. 如果付款在付款期结束时到期,则使用 0;如果付款在付款期开始时到期,则使用 1。Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. 如果省略,则假定为 0。If omitted, 0 is assumed.


年金是一段时间内一系列的固定的现金付款。An annuity is a series of fixed cash payments made over a period of time. 年金可以是贷款(如房产抵押),也可以是投资(如月存款计划)。An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

必须使用以相同单位表示的付款时段计算_费率_和_nper_ 参数The rate and nper arguments must be calculated by using payment periods expressed in the same units. 例如, 如果_rate_是使用月计算的, 则还必须使用月计算_nper_ 。For example, if rate is calculated by using months, nper must also be calculated by using months.

对于所有参数,已支出现金(例如,存款储蓄)用负数表示;已收现金(例如,股利支票)用正数表示。For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.


此示例使用 Pmt 函数返回固定时期内贷款的每月还款额。This example uses the Pmt function to return the monthly payment for a loan over a fixed period. 给定值为每个周期的利率 (APR / 12)、总付款次数 (TotPmts)、贷款的现值或本金 (PVal)、贷款的未来值 (FVal) 以及一个指示付款是否到期的数字付款期的开始或结束 (PayType)。Given are the interest percentage rate per period (APR / 12), the total number of payments (TotPmts), the present value or principal of the loan (PVal), the future value of the loan (FVal), and a number that indicates whether the payment is due at the beginning or end of the payment period (PayType).

Dim Fmt, FVal, PVal, APR, TotPmts, PayType, Payment
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.
Fmt = "###,###,##0.00"    ' Define money format.
FVal = 0    ' Usually 0 for a loan.
PVal = InputBox("How much do you want to borrow?")
APR = InputBox("What is the annual percentage rate of your loan?")
If APR > 1 Then APR = APR / 100    ' Ensure proper form.
TotPmts = InputBox("How many monthly payments will you make?")
PayType = MsgBox("Do you make payments at the end of month?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
Payment = Pmt(APR / 12, TotPmts, -PVal, FVal, PayType)
MsgBox "Your payment will be " & Format(Payment, Fmt) & " per month."

另请参阅See also

支持和反馈Support and feedback

有关于 Office VBA 或本文档的疑问或反馈?Have questions or feedback about Office VBA or this documentation? 请参阅 Office VBA 支持和反馈,获取有关如何接收支持和提供反馈的指南。Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.