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 RateDurationFinance amountRV3,50%37€20.000,00€10.000,004,10%37€19.000,00 3,50%25€18.000,00€4.000,004,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 Repayment Schedule.zip

Finance PMT with several loans

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

Repayment Schedule.zip

Iterative Macro

Accepted answers

ponraj

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-Installments-and/ta-p/37970

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

EMI calculation for Multiple loans.yxzp

All comments

ponraj

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-Installments-and/ta-p/37970

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

EMI calculation for Multiple loans.yxzp

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703