software

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?

quot;Xhawk57quot; wrote:
gt; Does anyone have a formula to calculate an APR for an
gt; adjustable rate mortgage? for example if I have a 200,000
gt; - 30 year mortgage that has a fixed rate of 6% for 3 years
gt; and then adjusts to a rate of 7.75 every year after that
gt; after that, with 1500 in finance charges.
gt; So far I can figure out the APR without adjustment of
gt; =rate(360,PMT(6%/12,360,200000),200000-1500)*12
gt; 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 quot;effectivequot; annual interest rate --
the quot;borrower's APRquot;.

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 quot;APRquot; is worth the trouble to
compute.-----
[1] web.mit.edu/11.431j/www/Fall91602/431_GMch17.ppt

[2]
mcb.unco.edu/web/fs/wps/worki...sion _2_.pdf

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

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 Amount200000 E2
term 360 E3
fixed period36 E4
finance Charges1500 E5
Initial rate 6.000% E6
index 5.000% E7
Margin 2.750% E8

Adjustable APR7.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?

quot; wrote:

gt; quot;Xhawk57quot; wrote:
gt; gt; Does anyone have a formula to calculate an APR for an
gt; gt; adjustable rate mortgage? for example if I have a 200,000
gt; gt; - 30 year mortgage that has a fixed rate of 6% for 3 years
gt; gt; and then adjusts to a rate of 7.75 every year after that
gt; gt; after that, with 1500 in finance charges.
gt; gt; So far I can figure out the APR without adjustment of
gt; gt; =rate(360,PMT(6%/12,360,200000),200000-1500)*12
gt; gt; Any suggestions to complete the formula?
gt;
gt; First, it is important to note that the following is from a US
gt; point of view. In particular, it might be different for Canada.
gt;
gt; Second, it is important to note that the following refers to the
gt; lender's APR, which is what US law requires the lender to report.
gt; This is very different from quot;effectivequot; annual interest rate --
gt; the quot;borrower's APRquot;.
gt;
gt; According to [1], the lender's APR is the IRR over the term of
gt; the loan contract. IMHO, this is difficult to compute using Excel
gt; because IRR() requires a cell for each cash flow. In contrast,
gt; the HP12 calculator allows for grouping similar cash flows.
gt;
gt; Also, according to [1] and [2], the annualized APR is simply
gt; 12 times the monthly APR -- another difference between the
gt; lender's APR and the borrower's APR. Note: I have not
gt; verified that assertion by looking at US law myself, but I take
gt; it for granted, given that it is stated in two different academic
gt; sources. But academia is not always in touch with reality.
gt;
gt; I feel certain that there is an easier way to formulate this
gt; computation. But off-hand, one way to compute the lender's
gt; APR using Excel's IRR() might be:
gt;
gt; 1. In A1, put =-(200000-1500): the contract loan less the
gt; loan fees. Note: The choice of sign is arbitrary, but it
gt; must be the opposite of the sign used for payments (below).
gt;
gt; 2. In A2, put =PMT(6%/12,30*12,-200000): the monthly
gt; payment for the first 3 years based on the initial contract
gt; loan terms.
gt;
gt; 3. In A3, put =A2, and copy down through A37 so that A2:A37
gt; comprise the first 36 payments.
gt;
gt; 4. In A38, put =PMT(7.75%/12,27*12,
gt; -FV(6%/12,3*12,A37,-200000): the payment for the
gt; remaining 27 years of the contract loan, based on the
gt; outstanding loan balance after the first 3 years.
gt;
gt; 5. In A39, put =A38, and copy down through A361. Thus,
gt; A38:A361 comprise the last 27 years of monthly payments.
gt;
gt; 6. In some cell, compute =12*IRR(A1:A361). Be sure to
gt; format as Percentage with 2 decimal places. I get 7.36%.
gt;
gt; It is important to note that while the lender's APR might be
gt; useful (or not!) for comparing loans, it serves no other
gt; purpose. The number (7.36%) tells us nothing about the
gt; actual interest paid on the loan, even if we assume that the
gt; future ARM interest rate estimate is accurate (not!). For
gt; some discussion of this, see [2]. Also see [3] for an
gt; explanation why the lender's APR might not even be suitable
gt; for comparison (klunk!).
gt;
gt; Bottom line: Unless you are just curious or an academic
gt; who wants to understand how loan terms are created
gt; (see [1]), I don't think this quot;APRquot; is worth the trouble to
gt; compute.
gt;
gt;
gt; -----
gt; [1] web.mit.edu/11.431j/www/Fall91602/431_GMch17.ppt
gt;
gt; [2]
gt; mcb.unco.edu/web/fs/wps/worki...sion _2_.pdf
gt;
gt; [3] www.mtg-net.com/sfaq/faq/apr.htm

please disreguard the 6.839%, obviously that is incorrect.

quot;Xhawk57quot; wrote:

gt; I got NUM# when i follow your instructions, I rechecked several times and
gt; could not figure out what i did wrong.
gt;
gt; I am hoping to find a formula that will calculate an adjustable rate APR in
gt; a single cell. so far i have come up with this:
gt;
gt; Loan Amount200000 E2
gt; term 360 E3
gt; fixed period36 E4
gt; finance Charges1500 E5
gt; Initial rate 6.000% E6
gt; index 5.000% E7
gt; Margin 2.750% E8
gt;
gt; Adjustable APR7.65667%:
gt; =(((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)))
gt;
gt; I have calculator that gave an apr of 6.839%
gt;
gt; any thoughts?
gt;
gt; quot; wrote:
gt;
gt; gt; quot;Xhawk57quot; wrote:
gt; gt; gt; Does anyone have a formula to calculate an APR for an
gt; gt; gt; adjustable rate mortgage? for example if I have a 200,000
gt; gt; gt; - 30 year mortgage that has a fixed rate of 6% for 3 years
gt; gt; gt; and then adjusts to a rate of 7.75 every year after that
gt; gt; gt; after that, with 1500 in finance charges.
gt; gt; gt; So far I can figure out the APR without adjustment of
gt; gt; gt; =rate(360,PMT(6%/12,360,200000),200000-1500)*12
gt; gt; gt; Any suggestions to complete the formula?
gt; gt;
gt; gt; First, it is important to note that the following is from a US
gt; gt; point of view. In particular, it might be different for Canada.
gt; gt;
gt; gt; Second, it is important to note that the following refers to the
gt; gt; lender's APR, which is what US law requires the lender to report.
gt; gt; This is very different from quot;effectivequot; annual interest rate --
gt; gt; the quot;borrower's APRquot;.
gt; gt;
gt; gt; According to [1], the lender's APR is the IRR over the term of
gt; gt; the loan contract. IMHO, this is difficult to compute using Excel
gt; gt; because IRR() requires a cell for each cash flow. In contrast,
gt; gt; the HP12 calculator allows for grouping similar cash flows.
gt; gt;
gt; gt; Also, according to [1] and [2], the annualized APR is simply
gt; gt; 12 times the monthly APR -- another difference between the
gt; gt; lender's APR and the borrower's APR. Note: I have not
gt; gt; verified that assertion by looking at US law myself, but I take
gt; gt; it for granted, given that it is stated in two different academic
gt; gt; sources. But academia is not always in touch with reality.
gt; gt;
gt; gt; I feel certain that there is an easier way to formulate this
gt; gt; computation. But off-hand, one way to compute the lender's
gt; gt; APR using Excel's IRR() might be:
gt; gt;
gt; gt; 1. In A1, put =-(200000-1500): the contract loan less the
gt; gt; loan fees. Note: The choice of sign is arbitrary, but it
gt; gt; must be the opposite of the sign used for payments (below).
gt; gt;
gt; gt; 2. In A2, put =PMT(6%/12,30*12,-200000): the monthly
gt; gt; payment for the first 3 years based on the initial contract
gt; gt; loan terms.
gt; gt;
gt; gt; 3. In A3, put =A2, and copy down through A37 so that A2:A37
gt; gt; comprise the first 36 payments.
gt; gt;
gt; gt; 4. In A38, put =PMT(7.75%/12,27*12,
gt; gt; -FV(6%/12,3*12,A37,-200000): the payment for the
gt; gt; remaining 27 years of the contract loan, based on the
gt; gt; outstanding loan balance after the first 3 years.
gt; gt;
gt; gt; 5. In A39, put =A38, and copy down through A361. Thus,
gt; gt; A38:A361 comprise the last 27 years of monthly payments.
gt; gt;
gt; gt; 6. In some cell, compute =12*IRR(A1:A361). Be sure to
gt; gt; format as Percentage with 2 decimal places. I get 7.36%.
gt; gt;
gt; gt; It is important to note that while the lender's APR might be
gt; gt; useful (or not!) for comparing loans, it serves no other
gt; gt; purpose. The number (7.36%) tells us nothing about the
gt; gt; actual interest paid on the loan, even if we assume that the
gt; gt; future ARM interest rate estimate is accurate (not!). For
gt; gt; some discussion of this, see [2]. Also see [3] for an
gt; gt; explanation why the lender's APR might not even be suitable
gt; gt; for comparison (klunk!).
gt; gt;
gt; gt; Bottom line: Unless you are just curious or an academic
gt; gt; who wants to understand how loan terms are created
gt; gt; (see [1]), I don't think this quot;APRquot; is worth the trouble to
gt; gt; compute.
gt; gt;
gt; gt;
gt; gt; -----
gt; gt; [1] web.mit.edu/11.431j/www/Fall91602/431_GMch17.ppt
gt; gt;
gt; gt; [2]
gt; gt; mcb.unco.edu/web/fs/wps/worki...sion _2_.pdf
gt; gt;
gt; gt; [3] www.mtg-net.com/sfaq/faq/apr.htm

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

Sorry: I forgot to type the quot;guessquot; 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.

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

So am I :-).

gt; so far i have come up with this:
gt; Loan Amount200000 E2
gt; term360 E3
gt; fixed period36 E4
gt; finance Charges1500 E5
gt; Initial rate6.000% E6
gt; index 5.000% E7
gt; Margin 2.750% E8
gt;
gt; Adjustable APR7.65667%:
gt; =(((RATE(E3,PMT(E6/12,E3,E2),E2-E5)*12)*(E4/E3))
gt; ((RATE(E3-E4,PMT((E7 E8)/12,E3-E4,E2),E2-E5)*12)*((E3-E4)/E3)))
gt; [....]
gt; 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.