Finance PMT with several loans

[Jirwin]

Hello,

I'm trying to use the Finance PMT function along with a iterative Macro to calculate the repayment scheduled of several loans.

The goal is to have have a summary of the total combined payments each month.

I can manage one loan, but when i add more loans I'm running into issues.

All the data i have is variable. (Annual Interest Rate, Duration, Finance Amount, Future value (RV).

Example:

Annual Interest Rate	Duration	Finance amount	RV
3,50%	37	€20.000,00	€10.000,00
4,10%	37	€19.000,00
3,50%	25	€18.000,00	€4.000,00
4,10%	25	€17.000,00

At the 37th month i should have an outstanding balance of €14k.

Unfortunately this isn't the case.

Issues:

1. When I have loans with a different duration the combined schedule isn't correctly amortized.

2 When i have a future value the combined schedule isn't correctly amortized.

Can anyone help with this issue?

Thanks in advance,

James

[ponraj · Accepted solution]

I am attaching a sample workflow which you can use to calculate EMI for multiple loan.  Originally I developed this workflow for the following post. 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Alteryx-for-Excel-Users-Calculating-Loan-Ins...

@cosgro17 Marking your name as It may be helpful for you. 

[ChipotleSam]

Fantastic! Thank you, @ponraj!!

[Haker123]

Hi Ponraj,
I do appreciate the workflow you've provided; it's been really helpful in addressing my issue. However, my situation involves APR that is different each month. Could you assist me in modifying the workflow to account for the monthly fluctuations in APR? I have the start date for a given contract, tenor in months (here [Term (months)] and a table of APRs that need to be integrated into the workflow to appropriate months. Could you please assist me in refining the workflow to accommodate these monthly APR?

Thanks in advance,
Rafal