Hi,

Thanks for the response. I made the adjustments and came up with the same
answer. In regard to that $1,693.05 payment reference for cells a42:a365,
that was a typo. Here are the websites that I was using to validate my APR;

http://www.lenderhomepage.com/tools/...calculator.php

http://www.dinkytown.net/java/Mortga...djustable.html

The information displayed for their calculation of the APR is pretty
straight-forward and there is no mention of any additional fees ($4010 plus).
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". Take a look at the URLs and let me know
what you think they are doing to derive their number other than to add some
mystery fees.

Once again, thank you for your input.

"joeu2004" wrote:

On Jul 25, 12:20 pm, mlion wrote:
I trying to solve and validate the APR on an ARM loan with the IRR
function based on the following,
[....]
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
[....]
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
[....]
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

Why did you choose to use different payment amounts in your
worksheet? The first pair of payment amounts ("based on the
following") is the correct one to use.

In your computation, the last parameter to PMT() for the first 36
months is incorrect. It should be simply 250000. It is computed as
if the lender gave you $250,000, and you paid the lender the amount of
the initial costs out-of-pocket.

The parameters to PMT() for the last 324 months seem correct; and
indeed, with those parameters, PMT() returns the correct amount,
namely $1688.45. I don't know how you got $1693.05; perhaps you
mistyped ".0728" for 7.25%.

A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)

For the IRR, each cash flow is the net of inflows and outflows in that
period. For the initial period, the inflow is the initial loan amount
($250,000), and the outflow is the sum of the initial loan costs (at
least 2500 + 750 + 598.96). That is, A5 should be (at least):

=250000 - 2500 - 750 - 598.96

the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

With the corrections above, I compute an annualized IRR of 7.0079%
(rounded), including the prepaid interest. To get 7.168% and 7.144%,
I would have to assume that the lender included an addition $4010 to
$4040 in initial costs, which you (or the lender) did not mention.
Although it seems unlikely that two lenders would make the same such
assumption, note that often different online APR calculators use the
same underlying "engine".

What am I missing?

Only that the APR is really not a good way to compare loans, at least
in the US. I would compare the loan terms and the initial loan costs
separately. But even then, comparing ARMs is tricking business
because of the flexibility that lenders have in determining rates and
payments after the initial fixed period.


--- complete original posting ---

On Jul 25, 12:20 pm, mlion wrote:
I trying to solve and validate the APR on an ARM loan with the IRR function
based on the following,
- 3/1 ARM with initial of 5.75% (rate good for 36 months)
- Loan term of 30 years (360 months)
- Loan amount of $250,000
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
- 1% in origination points; $2,500
- $750 in processing fees
- Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)
- An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the
37th month
- Loan balance at end of 36 month period is $239,771.05;
fv(.0575/12,36,1458.93,250000)
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
A1:A3; header information
A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

This produces an APR of 6.887%; the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?