Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Alteryx for Excel Users: Calculating Loan Installments and Amortization Schedules

WayneWooldridge
Alteryx
Alteryx
Created

Let's say you've got your eye a new car or home. Before visiting with a banker you'd like to run some numbers and see if payments fit within your budget. Excel has a couple of functions that can help you. To calculate your payment, you use the =PMT function. You can even take it a step further and calculate how much you'll pay in interest (=IPMT) and principle (=PPMT) for your first payment and each payment thereafter.

Payment:

Loan Payment 01.png

Payment applied towards principal (payment number 1 of 60):

Loan Payment 02.png

Payment applied towards interest (payment number 1 of 60):

Loan Payment 03.png

Summary:

Loan Payment 04.png

Just do that 59 more times, incrementing 'Loan Payment Number' by 1 until you reach 60 and you will create your very own amortization schedule. If this had been a home loan with a 30-year term (360 months), you would only need to update this spreadsheet 359 more times!

That's where an iterative macro can help and save you a lot of time. Attached is an app with an embedded iterative macro. It does the same thing the Excel spreadsheet above does but it loops through each payment period and calculates how much of your payment goes towards principal and interest and recalculates your new loan balance. When you run the app, you'll enter your loan amount, APR and the number of months in the term (length) of the loan.

Attachments
No ratings
Comments
Vishvini
5 - Atom

Hi Wayne,

 

This attachment was exactly what I wanted. Thank you so much. 

As I tried to improvise on this example, I encountered an issue. I am trying to including multiple loans in the same file so that my output gives me multiple files or tabs on an excel for each loan. But the problem is the amounts are interchanging. 

 

I am not sure how much I have been able to communicate there. Can you help me figure this out. I can share my trial file.

 

Thanks!

ponraj
13 - Pulsar

 @WayneWooldridge, I have solution for the problem which @Vishvini is facing.  I wanted to upload the solution here. However, I don't find any option for attaching workflow.  Can you advice? 

WayneWooldridge
Alteryx
Alteryx

@ponraj

 

You will need to start a new post in order to attach a file.  

Come back to this post and include a link to the new one.

cosgro17
5 - Atom

Sorry for digging up an old post, but I am interested in modifying this tool to calculate the interest that would be earned on many loans (100+) by uploading a file with the loan details and then tagging the key inputs.  I tried to modify the workflow but it was beyond my skill set.  @Ponraj, you mentioned having a solution of this nature, but I could not find the new thread.  Can you provide me with the link to where you uploaded this solution?