Back to site

Excel: Financial Risk VBA Toolkit

Runs entirely in your browser — nothing is uploaded.

Liquidity, leverage, coverage and valuation ratios for a quick company health check, plus a macro that writes a full loan amortization schedule to a new sheet.

Install

  1. Download the .bas file below.
  2. In Excel, press Alt+F11 to open the VBA editor.
  3. File → Import File… and pick the downloaded file.
  4. Save the workbook as a macro-enabled file (.xlsm) to keep it.

Every Function below then works as a normal worksheet formula (e.g. =ExpectedLoss(B2,C2,D2)); every Sub runs from Developer → Macros.

What's inside

CurrentRatio / QuickRatioStandard liquidity ratios.
DebtToEquity / DebtToEBITDALeverage ratios.
InterestCoverageRatio(EBIT, Interest)EBIT ÷ interest expense.
DSCR(EBITDA, Principal, Interest)Debt service coverage ratio.
CovenantHeadroomPct(Current, Threshold)Room left before a maximum-ratio covenant breaches.
DuPontROE(...)3-step DuPont ROE decomposition.
WACC(...)Weighted average cost of capital.
LoanEMI(Principal, Rate, Years)Equal monthly instalment.
Sub GenerateAmortizationSchedule()Prompts for a loan, writes a full month-by-month schedule to a new sheet.

Source

Attribute VB_Name = "FinancialRiskToolkit"
Option Explicit

' ============================================================
' Financial Risk Analysis Toolkit — Rajat Mani Karn
' Liquidity, leverage, coverage and valuation ratios for a
' quick company health check, plus a loan amortization generator.
'
' Install: Alt+F11 -> File -> Import File... and pick this .bas.
' ============================================================

Public Function CurrentRatio(ByVal CurrentAssets As Double, ByVal CurrentLiabilities As Double) As Double
    CurrentRatio = CurrentAssets / CurrentLiabilities
End Function

Public Function QuickRatio(ByVal CurrentAssets As Double, ByVal Inventory As Double, ByVal CurrentLiabilities As Double) As Double
    QuickRatio = (CurrentAssets - Inventory) / CurrentLiabilities
End Function

Public Function DebtToEquity(ByVal TotalDebt As Double, ByVal TotalEquity As Double) As Double
    DebtToEquity = TotalDebt / TotalEquity
End Function

Public Function DebtToEBITDA(ByVal TotalDebt As Double, ByVal EBITDA As Double) As Double
    DebtToEBITDA = TotalDebt / EBITDA
End Function

Public Function InterestCoverageRatio(ByVal EBIT As Double, ByVal InterestExpense As Double) As Double
    InterestCoverageRatio = EBIT / InterestExpense
End Function

' Debt Service Coverage Ratio: cash available for debt service / total debt service due this period.
Public Function DSCR(ByVal EBITDA As Double, ByVal PrincipalDue As Double, ByVal InterestDue As Double) As Double
    DSCR = EBITDA / (PrincipalDue + InterestDue)
End Function

' Headroom (as a decimal, e.g. 0.12 = 12%) before breaching a maximum-type covenant
' such as Net Debt/EBITDA <= 3.5x. Positive = compliant with room to spare; negative = breached.
Public Function CovenantHeadroomPct(ByVal CurrentRatioValue As Double, ByVal ThresholdRatio As Double) As Double
    CovenantHeadroomPct = (ThresholdRatio - CurrentRatioValue) / ThresholdRatio
End Function

' DuPont 3-step ROE decomposition: Net margin x Asset turnover x Equity multiplier.
Public Function DuPontROE(ByVal NetMargin As Double, ByVal AssetTurnover As Double, ByVal EquityMultiplier As Double) As Double
    DuPontROE = NetMargin * AssetTurnover * EquityMultiplier
End Function

' Weighted average cost of capital. CostOfEquity/CostOfDebt/TaxRate as decimals.
Public Function WACC(ByVal CostOfEquity As Double, ByVal CostOfDebt As Double, ByVal TaxRate As Double, _
                      ByVal EquityValue As Double, ByVal DebtValue As Double) As Double
    Dim total As Double, we As Double, wd As Double
    total = EquityValue + DebtValue
    we = EquityValue / total
    wd = DebtValue / total
    WACC = we * CostOfEquity + wd * CostOfDebt * (1 - TaxRate)
End Function

' Equal Monthly Instalment for a fully-amortizing loan.
Public Function LoanEMI(ByVal Principal As Double, ByVal AnnualRatePct As Double, ByVal Years As Double) As Double
    Dim r As Double, n As Double
    r = AnnualRatePct / 100 / 12
    n = Years * 12
    If r = 0 Then
        LoanEMI = Principal / n
    Else
        LoanEMI = Principal * r * (1 + r) ^ n / ((1 + r) ^ n - 1)
    End If
End Function

' Prompts for a loan's principal/rate/tenure and writes a full month-by-month
' amortization schedule (payment, principal, interest, balance) to a new sheet.
Public Sub GenerateAmortizationSchedule()
    Dim principal As Double, ratePct As Double, years As Double
    principal = Application.InputBox("Loan principal:", "Amortization Schedule", 1000000, Type:=1)
    If principal = 0 Then Exit Sub
    ratePct = Application.InputBox("Annual interest rate %:", "Amortization Schedule", 10, Type:=1)
    years = Application.InputBox("Tenure (years):", "Amortization Schedule", 5, Type:=1)

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Amortization " & Format(Now, "hhmmss")

    ws.Range("A1:E1").Value = Array("Month", "Payment", "Principal", "Interest", "Balance")
    ws.Range("A1:E1").Font.Bold = True

    Dim r As Double, n As Long, emi As Double, balance As Double, i As Long
    Dim interest As Double, principalPart As Double
    r = ratePct / 100 / 12
    n = years * 12
    emi = LoanEMI(principal, ratePct, years)
    balance = principal

    For i = 1 To n
        interest = balance * r
        principalPart = emi - interest
        balance = balance - principalPart
        If balance < 0.01 Then balance = 0

        ws.Cells(i + 1, 1).Value = i
        ws.Cells(i + 1, 2).Value = emi
        ws.Cells(i + 1, 3).Value = principalPart
        ws.Cells(i + 1, 4).Value = interest
        ws.Cells(i + 1, 5).Value = balance
    Next i

    ws.Columns("A:E").AutoFit
    MsgBox "Amortization schedule written to sheet """ & ws.Name & """.", vbInformation
End Sub