#1   Report Post  
Posted to microsoft.public.excel.misc
Xhawk57
 
Posts: n/a
Default Adjustable Rate APR

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Adjustable Rate APR

"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
  #3   Report Post  
Posted to microsoft.public.excel.misc
Xhawk57
 
Posts: n/a
Default Adjustable Rate APR

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Xhawk57
 
Posts: n/a
Default Adjustable Rate APR

please disreguard the 6.839%, obviously that is incorrect.

"Xhawk57" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Adjustable Rate APR

"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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
FVSCHEDULE should allow cell reference for interest rate schedule Philipm Excel Worksheet Functions 1 January 25th 06 11:20 AM
XNPV vs. NPV(quarterly) different results magis Excel Worksheet Functions 3 January 6th 06 06:21 PM
How do I calculate APR for an adjustable rate mortgage? mortgage expert Excel Discussion (Misc queries) 1 November 23rd 05 10:00 AM
subtraction of times, convert & multiply by a conditioned rate sanscull Excel Worksheet Functions 6 May 20th 05 02:02 AM
Is there an adjustable rate amortization schedule in Excel? dbmmsg Excel Worksheet Functions 1 May 19th 05 09:47 PM


All times are GMT +1. The time now is 07:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"