Stacked Area Chart: Visualizing Investments and Credit Defaults

This was more of a small programming exercise – partly because I wanted to see how things had actually developed as the number of projects going into collection increased. The data can be downloaded relatively easily from the website, but it needs to be transformed. The challenge is that the data is available per project, whereas for the stacked area chart we need to transform it so that it is available per month.

library(tidyverse)
library(lubridate)

today <- Sys.Date()

# Prepare time window
base <- portfolio %>%
  transmute(
    Loan Code
    , start_date = as.Date( Date Funded )
    , end_date = if_else(is.na( Repaid Date ), today, as.Date( Repaid Date ))
    , maturity_date = as.Date( Maturity Date )
    , invested =  Invested amount
    , status_raw = Status,
    days_late =  Days late
  )

# Calculate start of default
base2 <- base %>%
  mutate(
    late_since_raw = if_else(!is.na(days_late), today - days(days_late), as.Date(NA)),
    late_since = if_else(late_since_raw < maturity_date, maturity_date, late_since_raw),
    late_since = if_else(is.na(late_since) & status_raw == "Late", maturity_date, late_since),
    is_late = !is.na(late_since)
  )

# Create monthly sequence
months_seq <- seq(
  floor_date(min(base2$start_date), "month"),
  floor_date(today, "month"),
  by = "month"
)

# Expand: one row per loan per month
expanded <- base2 %>%
  rowwise() %>%
  mutate(month = list(months_seq[months_seq >= floor_date(start_date, "month") &
                                   months_seq <= floor_date(end_date, "month")])) %>%
  unnest(month) %>%
  mutate(
    status = case_when(
      is_late & month >= floor_date(late_since, "month") ~ "late",
      TRUE ~ "active"
    )
  )

# Aggregate per month and status
monthly <- expanded %>%
  group_by(month, status) %>%
  summarise(volume = sum(invested), .groups = "drop")

# Plot
ggplot(monthly, aes(x = month, y = volume, fill = status)) +
  geom_area(position = "stack") +
  scale_fill_manual(values = c("active" = "#4CAF50", "late" = "#F44336")) +
  scale_y_continuous(labels = scales::dollar_format(prefix = "€", big.mark = ".")) +
  labs(
    title = "Development of estateguru Portfolio",
    x = NULL, y = "Invested Volume (€)", fill = "Status"
  ) +
  theme_minimal()

As the plot clearly shows, the projects started going off the rails at the end of 2022/beginning of 2023; the €2,500 project mentioned in the previous article was overdue in January 2023. Since then, the total amount in collection has continued to grow. At the same time, you can see that I kept withdrawing money from estateguru whenever possible (each withdrawal incurs a fee, so I wait until a certain amount has accumulated).