Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 price? I can't believe there's nothing in Power BI to do this--or so it appears (or DAX).
Solved! Go to Solution.
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]
Hi @saturation
Hi @saturation
Could you show some data example or details of your requirement?
There are some examples of Mortgage Payment Calculation in Power BI:
https://community.powerbi.com/t5/Data-Stories-Gallery/Loan-Calculator/td-p/242585
https://www.youtube.com/watch?v=H65PLeAglBM
The loan calculator is pretty close. Basically, here's what I've got (static data, no need for the sliders).
ID | Loan Amt | Years | Rate | Monthly Pymt | Interest
1 10000 5 .0375 | 150 | 20
2 25000 30 .0425 | 250 | 35
The Monthly Pymt and Interest columns are the data that I need....Thoughts?
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]
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...
@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!
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]
@saturation I calculated them as following. Can you please advise how did you arrive on those numbers for those two columns?
ID | Loan Amt | Years | Rate | FV | # Months | Monthly PMT |
1 | 10000 | 5 | 0.0375 | $12,021.00 | 60 | $200.35 |
2 | 25000 | 30 | 0.0425 | $87,140.88 | 360 | $242.06 |
@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....
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |