View Single Post
  #21   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?

Clarification....

I wrote:
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 ;-).