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.

     

smpa01's avatar
smpa01
Community Champion
6 years ago

saturation   I calculated them as following. Can you please advise how did you arrive on those numbers for those two columns?

 

IDLoan AmtYearsRateFV# MonthsMonthly PMT
11000050.0375$12,021.0060$200.35
225000300.0425$87,140.88360$242.06
saturation's avatar
saturation
Frequent Visitor
6 years ago

smpa01 They're not real, only for display only purposes....I'm just looking to see how to get them to calculate properly (the last 2 columns), whatever they may be....

  • smpa01's avatar
    smpa01
    Community Champion
    6 years ago

    saturation  DAX (faster)/ M (slower) query can aboslutely cater to your need to prepare a dynamic amortization schedule, once you figure out how you can replicate excel CUMIPMT and CUMPRINC