Project Finance Sources Uses Cash Flow Waterfall Calculator

Purpose and model boundary

This model links construction sources and uses to an eight-period project cash waterfall. It funds construction spend and the first-period reserve requirement from committed debt and equity, calculates operating cash flow available for debt service (CFADS), pays scheduled debt service, optionally sweeps excess cash to debt, and distributes the residual to equity.

It is a transparent planning schedule, not a financing commitment, lender model, audit, valuation, solvency opinion, investment recommendation, tax opinion, or forecast guarantee. It does not establish whether a project can raise the debt or equity entered by the user.

Inputs and units

All monetary inputs use one user-consistent currency scale, shown by the workbook as currency millions. The schedule contains exactly eight rows. Each row has a unique text label, a phase of Construction or Operations, revenue, operating cost, construction spend, change in net working capital, entered cash tax, mandatory principal due, and cash interest due.

The funding selector chooses pro rata debt and equity, debt first, or equity first. The debt share applies to the pro rata route. Debt facility and equity commitment are cumulative draw caps. Opening cash and the minimum reserve control liquidity. The tax selector either uses the entered row taxes or calculates a simplified tax from the entered tax rate.

Governing relationships

For period t, calculated cash tax applies only to an Operations row:

Calculated tax_t = max(0, (Revenue_t - Operating cost_t - Cash interest due_t) × tax rate)

The active tax is calculated tax or entered cash tax according to the selected route. CFADS is:

CFADS_t = Revenue_t - Operating cost_t - Change in NWC_t - Active cash tax_t

Construction funding need is construction spend on a Construction row. In the first row it also includes any reserve top-up:

Reserve top-up = max(0, Minimum reserve - Opening cash)

Debt and equity draws follow the selected priority, but cumulative debt cannot exceed the facility and cumulative equity cannot exceed the commitment. Pro rata debt is based on the entered debt share. Debt-first uses available debt before equity. Equity-first reserves available equity before determining the debt requirement. Any remaining need becomes a construction funding gap.

Cash after funded construction is carried into the operating waterfall. From cash above the minimum reserve, the workbook pays cash interest first, then mandatory principal up to the lesser of principal due and debt outstanding. In sweep mode, remaining cash repays additional debt before any equity distribution. In scheduled-service-only mode, that sweep is zero. Equity receives cash remaining above the reserve after those payments.

Ending debt is beginning debt plus the current draw less scheduled principal and sweep. Scheduled DSCR is calculated on Operations rows with positive scheduled debt service:

Scheduled DSCR_t = CFADS_t / (Cash interest due_t + min(Debt available_t, Mandatory principal due_t))

The optional sweep is excluded from this ratio. Total initial uses equal total construction spend plus the entered minimum reserve.

Calculation sequence

1. Validate the three selectors, bounded scalar assumptions, eight unique labels, phases, and numeric grid cells.
2. Select entered or calculated tax and calculate CFADS for each row.
3. Determine construction and reserve needs, apply the chosen funding priority, and record debt, equity, and any funding gap.
4. Carry available cash and debt forward one period at a time.
5. Pay interest, scheduled principal, and any selected debt sweep while retaining the minimum cash reserve.
6. Distribute the remaining cash to equity and calculate ending cash, ending debt, shortfalls, and scheduled DSCR.
7. Sum the period results, populate the debt-and-cash chart, apply the numeric gate, and evaluate status in the order below.

Outputs and interpretation

The sources-and-uses stage reports total initial uses. Construction funding reports cumulative debt draws, cumulative equity draws, and any unfunded amount. Operating cash reports total CFADS. The debt-service stage reports ending debt, minimum positive scheduled DSCR, and aggregate shortfall. The final stage reports equity distributions and ending cash.

The chart compares period-end debt and cash. Minimum scheduled DSCR is an arithmetic coverage measure based on the entered interest and mandatory principal schedule. It is not a lender covenant interpretation, and a zero value means no positive eligible DSCR observation was available.

Validation and status logic

The workbook evaluates status in this order:

The input-domain condition has first precedence, followed by the derived numeric gate, then the shortfall warning.

Assumptions and limitations

• All rows use the same currency scale and equally spaced annual periods.
• Debt availability is based on cumulative draws. Repayment does not reopen the facility.
• Calculated tax is a simplified positive-margin convention, not a jurisdictional tax computation.
• Scheduled principal and interest are entered obligations. The model does not derive a loan amortization schedule.
• There is one debt facility, one equity commitment, and one cash reserve. Multiple tranches, reserve letters of credit, VAT, withholding, distribution lockups, cures, circular interest, foreign exchange, and intra-period timing are excluded.
• A funding or debt-service shortfall remains visible. The model does not add sponsor support, capitalize unpaid debt service, or assume refinancing.
• All shipped values are synthetic illustrations.

Restrictions and non-computing states

The grid must contain exactly eight complete rows. Labels must contain between 1 and 60 characters after trimming and be unique without regard to case. Phase must be Construction or Operations, and an Operations row must have zero construction spend. Revenue, operating cost, construction spend, entered tax, principal due, and interest due are nonnegative and within the published limits. Change in NWC may be positive or negative within the workbook's bounded range.

The debt share and cash-tax rate are from 0 through 1. Debt facility, equity commitment, opening cash, and minimum reserve are nonnegative. A valid zero-activity schedule can compute, but may produce zero ratios. Entries outside the published limits or with an invalid shape are rejected before calculation; relational failures that the input rules cannot express are returned by the workbook as NOT VALID.

Errors and warnings

A rejected entry means the submitted values did not meet the published rules for types, options, limits, or fixed-grid shape. Workbook NOT VALID means either the active model domain failed or the derived cash waterfall exceeded the supported numeric/sign range. Workbook CHECK preserves the computed schedule but identifies a funding or debt-service shortfall. A connection or calculation-service failure is a service error and is not a project-finance conclusion. OK confirms the modeled arithmetic only.

The sources, repayment, covenant, and completion-risk context follows the OCC Comptroller's Handbook: Commercial Real Estate Lending. Infrastructure-finance context follows the FHWA P3 Toolkit primers. No lender term, project case, financing assumption, table, or external dataset is embedded.