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

3 Replies

Sort By
  • 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