Loan analytics is hard because it depends on clean, well-structured data and on high-volume calculations that grow non-linearly with portfolio size. Portfolio-level IRR must be projected across multiple charge-off and prepayment scenarios and recalculated every month, and prepayment speeds cannot practically be tracked in spreadsheets at scale.
Credit unions face an increasingly complex lending environment, one that has made sophisticated loan analytics not just a competitive advantage — but an operational necessity. Before evaluating the build-versus-buy question, it is worth appreciating why loan analytics is difficult, and why the difficulty scales non-linearly with portfolio size.
Why does data quality matter for loan analytics?
Cleaning, normalizing and structuring loan data is the first hurdle to building a meaningful loan analytics solution. Moreover, how that data is maintained and stored is not only a privacy and security concern but also a performance issue. Loan analytics beyond simple aggregations, such as weighted average coupons over time, require high volume calculations. These depend upon well-architected databases and intelligent algorithms that use server memory to run the requisite number of base and stress scenarios on the short timelines to be useful for credit unions of even modest scale.
What is IRR, and why is it hard to calculate across a loan portfolio?
Internal Rate of Return (IRR) is among the most important metrics a lender can track. It reflects the true return on a loan, accounting for the timing and magnitude of all cash flows — origination fees, scheduled payments, prepayments, and charge-offs. For a single loan upon maturity, calculating IRR is straightforward. However, for a portfolio of tens of thousands or hundreds of thousands of loans that are at various stages of repayment, calculating accurate IRR ranges at the individual loan level, and then aggregating it meaningfully across segments, vintages, products, and risk tiers, becomes a significant computational and data engineering challenge.
Notably, IRR requires knowing all of the cashflows of each loan until maturity — which until maturity or charge-off is unknown. Therefore, IRR requires a lender to project forward all the potential outcomes to the end of the loan life across multiple charge-off and prepayment scenarios and then provide an IRR range, which narrows over time as additional loan behavior becomes known.
How often does portfolio IRR need to be recalculated?
That IRR range for each cohort of analysis must be recalculated each month as actual cash flows deviate from projected ones. A solution that cannot dynamically recalculate IRR as prepayments occur, delinquencies emerge, or rate environments shift provides an incomplete historical snapshot rather than actionable intelligence.
Compounding the difficulty is that meaningful investigation requires the ability to change and modify loan cohorts over time. When evaluating loan programs with dynamically changing underwriting criteria, lenders need to compare and contrast different loan cohorts among multiple key attributes like credit score, DTI, LTV, geography and time.
Why does prepayment analysis matter for credit unions?
Prepayment behavior is equally complex and consequential. For example, while many credit unions have had de minimis losses in their mortgage portfolios for nearly twenty years, nonetheless, they may have lost money on those portfolios due to prepayments depending on the costs of origination. In many cases, credit unions would have earned higher returns investing in government guaranteed securities versus even a no-loss residential loan program.
When a member pays off some or all of a loan early, the credit union loses anticipated interest income and must redeploy that capital — often in a different rate environment. Understanding prepayment patterns across loan types, member demographics, geographic markets, and interest rate cycles requires both historical data depth and sophisticated statistical modeling.
Can prepayment speeds be tracked in spreadsheets?
At scale, manually tracking prepayment speeds (not just tracking repayments in full), including constructing conditional prepayment rate (CPR) curves, and modeling the impact of prepayment on portfolio yield and duration is not practically achievable in spreadsheets. A credit union managing a consumer loan portfolio may be processing thousands of prepayment events per month – some full and some partial – each of which affects IRR calculations, income projections, and asset-liability positioning.
Given these challenges, should a credit union take them on internally? Next in the series: When should a credit union build its own loan analytics software?
Frequently asked questions
What is internal rate of return (IRR) for a loan?
IRR reflects the true return on a loan, accounting for the timing and magnitude of all cash flows: origination fees, scheduled payments, prepayments, and charge-offs.
Why can’t a lender calculate an exact IRR for an active loan?
IRR requires knowing all of a loan’s cash flows until maturity, which are unknown until the loan matures or is charged off. Lenders must project the potential outcomes across multiple charge-off and prepayment scenarios and report an IRR range, which narrows as more loan behavior becomes known.
What is a conditional prepayment rate (CPR)?
A conditional prepayment rate is the annualized share of a loan pool’s outstanding balance that is expected to be paid off early. Credit unions use CPR curves to model how prepayments affect portfolio yield and duration.
Can a loan program with no losses still lose money?
Yes. Many credit unions have had de minimis losses in their mortgage portfolios for nearly twenty years but may still have lost money on them because of prepayments, depending on the costs of origination. In many cases, government guaranteed securities would have earned a higher return than a no-loss residential loan program.
Related reading
- Buy vs. Build: A Strategic Framework for Loan Analytics Software at Credit Unions
- When Should a Credit Union Build Its Own Loan Analytics Software?
- Should Credit Unions Buy or Build Loan Analytics Software? Five Reasons to Buy
- Does Generative AI Change the Buy vs. Build Calculus for Loan Analytics?
About LoanStreet
Contact us today to learn more about our platform and products. With over 1,300 financial institutions registered and hundreds already receiving our reports, we are ready to help you build a more efficient loan participation model. sales@loan-street.com | www.loan-street.com