Free Excel Templates

rNPV Drug Asset Valuation Model (Excel)

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.

Get the Excel model

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.

The handbook behind this model

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.

Paperback PDF edition, $19.99 Book page and full workbook

What is inside the workbook

1. README
What the model does, the colour convention (blue inputs, black formulas), the rule that a delay moves five linked dates together, and a reminder that the prefilled rNPV must read $71.09 million before you change anything.
2. rNPV_Model
The rNPV, likelihood of approval, check cell and peak sales at the top; then every input in one block (conventions, phases and probabilities, patient funnel and net price, launch curve, IRA price year, Medicare share and discount, loss of exclusivity and erosion, cost structure, ex-US licence terms and royalty tiers); then the 2027 to 2050 engine row by row: discount factors, risked development costs, US sales, COGS, selling and medical costs, tax, ex-US royalties and milestones, risked cash flow and present value.

What the prefilled example returns

MeasureValue
Peak US net sales (320,000 x 50% x 10% x 75% x $54,000)$648.00 million
Cumulative likelihood of approval19.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 cell0

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.

Questions practitioners ask

How does the model apply probabilities?

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.

Why does the model use the mid-year convention?

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.

Can I use it for an asset I keep worldwide?

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.

Are any cells locked or macros required?

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.