Forum Discussion

jfschwartz2's avatar
jfschwartz2
Copper Contributor
Mar 10, 2020

Excel - formula for a mortgage payment for a Canadian Mortgage

Looking for a payment formula to calculate a payment for a canadian mortgage
excel
PReagan's avatar
PReagan
Bronze Contributor
Mar 10, 2020

Hello jfschwartz2,

 

Assuming constant payments and a constant interest rate, that could be done with the PMT() function. You would need to know...

  1. Rate - interest rate per period (i.e. 6% per year is 6%/12 per month)
  2. Nper - number of payments for the loan (monthly is 12, quarterly is 4, yearly is 1)
  3. PV - present value of loan (negative of loan amount)
  • jfschwartz2's avatar
    jfschwartz2
    Copper Contributor
    Mar 10, 2020
    Hi, thanks for this. This payment function works well for mortgages outside of Canada. In Canada, with the exception of variable rate mortgages, all mortgages are compounded semi-annually, by law. This has an impact on the outcome.
    • PReagan's avatar
      PReagan
      Bronze Contributor
      Mar 10, 2020

      jfschwartz2 

       

      My apologies, I was not aware that Canadian Mortgages were compounded semi-annually. Perhaps this may help:

      https://www.exceltip.com/excel-financial-formulas/calculating-canadian-mortgage-payments.html

Resources