Calculate the Cumulative Principal on a loan (CUMPRINC - Excel Formula) in Alteryx

Hello,

I'm working on turning our lease liability Excel spreadsheet into an Alteryx workflow, but have been getting stumped around some cumulative principal payments on lease calculations and how to replicate the CUMPRINC formula in our Alteryx workflow. I'm hoping someone in the Alteryx community has done this before.

For context - the CUMPRINC function helps calculate the cumulative principal amount paid on a loan, or the cumulative amount accrued by an investment.

In Excel -

CUMPRINC(rate, nper, pv, start_period, end_period, type)

The CUMPRINC function syntax has the following arguments:

Rate Required. The interest rate.
Nper Required. The total number of payment periods.
Pv Required. The present value.
Start_period Required. The first period in the calculation. Payment periods are numbered beginning with 1.
End_period Required. The last period in the calculation.
Type Required. The timing of the payment.

In Alteryx - I don't believe there's a 1:1 substitute for the CUMPRINC formula - but does anyone know how to calculate this in Alteryx?

Ideally - in Excel, this formula =CUMPRINC((0.0422/12), 36, 4741.83, 1, 12, 1) returns a value of 1,525.82. Basically a 36 month loan that has been active for 12 periods at a 4.22% interest rate with a PV of Payments of 4,741.83 (every period's payment is 139.97), has had cumulative principal payments of $1,525.82 after 12 periods.

There was a Alteryx Knowledge article from 2023 that mentions potentially doing this with an app that is attached. I can't seem to find anything attached to the article - is this just a me problem? I'd ideally like to avoid using a macro to calculate cumulative principal payments if possible, but maybe an app is the only way.

https://knowledge.alteryx.com/index/s/article/Alteryx-for-Excel-Users-Calculating-Loan-Installments-and-Amortization-Schedules-1583460632734

Also, there was a similar link from Google about Alteryx and CUMPRINC at this link, but it is archived - is there anyway to potentially dig that up and read it?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Cumulative-Principal-from-Several-Loans/td-p/529234

Your help would be greatly appreciated!

Workflow

Developer

Parse

Macros

Apps

Batch Macro

Preparation

Iterative Macro

Accepted answers

apathetichell

Hey - this is set up to show you in two ways:

1) formula tool - formula 1 - creates [pmt] - payment using the classic formula.

2) formula tool - formula 2/3 - replicates the excel function.

It creates the full table but you could swap it and just use the formula as explained.

NOTE - this is for a beginning vs an ending payment schedule.

amortization workflow.yxmd

All comments

Raj

@taxguy33 have you tried creating a app or macro with help of formula tool.
this is an easy alternative to your problem.

apathetichell

There's a slew of community posts on this - so search around for how to make an amortization schedule if you have trouble with these instructions:

1) use the pmt function in a formula tool to create the pmt internally in Alteryx - remember that you want a beginning payment - not a 0 base payment (ie there is a payment in the first month)

2) use generate rows generate a row for each nper

3) use multi-row formula to calculate the ending principal balance as ([row-1]:balance - (pmt - ([row-1:balance]*([rate]/[number of periods]))

4) I then tend to use a formula after the multi-row to extract out the principal paid per period and the interest paid per period from the pmt.

5) I then will use a running total to sum up interest/principal.

You can also get where you need to using MATH.

apathetichell

FYI - for MATH. because this is a beginning period you have to make some adjustments...

([PV-0]-[pmt])*pow((1+([interest]/[Periods])),11)-
([pmt]/([interest]/[Periods])

*(pow(1+([interest]/[Periods]),11)-1)) works for determining the principal balance outstanding at the 12th period. I just throw that in a formula tool - create a new field and then subtract that from your [PV-0] to determine the principal paid.

The key to remember here is that for a beginning period payment schedule the financed amount [PV-0] has to be adjusted by full amount of the beginning period payment ([pmt]) - because no interest has accrued so the entire amount is deducted from principal prior to the first compounding period.

for my multi-row (which I'd normally use) - I have something like if [Row-1:Periods]=null() then [PV-0]-[pmt] else [Row-1:beginning_balance]-([pmt]-([Row-1:beginning_balance]*([interest]/[Periods]))) endif to create a new field called beginning balance with rows that do not exist set to null.

bcoziahr

apathetichell - I am really new to Alteryx. Can you please provide an example of your first solution with the pmt function?

aatalai

@bcoziahr I'm away from my altery atm, but try looking at the finance functions in the summarisation tool that might help

apathetichell

Hey - this is set up to show you in two ways:

1) formula tool - formula 1 - creates [pmt] - payment using the classic formula.

2) formula tool - formula 2/3 - replicates the excel function.

It creates the full table but you could swap it and just use the formula as explained.

NOTE - this is for a beginning vs an ending payment schedule.

amortization workflow.yxmd

taxguy33

@apathetichell Thank you so much for your workflow example - that was exactly what I needed to fine-tune the solution. I have added a few more variables (my example above had 12 as periods to divide interest rate by and it was 12 periods into the loan/lease - so I think your Periods field was used in both places, when it should have been referencing different fields at certain points). I also added a quick calculation showing the cumulative interest paid and made the # of amortization schedule rows needed a dynamic amount as well.

@bcoziahr Will the attached work for your requested issue as well?

amortization workflow.yxmd

apathetichell

My periods field was set up for monthly payments. The 12 (in this case 11) in the formula tool was hard coded to match your example. The amortization table matched the term of the loan 36 months. Can you mark my post a solution?

bcoziahr

@apathetichell Thank you so much for your example and explanation!

@taxguy33 You are awesome, this is exactly what I needed. Thank you so much!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703