Stress test (simulation) of WFC balance sheet and income statement

v1 of the stress test simulator (scenario vector influences input accounts)
code
analysis
Author

David Harper, CFA, FRM

Published

May 15, 2024

This v1 simulator is discussed on my ai bucks substack.

First code block: pull statement data into tidy dataframes: balance_df and income_df

balance_df <- readRDS("balance_df.rds")
income_df <- readRDS("income_df.rds")

# balance_df (actual calcs below)
# 
# balance_df: Calculated Variables (7) as pulled from 10Q
# Total Debt Securities (TotDebtSec):   Sum of Trading, Available-for-Sale, and Held-to-Maturity Debt Securities.
# Net Loans (NetLoans):                 Loans plus Allowance for Loan Losses.
# Total Deposits (TotDep):              Sum of Noninterest-Bearing and Interest-Bearing Deposits.
# Total Liabilities (TotLiab):          Sum of Total Deposits, Short-Term Borrowings, Derivative Liabilities, 
#                                       Accrued Expenses and Other Liabilities, and Long-Term Debt.
# Total Shareholders' Equity (TotSHREq):Sum of Preferred Stock, Common Stock, Additional Paid-In Capital, 
#                                       Retained  Earnings, Accumulated Other Comprehensive Loss, Treasury Stock, 
#                                       Unearned ESOP Shares, and Noncontrolling Interests.
# Total Assets (TotAssets):             Sum of all asset accounts plus Total Liabilities and Equity.
# Total Liabilities and Equity (TotLiabEq):Sum of Total Liabilities and Total Shareholders' Equity.
#
# balance_df: Calculated Variables (7) with shorter names
# balance_df$TotDebtSec <- balance_df$TradDebtSec + balance_df$AvailSaleDebtSec + balance_df$HeldMatDebtSec
# balance_df$NetLoans   <- balance_df$Loans + balance_df$LoanLossAllow
# balance_df$TotDep     <- balance_df$NonIntBearDep + balance_df$IntBearDep
# balance_df$TotLiab    <- balance_df$TotDep + balance_df$STBorrow + balance_df$DerivLiab + balance_df$AccExpOthLiab +
#                          balance_df$LTDebt
# balance_df$TotSHREq   <- balance_df$PrefStock + balance_df$ComStock + balance_df$AddlPaidCap + balance_df$RetEarn +
#                          balance_df$AOCL + balance_df$TreasStock + balance_df$UnearnESOP + balance_df$NonCtrlInt
# balance_df$TotAssets  <- balance_df$Cash + balance_df$IntEarnDep + balance_df$FedFundsSold + balance_df$TotDebtSec +
#                          balance_df$LoansForSale + balance_df$NetLoans + balance_df$MortServRights + balance_df$PremEquip + 
#                          balance_df$Goodwill + balance_df$DerivAssets + balance_df$EquitySec + balance_df$OthAssets + 
# balance_df$TotLiabEq  <- balance_df$TotLiab + balance_df$TotSHREq
#
# balance_df: Input Variables (4)
# Loans (Loans)
# Allowance for Loan Losses (LoanLossAllow)
# Noninterest Bearing Deposits (NonIntBearDep)
# Interest Bearing Deposits (IntBearDep)
#
# balance_df: Drift Variables
# Cash (Cash)
# Interest Earning Deposits (IntEarnDep)
# Federal Funds Sold (FedFundsSold)
# Trading Debt Securities (TradDebtSec)
# Available-for-Sale Debt Securities (AvailSaleDebtSec)
# Held-to-Maturity Debt Securities (HeldMatDebtSec)
# Loans Held for Sale (LoansForSale)
# Mortgage Servicing Rights (MortServRights)
# Premises and Equipment (PremEquip)
# Goodwill
# Derivative Assets (DerivAssets)
# Equity Securities (EquitySec)
# Other Assets (OthAssets)
# Short-Term Borrowings (STBorrow)
# Derivative Liabilities (DerivLiab)
# Accrued Expenses and Other Liabilities (AccExpOthLiab)
# Long-Term Debt (LTDebt)
# Preferred Stock (PrefStock)
# Common Stock (ComStock)
# Additional Paid-In Capital (AddlPaidCap)
# Retained Earnings (RetEarn)
# Accumulated Other Comprehensive Loss (AOCL)
# Treasury Stock (TreasStock)
# Unearned ESOP Shares (UnearnESOP)
# Noncontrolling Interests (NonCtrlInt)
#
#
# income_df
# 
# income_df: Calculated variables (10)
# income_df$TotIntInc <- income_df$DebtSec + income_df$LoansSale + income_df$Loans + income_df$EqSec + income_df$OthIntInc
# income_df$TotIntExp <- income_df$DepIntExp + income_df$STBIntExp + income_df$LTBIntExp + income_df$OthIntExp
# income_df$NetIntInc <- income_df$TotIntInc - income_df$TotIntExp
# income_df$TotnonIntInc <- income_df$DepLendFees + income_df$InvAdvFees + income_df$CommBrkFees + income_df$InvBankFees +
#                           income_df$CardFees + income_df$MortBank + income_df$NetGainTrade + income_df$OthNonIntInc
# income_df$TotRev <- income_df$NetIntInc + income_df$TotNonIntInc
# income_df$TotNonIntExp <- income_df$Personnel + income_df$TechTelEquip + income_df$Occupancy + 
#                           income_df$OperLoss + income_df$ProfOutServ + 
#                           income_df$AdvPromo + income_df$OthNonIntExp
# income_df$IncBefTax <- income_df$TotRev - income_df$TotNonIntExp - income_df$ProvCredLoss
# income_df$NetIncBefNCI <- income_df$IncBefTax - income_df$TaxExp
# income_df$WFNetInc <- income_df$NetIncBefNCI - income_df$NetIncLossNCI
# income_df$WFNetIncCS <- income_df$WFNetInc - income_df$PrefStkDiv
#
#
# income_df: Input Variables (6) ... directly impacted by external macroeconomic changes and focus in stress test scenarios
# Loans (Loans):            Total amount of loans issued, sensitive to economic changes affecting credit demand and default rates.
# Equity Securities (EqSec):Investments in equity securities, influenced by market valuations and economic conditions.
# Other Interest Income (OthIntInc): Income generated from various miscellaneous financial sources, potentially sensitive to
#                                    interest rate changes and economic conditions.
# Provision for Credit Losses (ProvCredLoss): Reserves set aside to cover potential loan defaults, directly impacted by
#                                             economic conditions and credit risk assessments.
# Deposits Interest Expense (DepIntExp): Cost incurred from interest on deposits, which fluctuates with interest rate 
#                                        changes.
# Mortgage Banking (MortBank): Income from mortgage-related activities, which can vary significantly with housing market
#                              conditions and interest rates.
#
# income_df: Drift Variables
# Debt Securities (DebtSec): Typically more stable investments unless significantly impacted by interest rate changes or 
#       credit quality adjustments.
# Loans Held for Sale (LoansSale): Loans intended for sale in the near term, might not be directly impacted by long-term
#       economic trends.
# Short Term Borrowings Interest Expense (STBIntExp): Costs associated with short-term borrowings, which could have fixed
#       rates or short adjustment periods.
# Long Term Debt Interest Expense (LTBIntExp): Costs related to long-term borrowings, which may be fixed or less sensitive
#       to immediate market fluctuations.
# Other Interest Expense (OthIntExp): Miscellaneous interest expenses that may not directly correlate with macroeconomic
# Deposit Lending Related Fees (DepLendFees): Fees associated with deposit accounts and lending services, relatively stable
#       and less directly impacted by macro conditions.
# Investment Advisory & Other Asset-Based Fees (InvAdvFees): Fees earned from managing client assets, depending on market
#       conditions but also on contractual arrangements.
# Commissions & Brokerage Services Fees (CommBrkFees): Fees from brokerage and trading services, 
#       somewhat dependent on market activity levels.
# Investment Banking Fees (InvBankFees): Revenue from investment banking activities, which can fluctuate with 
#       market conditions but are also driven by deal flow.
# Card Fees (CardFees): Fees associated with credit and debit card usage, generally stable unless consumer spending
#       habits change significantly.
# Net Gains from Trading Securities (NetGainTrade): Profits from trading activities, highly variable but also dependent 
#       on trading strategies and market conditions.
# Other Noninterest Income (OthNonIntInc): Diverse sources of income not classified elsewhere, potentially variable 
#       but not directly linked to macroeconomic stressors.
# Personnel (Personnel): Employee-related expenses, relatively fixed in the short term.
# Technology, Telecommunications, and Equipment (TechTelEquip): Expenses related to technology and equipment, 
#       typically follow planned investment patterns.
# Occupancy (Occupancy): Costs associated with physical premises, largely fixed.
# Operating Losses (OperLoss): Losses from operational issues, not directly linked to economic conditions.
# Professional and Outside Services (ProfOutServ): Costs for external services, can vary with business activity 
#       but not directly tied to economic stressors.
# Advertising and Promotion (AdvPromo): Marketing and advertising costs, generally discretionary.
# Other Noninterest Expense (OthNonIntExp): Miscellaneous expenses not categorized elsewhere
# Income Tax Expense (TaxExp): Taxes on profits, varies with earnings but not directly impacted by most macroeconomic 
# Net Income Loss from Noncontrolling Interests (NetIncLossNCI): Earnings attributed to noncontrolling interests, 
#       varies based on subsidiary performance.
# Preferred Stock Dividends and Other (PrefStkDiv): Dividends and similar payments to preferred shareholders, 
#       typically fixed based on issuance terms.
#
# *summary:*
# balance_df
# Total Variables: 36
#   Calculated Variables: 7
#   Input Variables: 4
#   Drift Variables: 36 - 7 - 4 = 25
# 
# income_df
# Total Variables: 38
#   Calculated Variables: 10
#   Input Variables: 6
#   Drift Variables: 38 - 10 - 6 = 22

# Transpose dataframes
balance_df_t <- t(balance_df)
balance_df_t <- as.data.frame(balance_df_t)
income_df_t <- t(income_df)
income_df_t <- as.data.frame(income_df_t)

print("Balance Sheet Dataframe:")
[1] "Balance Sheet Dataframe:"
print(balance_df_t)
                    2022    2023
Cash               34596   33026
IntEarnDep        124561  204193
FedFundsSold       68036   80456
TradDebtSec        86155   97302
AvailSaleDebtSec  113594  130448
HeldMatDebtSec    297059  262708
TotDebtSec        496808  490458
LoansForSale        7104    4936
Loans             955871  936682
LoanLossAllow     -12985  -14606
NetLoans          942886  922076
MortServRights     10480    8508
PremEquip           8350    9266
Goodwill           25173   25175
DerivAssets        22774   18223
EquitySec          64414   57336
OthAssets          75838   78815
NonIntBearDep     458010  360279
IntBearDep        925975  997894
TotDep           1383985 1358173
STBorrow           51145   89559
DerivLiab          20067   18495
AccExpOthLiab      68740   71210
LTDebt            174870  207588
TotLiab          1698807 1745025
PrefStock          19448   19448
ComStock            9136    9136
AddlPaidCap        60319   60555
RetEarn           187968  201136
AOCL              -13362  -11580
TreasStock        -82853  -92960
UnearnESOP          -429       0
NonCtrlInt          1986    1708
TotSHREq          182213  187443
TotAssets        1881020 1932468
TotLiabEq        1881020 1932468
print("Income Statement Dataframe:")
[1] "Income Statement Dataframe:"
print(income_df_t)
               2021  2022  2023
DebtSec        9253 11781 16108
LoansSale       865   513   363
Loans         28634 37715 57155
EqSec           608   707   682
OthIntInc       334  3308 10810
TotIntInc     39694 54024 85118
DepIntExp       388  2349 16503
STBIntExp       -41   582  3848
LTBIntExp      3173  5505 11572
OthIntExp       395   638   820
TotIntExp      3915  9074 32743
NetIntInc     35779 44950 52375
DepLendFees    6920  6713  6140
InvAdvFees    11011  9004  8670
CommBrkFees    2299  2242  2375
InvBankFees    2354  1439  1649
CardFees       4175  4355  4256
MortBank       4956  1383   829
NetGainTrade   7264  1461  4368
OthNonIntInc   4408  2821  1935
TotNonIntInc  43387 29418 30222
TotRev        79166 74368 82597
ProvCredLoss  -4155  1534  5399
Personnel     35541 34340 35829
TechTelEquip   3227  3375  3920
Occupancy      2968  2881  2884
OperLoss       1568  6984  1183
ProfOutServ    5723  5188  5085
AdvPromo        600   505   812
OthNonIntExp   4131  3932  5849
TotNonIntExp  53758 57205 55562
IncBefTax     29563 15629 21636
TaxExp         5764  2251  2607
NetIncBefNCI  23799 13378 19029
NetIncLossNCI  1690  -299  -113
WFNetInc      22109 13677 19142
PrefStkDiv     1291  1115  1160
WFNetIncCS    20818 12562 17982

Second code block: simulate the go-forward input variables (influenced accounts)

  • Initialize coefficient matrices with hypothetical values.
  • Defines stress scenario changes
  • Calculate changes in the input variables based on the stress scenario.
  • Update INPUT accounts; i.e., newBalancevalues and newIncomeValues
  • Updates the balance_df and income_df dataframes; note the other values will be NA at the point.
library(tidyverse)
library(scales)

# Define names for input and macro variables for clarity
balanceInputVarNames <- c("Loans", "LoanLossAllow", "NonIntBearDep", "IntBearDep")
incomeInputVarNames <- c("Loans", "EqSec", "OthIntInc", "ProvCredLoss", "DepIntExp", "MortBank")

# Define macro variable names
macroVarNames <- c("GDP Growth", "Unemployment Rate", "Interest Rates", "Housing Market",
                   "Inflation Rate", "Stock Market", "Consumer Confidence", "Corporate Profits")

# Initialize the coefficient matrix with hypothetical values
balanceCoefficients <- matrix(runif(32, -0.1, 0.1), nrow = 4, ncol = 8, dimnames = list(balanceInputVarNames, macroVarNames))
incomeCoefficients <- matrix(runif(48, -0.1, 0.1), nrow = 6, ncol = 8, dimnames = list(incomeInputVarNames, macroVarNames))  # 6 income inputs and 8 macro variables

# Define stress scenario changes - assuming 1 set of changes for simplicity, which can be expanded
stressScenarios <- matrix(c(-0.01, 0.02, -0.03, 0.01, -0.02, 0.03, 0.04, -0.01), ncol = 1, dimnames = list(macroVarNames, "Change"))

# Extract the last row (2023 values) as the base for simulation
balanceInputs <- as.matrix(balance_df[nrow(balance_df), balanceInputVarNames])
incomeInputs <- as.matrix(income_df[nrow(income_df), incomeInputVarNames])

# Calculate changes for each matrix and transpose
balanceChangeMatrix <- balanceCoefficients %*% stressScenarios
balanceChangeMatrix_t <- t(balanceChangeMatrix)
incomeChangeMatrix <- incomeCoefficients %*% stressScenarios
incomeChangeMatrix_t <- t(incomeChangeMatrix)

# Apply changes to the original values from the last rows of each dataframe
newBalanceValues <- balanceInputs * (1 + balanceChangeMatrix_t)
newIncomeValues <- incomeInputs * (1 + incomeChangeMatrix_t)

# Update the dataframes with these new values for the next year's projection
balance_df[nrow(balance_df) + 1, balanceInputVarNames] <- t(newBalanceValues)
income_df[nrow(income_df) + 1, incomeInputVarNames] <- newIncomeValues

format_percent <- function(x) {
  sprintf("%.2f%%", x * 100)
}

formatted_balance_coefficients <- apply(balanceCoefficients, c(1, 2), format_percent)
formatted_balance_coefficients_df <- as.data.frame(formatted_balance_coefficients)

print("Balance Coefficient Matrix:")
[1] "Balance Coefficient Matrix:"
print(formatted_balance_coefficients_df, row.names = TRUE)
              GDP Growth Unemployment Rate Interest Rates Housing Market
Loans             -3.12%             2.73%          8.24%         -2.89%
LoanLossAllow      7.10%            -9.28%         -2.36%         -1.59%
NonIntBearDep      7.88%            -5.30%         -7.29%          3.29%
IntBearDep        -9.84%             7.36%         -0.43%         -0.62%
              Inflation Rate Stock Market Consumer Confidence Corporate Profits
Loans                 -3.40%        8.59%               4.32%             2.88%
LoanLossAllow          4.27%       -5.65%               7.45%             8.41%
NonIntBearDep          1.58%       -3.15%              -4.44%            -5.69%
IntBearDep            -5.51%        6.25%               4.21%             5.77%
# Format and print the income coefficient matrix
formatted_income_coefficients <- apply(incomeCoefficients, c(1, 2), format_percent)
formatted_income_coefficients_df <- as.data.frame(formatted_income_coefficients)

print("Income Coefficient Matrix:")
[1] "Income Coefficient Matrix:"
print(formatted_income_coefficients_df, row.names = TRUE)
             GDP Growth Unemployment Rate Interest Rates Housing Market
Loans            -5.55%             0.74%         -5.08%         -3.37%
EqSec            -3.55%            -3.00%         -1.78%         -1.02%
OthIntInc         4.33%            -8.75%         -4.83%          1.85%
ProvCredLoss     -7.57%            -6.37%         -4.73%          9.31%
DepIntExp         0.45%             2.24%         -6.16%          5.16%
MortBank         -4.33%            -0.73%         -4.04%          3.63%
             Inflation Rate Stock Market Consumer Confidence Corporate Profits
Loans                 2.17%       -1.18%               7.28%            -5.28%
EqSec                 9.44%        8.05%              -7.32%             0.57%
OthIntInc             1.43%        5.21%              -7.94%             8.02%
ProvCredLoss         -1.87%       -6.92%              -3.75%             7.53%
DepIntExp            -8.28%        1.09%              -2.73%             9.04%
MortBank             -1.33%        4.78%               9.79%             6.42%
print("Stress Scenario Changes:")
[1] "Stress Scenario Changes:"
print(stressScenarios)
                    Change
GDP Growth           -0.01
Unemployment Rate     0.02
Interest Rates       -0.03
Housing Market        0.01
Inflation Rate       -0.02
Stock Market          0.03
Consumer Confidence   0.04
Corporate Profits    -0.01
print("Balance Change Matrix:")
[1] "Balance Change Matrix:"
print(as.data.frame(apply(balanceChangeMatrix_t, 2, format_percent)))
              apply(balanceChangeMatrix_t, 2, format_percent)
Loans                                                   0.28%
LoanLossAllow                                          -0.24%
NonIntBearDep                                          -0.18%
IntBearDep                                              0.66%
print("Income Change Matrix:")
[1] "Income Change Matrix:"
print(as.data.frame(apply(incomeChangeMatrix_t, 2, format_percent)))
             apply(incomeChangeMatrix_t, 2, format_percent)
Loans                                                 0.45%
EqSec                                                -0.23%
OthIntInc                                            -0.33%
ProvCredLoss                                         -0.21%
DepIntExp                                             0.28%
MortBank                                              0.68%
# Let's just confirm with Loans
loans_coefficients <- balanceCoefficients["Loans", ]
stress_scenario_values <- stressScenarios[, "Change"]

intermediate_results_loans <- loans_coefficients * stress_scenario_values
total_change_loans <- sum(intermediate_results_loans)
total_change_loans_percentage <- total_change_loans * 100

print(paste("Total Change for Balance Sheet Loans (as percentage):", sprintf("%.2f%%", total_change_loans_percentage)))
[1] "Total Change for Balance Sheet Loans (as percentage): 0.28%"

Third code block: apply drift rates to the drift variables

# Example drift rates for demonstration

balanceCalcVarNames <- c("TotDebtSec", "NetLoans", "TotDep", "TotLiab", "TotSHREq", "TotAssets", "TotLiabEq")
incomeCalcVarNames <- c("TotIntInc", "TotIntExp", "NetIntInc", "TotNonIntInc", "TotRev", 
                        "TotNonIntExp", "IncBefTax","NetIncBefNCI", "WFNetInc", "WFNetIncCS")

# Define indices or names for drift variables and their rates
driftVarIndicesBalance <- setdiff(names(balance_df), c(balanceCalcVarNames, balanceInputVarNames))
driftVarIndicesIncome <- setdiff(names(income_df), c(incomeCalcVarNames, incomeInputVarNames))

# Example drift rates for demonstration
# vector of repeating value the length of driftVarIndicesBalance
driftRatesBalance <- rep(0.02, length(driftVarIndicesBalance))
driftRatesIncome <- rep(0.03, length(driftVarIndicesIncome))

# Apply drift
balance_df[nrow(balance_df), driftVarIndicesBalance] <- balance_df[nrow(balance_df) - 1, driftVarIndicesBalance] * (1 + driftRatesBalance)
income_df[nrow(income_df), driftVarIndicesIncome] <- income_df[nrow(income_df) - 1, driftVarIndicesIncome] * (1 + driftRatesIncome)

# Print the drift variables and their new values for balance_df
print("Balance Drift Variables and Their New Values:")
[1] "Balance Drift Variables and Their New Values:"
for (var in driftVarIndicesBalance) {
  cat(sprintf("%s: %.2f -> %.2f\n", var, balance_df[nrow(balance_df) - 1, var], balance_df[nrow(balance_df), var]))
}
Cash: 33026.00 -> 33686.52
IntEarnDep: 204193.00 -> 208276.86
FedFundsSold: 80456.00 -> 82065.12
TradDebtSec: 97302.00 -> 99248.04
AvailSaleDebtSec: 130448.00 -> 133056.96
HeldMatDebtSec: 262708.00 -> 267962.16
LoansForSale: 4936.00 -> 5034.72
MortServRights: 8508.00 -> 8678.16
PremEquip: 9266.00 -> 9451.32
Goodwill: 25175.00 -> 25678.50
DerivAssets: 18223.00 -> 18587.46
EquitySec: 57336.00 -> 58482.72
OthAssets: 78815.00 -> 80391.30
STBorrow: 89559.00 -> 91350.18
DerivLiab: 18495.00 -> 18864.90
AccExpOthLiab: 71210.00 -> 72634.20
LTDebt: 207588.00 -> 211739.76
PrefStock: 19448.00 -> 19836.96
ComStock: 9136.00 -> 9318.72
AddlPaidCap: 60555.00 -> 61766.10
RetEarn: 201136.00 -> 205158.72
AOCL: -11580.00 -> -11811.60
TreasStock: -92960.00 -> -94819.20
UnearnESOP: 0.00 -> 0.00
NonCtrlInt: 1708.00 -> 1742.16
# Print the drift variables and their new values for income_df
print("Income Drift Variables and Their New Values:")
[1] "Income Drift Variables and Their New Values:"
for (var in driftVarIndicesIncome) {
  cat(sprintf("%s: %.2f -> %.2f\n", var, income_df[nrow(income_df) - 1, var], income_df[nrow(income_df), var]))
}
DebtSec: 16108.00 -> 16591.24
LoansSale: 363.00 -> 373.89
STBIntExp: 3848.00 -> 3963.44
LTBIntExp: 11572.00 -> 11919.16
OthIntExp: 820.00 -> 844.60
DepLendFees: 6140.00 -> 6324.20
InvAdvFees: 8670.00 -> 8930.10
CommBrkFees: 2375.00 -> 2446.25
InvBankFees: 1649.00 -> 1698.47
CardFees: 4256.00 -> 4383.68
NetGainTrade: 4368.00 -> 4499.04
OthNonIntInc: 1935.00 -> 1993.05
Personnel: 35829.00 -> 36903.87
TechTelEquip: 3920.00 -> 4037.60
Occupancy: 2884.00 -> 2970.52
OperLoss: 1183.00 -> 1218.49
ProfOutServ: 5085.00 -> 5237.55
AdvPromo: 812.00 -> 836.36
OthNonIntExp: 5849.00 -> 6024.47
TaxExp: 2607.00 -> 2685.21
NetIncLossNCI: -113.00 -> -116.39
PrefStkDiv: 1160.00 -> 1194.80

Fourth code block: update calculated variables

# Update calculated variables directly in the latest row
balance_df[nrow(balance_df), "TotDebtSec"] <- sum(balance_df[nrow(balance_df), c("TradDebtSec", "AvailSaleDebtSec", "HeldMatDebtSec")])
balance_df[nrow(balance_df), "NetLoans"] <- sum(balance_df[nrow(balance_df), c("Loans", "LoanLossAllow")])
balance_df[nrow(balance_df), "TotAssets"] <- sum(balance_df[nrow(balance_df), c("Cash", "IntEarnDep", "FedFundsSold", "TotDebtSec", "LoansForSale", "NetLoans", "MortServRights", "PremEquip", "Goodwill", "DerivAssets", "EquitySec", "OthAssets")])
balance_df[nrow(balance_df), "TotDep"] <- sum(balance_df[nrow(balance_df), c("NonIntBearDep", "IntBearDep")])
balance_df[nrow(balance_df), "TotLiab"] <- sum(balance_df[nrow(balance_df), c("TotDep", "STBorrow", "DerivLiab", "AccExpOthLiab", "LTDebt")])
# balance_df[nrow(balance_df), "TotSHREq"] <- sum(balance_df[nrow(balance_df), c("PrefStock", "ComStock", "AddlPaidCap",     #       "RetEarn", "AOCL", "TreasStock", "UnearnESOP", "NonCtrlInt")])
balance_df[nrow(balance_df), "TotSHREq"] <- balance_df[nrow(balance_df), "TotAssets"] -
    balance_df[nrow(balance_df), "TotLiab"] 
balance_df[nrow(balance_df), "TotLiabEq"] <- sum(balance_df[nrow(balance_df), c("TotLiab", "TotSHREq")])

# Update calculated variables directly in the latest row
income_df[nrow(income_df), "TotIntInc"] <- sum(income_df[nrow(income_df), c("DebtSec", "LoansSale", "Loans", "EqSec", "OthIntInc")])
income_df[nrow(income_df), "TotIntExp"] <- sum(income_df[nrow(income_df), c("DepIntExp", "STBIntExp", "LTBIntExp", "OthIntExp")])
income_df[nrow(income_df), "NetIntInc"] <- income_df[nrow(income_df), "TotIntInc"] - income_df[nrow(income_df), "TotIntExp"]
income_df[nrow(income_df), "TotNonIntInc"] <- sum(income_df[nrow(income_df), c("DepLendFees", "InvAdvFees", "CommBrkFees", "InvBankFees", "CardFees", "MortBank", "NetGainTrade", "OthNonIntInc")])
income_df[nrow(income_df), "TotRev"] <- income_df[nrow(income_df), "NetIntInc"] + income_df[nrow(income_df), "TotNonIntInc"]
income_df[nrow(income_df), "TotNonIntExp"] <- sum(income_df[nrow(income_df), c("Personnel", "TechTelEquip", "Occupancy", "OperLoss", "ProfOutServ", "AdvPromo", "OthNonIntExp")])
income_df[nrow(income_df), "IncBefTax"] <- income_df[nrow(income_df), "TotRev"] - income_df[nrow(income_df), "TotNonIntExp"] - income_df[nrow(income_df), "ProvCredLoss"]
income_df[nrow(income_df), "NetIncBefNCI"] <- income_df[nrow(income_df), "IncBefTax"] - income_df[nrow(income_df), "TaxExp"]
income_df[nrow(income_df), "WFNetInc"] <- income_df[nrow(income_df), "NetIncBefNCI"] - income_df[nrow(income_df), "NetIncLossNCI"]
income_df[nrow(income_df), "WFNetIncCS"] <- income_df[nrow(income_df), "WFNetInc"] - income_df[nrow(income_df), "PrefStkDiv"]

rownames(balance_df)[nrow(balance_df)] <- "2024"
rownames(income_df)[nrow(income_df)] <- "2024"

# Print the updated dataframes
balance_df_t <- t(balance_df)
balance_df_t <- as.data.frame(balance_df_t)
income_df_t <- t(income_df)
income_df_t <- as.data.frame(income_df_t)

print("Balance Sheet Dataframe:")
[1] "Balance Sheet Dataframe:"
print(balance_df_t)
                    2022    2023       2024
Cash               34596   33026   33686.52
IntEarnDep        124561  204193  208276.86
FedFundsSold       68036   80456   82065.12
TradDebtSec        86155   97302   99248.04
AvailSaleDebtSec  113594  130448  133056.96
HeldMatDebtSec    297059  262708  267962.16
TotDebtSec        496808  490458  500267.16
LoansForSale        7104    4936    5034.72
Loans             955871  936682  939299.44
LoanLossAllow     -12985  -14606  -14570.53
NetLoans          942886  922076  924728.91
MortServRights     10480    8508    8678.16
PremEquip           8350    9266    9451.32
Goodwill           25173   25175   25678.50
DerivAssets        22774   18223   18587.46
EquitySec          64414   57336   58482.72
OthAssets          75838   78815   80391.30
NonIntBearDep     458010  360279  359630.90
IntBearDep        925975  997894 1004485.92
TotDep           1383985 1358173 1364116.83
STBorrow           51145   89559   91350.18
DerivLiab          20067   18495   18864.90
AccExpOthLiab      68740   71210   72634.20
LTDebt            174870  207588  211739.76
TotLiab          1698807 1745025 1758705.87
PrefStock          19448   19448   19836.96
ComStock            9136    9136    9318.72
AddlPaidCap        60319   60555   61766.10
RetEarn           187968  201136  205158.72
AOCL              -13362  -11580  -11811.60
TreasStock        -82853  -92960  -94819.20
UnearnESOP          -429       0       0.00
NonCtrlInt          1986    1708    1742.16
TotSHREq          182213  187443  196622.88
TotAssets        1881020 1932468 1955328.75
TotLiabEq        1881020 1932468 1955328.75
print("Income Statement Dataframe:")
[1] "Income Statement Dataframe:"
print(income_df_t)
               2021  2022  2023       2024
DebtSec        9253 11781 16108 16591.2400
LoansSale       865   513   363   373.8900
Loans         28634 37715 57155 57414.4138
EqSec           608   707   682   680.4498
OthIntInc       334  3308 10810 10774.8639
TotIntInc     39694 54024 85118 85834.8574
DepIntExp       388  2349 16503 16548.3975
STBIntExp       -41   582  3848  3963.4400
LTBIntExp      3173  5505 11572 11919.1600
OthIntExp       395   638   820   844.6000
TotIntExp      3915  9074 32743 33275.5975
NetIntInc     35779 44950 52375 52559.2599
DepLendFees    6920  6713  6140  6324.2000
InvAdvFees    11011  9004  8670  8930.1000
CommBrkFees    2299  2242  2375  2446.2500
InvBankFees    2354  1439  1649  1698.4700
CardFees       4175  4355  4256  4383.6800
MortBank       4956  1383   829   834.6686
NetGainTrade   7264  1461  4368  4499.0400
OthNonIntInc   4408  2821  1935  1993.0500
TotNonIntInc  43387 29418 30222 31109.4586
TotRev        79166 74368 82597 83668.7186
ProvCredLoss  -4155  1534  5399  5387.5345
Personnel     35541 34340 35829 36903.8700
TechTelEquip   3227  3375  3920  4037.6000
Occupancy      2968  2881  2884  2970.5200
OperLoss       1568  6984  1183  1218.4900
ProfOutServ    5723  5188  5085  5237.5500
AdvPromo        600   505   812   836.3600
OthNonIntExp   4131  3932  5849  6024.4700
TotNonIntExp  53758 57205 55562 57228.8600
IncBefTax     29563 15629 21636 21052.3240
TaxExp         5764  2251  2607  2685.2100
NetIncBefNCI  23799 13378 19029 18367.1140
NetIncLossNCI  1690  -299  -113  -116.3900
WFNetInc      22109 13677 19142 18483.5040
PrefStkDiv     1291  1115  1160  1194.8000
WFNetIncCS    20818 12562 17982 17288.7040