Adjustable Rate APR

[Guest]

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?

 

[Guest]

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/workingPapersPDF/JFSP submission _2_.pdf

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

 

[Guest]

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?

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/workingPapersPDF/JFSP submission _2_.pdf

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

 

[Guest]

please disreguard the 6.839%, obviously that is incorrect.

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?

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/workingPapersPDF/JFSP submission _2_.pdf

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

 

[Guest]

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.