An unlocked Excel model that values one drug program by risk-adjusted NPV, from phase probabilities and a patient funnel to exclusivity, the IRA and licence income.
Enter the remaining phases with their cost, timing and probability of success, the patient funnel and net price, a launch curve, the IRA and loss of exclusivity dates and any licence terms. In the prefilled case (VLT-201, a fictional oral small molecule in Phase 2b for ulcerative colitis, USD millions), peak US net sales of $648.00 million and a 19.53 percent likelihood of approval give an rNPV of $71.09 million, against an unrisked NPV of $718.85 million.
One unlocked Excel workbook, zero macros, zero circular references. Free: enter your email address and the download opens at once, along with the other free finance models.
rNPV_Drug_Asset_Model.xlsx (14 KB)
You join the reader list: one email when a new free model or book comes out. Unsubscribe in one click. Already subscribed? Enter the same address. How your address is used.
This template is a standalone extract of the 11-tab workbook in The Biotech Valuation Handbook, which values a whole clinical-stage company: three assets, a licensing term sheet from both sides, a preclinical decision tree, the sum-of-the-parts, eight sensitivities and the cash runway.
| Measure | Value |
|---|---|
| Peak US net sales (320,000 x 50% x 10% x 75% x $54,000) | $648.00 million |
| Cumulative likelihood of approval | 19.53 percent |
| PV of risked development costs | -$137.04 million |
| PV of risked US commercial cash flows | $175.22 million |
| PV of risked licence income | $32.92 million |
| rNPV at January 1, 2027 | $71.09 million |
| Unrisked NPV at 11 percent | $718.85 million |
| Check cell | 0 |
The probabilities are case-specific: 35 percent from Phase 2 to 3, 62 percent from Phase 3 to filing and 90 percent at approval, against published non-oncology benchmarks of 31.2, 61.3 and 90.2 percent. The negotiated Medicare price applies from 2041 (approval in 2031 plus the selection rules for a small molecule), generics arrive in 2044, and ex-US royalties come from a licensee under three tiers of 10, 13 and 16 percent. For the full method read how to calculate rNPV; for the probabilities, probability of success by clinical phase; for deal terms, how to value a biotech licensing deal.
Benchmarks from BIO, Informa Pharma Intelligence and QLS Advisors, Clinical Development Success Rates 2011-2020. US exclusivity rules and the Medicare Drug Price Negotiation Program described as read in November 2026. The company and figures are fictional. Education only, not investment advice.
Each development cost is weighted by the probability of reaching the phase in which it is spent, and every post-approval cash flow by the cumulative likelihood of approval, the product of the phase probabilities. In the prefilled case the Phase 3 costs carry 35 percent, filing and pre-launch spend 21.7 percent and commercial cash flows 19.53 percent. The discount rate (11 percent) reflects market risk only.
Sales and costs arrive through the year, not on December 31. A cash flow of year Y is discounted (Y minus the first year plus 0.5) years, so the 2032 factor is 0.5633 at 11 percent.
Yes. Set the ex-US peak sales, the milestones and the royalty rates to zero and the licence block drops out. For a licence-in, enter the payments you owe as negative milestones only after checking the sign convention on the model sheet.
No. Blue cells are inputs, black cells are live formulas with no circular references, and the check cell must read 0. Open the file in Excel and let it calculate before reading the rNPV.