 Problem:

The following PMT formula calculates the monthly payment for a \$100,000 mortgage, repaid over a period of 20 years, at 8% annual interest:
=PMT(8%/12,12*20,100000,0,0)
As Canadian interest rates are calculated semi-annually, rather than annually, the above formula will not calculate the payments correctly.
How can we modify the above PMT formula to calculate monthly payments for Canadian mortgages?

Solution:

With interest rate in column A, period (years) in column B, and mortgage sum in column C, use the PMT function as shown in the following formula:
=PMT((A2/2+1)^(2/12)-1,12*B2,C2,0,0)

1. Hi there, I'm trying to figure out the accelerated bi weekly payments for canadian mortgages. The above formula gives me the answer for monthly payments, however many people prefer accelerated bi weekly payments. Here are some common definitions.
Accelerated bi-weekly has larger payments than bi-weekly.

This is because the regular bi-weekly takes a 24-payment cycle and separates it out into 26 payments, thus making each payment slightly smaller. The net amount paid each year is unchanged.

The accelerated takes a 24-payment cycle and adds on 2 more payments of the same size, for a total of 26 payments. So you are paying slightly more each year.

• Thats easy, change the 12*B2 from 12 to 26 or 52

• How would you calculate IPMT and PPMT?

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.