On Jul 25, 8:30 pm, mlion wrote:
Here are the websites that I was using to validate my APR;
http://www.lenderhomepage.com/tools/...age-apr-calcul...
http://www.dinkytown.net/java/Mortga...djustable.html
[....]
I've ran my IRR function with many different variables trying to match their
APR, and just could not accept that their number could be "wrong" and the
solution via excel was "correct".

In this posting, I'll pick apart the dinkytown calculator. I do
conclude that its APR computation is incorrect when the interest rate
varies. Like you, I am relunctant to reach that conclusion. But
consider the following.

First, it appears that the dinkytown calculator treats the starting
interest rate plus margin as the (lifetime) interest rate cap. Also,
it appears that it adds the "max adjustment" to the starting rate
until that rate cap is reached, starting after "months before first
adjustment" at intervals of "months between adjustments". The current
index does not seem to be a factor at all. I believe that is a misuse
of those factors -- or those factors are misnamed. But I will take
their usage for granted.

If we set "max adjustment" to 0%, we get a fixed-rate loan. With a
starting rate of 5.75% for a loan amount of 250,000 over 30 years, the
first 359 payments are 1458.93 (rounded), and the last payment is
1460.88 (rounded). [Aside: Using FV() in Excel, I compute a final
payment of 1460.98 rounded. The descrepancy arises because it appears
the dinkytown rounds each periodic interest amount. I cannot say with
impunity whether that is right or wrong, or common or uncommon among
lenders.]

With points of 2500 (1%), other fees of 750 and 0% origination fee
(and no prepaid interest), the dinkytown calculator reports an APR of
5.871%. In Excel, I compute an IRR of 5.8706% (rounded) --
essentially the same -- by setting the initial cash flow to 250000 -
2500 - 750 and by multiplying the resulting monthly IRR by 12.

I believe that demonstrates that the IRR paradigm that I suggested in
my previous posting is correct, at least to the extent that it matches
the dinkytown calculator.

So far, so good. But....

If we set "max adjustment" to 0.5%, the dinkytown calculator computes
payments of 1458.93, 1533.77, 1608.39 and 1682.64 and interest rates
of 5.75%, 6.25%, 6.75% and 7.25% starting with payment numbers 1, 37,
49 and 61 respectively, with a last payment of 1682.46. The reported
APR is 7.144%.

But the calculator reports the same 7.144% APR when "max adjustment is
1.0% and 1.5% or more, even though: (a) for a 1.0% adjustment, the
payments are 1458.93, 1610.30 and 1686.58 starting with payment
numbers 1, 37 and 49, with a last payment of 1678.71; and (b) for a
1.5% adjustment or more, the payments are 1458.93 and 1688.45 starting
with payment numbers 1 and 37, with a last payment of 1681.81.

I do not believe the APR should be the same in all 3 cases. FYI, I
compute IRRs of 6.8779%, 6.9479% and 6.9844% (rounded) for the 3 cases
respectively.

(I will examine the lenderhomepage calculator in a later posting.
It's late now for me.)