1 Reply Latest reply on May 11, 2017 11:05 PM by Zhouyi Zhang

# Breaking down a lending portfolio (Paid Vs. Unpaid in multiple categories)

So I have what is seemingly an easy question but not sure how to go about it on Tableau....

Basically imagine I have a lending portfolio and a % of the balances are paid, and we categorize each paying client into specific categories as follows...

 Client Paying Category Original Balance Paid Balance % Balance Left % of Portfolio A Slow 200 50 150 75% 30% B On Time 200 100 100 50% 20% C On Time 100 50 50 50% 10% D Paid Off 200 200 0 0% 0% E Default 200 0 200 100% 40%

Now I can make a bar chart by PAYING CATEGORY, but I need the extra step of breaking it down by payment progress... For example if I'd have a graph of (Each graph is over time so January represents a bar, then second bar is Feb, March, etc.)

The rightside and the black line shows the overall portfolio balance decreasing, and that current stacked bar graph is showing % of the portfolio and its composition of all paying categories... Blue indicates Active clients (Slow AND On Time), whereas other colors means Default/Chargeoff/Delinquents...

Now what I want to show further is that over time those "On Time" AND "Slow" clients paid % start showing up (with a new color green for example) - I know that because I'm "Coloring" it by the payment category and since they're not transitioned into "Paid off" that they will stay blue until completely changed... How would I go about this?

MY IDEAL GRAPH

My only broken solution is to do a "conditional" measure where if the account becomes paid off, take the original balance amount otherwise use the remaining balance amount, but as you know this math is somewhat flawed...

Any suggestions? I'm going crazy over this I was thinking of also splitting it into "On Time PAIDOFF %, ON TIME Outstanding %, Slow Paidoff %, Slow Outstanding %, etc. etc." But then not sure how that would work in this scenario.

• ###### 1. Re: Breaking down a lending portfolio (Paid Vs. Unpaid in multiple categories)

Hi, Qicheng

Not quite sure what exactly you expected without seeing your underlining data structure. If you could provide a sample workbook with some make up data to reflect your issue will be more helpful.

ZZ