Fair Value Estimation for Loan Portfolio using RStudio: A Step-by-Step Guide
Introduction
In the contemporary financial landscape, traditional banking loans are no longer the sole source of credit. The surge in e-commerce has sparked a demand for more adaptable credit solutions. Fintech companies have swiftly responded to this demand, offering easily accessible online loans to a broad audience. These innovative fintech models present straightforward options, including flexible payment structures and buy-now-pay-later schemes, accessible with a few clicks. RStudio, a powerful integrated development environment for R, provides an excellent platform for conducting such financial analyses. In this article, we will walk you through the process of estimating the fair value of a loan portfolio using RStudio. We will use real-world data from my virtual experience job on PwC “Digital Intelligence”, a leading global consultancy firm, to perform the calculations. You can download the data from this link.
Case Overview
Online merchants, who often receive payments post-sale, require fresh capital sources. To meet this demand, new credit offerings have emerged, where loan repayments depend directly on online sales, presenting unique cash flow challenges. Our client, a major global online lending platform, seeks to ensure the accuracy of their balance sheet values for loans classified as assets. We’ve been called upon to assist our audit colleagues in this task as the loan portfolio’s value depends on uncertain future cash flows in the dynamic fintech landscape.
Setting the Stage
To begin, let’s set the working directory to where our data is located and load the data into RStudio.
setwd("~/Downloads") #we set our working directory to the location where our dataset
data <- read.csv("Data.csv", sep=";") #loading data
View(data) #show the data
Defining Key Parameters
Next, we need to define essential parameters for our analysis, including the number of forecast periods, the number of vintages, and the discount rate. These parameters are essential for calculating the fair value of the loan portfolio.
n.forecast <- 30 # Number of forecast periods
n.vintage <- nrow(data) # Number of vintages
discount.rate <- 0.025 # Discount rate (annual)
Preparing the Data
We then prepare our data for analysis by extracting relevant information from our dataset. We create a matrix of historical cash flows and a vector representing the amounts originated for each vintage.
historical.cf <- as.matrix(data[,3:ncol(data)]) # Matrix of historical cash flows
amount.originated <- data[,2] # Vector of amounts originated per vintage
Calculating Repayment Percentages
Repayment percentages are crucial for our fair value estimation. We calculate these percentages based on historical data. We compute repayment percentages, including the first and second periods. The second period’s calculation includes a special consideration for the last vintage.
paid.percentages <- historical.cf / amount.originated # Repayment percentages
first.period <- diag(paid.percentages) # Repayment percentage in the period loans were originated
second.period <- c(diag(paid.percentages[-nrow(paid.percentages),-1]), as.numeric(paid.percentages[n.vintage,n.vintage] * 2))
Forecasting Expected Repayment Percentages
Now, we’ll forecast expected repayment percentages using a formula. This step is critical for estimating the fair value of the portfolio.
p <- matrix(0, nrow = n.vintage, ncol = n.forecast) # Matrix of zeros
p[,1] <- first.period
p[,2] <- second.period
for (i in 1:n.vintage) {
for (j in 3:n.forecast) {
p[i,j] <- max(0, p[i,2] * log(1 + (1 - sum(p[i,1:(j-1)]))) * (1 - (j-1) / n.forecast))
}
}In this code snippet, we calculate expected repayment percentages for each vintage and forecast period.
Forecasting Cash Flows and Present Value
Now, we’ll calculate the present value of forecasted cash flows. We calculate the present value of forecasted cash flows using discount factors and then sum them to get the fair value estimate for the loan portfolio.
periods.remaining <- n.forecast - n.vintage:1 #number of periods to be forecasted per vintage
p.forecast <- matrix(0, n.vintage, n.forecast-1)
for (i in 1:n.vintage) {
for (j in 1:periods.remaining[i]) {
p.forecast[i,j] <- p[i,n.forecast-periods.remaining[i]+j]
}
}
discount.factors <- 1 / (1 + discount.rate)^((1:(n.forecast-1))/12) # Discount factors
pv <- t(t(p.forecast) * discount.factors) * amount.originated # Present value of forecasted cash flows
result = sum(pv) # Sum of all present values, i.e., the value of the portfolio
Financial Analysis
The client’s estimate of the portfolio value is CHF 84,993,122.67, while our own estimate stands at CHF 84,779,941.82. Let’s calculate both the absolute and relative differences to assess how closely they align:
# Client's estimate and your estimate
client_estimate <- 84993122.67
our_estimate <- 84779941.82
# Calculate absolute difference
absolute_difference <- abs(client_estimate - your_estimate)
# Calculate relative difference (percentage difference)
relative_difference <- (absolute_difference / client_estimate) * 100
# Print results
cat("Absolute Difference:", absolute_difference, "\n")
cat("Relative Difference (%):", relative_difference, "\n")
The absolute difference between the client’s estimate and our own estimate is approximately CHF 213,180.85. This represents a relatively small difference given the scale of the portfolio’s value.
When considering the relative difference, which is around 0.25%, it becomes evident that this deviation is quite minor. Generally, a relative difference below 1% is considered very close in the context of financial valuations.
Therefore, it can be concluded that the difference between our estimate and the client’s estimate falls well below the client’s threshold. The valuations are remarkably close, suggesting a high degree of accuracy in our estimation process.
Conclusion
In this article, we’ve demonstrated how to estimate the fair value of a loan portfolio using RStudio. This step-by-step guide takes you through setting up your environment, preparing data, calculating repayment percentages, forecasting expected cash flows, and determining the fair value. Accurate fair value estimation is essential for making informed financial decisions, and RStudio provides the tools needed for such complex analyses. In the example above, we’ve used real-world data to illustrate the process, but you can apply this methodology to your own financial datasets for more informed decision-making in the world of finance.
