Canadian mortgage, weekly payment and nper

[paul_silverman]

I have setup my formula for calculating a Canadian mortgage with an
accelerated weekly payment.

=PMT((rate/2+1)^(1/26)-1,52*25,value,0,0)

The formula does not give an accurate value since the weekly
accelerated payment actually reduces the total number of payment, i.e.
the loan gets paid in 21.x years instead of 25 years. How do I fix
this formula? Do I need to compute first the real nper value based on
the fact that i am doing weekly payments?

Thanks.

Paul

 

[Tyro]

The fact that it is a Canadian mortgage means nothing to the PMT function.
Exactly what do you mean by "accelerated payment"?

Tyro

 

[paul_silverman]

The fact that it is a Canadian mortgage means nothing to the PMT function.
Exactly what do you mean by "accelerated payment"?

Tyro

Not true. U.S. and Canadian mortgage differ a bit, where in a
Canadian mortgage 6% is actually 3% compounded semiannually.
Therefore, the PMT function is slightly different. Weekly accelerated
payments, my understanding is that the payment amount seems to be
based on the total monthly payments for 13 months (i.e. a year plus
one month), based the original loan term. In other words, a 300 month
loan (25 years) gets paid in around 245 months if you do weekly
payments.

Paul

 

[Tyro]

Excel's PMT function could not care less. Where do you see a parameter in
the PMT function specifying "Canadian" or "US"?


Tyro

The fact that it is a Canadian mortgage means nothing to the PMT function.
Exactly what do you mean by "accelerated payment"?

Tyro

Not true. U.S. and Canadian mortgage differ a bit, where in a
Canadian mortgage 6% is actually 3% compounded semiannually.
Therefore, the PMT function is slightly different. Weekly accelerated
payments, my understanding is that the payment amount seems to be
based on the total monthly payments for 13 months (i.e. a year plus
one month), based the original loan term. In other words, a 300 month
loan (25 years) gets paid in around 245 months if you do weekly
payments.

Paul

 

[paul_silverman]

Excel's PMT function could not care less. Where do you see a parameter in
the PMT function specifying "Canadian" or "US"?




Not true.  U.S. and Canadian mortgage differ a bit, where in a
Canadian mortgage 6% is actually 3% compounded semiannually.
Therefore, the PMT function is slightly different.  Weekly accelerated
payments, my understanding is that the payment amount seems to be
based on the total monthly payments for 13 months (i.e. a year plus
one month), based the original loan term.  In other words, a 300 month
loan (25 years) gets paid in around 245 months if you do weekly
payments.

Paul

I found a workaround, I need to first compute my monthly payment, and
then my weekly payment = (monthly payment * 13) / 52.

Paul.

 

[Dave Peterson]

Maybe...

http://www.vertex42.com/Calculators/Canadian-mortgage.html

 

[Fred Smith]

Tyro,

Canadian mortgages are compounded semi-annually. So when Canadians ask how
to compute mortgage payments, they want to know how to convert the posted
rate (eg 6%) into the correct periodic rate. In Paul's case, however, he
calculated the periodic rate correctly, but miscalculated the weekly
payment.

Regards,
Fred.

 

[Fred Smith]

Paul,

I'm glad you got the correct result. However, it's useful to remember that
you are getting the benefit of a reduced amortization period not because of
paying weekly, but because you are making higher payments over the year.

If your normal payments are say, $1000/month, and you decide to pay weekly,
the equivalent payment would be 1000 x 12 / 52, or $230.77. Both these
payment schedules total $12,000/year.

However, most people find that their budget can accommodate a weekly payment
of $1000/4 or $250/week. This is what you call 'accelerated weekly'
payments, and your formula of 1000 x 13 / 52 yields the same result.

But remember, you are getting the reduced amortization period because you
are paying and extra $1000/year ($13,000 vs $12,000). Paying weekly (vs
monthly) has only a minor effect on amortization.

Regards,
Fred

Excel's PMT function could not care less. Where do you see a parameter in
the PMT function specifying "Canadian" or "US"?




Not true. U.S. and Canadian mortgage differ a bit, where in a
Canadian mortgage 6% is actually 3% compounded semiannually.
Therefore, the PMT function is slightly different. Weekly accelerated
payments, my understanding is that the payment amount seems to be
based on the total monthly payments for 13 months (i.e. a year plus
one month), based the original loan term. In other words, a 300 month
loan (25 years) gets paid in around 245 months if you do weekly
payments.

Paul

I found a workaround, I need to first compute my monthly payment, and
then my weekly payment = (monthly payment * 13) / 52.

Paul.

 

[Tyro]

The PMT function does not care. You must supply the correct values to it, to
get the correct answer. It does not recognize the "mortgages" of various
countries. That is my point.

Tyro

 

[joeu2004]

I found a workaround, I need to first compute my monthly payment,
and then my weekly payment = (monthly payment * 13) / 52.

It's not a work-around. Depending on the terms of your loan, that
might be the right answer, although I quibble with the unnecessary
extra math.

See http://homebuying.about.com/cs/mortgagearticles/a/biweekly_plan.htm
for a model of how accelerated payment plans are typically
structured. Although that explanation is for biweekly payments, it is
easy to apply it to a weekly plan.

A monthly payment is computed based on the "normal" amortization
schedule. You are correct that for Canadian loans, the rate argument
to the PMT() function must be different than for a US loan. For
example, for a $100,000 Canadian loan at 6%, the payment is
approximately:

=pmt(rate(6,0,-1,1+6%/2), 25*12, -100000)

or

=pmt((1+6%/2)^(1/6)-1, 25*12, -100000)

For weekly accelerated payments, that amount is simply divided by 4 --
the same as your multiplying by 13/52. Because you are making 52
payments per year, your total annual payment is the same as making 13
monthly payments.

Caveat: If you are talking to a lender about a weekly payment plan,
be sure that this is what the lender is talking about. Alternatively,
a weekly plan could simply amortize the loan over the full term. In
that case, the payment amount is computed exactly as you had done in
the first place.

Note: In the real world, the PMT() result must rounded at least to
the cent. If it is always rounded up, that will result in a smaller
final payment.

 

[joeu2004]

The PMT function does not care. You must supply the correct values to it, to
get the correct answer. It does not recognize the "mortgages" of various
countries. That is my point.

And the OP never said or implied anything different. That is the
point that everyone else has been trying to make. It is self-evident
that the OP understood that your point before you made it. Look at
his use of PMT().

 

[Tyro]

I simply asked him what he meant by "accelerated payment". Got no answer.
Why are you getting bent out of shape?

Tyro

The PMT function does not care. You must supply the correct values to it,
to
get the correct answer. It does not recognize the "mortgages" of various
countries. That is my point.

And the OP never said or implied anything different. That is the
point that everyone else has been trying to make. It is self-evident
that the OP understood that your point before you made it. Look at
his use of PMT().

 

[joeu2004]

Maybe...
http://www.vertex42.com/Calculators/Canadian-mortgage.html

Maybe not. That calculates the weekly payment essentially the same
that the OP did initially, which the OP said he does not want. That
approach assumes that interest is computed based on the weekly
balance. With so-called accelerated payment plans, interest is still
computed based on the monthly balance.

As a consequence, while it is true that the borrower pays more
annually, he pays the same amount of interest. The result is an
accelerated reduction of the principal, equivalent to adding extra to
one payment in a year equal to the monthly payment.

IMHO, the latter (adding extra to one or more payments) is better
because you have the flexibility of not doing that when money is tight.