Complex PMT Calculation help required

[paulsolar]

Hi All

I think this is a question for all the excel formula gurus out there ray:

I have used the formula =PMT(A2/12,A3,A4,0) to calculate the repayments of a loan over a given period where:
A2 = Annual interest rate
A3 = Number of months of payments
A4 = Amount of loan
And the first payment is due at the end of the first month.

This works fine, but on reading the Consumer Credit Act in more detail for this type of fixed rate loan I have to provide settlement figures at various break points in the loan for demonstration purposes. (The settlement figure is how much would need to be paid to pay the loan off in full)

I need to provide settlement figures for ¼, ½ & ¾ of the way through original loan period, is there a formulaic method of doing this or do I need to construct a table to do this? The answers need to be in separate cells as they are being used elsewhere to populate different sentences in a work document.

The loan can be for up to 15 years so a table would be huge and complex to construct.

Kind regards

Paul

 

[shg]

See the CUMPRINC function

 

[paulsolar]

Hi Shg

thanks for the intro to this function, never used it before, What I'm looking at is how I'll use it to do what I need now.

Is there any better reading on how to use this than the microsoft tutorial?

regards

Paul

 

[paulsolar]

WOW!!!

thanks for this, never seen it before but what a superb function, watched a you tube on it easy as can be to use!!

Many thanks

Paul

 

[shg]

You're welcome.