Financial Model

---

We are going to use the Loss Model and simulation tools we have developed to generate a projection for Opco and Fico performance.

We can then use the projection to evaluate:

1. investment required

2. attractiveness of the investment

Inputs

• 15 years, tracked quarterly

• Loan Growth: target of $4MM of new originations per quarter by Year 15

• Loan Size: average loan size of $100K growing to $300K by Year 15

• Credit Quality: average BB+/BB rating

• LGD: calculated using Fair Market Value (FMV) approach. 10% liquidation costs assumed.

• Pricing: market-driven pricing utilized. Current public debt rates available from St. Louis Federal Reserve. Missing ratings is interpolated.

  • Additional markups (total eyeball):

    • Liquidity Premium: 1%

    • Credit Risk Premium: 0.5%

    • Profit: 1%

  • Minimum Rate: 4.5%

• Cost of Debt to Opco: LIBOR + 1.4%

Assumptions

• All loans amortized over 5 years

• PDs do not change over time

• PDs are correlated

• FMV depreciation curve is the same for each loan

• FMV depreciation estimated using tried-on-true “eyeball” approach

• PDs and LGDs are not correlated

Process

1. generate new loan originations

2. generate borrower credit ratings and assign PDs

3. generate default correlation matrix

4. generate pricing targets at each credit rating

5. generate FMV depreciation curve

6. assign FMV to each loan for each quarter in the projection

   • FMV is further augmented as normally-distributed around expected FMV

7. For each Year in the projection

   • build loss model based on new and existing loans (carried forward from prior quarter)

   • generate 15,000 simulations for each quarter and build loss distribution

   • assign pricing based on credit quality

   • assign economic capital required

   • assign payment and amortization schedules for new loans

8. Build financial statements from the quarterly proe

Building the Code

Here we build out the loan code.

# Build Inputs
years = 15
periods = years * 4
n_amort = 5 * 4
loan_tgt = 1000000

dist = expon.pdf(np.arange(periods), 0, periods/3)*periods
originations = (-dist + dist.max()) * loan_tgt

start_avg = 100000
end_avg = 300000
step = (end_avg - start_avg) / (periods)
AVG_LOAN_SIZE = np.arange(start_avg, end_avg, step)

n_loans = np.ceil(originations / AVG_LOAN_SIZE).astype(np.int16)

# Generate New Loans
loans = []
for i in range(n_loans.shape[0]):
    rl = np.random.uniform(0.1, 1, n_loans[i])
    loans.append(originations[i] * rl / rl.sum())

df_loans = pd.DataFrame(loans).T
fillsize = n_loans.sum() - df_loans.shape[0]

filler = np.empty(shape=(fillsize, periods))
filler[:] = np.nan
df_loans = pd.concat([df_loans, pd.DataFrame(filler)])

df_loans = df_loans.apply(vintshift).reset_index(drop=True)

filler = np.empty(shape=(n_amort, n_loans.sum()))
filler[:] = np.nan
df_loans = pd.concat([df_loans.T, pd.DataFrame(filler)]).reset_index(drop=True).T
loan_nums = np.argwhere(df_loans.notna().values)

The code above results in the following loan growth forecasted:

# Generate Credit Ratings
mu = 15
std = 6
n, p = norm_to_binom(mu, std)
pd_idx = np.random.binomial(SPs.shape[0], p, n_loans.sum())

Below we show the distribution of credit ratings in the loan portfolio.

# Assign PDs to loans based on Credit Ratings
p_of_ds = nan_like(df_loans)
pd_vars = nan_like(df_loans)

p_of_d = PDs[pd_idx]
pd_var = bern.var(p_of_d)

assert p_of_d.shape[0] == loan_nums.shape[0]

for i in trange(p_of_d.shape[0], leave=False):
    x, y = loan_nums[i]
    p_of_ds.iloc[x,y] = p_of_d[i]
    pd_vars.iloc[x,y] = pd_var[i]

p_of_ds = p_of_ds.ffill(axis=1)
pd_vars = pd_vars.ffill(axis=1)

# Correlation Matrix of Defaults at each Vintage
corrmats = []
for v, col in tqdm(df_loans.iteritems(), total=df_loans.shape[1], leave=False):
    df_todate = df_loans.iloc[:, :v+1]
    n = df_todate.notna().values.sum()

    if n > 0:
        p = np.random.uniform(.3, .6, n)
        corrmat = corrs_to_corrmat(p)
        corrmat = fix_corrmat(corrmat)
        corrmats.append(corrmat)
    else:
        corrmats.append(np.array([]))

# Pricing
yields = get_yields()
y_ = yields.iloc[-1].values[1:].reshape(-1, 1)
zeros = np.zeros((yields.iloc[-1].shape[0] -1, 2))
zeros[:] = np.nan
y_ = np.hstack((y_, zeros)).reshape(-1)

yields = pd.Series(y_).interpolate(method='slinear').tolist()
yields = np.array(yields)

lqd_mu = .01
cred_mu = .005
prof_mu = .01

rates = yields + lqd_mu + cred_mu + prof_mu
min_rate = .045

  0%|          | 0/616 [00:00<?, ?it/s]

Below we show the target interest rate pricing for each possible credit rating:

# Generate FMV depreciation curve
n_amort = 20
x = np.arange(n_amort*2)
mv_curve = line_by_line(x)

And below we see the assumed market value depreciation of the equipment being financed.

# Generate FMV for each loan over the entire projection
std = 5000
lqd_exp = .1
fmv = nan_like(df_loans)
for x,y in loan_nums:
    dist = min(fmv.shape[1] - y, mv_curve.shape[0])
    fmv.iloc[x, y:y+dist] = df_loans.iloc[x, y]*mv_curve[:dist]

fmv.iloc[:, :] = norm.rvs(fmv, std)*(1-lqd_exp)

# Generate Projection
interest = nan_like(df_loans)
int_rates = []
econ_caps = np.zeros(df_loans.shape[1])
charge_offs = np.zeros(df_loans.shape[1])
n_amort = 5*4
n_samples = 15000
ba = .0042
spread = .01
debt_rate = ba + spread

with warnings.catch_warnings():
    warnings.filterwarnings('ignore')
    for vintage, col in df_loans.iteritems():
        print (f'Vintage: {vintage}', end='\r')
        if vintage > 0:
            df_loans, interest, econ_caps, charge_offs, int_rates = booksim(vintage, df_loans, p_of_ds, pd_idx, corrmats, fmv, interest, loan_nums, econ_caps, charge_offs, rates, int_rates, min_rate=min_rate, cost_of_debt=debt_rate, n_samples=15000)

Vintage: 79

Financials

We can now build financial statements from the simulated data.

The key consideration for assessing the project is including new sales by Opco (financed by Fico) in the cash flow assumption.

Key Assumptions:

• Opco provides ALL capital support to Fico

• Opco invests both debt and equity into Fico

• Opco invests equity equal to economic capital required.

• Remainder of Fico capital is injected as shareholder debt, charged at Opco’s cost of debt.

• In exchange for low interest rate on debt, 100% of Fico loans are sales by Opco.

• New sales earn 15% gross margin

• Assume $150K in general expenses for Opco/Fico (paid for by Opco) growing at 7% per year

Process for Fico

1. create quarterly income statement and balance sheet

   • revenue found via interest on loans

   • charge-Offs found as Expected Losses from loss distribution generated from simulations in each quarter

   • pulled from projection:

     • Loans: Total advances less repayments and charge-offs

     • Req Equity: economic capital required as per Loss distribution in each quarter

   • implied:

     • Debt: difference between Loans and Req Equity.

     • Cash: cumulative profits generated (offset of Retained Earnings)

     • Net Debt: Debt less Cash (assumes Debt is reduced by whatever cash is avaialble)

2. adjust for interest expense shield from profits

3. consolidate to annual statements

4. create table of key ratios

Process for Opco

1. Generate Income Statement assuming 100% of new loan originations booked as revenue

2. Interest Expense earned is assumed to be offset by interest paid to Opco lender

Handling Circular Debt/Income Relationship

• debt creates interest expense which lowers profitability which leads to higher debt

• so there is an optimal level of debt that must be determined

• the asset side of our balance sheet is fixed, i.e. the amount of loans is already known, we can find the debt level with some simple math. in our approach, we find the cash level, which is a function of the income, which then provides the debt level.

\(Income_i = Revenue_i - ChargeOffs_i - (Debt_i - Cash_i)*Cost of Debt_i / 4 \\Loans_i = Debt_i + E_i = (Debt_i - Cash_i) + Required Equity_i + Retained Earnings_{i-1} + Income_i \\Cash_i = Loans_i - Required Equity_i - Retained Earnings_{i-1} - Revenue + ChargeOffs + Debt*(Cost of Debt/4 - 1) / (Cost of Debt/4 - 1)\)

# Create IS and BS
index = ['Loans', 'Assets', 'Cash', 'Debt', 'Net Debt', 'Req Equity', 'Retained Earnings', 'Equity', 'D+E']
assets = df_loans.sum(axis=0)
debt = assets - econ_caps
cash = np.zeros_like(debt)
net = np.zeros_like(debt)
re = np.zeros_like(debt)
eq = np.zeros_like(debt)
de = np.zeros_like(debt)
BS = pd.DataFrame([assets, assets, cash, debt, net, econ_caps, re, eq, de], index=index).iloc[:, :periods]

index = ['Revenue', 'Charge Offs', 'Interest Exp', 'Profit']
cost_of_debt = np.zeros_like(debt)
prof = np.zeros_like(debt)
IS = pd.DataFrame([interest.sum(axis=0), charge_offs, cost_of_debt, prof], index=index).iloc[:, :periods]

# Adjust Interest Expense and Debt for profits
for i, col in BS.iteritems():
    re = 0 if i == 0 else BS.loc['Retained Earnings', i - 1]
    num = col.loc['Loans'] - col.loc['Req Equity'] - IS.loc['Revenue', i] \
        - re + IS.loc['Charge Offs', i] + col.loc['Debt']*(debt_rate/4 - 1)
    denom = (debt_rate/4 - 1)
    col.loc['Cash'] = -num/denom
    col.loc['Net Debt'] = col.loc['Debt'] + col.loc['Cash']

    IS.loc['Interest Exp', i] = col.loc['Net Debt']*debt_rate/4
    IS.loc['Profit', i] = IS.loc['Revenue', i] - IS.loc['Charge Offs', i] \
        - IS.loc['Interest Exp', i]

    col.loc['Retained Earnings'] = re + IS.loc['Profit', i]
    col.loc['Equity'] = col.loc['Retained Earnings']  + col.loc['Req Equity']

BS.loc['D+E'] = BS.loc['Net Debt'] + BS.loc['Equity']
BS.loc['bal'] = BS.loc['D+E'] - BS.loc['Assets']

# Aggregate to Annual
IS_annual = IS.T.groupby(IS.T.index//4).sum().T
IS_annual.columns = np.arange(1, IS_annual.shape[1] + 1)

BS_annual = BS[np.arange(3,BS.shape[1],4)].T.reset_index(drop=True).T
BS_annual.columns = np.arange(1, BS_annual.shape[1] + 1)

# Create Table of Key Ratios
index = ['Cap Ratio', 'ROE', 'Charge-Off Rate']
caprat = BS_annual.loc['Equity'] / BS_annual.loc['Loans']
roe = IS_annual.loc['Profit'] / BS_annual.loc['Equity']
cumroe = IS_annual.loc['Profit'].cumsum() / BS_annual.loc['Equity'].iloc[-1]
co_annual = charge_offs[:periods].reshape(periods//4,4).sum(axis=1)
co_rate = co_annual / BS_annual.loc['Loans']
rats1 = pd.DataFrame([caprat, roe, co_rate], index=index)

We can see the results below. First, with the Income Statement:

Then, the Balance Sheet:

Finally, we summarize some key ratios:

index = ['Sales', 'Gross Profit', 'Interest Revenue']
sales = np.array([loan.sum() for loan in loans])
gm = .15
gp = sales*gm
int_rev = IS.loc['Interest Exp']

OPCO_IS = pd.DataFrame([sales, gp, int_rev], index=index).iloc[:, :periods]
OPCO_IS.loc['Gross Profit'] = np.where(OPCO_IS.loc['Gross Profit'].isna(), 0, OPCO_IS.loc['Gross Profit'])
OPCO_IS.loc['General Exp'] = 150000/4*((1 + .07/4)**np.arange(periods))
OPCO_IS.loc['Profit'] = OPCO_IS.loc['Gross Profit'] - OPCO_IS.loc['General Exp']

OPCOIS_annual = OPCO_IS.T.groupby(OPCO_IS.T.index//4).sum().T
OPCOIS_annual.columns = np.arange(1, OPCOIS_annual.shape[1] + 1)

Below we highlight some key details of the projection.

Investment Evaluation

We can now use our projections to evaluate the value of the investment to Opcos’ shareholders using Internal Rate of Return (IRR) and Net Present Value (NPV).

Assumptions:

• Cash outflows (generally debt or equity investments) and inflows (profit or proceeds of financing) are netted for each year.

• A terminal value is determined using a multiple of Year 15 profit, discounted back to present

  • Opco TV multiple of 5.5x

  • Fico TV multiple of 5.5x

discount = .10
fico_mult = opco_mult = 5.5

d_co = BS_annual.loc['Net Debt'].shift(1).fillna(0) - BS_annual.loc['Net Debt']
d_ci = OPCOIS_annual.loc['Profit']
opco_tv = OPCOIS_annual.loc['Profit', 15]*fico_mult

d_co.loc[d_co.shape[0] + 1] = 0
d_ci.loc[d_ci.shape[0] + 1] = opco_tv

d_irr = npf.irr(d_co + d_ci)
d_npv = npf.npv(discount, d_co + d_ci)

e_co = -(BS_annual.loc['Req Equity'] - BS_annual.loc['Req Equity'].shift(1).fillna(0))
e_ci = IS_annual.loc['Profit']
fico_tv = IS_annual.loc['Profit', 15]*opco_mult

e_co.loc[e_co.shape[0]+1] = 0
e_ci.loc[e_ci.shape[0]+1] = fico_tv

e_irr = npf.irr(e_co + e_ci)
e_npv = npf.npv(discount, e_co + e_ci)

irr = npf.irr(d_co + d_ci + e_co + e_ci)
npv = npf.npv(discount, d_co + d_ci + e_co + e_ci)

irr_ex_tv = npf.irr(d_co[:-1] + d_ci[:-1] + e_co[:-1] + e_ci[:-1])
npv_ex_tv = npf.npv(discount, d_co[:-1] + d_ci[:-1] + e_co[:-1] + e_ci[:-1])

3rd Party Borrowing

We will adjust the evaluation above to consider the impact of acquiring 3rd party financing for Fico at the 5-year mark. The addition of 3rd party financing should reduce Opco capital requirements and enhance the project’s return.

Assumptions:

• Year 0 - 5: 0% of debt financed by 3rd Party

• Year 5 - 10: 50% of debt financed by 3rd Party

• Year 10 - 15: 75% of debt financed by 3rd Party

• 3rd Party Finance Rate: 2.75%

BS3 = BS_annual.copy(deep=True)
IS3 = IS_annual.copy(deep=True)
OPCO_IS3 = OPCOIS_annual.copy(deep=True)

bank_debt_rate = .0275

BS3.loc['Bank Debt'] = 0
BS3.loc['Shhr Debt'] = 0
BS3.loc['Retained Earnings'] = 0
BS3 = BS3.reindex(['Loans', 'Assets', 'Cash', 'Debt', 'Bank Debt', 'Shhr Debt', 'Net Debt', 'Req Equity', 'Retained Earnings', 'Equity', 'D+E'])

OPCO_IS3.loc['General Exp'] = 150000*((1 + .07)**np.arange(15))

OPCO_IS3 = OPCO_IS3.reindex(['Sales', 'Gross Profit', 'Interest Revenue', 'General Exp', 'Profit'])
OPCO_IS3.loc['Profit'] = OPCO_IS3.loc['Gross Profit'] - OPCO_IS3.loc['General Exp']

# Adjust Interest Expense and Debt for profits
for i, col in BS3.iteritems():
    bank_factor = 0 if i < 5 else (.5 if i < 10 else .75)
    rate_factor = debt_rate if i < 5 else ((debt_rate + bank_debt_rate)*.5 if i < 10 else (bank_debt_rate*.75 + debt_rate*.25))
    re = 0 if i == 1 else BS3.loc['Retained Earnings', i - 1]
    num = col.loc['Loans'] - col.loc['Req Equity'] - IS3.loc['Revenue', i] \
        - re + IS3.loc['Charge Offs', i] + col.loc['Debt']*(rate_factor - 1)
    denom = rate_factor - 1

    col.loc['Cash'] = -num/denom
    col.loc['Net Debt'] = col.loc['Debt'] + col.loc['Cash']
    col.loc['Bank Debt'] = col.loc['Net Debt']*bank_factor
    col.loc['Shhr Debt'] = col.loc['Net Debt'] - col.loc['Bank Debt']

    IS3.loc['Interest Exp', i] = col.loc['Shhr Debt']*debt_rate + col.loc['Bank Debt']*bank_debt_rate
    IS3.loc['Profit', i] = IS3.loc['Revenue', i] - IS3.loc['Charge Offs', i] \
        - IS3.loc['Interest Exp', i]

    col.loc['Retained Earnings'] = re + IS3.loc['Profit', i]
    col.loc['Equity'] = col.loc['Retained Earnings']  + col.loc['Req Equity']

BS3.loc['D+E'] = BS3.loc['Net Debt'] + BS3.loc['Equity']
BS3.loc['bal'] = BS3.loc['D+E'] - BS3.loc['Assets']
BS3.loc['bal2'] = BS3.loc['D+E'] - BS3.loc[['Bank Debt', 'Shhr Debt', 'Req Equity', 'Retained Earnings']].sum()


index = ['CapRat', 'ROE', 'CORate']
tnw = np.where(BS3.columns >= 5, BS3.loc['Equity'] + BS3.loc['Shhr Debt'],  BS3.loc['Equity'])
caprat = tnw / BS3.loc['Loans']
roe = IS3.loc['Profit'] / BS3.loc['Equity']
cumroe = IS3.loc['Profit'].cumsum() / BS3.loc['Equity'].iloc[-1]
co_annual = charge_offs[:periods].reshape(periods//4,4).sum(axis=1)
co_rate = co_annual / BS3.loc['Loans']
rats2 = pd.DataFrame([caprat, roe, co_rate], index=index)

discount = .10

d_co = BS3.loc['Shhr Debt'].shift(1).fillna(0) - BS3.loc['Shhr Debt']
d_ci = OPCO_IS3.loc['Profit']
opco_tv = OPCO_IS3.loc['Profit', 15]*fico_mult

d_co.loc[d_co.shape[0] + 1] = 0
d_ci.loc[d_ci.shape[0] + 1] = opco_tv

d_irr = npf.irr(d_co + d_ci)
d_npv = npf.npv(discount, d_co + d_ci)

e_co = -(BS3.loc['Req Equity'] - BS3.loc['Req Equity'].shift(1).fillna(0))
e_ci = IS3.loc['Profit']
fico_tv = IS3.loc['Profit', 15]*opco_mult

e_co.loc[e_co.shape[0]+1] = 0
e_ci.loc[e_ci.shape[0]+1] = fico_tv

e_irr = npf.irr(e_co + e_ci)
e_npv = npf.npv(discount, e_co + e_ci)

irr = npf.irr(d_co + d_ci + e_co + e_ci)
npv = npf.npv(discount, d_co + d_ci + e_co + e_ci)

irr_ex_tv = npf.irr(d_co[:-1] + d_ci[:-1] + e_co[:-1] + e_ci[:-1])
npv_ex_tv = npf.npv(discount, d_co[:-1] + d_ci[:-1] + e_co[:-1] + e_ci[:-1])

We can above that refinancing of shareholder loans via standard 3rd party debt (likely bank debt) can significantly improve shareholder returns.