Loan Portfolio Data Fields Explained: LTV, DTI, FICO, Grade

A consumer loan portfolio report depends on the data underneath it. Before any macro moves numbers from Excel into PowerPoint, you need to know what each field means: loan key, origination date, amount, term, rate, borrower and co-borrower FICO, LTV, DTI, grade, purpose, state and channel. This guide explains each of those fields and why lenders care about them.

The dataset behind the report

The example portfolio has 20,000 consumer loans. It started from a public loan dataset on Kaggle but was changed a great deal, so it no longer matches anything published there. Each row is one loan. The same table later feeds an Excel report that gets connected to PowerPoint for automated slide production.

One layout habit is worth copying. The column headers sit in row 10 and the data starts in column B, not at cell A1. The space above the headers is a place to leave notes about a column. It also leaves room for a formula strategy that keeps Excel fast when files get large.

Identifiers and loan basics

Index

The first column is a simple running index, similar to the index of a pandas DataFrame.

Loan key

Each loan has its own unique key. In analytics, unique identifiers are of paramount importance, so confirm that no key appears twice before you build anything on top of the data. In this portfolio, a duplicate check finds none, which confirms that the loan key really is unique.

Origination date

The origination date is the day the loan was made to the borrower. It marks the formal beginning of the loan's life.

Original loan amount

This is the amount the customer borrowed. Every loan here is fully amortizing and fully funded at origination. The portfolio has no revolving accounts, such as credit cards, where the balance goes up and down as the borrower draws on it. Balances start out fixed and the borrower pays them down over time. Fully amortizing means that if the borrower makes every scheduled payment, the balance reaches zero at the end of the term.

Loan term

In consumer lending, the term is how long the borrower is scheduled to keep repaying. A term of 36 means 36 monthly payments, assuming the borrower doesn't miss any payments or fall delinquent. That is the same as a 3-year loan. The portfolio also has 60-month loans, which are 5-year loans.

Interest rate

Rates are stored as plain numbers rather than formatted percentages, so a value of 27.39 means 27.39%. This matters any time a report formats or aggregates the field.

Credit risk metrics: FICO, LTV and DTI

Borrower and co-borrower FICO

Two columns hold FICO scores, one for the primary borrower and one for the co-borrower. These scores are very important for credit risk. The lower the FICO score, the riskier the borrower, meaning the borrower is more likely to default or fail to repay. Some loans have a sole borrower. Others have a primary borrower and a co-borrower. A loan has a co-borrower whenever the co-borrower FICO column is not null.

Loan to value (LTV)

LTV is a common credit risk ratio, used often in mortgage lending and in secured lending generally. Say a customer wants to borrow $500,000 to buy a house worth $510,000. The LTV is 500,000 divided by 510,000, which is about 98%. Outside a few government programs, a lender would be unlikely to make that loan. In the U.S., 80% LTV is a key threshold: a conventional loan above it usually requires mortgage insurance, which is why a 20% down payment is the traditional benchmark. Higher LTV programs exist, such as conventional loans up to 97% LTV, FHA loans at 96.5% and VA loans up to 100%, but they come with added requirements and cost.

Debt to income (DTI)

DTI helps a lender judge whether a borrower can absorb unexpected expenses from month to month. Take a borrower with $1,000 of monthly income who spends $900 of it on minimum credit card payments alone. The DTI is 900 divided by 1,000, or 90%. That is a very risky borrower, and a lender would likely not lend to them. For mortgages, 50% is already quite high: Fannie Mae generally caps DTI at 50% for loans approved through its automated underwriting, and at 36% to 45% for manually underwritten loans.

Grade, purpose, state and channel

Grade

Grade is a categorical risk measure that runs from A to G. Grade A is the safest and grade G is the riskiest. You would expect the weighted average FICO score of grade A loans to be higher than that of grade G loans, which makes grade a natural dimension to report on.

Loan purpose

Purpose records why the customer borrowed. Examples include paying off credit card debt, a home improvement project and buying a car. Debt consolidation is another. Picture a customer with seven medical bills, each due on a different date and for a different amount, totaling $10,000. The customer takes out one $10,000 loan, pays off every bill and is left with a single monthly payment to the lender.

State

State records where the primary borrower lives. For mortgages this is the property state, and it is quite important because property state drives a number of legal considerations for mortgage lenders.

Channel

Channel shows where the borrower came from. For example, a bank might want to know what share of its originations came from customers who walked into a branch versus customers who applied through a digital platform or website.

Why the data dictionary matters before automation

These fields are only a small sliver of the characteristics and metrics worth capturing for reporting and analytics. Industries outside lending will care about different metrics. A retail clothing store, for example, might report sales by product or product type, such as menswear versus womenswear or shirts versus pants.

In any industry, understanding each field comes first. The Excel report built from this table is what later gets connected to PowerPoint. What each column means, how its values are stored and which key is unique all carry through to the slides the automation produces.

Before building a report on any dataset, confirm the unique key really is unique and note how values such as interest rates are stored.

Key takeaways

• The example portfolio holds 20,000 fully amortizing, fully funded consumer loans with no revolving accounts.
• Putting headers in row 10 and starting data in column B leaves room for notes and for later performance work.
• A unique loan key is essential, so confirm it has no duplicates before building on the data.
• Interest rates are stored as numbers, so 27.39 means 27.39%.
• A lower FICO score means a riskier borrower. A co-borrower is present when the co-borrower FICO is not null.
• LTV compares the loan amount to the asset's value. Most U.S. mortgage lenders prefer to stay at or below 80.
• DTI measures how much of a borrower's income is already committed. 50% is quite high.
• Grade runs from A (safest) to G (riskiest). Purpose, state and channel add context about why, where and how loans were made.