How to calculate Annual Percentage Rate (APR) given the following:
A - Loan borrowed at the beginning (USD 1000),
B - The sum of total loan final costs paid by the borrower in equal installments (USD 2000),
c - Number of compounding periods per year (52 weeks),
k - Number of periods to pay the loan (60 weeks)
All the formulas found in literature use nominal interest rate (also Excel Effective function) but here we do not have this argument.
By APR I mean: http://en.wikipedia.org/wiki/Annual_percentage_rate
I have constructed a spreadsheet where I can find APR with Excel Solver by changing nominal interest rate. Wouldn't there be more elegant solution for that?
Here goes the code for APR. I will appreciate any remarks on optimizing the search mechanism and the types I assigned to variables (Double, Integer).
The idea of the macro is to increase nominal interest rate (i) starting from 1% by a very small value (step) as long as it brings us closer for proper calculation of Total cost of a loan paid in equal installments (B).
Function APR(A As Double, B As Double, c As Integer, k As Integer, Optional i As Double = 0.01, Optional step As Double = 0.0001) As Double
'Author Przemyslaw Remin, thanks for explanation to Chris Degnen
'
'A - Loan amount we borrow
'B - Total amount we pay back in equal installments in k periods
'c - Number of compoundings per year
'k - Number of periodic payments
'i - Nominal interest rate, here it will be used as iterator to find correct B
'step - how much we change i, the smaller the step the more precision we get
'
Dim target1 As Double
Dim target2 As Double
Do Until target1 < target2 'we do the loop until the target falls
target1 = ((i / c) / (1 - (1 + i / c) ^ (-k)) - (B / (k * A))) ^ 2
i = i + step
target2 = (((i + step) / c) / (1 - (1 + (i + step) / c) ^ (-k)) - (B / (k * A))) ^ 2
Loop
APR = (1 + i / c) ^ c - 1
End Function
Thanks to Chris Degnen for his kind explanation on APR calculation mechanism provided here: https://money.stackexchange.com/questions/43450/how-to-calculate-annual-percentage-rate?noredirect=1#comment64347_43450