NPer 函数示例(VBA)

gemini527

本示例使用 NPer 函数计算分期偿还贷款之总期数，贷款金额为 PVal。计算时尚需给定每期利率（APR / 12），每期付款金额（Payment），贷款的未来值（FVal）及付款方式，以数值表示期初或期末付款（PayType）。
Dim FVal, PVal, APR, Payment, PayType, TotPmtsConst ENDPERIOD = 0, BEGINPERIOD = 1   ' 付款方式。FVal = 0   ' 对贷款而言通常为零。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   ' 确保格式正确。Payment = InputBox("How much do you want to pay each month?")PayType = MsgBox("Do you make payments at the end of month?", vbYesNo)If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIODTotPmts = NPer(APR / 12, -Payment, PVal, FVal, PayType)If Int（TotPmts） <> TotPmts Then TotPmts = Int（TotPmts） + 1MsgBox "It will take you " & TotPmts & " months to pay off your loan."