andieje
asked on
Calculating APR using Newton Raphson - excel spreadsheet
I am trying to calculate APR based on UK rules. Here is a booklet with some details in
http://www.creditunion.ie/files/file_20050316024652OFT%20-%20Credit%20Charges%20and%20APR.pdf
The formula is on page 9.
Later on the booklet advises how to solve the formula (p52) using Newton Raphson and I need help doing this
The first step is to calculate ¿PV which represents the sum (total) of the PVs for the instalments minus the sum of the PVs for the advances.
I am not sure what the difference is between an installment and an advance? I am assuming an installment is a repayment. The booklet says there is normally one advance so I am presuming it is the amount borrowed. The booklet says PV is calculated according to the formula PV = Ax^t
The next step is to calculate ¿dV which represents the sum of the dVs for the instalments minus the sum of the dVs for the advances. The derivative is calculated as tAx^(t-1).
I have put all of these steps into a spreadsheet and I hope they are right. I have put in an initial guess of x as 1.001 as recommended by the booklet. However if i then take my new value of x as calculated by the spreadsheet and feed it back in (0.95) at cell B4, the answer for the apr moves further away. Please can you advise where I am going wrong in my calculations. I thought that by feeding the value for x back in manually I would be simulating solving the problem by Newton Raphson
http://www.dev3.co.uk/temp/interest_calculator.xlsx
Many thanks in advance for any help you can give.
http://www.creditunion.ie/files/file_20050316024652OFT%20-%20Credit%20Charges%20and%20APR.pdf
The formula is on page 9.
Later on the booklet advises how to solve the formula (p52) using Newton Raphson and I need help doing this
The first step is to calculate ¿PV which represents the sum (total) of the PVs for the instalments minus the sum of the PVs for the advances.
I am not sure what the difference is between an installment and an advance? I am assuming an installment is a repayment. The booklet says there is normally one advance so I am presuming it is the amount borrowed. The booklet says PV is calculated according to the formula PV = Ax^t
The next step is to calculate ¿dV which represents the sum of the dVs for the instalments minus the sum of the dVs for the advances. The derivative is calculated as tAx^(t-1).
I have put all of these steps into a spreadsheet and I hope they are right. I have put in an initial guess of x as 1.001 as recommended by the booklet. However if i then take my new value of x as calculated by the spreadsheet and feed it back in (0.95) at cell B4, the answer for the apr moves further away. Please can you advise where I am going wrong in my calculations. I thought that by feeding the value for x back in manually I would be simulating solving the problem by Newton Raphson
http://www.dev3.co.uk/temp/interest_calculator.xlsx
Many thanks in advance for any help you can give.
Can I ask why you are using the Newton method and not the IRR method?
File attached with IRR method.
interest-calculator.xlsx
interest-calculator.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much. I was so close :)