Urgent Unable to create amortization table in Power BI

[Rahulsingh]

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.

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.  

[Vvelarde]

@Rahulsingh

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

I'm not familiarized with mortgage Calcs.

Regards

Victor

[Rahulsingh]

@Vvelarde

Hi 

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

[Rahulsingh]

@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.

[sbone]

I have the same question .....

[Rahulsingh]

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

[Greg_Deckler]

@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.

[oscarpowerpro]

@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!

[oscarpowerpro]

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

[Rahulsingh]

@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. 

[sbone]

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.

[sbone]

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.