View Single Post
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] joeu2004@hotmail.com is offline
external usenet poster
 
Posts: 418
Default How do I caluclate an Annual Percentage Rate in Excel for an ARM?

JON wrote:
I have a term of 30 years or 360 payments
loan amount of $156,462
Prepaid finance costs of $7,421.77
Amount Financed after Prepaid Fin costs of $149,040.23
zero future value
starting rate for 5 years @ 5.125%
Mortgage insurance of $101.7 for 153 payments and then it drops
Rate goes to 8.049 after 60 payments.
My mortgage processing software is coming up with 7.981% for APR
I don't believe the starting rate or adjusting rate is a factor needed.
What function or formula can I use in excel?


For a US loan, the following describes one way. I suspect it is not
the best way. But it does produce the same result as your mortgage
software.

The loan payment (excluding the mortgage insurance premium) for the
first 60 payments can be computed as follows ($851.92):

A1: =ROUND(PMT(5.125%/12, 360, -156462), 2)

The remaining balance of the loan can be computed as follows
($143,929.57):

A2: =FV(5.125%/12, 60, A1, -156462)

The loan payment (excluding the mortgage insurance premium) for the
remaining 300 payments can be computed as follows ($1115.55):

A3: =ROUND(PMT(8.049%/12, 300, -A2), 2)

Now fill in the following table. The first entry is the mortgage
amount less finance costs ($149,040.23). The next 153 entries are the
appropriate payment plus mortgage insurance, expressed as a negative
number.

B1: =156462 - 7421.77
B2:B62: =-($A$1 + 101.70)
B63:B154: =-($A$3 + 101.70)
B155:B361: =-$A$3

Finally, in any cell, compute APR as follows (7.9812%):

=12*IRR(B1:B361, 8%/12)

Note: I did not think that mortgage insurance premiums (as well as any
other periodic insurance premiums and taxes) are included with the loan
payment for the purpose estimating APR. I base that primarily on the
results of some online ARM APR calculators the allow you to specify
such additional periodic amounts separately. Their APR estimate is the
same whether the additional amounts are zero or not. Excluding the
mortgage insurance premium, the ARM APR is 7.0198%.

On the other hand, after a quick reading of Reg Z (Truth in Lending
Act), it seems that such additional amounts might be included, at least
under some circumstances. See TLA section 226.4(b) and exceptions in
226.4(c)-(e).