How do I caluclate an Annual Percentage Rate in Excel?

[joeu2004]

Clarification....

Can you provide a pointer to an online copy of the equivalent Canadian
regulations?

I inferred that, in fact, Canada does also compute a nominal rate for
the APR, perhaps the same way as the US, because one online Canada APR
calculator states that the APR is the same as the nominal rate if there
are no "borrowing charges" (loan fees). I do not believe that would be
the case if the Canadian APR is computed by compounding.

I did not intend to imply that Canadians "nominalize" the monthly rate
in the same way as the US, namely 12*RATE(...) in the simple case. And
perhaps I should not use the term "nominal" altogether for the Canadian
APR, since Canadian mortgage interest rates are specified as twice a
semiannual compounded rate.

Using the OP's terms, the following is my __guess__ as to how a
Canadian APR might be computed for both fixed-rate mortgages and ARMs.
I cannot find a (free) online Canadian mortgage calculator that shows
APR, perhaps because Canadians are enlightened enough to realize that
the APR is virtually useless ;-). So I cannot confirm my guess. If
someone has access to professional mortgage software for Canada, it
would be great if you would post its results for these examples.


Canadian FIXED RATE, FIXED PAYMENT LOAN (simplified):
Amortization term: 360 months
Loan amount: $156,462
Balloon payment: $0
Prepaid loan cost: $7421.77
Additional financial payments: $0
Fixed rate: 5.125%

Monthly payment ($846.74):
A1: =round( pmt( rate(6, 0, -1, 1+5.125%/2), 360, -156462 ), 2 )

APR (5.5689%):
=2 * ( fv( rate(360, A1, -(156462 - 7421.77)), 6, 0, -1 ) - 1 )


Canadian VARIABLE RATE, VARIABLE PAYMENT LOAN:
Amortization term: 360 months
Loan amount: $156,462
Balloon payment: $0
Prepaid loan cost: $7421.77
Additional financial payments: $101.70 for 1st 153 payments (mortgage
insurance)
Fixed rate: 5.125%
Fixed term: 60 months
Initial variable rate: 8.049%
Variable term: 300 months

Loan payment (excluding the mortgage insurance premium) for the
first 60 payments ($846.74):

A1: =round( pmt( rate(6, 0, -1, 1 + 5.125%/2), 360, -156462 ), 2 )

Remaining balance of the loan after the first 60 payments
($143,821.10):

A2: =fv( rate(6, 0, -1, 1 + 5.125%/2), 60, A1, -156462 )

Loan payment (excluding the mortgage insurance premium) for the
remaining 300 payments ($1102.16):

A3: =round( pmt( rate(6, 0, -1, 1 + 8.049%/2), 300, -A2 ), 2 )

Cash flow table:

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

APR (8.0147%)
=2 * ( fv( irr(B1:B361, 8%/12), 6, 0, -1 ) -1 )

Caveat: As I noted elsewhere, I am not sure that the mortgage
insurance premium should be included in the APR calculation. I retain
it, in part, for comparison with the US loan and, in part, because it
makes the problem more interesting ;-).

 

[Guest]

Hi,

I trying to solve and validate the APR on an ARM loan with the IRR function
based on the following,
- 3/1 ARM with initial of 5.75% (rate good for 36 months)
- Loan term of 30 years (360 months)
- Loan amount of $250,000
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
- 1% in origination points; $2,500
- $750 in processing fees
- Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)
- An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the
37th month
- Loan balance at end of 36 month period is $239,771.05;
fv(.0575/12,36,1458.93,250000)
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
A1:A3; header information
A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

This produces an APR of 6.887%; the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

 

[joeu2004]

I trying to solve and validate the APR on an ARM loan with the IRR
function based on the following,
[....]
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
[....]
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
[....]
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

Why did you choose to use different payment amounts in your
worksheet? The first pair of payment amounts ("based on the
following") is the correct one to use.

In your computation, the last parameter to PMT() for the first 36
months is incorrect. It should be simply 250000. It is computed as
if the lender gave you $250,000, and you paid the lender the amount of
the initial costs out-of-pocket.

The parameters to PMT() for the last 324 months seem correct; and
indeed, with those parameters, PMT() returns the correct amount,
namely $1688.45. I don't know how you got $1693.05; perhaps you
mistyped ".0728" for 7.25%.

A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)

For the IRR, each cash flow is the net of inflows and outflows in that
period. For the initial period, the inflow is the initial loan amount
($250,000), and the outflow is the sum of the initial loan costs (at
least 2500 + 750 + 598.96). That is, A5 should be (at least):

=250000 - 2500 - 750 - 598.96

the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

With the corrections above, I compute an annualized IRR of 7.0079%
(rounded), including the prepaid interest. To get 7.168% and 7.144%,
I would have to assume that the lender included an addition $4010 to
$4040 in initial costs, which you (or the lender) did not mention.
Although it seems unlikely that two lenders would make the same such
assumption, note that often different online APR calculators use the
same underlying "engine".

What am I missing?

Only that the APR is really not a good way to compare loans, at least
in the US. I would compare the loan terms and the initial loan costs
separately. But even then, comparing ARMs is tricking business
because of the flexibility that lenders have in determining rates and
payments after the initial fixed period.


--- complete original posting ---

 

[joeu2004]

Errata....

With the corrections above, I compute an annualized IRR of 7.0079%
(rounded), including the prepaid interest. To get 7.168% and 7.144%,
I would have to assume that the lender included an addition $4010 to
$4040 in initial costs, which you (or the lender) did not mention.

I think it is more likely that that is simply the NPV of costs added
to each payment, for example to cover PMI. If you provide the URL for
the mortgage calculators that you used, perhaps I can explain the
descrepancy better.

Only that the APR is really not a good way to compare loans, at least
in the US.

Interestingly, the Consumer Handbook on ARMs
(at http://www.federalreserve.gov/Pubs/arms/armsbrochure.pdf)
says exactly the opposite. It states: "Because all lenders follow
the same rules when calculating the APR, it provides you with a good
basis for comparing the cost of loans".

Well, yes, "all lenders follow the same rules". But the rules have
such great flexibility that two lenders with identical loan terms and
costs can disclose different APRs in advertisements and pre-loan
documents as long as they state that the APR might increase after
consummation.

 

[Guest]

Hi,

Thanks for the response. I made the adjustments and came up with the same
answer. In regard to that $1,693.05 payment reference for cells a42:a365,
that was a typo. Here are the websites that I was using to validate my APR;

http://www.lenderhomepage.com/tools/variable-rate-mortgage-apr-calculator.php

http://www.dinkytown.net/java/MortgageAprAdjustable.html

The information displayed for their calculation of the APR is pretty
straight-forward and there is no mention of any additional fees ($4010 plus).
I've ran my IRR function with many different variables trying to match their
APR, and just could not accept that their number could be "wrong" and the
solution via excel was "correct". Take a look at the URLs and let me know
what you think they are doing to derive their number other than to add some
mystery fees.

Once again, thank you for your input.

I trying to solve and validate the APR on an ARM loan with the IRR
function based on the following,
[....]
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
[....]
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
[....]
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

Why did you choose to use different payment amounts in your
worksheet? The first pair of payment amounts ("based on the
following") is the correct one to use.

In your computation, the last parameter to PMT() for the first 36
months is incorrect. It should be simply 250000. It is computed as
if the lender gave you $250,000, and you paid the lender the amount of
the initial costs out-of-pocket.

The parameters to PMT() for the last 324 months seem correct; and
indeed, with those parameters, PMT() returns the correct amount,
namely $1688.45. I don't know how you got $1693.05; perhaps you
mistyped ".0728" for 7.25%.

A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)

For the IRR, each cash flow is the net of inflows and outflows in that
period. For the initial period, the inflow is the initial loan amount
($250,000), and the outflow is the sum of the initial loan costs (at
least 2500 + 750 + 598.96). That is, A5 should be (at least):

=250000 - 2500 - 750 - 598.96

the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

With the corrections above, I compute an annualized IRR of 7.0079%
(rounded), including the prepaid interest. To get 7.168% and 7.144%,
I would have to assume that the lender included an addition $4010 to
$4040 in initial costs, which you (or the lender) did not mention.
Although it seems unlikely that two lenders would make the same such
assumption, note that often different online APR calculators use the
same underlying "engine".

What am I missing?

Only that the APR is really not a good way to compare loans, at least
in the US. I would compare the loan terms and the initial loan costs
separately. But even then, comparing ARMs is tricking business
because of the flexibility that lenders have in determining rates and
payments after the initial fixed period.


--- complete original posting ---

I trying to solve and validate the APR on an ARM loan with the IRR function
based on the following,
- 3/1 ARM with initial of 5.75% (rate good for 36 months)
- Loan term of 30 years (360 months)
- Loan amount of $250,000
- Initial payment of $1,458.93; pmt(.0575/12,360,250000)
- 1% in origination points; $2,500
- $750 in processing fees
- Prepaid interest of $598.96 ($250k @ 5.75% for 15 days/360 day basis)
- An index equal to 5.5%, plus a 1.75% margin; 7.25% rate starting in the
37th month
- Loan balance at end of 36 month period is $239,771.05;
fv(.0575/12,36,1458.93,250000)
- Loan payment starting in month 37 is, $1,688.45; pmt(.0725/12,324,239771.05)

My worksheet is set up as follows,
A1:A3; header information
A4; 12*IRR(A5:A365,0.1/12)
A5; $250,000 (the loan amount)
A6:A41; ($1,481.39) neg. #; pmt(.0575/12,360,(250000+2500+750+598.96))
A42:A365; ($1,693.05) neg. #; pmt(.0725/12,324,239771.05)

This produces an APR of 6.887%; the APR on one website illustrates an APR of
7.168% (includes the prepaid interest); another site that does not take into
account the prepaid interest illustrates an APR of 7.144% (note: when I strip
out the prepaid interest at the first website, it also produces a 7.144%
APR). What am I missing?

 

[joeu2004]

http://www.lenderhomepage.com/tools/variable-rate-mortgage-apr-calcul...
http://www.dinkytown.net/java/MortgageAprAdjustable.html
[....]
I've ran my IRR function with many different variables trying to match their
APR, and just could not accept that their number could be "wrong" and the
solution via excel was "correct".

In this posting, I'll pick apart the dinkytown calculator. I do
conclude that its APR computation is incorrect when the interest rate
varies. Like you, I am relunctant to reach that conclusion. But
consider the following.

First, it appears that the dinkytown calculator treats the starting
interest rate plus margin as the (lifetime) interest rate cap. Also,
it appears that it adds the "max adjustment" to the starting rate
until that rate cap is reached, starting after "months before first
adjustment" at intervals of "months between adjustments". The current
index does not seem to be a factor at all. I believe that is a misuse
of those factors -- or those factors are misnamed. But I will take
their usage for granted.

If we set "max adjustment" to 0%, we get a fixed-rate loan. With a
starting rate of 5.75% for a loan amount of 250,000 over 30 years, the
first 359 payments are 1458.93 (rounded), and the last payment is
1460.88 (rounded). [Aside: Using FV() in Excel, I compute a final
payment of 1460.98 rounded. The descrepancy arises because it appears
the dinkytown rounds each periodic interest amount. I cannot say with
impunity whether that is right or wrong, or common or uncommon among
lenders.]

With points of 2500 (1%), other fees of 750 and 0% origination fee
(and no prepaid interest), the dinkytown calculator reports an APR of
5.871%. In Excel, I compute an IRR of 5.8706% (rounded) --
essentially the same -- by setting the initial cash flow to 250000 -
2500 - 750 and by multiplying the resulting monthly IRR by 12.

I believe that demonstrates that the IRR paradigm that I suggested in
my previous posting is correct, at least to the extent that it matches
the dinkytown calculator.

So far, so good. But....

If we set "max adjustment" to 0.5%, the dinkytown calculator computes
payments of 1458.93, 1533.77, 1608.39 and 1682.64 and interest rates
of 5.75%, 6.25%, 6.75% and 7.25% starting with payment numbers 1, 37,
49 and 61 respectively, with a last payment of 1682.46. The reported
APR is 7.144%.

But the calculator reports the same 7.144% APR when "max adjustment is
1.0% and 1.5% or more, even though: (a) for a 1.0% adjustment, the
payments are 1458.93, 1610.30 and 1686.58 starting with payment
numbers 1, 37 and 49, with a last payment of 1678.71; and (b) for a
1.5% adjustment or more, the payments are 1458.93 and 1688.45 starting
with payment numbers 1 and 37, with a last payment of 1681.81.

I do not believe the APR should be the same in all 3 cases. FYI, I
compute IRRs of 6.8779%, 6.9479% and 6.9844% (rounded) for the 3 cases
respectively.

(I will examine the lenderhomepage calculator in a later posting.
It's late now for me.)

 

[joeu2004]

Errata...

First, it appears that the dinkytown calculator treats the starting
interest rate plus margin as the (lifetime) interest rate cap.

Correction: It treats the current index plus margin as the lifetime
rate cap.

 

[joeu2004]

Here are the websites that I was using to validate my APR;
http://www.lenderhomepage.com/tools/variable-rate-mortgage-apr-calcul...
http://www.dinkytown.net/java/MortgageAprAdjustable.html

Aha! I believe I know how those calculators arrive at an "APR" of
7.168% and 7.144% with and without the prepaid interest of 598.96 --
at least in the case of an initial rate of 5.75% for 36 months and
7.25% for the remaining 324 months.

Note that in that case, the total of payments is 599,582.80 and,
dividing by 360, the average payment is 1665.51. (The lenderhomepage
calculator truncates the average to 1665.50.)

Making the simplifying assumption of a constant payment of 1665.51
over the full 360-month term, we do indeed compute an annual rate of
7.168% and 7.144% with and without the prepaid interest of 598.96. In
Excel, they can be computed with the RATE() function as follows:

=12*rate(360, 1665.51, -(2500000-2500-750-598.66))
=12*rate(360, 1665.51, -(2500000-2500-750))

Clearly, that is only an approximation of the APR -- and not a good
one(!). I do not believe it follows the algorithm for computing the
APR in Appendix J of Reg Z -- the US regulation commonly called "Truth
in Lending".

PS: That still does not explain how the dinkytown calculator computes
the same "APR" of 7.144% (without prepaid interest) with different
payment schedules based on varying adjustments. The average payments
differ significantly, resulting in different "APRs" based on the
simplifying paradigm above. For example, for adjustments of 0.5% and
1.0%, the simplified "APR" would be 7.067% and 7.118% respectively. I
think the dinkytown calculator simply has a defect.

 

[Guest]

Kudos for your effort; that was outstanding! I’m interested to see the TIL
position on calculating the APR. Thanks again for your assistance.

 

[joeu2004]

I'm interested to see the TIL position on calculating the APR.

The Truth in Lending method of computing APR is essentially the IRR.
And I might note that it is the Excel function IRR(), not XIRR(). A
(US) loan APR is a nominal rate, not a compounded rate like the APY.

You can find all of Reg Z at
http://www.fdic.gov/regulations/laws/rules/6500-1400.html .
The link to Appendix J takes you to
http://www.fdic.gov/regulations/laws/rules/6500-1950.html#6500appendixjtopart226
..

Unfortunately, the presentation and confusing terminology leave much
to be desired, IMHO . But you might recognize the "general equation"
as an NPV formula, where the left side is the NPV of the advances
(e.g. loan amount) and the right side is the NPV of the payments.

Thanks again for your assistance.

My pleasure. Helping others is always an education for myself, too.