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

I am hoping to find a formula that will calculate an adjustable rate APR in
a single cell. 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)))

I have calculator that gave an apr of 6.839%

any thoughts?

" wrote:

"Xhawk57" wrote:
Does anyone have a formula to calculate an APR for an
adjustable rate mortgage? for example if I have a 200,000
- 30 year mortgage that has a fixed rate of 6% for 3 years
and then adjusts to a rate of 7.75 every year after that
after that, with 1500 in finance charges.
So far I can figure out the APR without adjustment of
=rate(360,PMT(6%/12,360,200000),200000-1500)*12
Any suggestions to complete the formula?

First, it is important to note that the following is from a US
point of view. In particular, it might be different for Canada.

Second, it is important to note that the following refers to the
lender's APR, which is what US law requires the lender to report.
This is very different from "effective" annual interest rate --
the "borrower's APR".

According to [1], the lender's APR is the IRR over the term of
the loan contract. IMHO, this is difficult to compute using Excel
because IRR() requires a cell for each cash flow. In contrast,
the HP12 calculator allows for grouping similar cash flows.

Also, according to [1] and [2], the annualized APR is simply
12 times the monthly APR -- another difference between the
lender's APR and the borrower's APR. Note: I have not
verified that assertion by looking at US law myself, but I take
it for granted, given that it is stated in two different academic
sources. But academia is not always in touch with reality.

I feel certain that there is an easier way to formulate this
computation. But off-hand, one way to compute the lender's
APR using Excel's IRR() might be:

1. In A1, put =-(200000-1500): the contract loan less the
loan fees. Note: The choice of sign is arbitrary, but it
must be the opposite of the sign used for payments (below).

2. In A2, put =PMT(6%/12,30*12,-200000): the monthly
payment for the first 3 years based on the initial contract
loan terms.

3. In A3, put =A2, and copy down through A37 so that A2:A37
comprise the first 36 payments.

4. In A38, put =PMT(7.75%/12,27*12,
-FV(6%/12,3*12,A37,-200000): the payment for the
remaining 27 years of the contract loan, based on the
outstanding loan balance after the first 3 years.

5. In A39, put =A38, and copy down through A361. Thus,
A38:A361 comprise the last 27 years of monthly payments.

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

It is important to note that while the lender's APR might be
useful (or not!) for comparing loans, it serves no other
purpose. The number (7.36%) tells us nothing about the
actual interest paid on the loan, even if we assume that the
future ARM interest rate estimate is accurate (not!). For
some discussion of this, see [2]. Also see [3] for an
explanation why the lender's APR might not even be suitable
for comparison (klunk!).

Bottom line: Unless you are just curious or an academic
who wants to understand how loan terms are created
(see [1]), I don't think this "APR" is worth the trouble to
compute.


-----
[1] http://web.mit.edu/11.431j/www/Fall91602/431_GMch17.ppt

[2]
http://mcb.unco.edu/web/fs/wps/worki...sion%20_2_.pdf

[3] http://www.mtg-net.com/sfaq/faq/apr.htm