lifeson
02-09-2009, 10:38 AM
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???
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???