Portfolio at risk (PAR30): formula and a worked example

The PAR formula

PAR(X) = outstanding balance of loans with a payment more than X days overdue ÷ gross loan portfolio

X is usually 30 (PAR30) or 90 (PAR90), sometimes 1, 60 or 180. Three rules make the ratio meaningful:

• The whole balance counts. When a borrower misses one instalment on a $650 loan, the full $650 is at risk, because a loan in arrears rarely stops at one missed payment.
• It measures money, not loans. In the example 30.0% of the loans are more than 30 days late, but they hold 19.4% of the balance.
• It is a snapshot taken on one date, usually the month-end, so it is not flattered by old loans that were repaid long ago, the way a cumulative repayment rate can be.

Step 1: days overdue per loan

Start from the loan list on the reporting date: the outstanding principal and the number of days since the oldest unpaid instalment fell due. A loan that is current has 0 days. Most loan systems export this directly. If you only have due dates, the days overdue are the reporting date minus the oldest unpaid due date.

Worked example: PAR30 for one loan officer's portfolio

Columns B and C come from the loan system on the reporting date. Column D and the ageing table in rows 14 to 20 are formulas. The institution and the loans are fictional.

Worked example: PAR30 for one loan officer's portfolio

	A	B	C	D
1	Loan	Outstanding ($)	Days overdue	Bucket
2	L-01	420	0	Current
3	L-02	1,150	0	Current
4	L-03	780	12	1–30
5	L-04	2,300	0	Current
6	L-05	650	45	31–60
7	L-06	1,500	0	Current
8	L-07	960	30	1–30
9	L-08	310	95	91–180
10	L-09	1,840	0	Current
11	L-10	1,200	64	61–90
12	Gross loan portfolio	11,110
13
14	From (days)	Bucket	Balance ($)	Share
15	0	Current	7,210	64.9%
16	1	1–30	1,740	15.7%
17	31	31–60	650	5.9%
18	61	61–90	1,200	10.8%
19	91	91–180	310	2.8%
20	181	Over 180	0	0.0%
21
22	PAR1: any payment overdue			35.1%
23	PAR30: more than 30 days overdue			19.4%
24	PAR90: more than 90 days overdue			2.8%
25	Share of loans (by count) more than 30 days late			30.0%

Educational example with illustrative figures, not financial advice. Institutions differ in how they treat restructured and rescheduled loans, so check the definition behind any PAR you compare.

Step 2: the ageing table with VLOOKUP and SUMIF

An ageing table groups the balances into buckets: current, 1–30, 31–60, 61–90, 91–180 and over 180 days. Put the lower limit of each bucket in a sorted column (0, 1, 31, 61, 91, 181) and look up each loan's bucket with an approximate match:

=VLOOKUP(C2,$A$15:$B$20,2,TRUE)

With TRUE, VLOOKUP picks the last limit that is not above the days, so 45 days lands in 31–60 and exactly 30 days in 1–30. Then total each bucket with =SUMIF($D$2:$D$11,B15,$B$2:$B$11) and divide by the portfolio. The XLOOKUP vs VLOOKUP guide explains approximate match in more detail.

Step 3: PAR30 and PAR90

You do not need the buckets for the ratio itself. One SUMIF adds up the balances of every loan more than 30 days overdue:

=SUMIF(C2:C11,">30",B2:B11)/B12

In the example the 31–60, 61–90 and 91–180 buckets hold $650, $1,200 and $310, so PAR30 is 19.4%. PAR90 counts only the $310 loan: 2.8%. PAR1, any payment late at all, is 35.1%. Loan L-07, exactly 30 days overdue, is not in PAR30, because the definition says more than 30 days: use ">30", not ">=30".

What is a good PAR30?

Lenders and investors in microfinance often treat a PAR30 above about 5% as a warning sign, although the level that is normal depends on the market, the product and the economic cycle. The 19.4% in the example belongs to one loan officer's portfolio that a branch has flagged for review. Watch the trend as well as the level, and read PAR together with write-offs: writing off bad loans removes them from both the numerator and the portfolio and makes PAR look better without anyone repaying. A fast-growing portfolio also dilutes PAR for a while, because new loans cannot be late yet.

From PAR to loan loss provisions

The ageing table is also the base for provisioning. Each bucket gets a provision rate that rises with the days overdue, and the required loan loss reserve is the sum of balance × rate over all buckets. The risk coverage ratio, reserve ÷ PAR30 balance, shows how much of the risky portfolio is already covered. Lenders to larger businesses look at repayment capacity before a loan is made with measures such as the debt service coverage ratio.

Common mistakes

• Counting only the overdue instalments. PAR uses the whole outstanding balance of a late loan. Counting just the missed payments understates the risk many times over.
• Counting loans instead of balances. 30.0% of the loans being late is not the same as 19.4% of the portfolio at risk.
• Using >=30 instead of >30. PAR30 means more than 30 days. A loan exactly 30 days late belongs in the 1–30 bucket.
• An unsorted ageing table. VLOOKUP with TRUE needs the lower limits in ascending order, or it returns wrong buckets without an error.
• Ignoring write-offs and restructured loans. Writing off or rescheduling late loans makes PAR fall without any repayment. Report write-offs next to PAR.
• Comparing PAR across a growth spurt. New loans cannot be late yet, so rapid growth lowers PAR for a while.