"Xhawk57" wrote:
I got NUM# when i follow your instructions, I rechecked
several times and could not figure out what i did wrong.

Sorry: I forgot to type the "guess" argument for IRR() when
I posted earlier. The last instruction should be:

6. In some cell, compute =12*IRR(A1:A361,7%/12). Be sure
to format as Percentage with 2 decimal places. I get 7.36%.

Presumably you fixed the other typo in my previous posting:
a missing close-parenthesis for PMT() in step #4.

I am hoping to find a formula that will calculate an adjustable
rate APR in a single cell.

So am I :-).

so far i have come up with this:
Loan Amount 200000 E2
term 360 E3
fixed period 36 E4
finance Charges 1500 E5
Initial rate 6.000% E6
index 5.000% E7
Margin 2.750% E8

Adjustable APR 7.65667%:
=(((RATE(E3,PMT(E6/12,E3,E2),E2-E5)*12)*(E4/E3))
+((RATE(E3-E4,PMT((E7+E8)/12,E3-E4,E2),E2-E5)*12)*((E3-E4)/E3)))
[....]
any thoughts?

You are attempting to compute a weighted average of the
two interest rates. I do not believe that is mathematically
correct. But it might become a reasonable estimate if done
correctly, given the big difference of periods (36 v. 324
months).

I quibble with your choice of E2 for PV in the PMT() function
for the 2nd rate period and your choice of E2-E5 for PV in
the RATE() function for the 2nd rate period.

I was going to explain further and even offer an alternative
method of estimation derived from the mathematical IRR
formulation. But now I am not sure I know how to compute
the ARM APR correctly -- or at least not according to convention.

I discovered two different ARM APR calculators on the web
that compute an APR of 7.554%, not 7.358% as I do. At this
time, I cannot explain the difference. The difference is strange
because we do agree on the general structure of the loan,
namely: 36 months of payments of $1199.10 and 324 months
of payments of $1417.11 for a $200,000 loan with $1500 in
loan fees, including any prepaid interest.

So I must withhold further comment until I or someone can
explain the ARM APR derivation correctly.