Click main image to view in full screen. Unlock all 14 preview images:
Pharma rNPV Valuation, IRA, and Royalty Model – Excel XLSX
Excel (XLSX) + supplemental PDF
BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT
- Integrates clinical probability, patient-based revenue, payer deductions, IRA MFP exposure, rNPV valuation, and royalty monetization in one traceable model.
- Provides scenario analysis, sensitivities, deterministic Monte Carlo outputs, and three executive dashboards for decision-ready investment review.
- Improves transparency through centralized assumptions, consistent formula conventions, live Audit & QA checks, and a fully populated illustrative case.
HEALTHCARE EXCEL DESCRIPTION
๐ Pharma Risk-Adjusted NPV (rNPV) Financial Model
Integrated biopharmaceutical valuation, IRA negotiation, gross-to-net and royalty monetization model
The Pharma Risk-Adjusted NPV Financial Model is an integrated Excel valuation framework for analysing the commercial potential and probability-adjusted value of a pharmaceutical or biotechnology asset.
The model connects clinical development risk, patient population, market penetration, product pricing, gross-to-net deductions, Medicare negotiation exposure, operating costs and commercialization assumptions to a complete risk-adjusted cash-flow valuation.
It also includes advanced modules for IRA Medicare Drug Price Negotiation, Maximum Fair Price analysis, patent expiry, loss-of-exclusivity erosion, royalty monetization, scenario analysis, sensitivities and Monte Carlo valuation.
The workbook is designed for:
Pharmaceutical and biotechnology companies
Corporate development teams
Business development and licensing professionals
Investment banking and equity research teams
Venture capital and private equity investors
Royalty and healthcare investment funds
Financial modelling and valuation professionals
Pharmaceutical consultants and advisers
Portfolio strategy and commercial finance teams
๐ฏ What Is the Model Used For?
The model can be used to estimate the risk-adjusted value of a pharmaceutical asset throughout its clinical and commercial lifecycle.
It helps users:
Value preclinical, clinical-stage or commercial pharmaceutical assets
Estimate probability-adjusted product revenue
Model phase-by-phase clinical development risk
Calculate cumulative probability of technical and regulatory success
Forecast patient volumes, market penetration and product uptake
Build gross and net product revenue
Analyse rebates, discounts and other gross-to-net deductions
Evaluate Medicare negotiation eligibility and timing
Estimate the potential impact of Maximum Fair Price implementation
Model patent expiry and post-exclusivity revenue erosion
Forecast R&D, COGS, SG&A, taxes and free cash flow
Calculate NPV and risk-adjusted NPV
Evaluate royalty or milestone monetization structures
Calculate buyer IRR, MOIC and payback
Compare seller-retained value with pre-monetization value
Perform scenario, sensitivity and probabilistic analysis
Present the investment case through executive dashboards
The model can support licensing negotiations, acquisition analysis, portfolio prioritisation, strategic planning, financing, royalty monetization and investment-committee review.
๐งฌ 1. Asset and Indication Assumptions
The model begins with a structured set of product and indication assumptions.
Users can configure:
Asset or molecule name
Therapeutic indication
Development phase
Therapeutic area
Small-molecule or biologic classification
Target patient population
Diagnosed and eligible patients
Addressable market
Expected market penetration
Treatment duration
Dosing and unit assumptions
Wholesale acquisition cost
Launch year
Approval year
Patent expiry year
Exclusivity period
Commercial uptake assumptions
Pricing growth
Market-share progression
Loss-of-exclusivity assumptions
The module allows the commercial forecast to reflect the specific characteristics of the asset instead of relying on a generic top-down revenue growth rate.
๐งช 2. Phase-Transition Probability Engine
Clinical-stage pharmaceutical assets carry material technical and regulatory risk. The model therefore applies phase-specific probability assumptions before calculating risk-adjusted value.
The probability engine covers:
Phase 1 to Phase 2 transition
Phase 2 to Phase 3 transition
Phase 3 to regulatory filing
Filing to approval
Cumulative probability of success
Therapeutic-area-specific probability assumptions
Current development-stage selection
Scenario adjustments to probability of success
The model calculates a cumulative probability of success based on the selected development stage and relevant transition probabilities.
These probabilities flow directly into the risk-adjusted cash-flow calculation. They are not presented only as separate reference statistics.
This allows users to compare:
Unadjusted commercial value
Probability-adjusted commercial value
Development-stage risk
Changes in value following clinical advancement
Upside or downside from alternative probability assumptions
๐ 3. Patient-Based Revenue Forecast
The revenue build translates epidemiological and commercial assumptions into annual product sales.
The model considers:
Target patient population
Eligible patient percentage
Diagnosed or treated population
Market penetration
Product uptake curve
Units per patient
Treatment duration
Gross price per unit
Annual price growth
Commercial launch timing
Peak penetration
Peak sales timing
Competitive and lifecycle erosion
Patent-expiry timing
A structured uptake curve models the progression from launch to peak penetration.
The resulting patient volumes and pricing assumptions are used to calculate:
Treated patients
Units sold
Gross price per unit
Gross product revenue
Net price per unit
Net product revenue
Peak gross sales
Peak net sales
๐ฐ 4. Detailed Gross-to-Net Bridge
The model includes a comprehensive gross-to-net analysis that converts gross WAC revenue into net revenue.
Instead of using a single generic discount percentage, the bridge separates the major deductions affecting pharmaceutical revenue.
The model includes:
Commercial payer rebates
Medicaid rebates
Inflation-related rebate assumptions
340B programme discounts
GPO and wholesaler fees
Distribution fees
Medicare Part D manufacturer obligations
Co-pay assistance
Patient-support programme costs
Bad-debt provisions
Product returns and reserves
Other applicable gross-to-net deductions
Each component can be driven by:
Payer mix
Channel-specific assumptions
Applicable discount percentages
Annual trend assumptions
Product lifecycle
Competitive pressure
Negotiation exposure
The gross-to-net module calculates:
Gross WAC revenue
Individual deduction amounts
Total gross-to-net deductions
Net revenue
Net price per unit
Net-to-gross percentage
Annual net-to-gross trend
Supporting charts show how the deduction mix and net-to-gross ratio change throughout the forecast period.
๐๏ธ 5. IRA Medicare Negotiation and MFP Analysis
The model includes a dedicated module for analysing the potential financial impact of the Medicare Drug Price Negotiation Program.
The framework distinguishes between:
Small-molecule products
Biologic or large-molecule products
The selected molecule type drives a configurable negotiation eligibility timeline.
The module presents the sequence from:
Product approval
Years since approval
Potential eligibility
CMS selection
Negotiation period
Maximum Fair Price effective year
The model also includes configurable exclusion or delay assumptions for circumstances that may affect applicability.
The Maximum Fair Price analysis compares:
A configurable statutory ceiling benchmark
A negotiated discount from the applicable price
The resulting lower-price outcome
The counterfactual price without negotiation
The post-negotiation net-price trajectory
The MFP-adjusted price flows directly into:
Net revenue
Commercial cash flow
Risk-adjusted cash flow
rNPV
Sensitivity analysis
Executive dashboards
This allows users to assess the potential valuation impact of:
Earlier or later negotiation
Different MFP discounts
Alternative eligibility assumptions
Molecule classification
Medicare exposure
Changes in payer mix
All legal, regulatory and pricing assumptions remain editable because the applicable requirements and guidance must be independently verified for each asset and valuation date.
๐ 6. Patent Expiry and Loss-of-Exclusivity Erosion
The commercial forecast includes a patent-cliff and loss-of-exclusivity module.
The model identifies the expected patent or exclusivity expiry year and applies a configurable post-LOE erosion curve.
Different erosion profiles can be selected for:
Small-molecule products facing generic competition
Biologic products facing biosimilar competition
The module models:
Pre-expiry sales
Patent-cliff timing
Initial post-LOE revenue decline
Continuing price erosion
Continuing volume erosion
Remaining post-LOE revenue
End-of-forecast product value
This prevents the valuation from applying an inappropriate perpetual-growth assumption beyond the economically relevant product lifecycle.
๐ธ 7. Risk-Adjusted Cash-Flow Engine
The rNPV engine converts the commercial forecast into annual probability-adjusted free cash flow.
The calculation includes:
Gross revenue
Gross-to-net deductions
Net revenue
Cost of goods sold
Gross profit
Research and development expenditure
Clinical development costs
Regulatory and filing expenditure
Selling, general and administrative costs
Commercial launch expenditure
Operating profit
Taxes
Unlevered free cash flow
Cumulative probability of success
Probability-adjusted free cash flow
Discount factors
Present value of annual cash flows
The model separates commercial opportunity from clinical-development risk, giving users visibility over both the unadjusted project economics and risk-adjusted asset value.
๐ 8. rNPV Valuation Summary
The valuation summary consolidates the principal commercial and financial outputs.
Key outputs include:
Unadjusted NPV
Risk-adjusted NPV
Peak gross sales
Peak net sales
Cumulative probability of success
Approval and launch timing
Total development expenditure
Commercial cash-flow value
Patent-cliff impact
IRA negotiation impact
Gross-to-net impact
Discounted value by forecast year
Value contribution by major driver
A valuation bridge helps users understand how the model moves from gross commercial opportunity to risk-adjusted present value.
The bridge can illustrate the effects of:
Gross sales potential
Gross-to-net deductions
Operating costs
Development expenditure
Clinical probability adjustment
Medicare negotiation
Patent expiry
Discounting
๐ค 9. Royalty and Milestone Monetization
The model includes a dedicated synthetic royalty monetization module for evaluating a potential royalty financing or asset-backed transaction.
Users can configure:
Royalty rate
Royalty commencement year
Annual royalty cap
Aggregate transaction cap
Buyer purchase price
Target buyer return
Probability adjustment
Transaction timing
Seller-retained economics
The module calculates:
Uncapped royalty payments
Annual capped royalty payments
Aggregate capped payments
Buyer cash flows
Buyer IRR
Buyer MOIC
Buyer payback period
Aggregate cap-utilisation year
Seller upfront proceeds
Seller-retained cash flow
Retained rNPV after monetization
Effective cost of capital
Value allocation between buyer and seller
The workbook can evaluate the transaction from both sides:
Buyer perspective
Purchase price
Risk-adjusted royalty receipts
IRR
MOIC
Payback period
Cap utilisation
Seller perspective
Upfront monetization proceeds
Retained royalty or commercial value
Pre-transaction rNPV
Post-transaction retained rNPV
Effective financing cost
Value transferred to the royalty buyer
Supporting charts show the annual royalty stream, seller-versus-buyer value allocation and utilisation of the annual and aggregate caps.
๐ 10. Scenario and Sensitivity Analysis
The model includes configurable base, bull and bear scenarios.
Scenarios can adjust important valuation drivers such as:
Patient population
Market penetration
Product price
Gross-to-net deductions
Clinical probability of success
Launch timing
Development costs
Discount rate
MFP timing
MFP discount
Patent-expiry erosion
Operating cost assumptions
Sensitivity tables evaluate changes in rNPV against:
Discount rate
Probability of success
Peak market penetration
Gross-to-net percentage
MFP effective year
Negotiated price reduction
Patent-expiry erosion
Peak pricing assumptions
The module includes heatmaps and tornado analysis to identify which assumptions have the greatest effect on valuation.
๐ฒ 11. Monte Carlo Valuation Analysis
The workbook includes a native Excel-based probabilistic valuation module.
The Monte Carlo analysis evaluates rNPV outcomes under uncertainty in variables such as:
Probability of success
Patient population
Market penetration
Product price
Launch timing
Gross-to-net deductions
MFP impact
Development expenditure
The outputs can include:
Mean rNPV
Median rNPV
Minimum and maximum outcomes
Valuation percentiles
Downside probability
Probability of a positive valuation
Frequency distribution
Cumulative probability distribution
This gives users a broader view of valuation uncertainty than a single deterministic base case.
๐ 12. Three Executive Dashboards
The workbook includes three specialised executive dashboards.
Dashboard 1 – Valuation and Cash Flow
Risk-adjusted NPV
Peak net sales
Probability of success
Annual risk-adjusted cash flow
Sales trajectory
Development-stage probability
Valuation bridge
Dashboard 2 – Gross-to-Net and IRA Impact
Gross-to-net deduction mix
Net-to-gross trend
Gross versus net revenue
Medicare payer exposure
MFP price step-down
Negotiated versus non-negotiated price
IRA valuation impact
Dashboard 3 – Monetization and Returns
Buyer purchase price
Buyer IRR
Buyer MOIC
Payback period
Royalty cash-flow profile
Seller-retained value
Buyer-versus-seller value allocation
Transaction cap utilisation
The dashboards use a consistent institutional colour palette and chart format so the workbook presents as one cohesive financial product.
โ
13. Audit and Quality-Control Framework
The model contains a dedicated Audit and QA worksheet with automated controls.
The checks cover:
Visible formula errors
Calculation-range completeness
Formula consistency
Cross-sheet link integrity
Gross-to-net reconciliation
rNPV valuation reconciliation
Journal and cash-flow tie-outs where applicable
Scenario consistency
Chart-source completeness
Model-status reporting
The model's cover page and audit worksheet provide a visible overall model-status indicator.
These controls help reviewers identify potential issues but do not replace independent financial, accounting, commercial or regulatory verification.
Got a question about the product? Email us at support@flevy.com or ask the author directly by using the "Ask the Author a Question" form. If you cannot view the preview above this document description, go here to view the large preview instead.
Source: Best Practices in Healthcare, Integrated Financial Model Excel: Pharma rNPV Valuation, IRA, and Royalty Model Excel (XLSX) Spreadsheet, PDMM Financial Models
ABOUT THE AUTHOR
PDMM Financial Models develops professional, decision-ready Excel models for business owners, entrepreneurs, investors, consultants, analysts, and finance teams. Our objective is to transform complex business assumptions into clear, structured analysis that supports better planning and investment decisions.
Our models cover business planning, forecasting, budgeting, feasibility analysis,
... [read more]
Ask the Author a Question
You must be logged in to contact the author.
