Projects | Shyam Talati

Financial Modeling

Financial Modeling Projects

Models built in Excel from scratch as part of financial coursework at Drexel University and independent study. Download any file to review the full model.

Excel Pro Forma DCF Cap Rate

Real Estate Investment Analysis: Multi-Scenario Pro Forma

Multi-scenario investment analysis across three integrated worksheets: a full operating pro forma, NOI capitalization, and DCF valuation. Built from scratch with tenant-by-tenant rent analysis, add-on factor adjustments, and multi-floor building configurations.

Key Finding Market cap rate scenario (5%) vs. alternative (3.7%) produced materially different property valuations, demonstrating how cap rate compression drives value in real estate cycles.

Download

Model Structure

Pro Forma: PGI → EGI → NOI with tenant-by-tenant rent schedules and add-on percentages
NOI Cap: Direct capitalization at two market cap rate scenarios (5% and 3.7%)
DCF: Multi-year discounted cash flow with terminal value and IRR output

Key Techniques

Multi-floor building configuration with per-SF rent analysis
Operating expense breakdown (insurance, utilities, maintenance, management)
Sensitivity to cap rate assumptions for valuation range
PGI to NOI waterfall: vacancy allowance, operating expenses, reserves

Excel After-Tax Depreciation Escalation

After-Tax Hold Period Analysis with Escalation

Comprehensive 5-year after-tax analysis incorporating income and expense escalation rates, depreciation schedules, debt service, and a full sale scenario. Quantifies the impact of ordinary income tax, capital gains tax, and depreciation recapture on investor returns.

Key Finding Depreciation recapture at sale materially reduces after-tax equity reversion, a critical consideration often missed in before-tax-only analysis.

Download

Model Structure

Assumptions sheet: property inputs, LTV, interest rate, tax rates
Amortization schedule: monthly principal/interest breakdown
Calculations: 5-year BTCF and ATCF with annual escalation on PGI, OE, and CAPEX

Tax Analysis Components

Annual depreciation (building value ÷ 39-year MACRS life)
Taxable income: NOI less interest and depreciation deductions
After-tax cash flow: BTCF less tax liability at ordinary income rate
Sale: capital gains tax, depreciation recapture tax, after-tax equity reversion

Excel DCF Direct Cap Pro Forma

Multi-Tenant Property Valuation: Three-Approach Model

Valuation model using three parallel approaches: discounted cash flow, direct capitalization, and a detailed operating pro forma. Includes retail percentage rent triggers, TI and leasing commissions, CAPEX reserves, and parking income, all reconciled to a single value estimate.

Key Finding The DCF and direct cap approaches produced a valuation spread driven by terminal cap rate sensitivity, a direct illustration of why hold-period assumptions matter in income property analysis.

Download

Model Structure

Pro Forma: base rent, escalations, vacancy, operating expenses, parking income
Direct Capitalization: stabilized NOI ÷ market cap rate → value
DCF Analysis: 5-year cash flows + terminal value discounted to PV

Advanced Components

Percentage rent: revenue above natural breakpoint for retail tenants
Tenant Improvements (TI) and leasing commissions as capital costs
CAPEX reserve included in operating expense waterfall
Base rent escalation by tenant over hold period

Excel FCFF DCF Sensitivity

Apple Inc. Intrinsic Valuation: FCFF DCF Model

Full Free Cash Flow to Firm (FCFF) DCF model built from scratch. Covers a 5-year revenue and EBIT projection, NOPAT, D&A and CapEx drivers, working capital adjustments, WACC construction, Gordon Growth terminal value, and an equity bridge to implied share price.

Key Finding Terminal value accounts for the majority of intrinsic value. The sensitivity table shows how a 100 bps shift in WACC or terminal growth rate produces a material spread in implied price, illustrating why DCF assumptions require careful justification.

Download

Model Structure (6 Sheets)

Inputs: base-year financials, forecast-year operating drivers, WACC components, and balance-sheet bridge items
Forecast: 5-year projection covering Revenue, EBIT, NOPAT, D&A, CapEx, NWC changes, and FCFF
DCF: PV of forecast FCFFs + Gordon Growth terminal value → Enterprise Value → Equity Value → implied share price
Valuation Summary: one-page output with key assumptions and upside/downside vs. current market price
Sensitivity: 7x6 table with WACC (9%–12%) vs. terminal growth rate (1.5%–4%), each cell showing implied share price

Key Techniques

FCFF = NOPAT + D&A − CapEx − ΔNWC, with NWC modeled as a % of revenue
WACC constructed from CAPM cost of equity (risk-free rate + beta × ERP) and after-tax cost of debt
Terminal value via Gordon Growth Model; TV discounted back 5 years to present
Sensitivity table highlights that PV of terminal value drives the majority of total enterprise value

More Work

Additional Models

Loan Analysis EBC

Effective Borrowing Cost Analysis

Loan model quantifying the gap between contract interest rate and true borrowing cost after up-front fees: discount points, appraisal, and title insurance. Includes full amortization schedule and lender's yield calculation.

Download

Construction Finance Development

Construction Financing & Development Pro Forma

Pre-development financial model covering land acquisition, hard/soft costs per GSF, LTC ratio, equity percentage, and a monthly cash flow distribution schedule across the construction period.

Download

DCF Amortization

DCF & Loan Amortization Model

Foundational two-part model combining a 10-year DCF (NPV, IRR, terminal value, growth rate) with a full loan amortization schedule covering LTV, monthly payment breakdown, and principal/interest split over time.

Download

Have a project or opportunity to discuss?

Currently interning at Amtrak, open to FP&A and corporate finance opportunities after graduation in March 2027.

Get in Touch