Canadian Mortgage Calculation

Does it work if you take a US version and simply add the optional 5th arugment of 1 to the Payment formula?

Ie
replace
=PMT(rate,Nper,PV)
with
=PMT(rate,Nper,PV,,1)
This message was edited by IML on 2002-06-13 07:09

Some Canadian mortgage have monthly payments but the interest compounds half-yearly.
The same concept can apply with bi-monthly payments etc.

You can adjust the interest factor and use PMT.

Example with 5%

=(1+.05/2)^(2/12)-1 calculated rate

=PMT((1+.05/2)^(2/12)-1,B3*12,B1,0,0)

Adjust the cell references for your information and replace .05 with a cell reference for the rate.
This message was edited by Dave Patton on 2002-06-13 13:11

I still don't follow. I find your explanation confusing Can you give an example for, say $10,000. at 5% over 5 years with monthly payments. Also, after calculating the monthly payment, how do I determine how much is applied to the principal so I can print an amortization chart.

On 2002-07-30 11:36, gord9b wrote:
I still don't follow. I find your explanation confusing Can you give an example for, say $10,000. at 5% over 5 years with monthly payments. Also, after calculating the monthly payment, how do I determine how much is applied to the principal so I can print an amortization chart.

Click to expand...

I will e-mail a sample spreadsheet.
I will send it later today.