Does anyone have a function that will calculate the interest rate on a loan when given: Amount Financed, #Months (ie the term of the loan) and the Finance Charge?
I have tried a number of different approaches including PV2()
'Present_Value as N = PV2( FinanceCharge as N, Interest_Rate as N, Number_Of_Time_Periods as N )
'PV2() returns the present value of a stream of Investment s, invested at a fixed Interest_Rate for a certain Number_Of_Time_Periods. The PV() function is used to find the amount that must be invested now to produce a given future amount.
'The Internal Rate of Return is the interest rate that makes the Net Present Value zero... so I iterate after a first guess
FUNCTION FinancePercent AS N (FinanceCharge As N, Months As N )
dim FirstGuess As N
dim NextGuess As N
dim PresentValue As N
dim StopValue As N
FirstGuess = 0.18 '(ie 18%... the function uses the RATE not the percent)
NextGuess = 0.18
PresentValue = FinanceCharge
StopValue = 0.01 * FinanceCharge
while abs(PresentValue) > StopValue
PresentValue = PV2( FinanceCharge, NextGuess, Months )
'if PresentValue is > 0 then we increase the NextGuess (ie the guessed FinanceRate)
'if PresentValue is < 0 then we decrease the NextGuess
if PresentValue > 0 then
NextGuess = NextGuess + NextGuess * 0.1
else
NextGuess = NextGuess - NextGuess * 0.1
end if
end while
FinancePercent = NextGuess * 100
END FUNCTION
which doesn't work!
I have tried a number of different approaches including PV2()
'Present_Value as N = PV2( FinanceCharge as N, Interest_Rate as N, Number_Of_Time_Periods as N )
'PV2() returns the present value of a stream of Investment s, invested at a fixed Interest_Rate for a certain Number_Of_Time_Periods. The PV() function is used to find the amount that must be invested now to produce a given future amount.
'The Internal Rate of Return is the interest rate that makes the Net Present Value zero... so I iterate after a first guess
FUNCTION FinancePercent AS N (FinanceCharge As N, Months As N )
dim FirstGuess As N
dim NextGuess As N
dim PresentValue As N
dim StopValue As N
FirstGuess = 0.18 '(ie 18%... the function uses the RATE not the percent)
NextGuess = 0.18
PresentValue = FinanceCharge
StopValue = 0.01 * FinanceCharge
while abs(PresentValue) > StopValue
PresentValue = PV2( FinanceCharge, NextGuess, Months )
'if PresentValue is > 0 then we increase the NextGuess (ie the guessed FinanceRate)
'if PresentValue is < 0 then we decrease the NextGuess
if PresentValue > 0 then
NextGuess = NextGuess + NextGuess * 0.1
else
NextGuess = NextGuess - NextGuess * 0.1
end if
end while
FinancePercent = NextGuess * 100
END FUNCTION
which doesn't work!
Comment