PV for modified number of payments and payment amount

[iveaghlo]

Dear Alteryxers,

first of all big thanks to all contributors in the community, you've been a treasure for me so far!

Currently however, got stuck on a PV calculation of loan values, which compares the PVs between regular loan period (maturity date - signed date) and a loan period extended by a grace of 6 months (when no principal or interest is paid but interest is accrued).

The problem for me now is to find a workaround for the PV formula to work for the modified cash flow stream (extended periods by 6 months, when no payments are made and addition of postponed interest to annuity for certain period of time (maturity date - current date/2, in months)).

The attached excel file 'Excel NV Calculation' demonstrates the above calculations. The challenge for me is now to execute them in Alteryx.

I've already developed a workflow (input 'sample data'), where the regular PV formula works for calculating the results of the table on the left in the excel file. But after some solid hours of headbanging, I begin to wonder - would it be at all possible to calculate the modified PV with a regular wf, or should I try using iterative macro?

Any help would be much appreciated!

Thanks in advance,

Eivo

[NicoleJohnson · Accepted solution]

Hi @iveaghlo !

PV calculations are such a joy, aren't they... 🙂 

So there might very well be more than one way to solve this problem, but I've chosen this as an excellent opportunity to showcase the power of the Generate Rows tool and the Multi-Row Formula tool! I've attached a solution that I believe will get you to the right calculations, as well as hopefully providing some flexibility in the event that conditions change (contract start & end dates, length of COVID Grace Period, etc.)

I have added a few helpful notes in the workflow, as well as some high-level callouts below - I'd also highly recommend checking out some of the additional resources on Generate Rows and Multi-Row Formula on the Community and in the tool examples in Designer. They can be a bit tricky to master at first, but SUPER POWERFUL. 

 

Generally, here's how I approached this:

1. I needed to create a row for every period, for each loan, which is where Generate Rows really comes in handy. This allowed me to create a "Period" for each loan.

2. Used a Multi-Row Formula to create the period number for each loan (Row Prior + 1)

3. Determined whether a period was a "Grace Period" and calculated the postponed interest amount

4. Branched off to calculate total postponed interest, then joined that back to the data, where I then calculated the postponed interest amount to apply during the repayment period

5. Used Multi-Row again to calculate the ending Principal amount, based on the ending principal amount of the row prior. Was able to use the Excel example to mirror the correct calculations. Also used another Multi-Row formula to then document the beginning Principal Amount (prior period ending principal)

6. A few more calculations to get to final values for Discounted CF, Monthly Principal, and Interest Accrued

And voila! A schedule of PV/payments, and a summary of the discounted CF value per loan. 

Hopefully this helps serve as a solution to your PV question AND an introduction to the wild and fabulous world of Generate Rows and Multi-Row Formulas!! 🙂 

Cheers!

NJ

6. 

[iveaghlo · Accepted solution]

Hey, @NicoleJohnson , thank you so much for the reply!

Your solution looks great, thank you!

I've solved the problem using a batch macro (attached), which also did the trick. I was just not confident using a macro.

Anyway, as always, long live the Alteryx community!

best,

Ivo