Forum Discussion

Jalil1985's avatar
Jalil1985
Copper Contributor
Jul 22, 2022

Power Query creating a custom function automate

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

office 365
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 23, 2022

    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

     

     

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

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jul 22, 2022

    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:

     

Resources