Power Query creating a custom function automate

Copper Contributor

Dear all,

Currently I am trying to automate loan schedule in power query the following are the key points:

 

Opening Balance is Rs.150,000

Monthly deduction is fixed per month Rs.4500 

Interest rate is 8% per annum

Interest Amount = Opening Balance * Interest Rate/12 (i.e = Rs.1,000 per month)

Principal Amount = Monthly deduction – Interest Amount (3500)

 

The above calculation is preformed on manual in power query all I want to automate the process for the complete year. Is there any functionality in Power Query?

(simple file is attached)

 

 I will remain thankful to yours help.

Regards

Jalil

2 Replies

@Jalil1985 

 

I'm not at all sure why you're linking this all to Power Query. The kind of results you describe appear to be available solely through using a few of the many Financial Functions in "normal" Excel. You can find them all on this page from a helpful and instructive website.

 

Here to illustrate is a partial list:

mathetes_0-1658502191216.png

 

Hi @Jalil1985 

 

With a fixed monthly payment of 4500 and an annual rate of 8% I can't reconcile the numbers  (just a few) highlighted in red

 

_Screenshot1.png

 

On the other hand getting the following with PQ + a Pivot Table is no problem:

_Screenshot.png