Monthly Loan Payment Using Compound Interest

[koolwaters]

I need to create a loan amortization schedule using Access. My challenge comes as we are using compound interest and I need to determine the monthly payments.

I am able to get the total to be repaid using the formula:
=[LoanAmount]*((1+[InterestRate]/[CompoundType)^([LoanTerm]*CompoundType))

For example:
Loan Amount $15,000
Interest Rate 5%
Compound Type Monthly
Loan Term 5 Years

My formula returns $19,250.38.

What I need to do is determine what the monthly payment should be for that same loan using compound interest but I cannot seem to figure it out. If I use the PMT function in VBA, that gives me $283.07 as the monthly payment, which equates to $16,984.11 over the 5 years, a difference of $2,266.37.

Thanks for any feedback.

 

[xenou]

There is only one PMT function so it won't work for both of these. For your second scenario you can simply divide the total by the number of periods: 1276.28/60 = $21.27 with some rounding adjustment required in practice (it will be off by about 8 cents after 60 payments - not sure what banks do. I I would simply reduce or increase the final payment to make up the gap).

Note that you don't actually need the payment for using FV. Here you would use zero for the payment (to calculate the FV of the total interest assuming no payments, which is effectively 5 full years of compounding interest). The payment argument is irrelevant because you don't want payments to apply against principal. Again, that function assumes that payments are applied against principal and interest and will reduce the principal and interest over time as the principal is paid down.

 

[koolwaters]

Thanks for the response xenou.

I am now back at it so I will give it try and let you know if it works.