Help with Finance calculation please

[lifeson]

I am trying to replicate a finance calculator/presenter which our company uses in VBA

I dont know if this is a standard method of selling finance within the industry but this is the principle of the plan

Example
Loan value £3000
The deal is set up for 120 months and the customer can pay the minimum repayment if they wish
This gives me the correct minimum repayment:

£58.30 for £3000


dMPR = 1.68 'monthly % rate
dARI = (dMPR * 12) / 1200
dTerm = 120
minPymt = -Pmt(dARI, dTerm, -FV(dARI, 1 - 1, 0, dBalance))

However the customer can elect to make overpayments upto double the minimum monthly repayment

£116.60 for £3000
Question
How do I calculate the repayment term if the customer makes a payment of anywhere between the minimum and maximum repayment e.g. £58.30 & £116.60

The attached workbook has a demo of the presenter I am trying to replicate
When you move the Flexi Term Adjuster to adjust the term the correct figures are shown in the Flexi Repayment Box
but I need to ba able to adjust the Flexi Repayment Adjuster and show the reduced term

There is a second sheet which I think has the right calculations on it but Ideally I want to do this all by vba if thats possible???

[Bob Phillips]

numPeriods = NPER(dari,dPayment,-dBalance)

[lifeson]

numPeriods = NPER(dari,dPayment,-dBalance)


That simple :p :p
I was expecting some mega amount of code

Thanks XLD

[mdmackillop]

That simple :p :p
I was expecting some mega amount of code
That's why he is the "Distinguished Lord"