Forum Discussion

saturation's avatar
saturation
Frequent Visitor
6 years ago
Solved

Mortgage Payment Calculation

Hi, I've checked the forums....It doesn't appear it's possible to do mortgage amortization, but I'm wondering if it's possible just to calculate a simple monthly payment based on # years, rate, and p...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi FAIRFIELD1511 

    I modify my formula according to this article.

    Assume the second line is :

    you borrow 25000 at 0.0425 rate for 30 years,

    then formula for part1-part3 are as follows:

    monthly rate = [yearly rate]/12

part1 = POWER(1+[monthly rate],[years]*12)-1

part2 = [monthly rate]*POWER(1+[monthly rate],[years]*12)

part3 = [part1]/[part2]

Loan payment monthly = [loan amt]/[part3]

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi saturation 

If i understand the Mortgage Payment Calculation correctly,

And your [rate] is monthly rate,

You could create calculated columns in Power BI Desktop

monthly pymt =
VAR power1 =
    POWER ( ( 1 + [rate] ), [years] * 12 )
RETURN
    [loan amt]
        * DIVIDE ( [rate] * power1, power1 + 1 )

total interest = [monthly pymt]*[years]*12-[loan amt]

 

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

FAIRFIELD1511's avatar
FAIRFIELD1511
Regular Visitor
6 years ago

Thanks v-juanli-msft . I think your formula is very close, although there's a miscalculation somewhere, and I can't seem to locate it. Basically if I do mortgage calculator for the 2nd loan in the example (just googled it), the actualy monthly payment should come out to $123. I'm struggling to figure out where in the formula needs to be corrected...

  • FAIRFIELD1511's avatar
    FAIRFIELD1511
    Regular Visitor
    6 years ago

    @v-juanli-msft Any thoughts on the monthly payment results provided from the Google mortgage calculator vs what your results look like in the example? Just trying to figure out where the formula needs to be corrected. Any help is appreciated!

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi FAIRFIELD1511 

      I modify my formula according to this article.

      Assume the second line is :

      you borrow 25000 at 0.0425 rate for 30 years,

      then formula for part1-part3 are as follows:

      monthly rate = [yearly rate]/12

part1 = POWER(1+[monthly rate],[years]*12)-1

part2 = [monthly rate]*POWER(1+[monthly rate],[years]*12)

part3 = [part1]/[part2]

Loan payment monthly = [loan amt]/[part3]

      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.