Loan profitability and credit review

The engagement

Rawdah Home Retail Co. (a fictional home-furnishings retailer) has asked Najd Commercial Bank (a fictional commercial bank) for a facility package to fund its store roll-out: a SAR 150m term loan, drawn in full at closing, and a SAR 80m revolving facility with SAR 30m drawn at closing. The relationship manager has offered 7.75% on drawn balances and a 0.5% commitment fee on the undrawn revolver.

You are the credit and pricing analyst. The credit committee wants one workbook that answers three questions: can Rawdah carry this debt (pro forma and under stress), does the facility pay for its risk and capital at the offered terms, and what should the bank do — approve, reprice or decline?

The Borrower tab holds Rawdah's FY2025 figures, the request, Najd's credit policy and its rating grid. The Pricing tab holds the pricing parameters. Every rate, PD, LGD, CCF, capital allocation and threshold is illustrative: these are simplified educational models, not any bank's rules or any regulatory requirement.

Deliverables

Complete every white cell with a formula linked to the given figures or to other workings — no typed numbers. The workbook will be re-tested with different input figures, so typed results will be found.

Conventions (follow exactly)

• Units: SAR millions; rates are annual and apply to the balances at closing for a full first year (no day count, no amortization in year one).
• Pro forma = as if the facility were drawn as at closing: borrowings = FY2025 borrowings + term loan + revolver drawn; cash is unchanged (proceeds are spent). Net debt = borrowings − cash. Leverage = net debt ÷ EBITDA.
• Pro forma interest = FY2025 interest + (term loan + revolver drawn) × offered rate. Interest cover = EBIT ÷ interest.
• Free cash flow = CFO − capex (capex is given as a positive spend).
• EBITDA cushion = 1 − pro forma net debt ÷ (maximum leverage × EBITDA): the fall in EBITDA the leverage limit can absorb.
• Stress: EBITDA falls by the stress percentage; EBIT falls by the same amount (EBIT − EBITDA × stress); net debt and pro forma interest are unchanged. Within policy = stressed leverage ≤ maximum and stressed cover ≥ minimum, as TRUE/FALSE.
• PD: look up pro forma (unstressed) leverage in the rating grid with VLOOKUP(…, TRUE): each band runs from its lower bound up to the next.
• Exposure: undrawn = limit − drawn on the revolver; EAD = drawn + CCF × undrawn. Expected loss = PD × LGD × EAD.
• Capital allocated = capital % × EAD. Pre-tax hurdle = target ROE ÷ (1 − zakat and tax rate).
• Minimum rate on drawn balances = funding cost + operating cost + EL ÷ drawn + capital charge − commitment-fee credit, where capital charge = capital ÷ drawn × (pre-tax hurdle − funding cost) and fee credit = commitment fee × undrawn ÷ drawn. (With no revolver this is the track's build-up: funding + operating cost + PD × LGD + capital × (hurdle − funding).)
• Profitability at the offered rate, costs as positive numbers: interest = drawn × offered rate; fees = undrawn × commitment fee; funding cost = drawn × funding cost; operating cost = drawn × operating cost %; expected loss as above; capital benefit = capital × funding cost (the equity-funded slice needs no borrowed funding). Risk-adjusted profit = interest + fees − funding − operating cost − EL + capital benefit.
• RAROC (an educational risk-adjusted return measure): pre-tax RAROC = risk-adjusted profit ÷ allocated capital; after-tax RAROC = pre-tax × (1 − zakat and tax rate). Economic profit = (after-tax RAROC − target ROE) × capital.
• Break-even PD = the PD at which pre-tax RAROC equals the pre-tax hurdle at the offered rate, holding capital fixed: (interest + fees − funding − operating cost + capital benefit − hurdle × capital) ÷ (LGD × EAD).
• Decision: "Decline" if the request is not within policy under stress; otherwise "Approve" if after-tax RAROC ≥ target ROE, else "Reprice". Use IF. Rate to quote = the higher of the offered and minimum rates; show the after-tax RAROC at that rate. Largest term loan within the stressed leverage limit = maximum leverage × stressed EBITDA − (borrowings + revolver drawn − cash).
• No rounding. Results are graded to within 0.05%.

Data source

Rawdah Home Retail Co. and Najd Commercial Bank are fictional companies created for this course. Rawdah's figures come from its course statements; Najd's funding cost (its term-borrowing rate) and zakat and tax rate come from the bank's course case. Neither is based on any real business, and no figure here describes a real market rate or regulatory requirement.