In this seminar, are you assuming that the loan customers are making payments at the end of the period versus the beginning of the period? This is an important question, as the payment will be different. For example, if a customer is going to borrow $100,000 at an annual rate of 5 percent over 30 years and is going to make monthly payments at the end of the month, then the formula and monthly payment is:

=PMT(5%/12,360,100000,0,0) or $536.82

However, if the customer is going to borrow the same amount of $100,000 at the same rate of 5 percent over 30 years, but make payments at the beginning of the month, then the formula and monthly payment is:

=PMT(5%/12,360,100000,0,1) or $534.59

In the Private Sub Command22_Click object, you have the following formula:

PaymentAmount = Pmt(InterestRate / FrequencyCombo.Column(2), _
                        NumberOfPayments, LoanAmount) * -1

I believe you have omitted two variables in the payment formula you are using. These two variables would show up after the LoanAmount variable. I believe you need to add a variable for the future value, which in this case would be equal to 0, and a 0/1 toggle variable where 0 would be used for a payment formula where the customer makes payments at the END of the month and 1 would be used for a payment formula where the customer makes payments at the BEGINNING of the month. If you have not programmed this logic, then please include in your response what an interested person would have to do to accomodate customers who would like to make loan payments at the beginning of the month.

Thank you,
Bruce

---

Reply from Richard Rost:

Bruce, for loans, the FV (Future Value) is generally 0. The only way you'd want to specify a FV is if you want to make payments for a few years and then have a balloon payment of, say, $5000 at the end of the loan period. I did not cover this example in the seminar.

Yes, for all of my calculations, I assume the loan payment is being made at the END of the period. Like you pointed out, if you want the payment due at the BEGINNING of the period, the change the final variable [Type] to 1 instead of 0 (the default).

When creating this seminar, I used the final database to check loan amortization statements prepared by my bank for my mortgage PLUS three of my past car loans, and they all checked out. I believe the technique I used in the seminar is considered proper accounting practice - although the variations you mentioned are certainly possible.