Function ARate(nper, pmt, pv, fv, typ, guess) As Double
' Solve for the amortization rate
' Invert an of i and return i
f0 = 2
ep = 0.0000001
i0 = 0#
i1 = guess
If i1 = 0# Then
i1 = 5# / 100
End If
am = (1 - Exp(-nper * Log(1 + i1))) / (nper * i1) + fv * Exp(-nper * Log(1 + i1)) / nper + fv * Exp(-nper * Log(1 + i1)) / nper
am = am * (1 + typ * i1)
an = pv / (nper * pmt)
' Determine bounds
While (am > an)
i1 = i1 * f0
am = (1 - Exp(-nper * Log(1 + i1))) / (nper * i1) + fv * Exp(-nper * Log(1 + i1)) / nper + fv * Exp(-nper * Log(1 + i1)) / nper
am = am * (1 + typ * i1)
Wend
While (am < an)
i1 = i1 / f0
am = (1 - Exp(-nper * Log(1 + i1))) / (nper * i1) + fv * Exp(-nper * Log(1 + i1)) / nper
am = am * (1 + typ * i1)
Wend
' Bisection
i0 = i1
i1 = (i0 + i0 * f0) / 2
i2 = i0 * f0
am = (1 - Exp(-nper * Math.Log(1 + i1))) / (nper * i1) + fv * Exp(-nper * Log(1 + i1)) / nper
am = am * (1 + typ * i1)
While (Abs(am - an) > ep)
'am = (1 - Exp(-nper * Math.Log(1 + i1))) / (nper * i1) + fv * Exp(-nper * Log(1 + i1)) / nper
'am = am * (1 + typ * i1)
If (am > an) Then
i0 = i1
i1 = (i1 + i2) / 2
Else
i2 = i1
i1 = (i0 + i1) / 2
End If
am0 = am
am = (1 - Exp(-nper * Log(1 + i1))) / (nper * i1) + fv * Exp(-nper * Log(1 + i1)) / nper
am = am * (1 + typ * i1)
' Unable to converge?
If am = am0 Then
am = an
End If
Wend
ARate = i1
End Function
Function SRate(nper, pmt, pv, fv, typ, guess) As Double
' Solve for the sinking fund rate
' Invert sn of i and return i
f0 = 2
ep = 0.0000001
i0 = 0#
i1 = guess
If i1 = 0# Then
i1 = 5# / 100
End If
am = (Exp(nper * Log(1 + i1)) - 1) / (nper * i1) + fv / nper
am = am * (1 + typ * i1)
an = pv / (nper * pmt)
' Determine bounds
While (am < an)
i1 = i1 * f0
am = (Exp(nper * Log(1 + i1)) - 1) / (nper * i1) + fv / nper
am = am * (1 + typ * i1)
Wend
While (am > an)
i1 = i1 / f0
am = (Exp(nper * Log(1 + i1)) - 1) / (nper * i1) + fv / nper
am = am * (1 + typ * i1)
Wend
' Bisection
i0 = i1
i1 = (i0 + i0 * f0) / 2
i2 = i0 * f0
am = (Exp(nper * Log(1 + i1)) - 1) / (nper * i1) + fv / nper
am = am * (1 + typ * i1)
While (Abs(am - an) > ep)
'am = (Exp(nper * Log(1 + i1)) - 1) / (nper * i1) + fv / nper
'am = am * (1 + typ * i1)
If (am < an) Then
i0 = i1
i1 = (i1 + i2) / 2
Else
i2 = i1
i1 = (i0 + i1) / 2
End If
am0 = am
am = (Exp(nper * Log(1 + i1)) - 1) / (nper * i1) + fv / nper
am = am * (1 + typ * i1)
' Unable to converge?
If am = am0 Then
am = an
End If
Wend
SRate = i1
End Function