cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Urgent Unable to create amortization table in Power BI

Hi Everyone, 

 

I am trying to create an amortization table in power bi from a database.

 

In the database, I have mortgage balance, Account and Term Interest Rate. Currently, loan database only has the payments made in prior months. The user is unable to see entire Loan amortization table. 

 

I have created a date Dimension but I don't know how to generate an entire amortization table.

Power BI question.JPG

If I can get help in generating a table with future dates until MtgBalance becomes zero that would be a great help. 

 

Thank you in advance.  

 

11 REPLIES 11
Community Champion
Community Champion

@Rahulsingh

 

Hi, Can you show Data Sample and the Way that you want the result.

 

I'm not familiarized with mortgage Calcs.

 

Regards

 

Victor




Lima - Peru

 

@Vvelarde

Hi 

That would be a great help, I have the mortgage formula measure already made.

@Vvelarde

Hi 

If you look at the screenshot I have pasted current table and future table, in future table I have added the two rows below and I would like to see dates repeating every month in the future until mortgage balance becomes zero.

Frequent Visitor

I have the same question .....

I wish someone can shed some light , it be great help 

@Rahulsingh@sbone

 

OK, I created it this way:

 

First, create Measures for P, n and i like this:

 

n = 360
n = 0.05
P = 250000
Payment = [P]*(([i]/12)*POWER(1+([i]/12),[n]))/(POWER(1+([i]/12),[n])-1)

Then, create a Table with this formula:

 

Mortgage = GENERATESERIES(0,[n],1)

Now create the following columns:

 

Balance = [P]*(POWER(1+[i]/12,[n])-POWER(1+[i]/12,[Value]+1))/(POWER(1+[i]/12,[n])-1)

MonthlyInterest = ([i]/12)*[Balance]

MonthlyPrincipal = [Payment]-[MonthlyInterest]

MonthlyPayment = [Payment]

I will do some work on this and see if I can get this to be truly dynamic but let me know if this is along the lines of what you are looking for.

 

 

 

 

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler  could you please let me know what the column [Value] is referencing in the Balance formula below?

 

Balance = [P]*(POWER(1+[i]/12,[n])-POWER(1+[i]/12,[Value]+1))/(POWER(1+[i]/12,[n])-1)

 

Thanks!

do you have the pbix file on this Greg? Super interesed in recreating something similar! @Greg_Deckler 

@Greg_Deckler

 

Hi Greg

I do not have the value of n  but I was able to create the Mortgage Interest Rate, Monthly payments, and the Balance field because I can get the loan amount, Interest rate,  first loan payment date from the database but I need to generate entire amortization table with future dates. So essentially I would like to get future dates until Balance becomes zero. 

Thank you Greg. I appreciate your response. If there was a way to be able to input variables into that table we would be good, but I can't find a way to do that.

FYI our firm made the decision this afternoon to move away from Power BI since it cannot handle mortgage amortization tables within the app itself. Good luck.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors