Visual Basic for Applications and Function Essay

1169 Words Nov 14th, 2013 5 Pages
A brief introduction of PMT, IPMT and
PPMT Excel functions MS Excel – PMT Function(WS,
VBA)
• In Excel, the PMT function returns the payment amount for a loan based on an interest rate and a constant payment schedule. • The syntax for the PMT function is:
• PMT( interest_rate, number_payments, PV, [FV], [Type] )






interest_rate is the interest rate for the loan. number_payments is the number of payments for the loan.
PV is the present value or principal of the loan.
FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PMT function assumes a FV value of
0.

• Type is optional. It indicates when the payments are
due.
…show more content…
The interest payment is calculated for the 30th week and payments are due at the beginning of each week.
• =IPMT(6%/52, 30, 4*52, 8000, 0 ,1)

• This next example returns the interest payment for a $6,500 investment that earns 5.25% annually for 10 years. The interest payment is calculated for the 4th year and payments are due at the end of each year.
• =IPMT(5.25%/1, 4, 10*1, 6500)

VBA Function Example
• The IPMT function can also be used in VBA code. For example:
• Dim LNumber As Currency
• LNumber = IPmt(0.0525/1, 4, 10*1, 6500)

MS Excel: PPMT Function (WS,
VBA)
• In Excel, the PPMT function returns the payment on the principal for a particular payment based on an interest rate and a constant payment schedule.
• The syntax for the PPMT function is:
• PPMT( interest_rate, period, number_payments, PV, [FV],
[Type] )

• interest_rate is the interest rate for the loan.
• period is the period used to determine how much principal has been repaid. Period must be a value between 1 and number_payments.
• number_payments is the number of payments for the loan.
• PV is the present value or principal of the loan.
• FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PPMT function assumes a FV value of 0.

• Type is optional. It indicates when the payments are due. Type can be one of the