Convertible Note Conversion in Excel: Interest, Cap, Discount, and the Round Price

A convertible note is a loan that turns into equity at the next priced round. The conversion math has three parts. The note converts on principal plus accrued interest. It converts at the lower of the cap price and the discount price. And the round price it is measured against often depends on the note shares themselves. Most spreadsheets get the first two right and fudge the third. This guide builds all three in Excel and shows how much each one moves the share count on a real-sized example.

The example

We ignore a new option pool top-up here so the note math stays visible. The pool adds one more step, covered in our option pool shuffle guide.

Step 1: accrued interest

Most notes accrue simple interest, and the note converts on the full balance. Read your note, because a few compound.

Put the inputs in named cells: Principal, Rate, IssueDate, CloseDate.

` Years = YEARFRAC(IssueDate, CloseDate, 3) Accrued = Principal (1 + Rate Years) `

Basis 3 is actual/365, which is what most notes specify. Check the day count clause, because some notes use a 360-day year.

The day count moves the interest more than people expect. A note signed January 15, 2025 and converted July 15, 2026 has run 546 days, not exactly 1.5 years. On actual/365 the interest is $44,876.71. On actual/360 it is $45,500.00. That is a $623 spread on the same note.

This guide uses a clean 1.5 years so the numbers stay round. That gives $500,000 plus $45,000 of interest, so $545,000 converts.

If the note compounds annually, use Principal * (1 + Rate) ^ Years, which gives $545,668.40. Monthly compounding gives $546,964.47. The gap is small at 6% over 18 months, but it grows with the rate and the term.

Interest matters more than founders expect. At the conversion price below, the $45,000 of interest buys 56,250 shares. That is 0.4% of the company that shows up in nobody's pitch deck.

Step 2: cap price and discount price

The note converts at whichever price gives the holder more shares, which is the lower price.

` CapPrice = Cap / PreShares DiscountPrice = RoundPrice * (1 - Discount) ConvPrice = MIN(CapPrice, DiscountPrice) NoteShares = Accrued / ConvPrice `

With 10,000,000 pre-round shares the cap price is $8,000,000 / 10,000,000 = $0.80.

Check what your note defines as the cap denominator. Some notes use fully diluted shares including the pool, some exclude unissued pool shares, and a few include other converting instruments. That one definition can move the cap price by several percent, so match the document instead of assuming.

Step 3: the round price, and why it is circular

The quick way to set the round price is pre-money divided by pre-round shares:

` RoundPrice = PreMoney / PreShares `

That gives $20,000,000 / 10,000,000 = $2.00. The discount price is $1.60, so the cap wins at $0.80 and the note gets 681,250 shares.

The problem is that most Series A term sheets say the pre-money valuation includes the shares issued on conversion of notes and SAFEs. If the note shares are inside the pre-money, the round price has to be:

` RoundPrice = PreMoney / (PreShares + NoteShares) `

But NoteShares depends on ConvPrice, which can depend on RoundPrice. That loop is why spreadsheets throw a circular reference warning or quietly skip the step.

You do not need iterative calculation. Solve each case in closed form, then check which one is consistent.

If the cap wins, the note shares do not depend on the round price:

` NoteShares_cap = Accrued / CapPrice RoundPrice_cap = PreMoney / (PreShares + NoteShares_cap) `

If the discount wins, substitute and solve. RoundPrice (PreShares + Accrued / ((1 - Discount) RoundPrice)) = PreMoney simplifies to:

` RoundPrice_disc = (PreMoney - Accrued / (1 - Discount)) / PreShares `

Pick the one that holds. The cap case is valid when its discount price is no lower than the cap price:

` RoundPrice = IF((1 - Discount) RoundPrice_cap >= CapPrice, RoundPrice_cap, RoundPrice_disc) ConvPrice = MIN(CapPrice, (1 - Discount) RoundPrice) NoteShares = Accrued / ConvPrice `

Add a check cell that should equal the pre-money exactly:

` = RoundPrice * (PreShares + NoteShares) `

Case A: the cap wins

At a $20M pre-money:

| | Quick method | Pre-money includes the note | |---|---|---| | Round price | $2.0000 | $1.8724 | | Conversion price | $0.8000 (cap) | $0.8000 (cap) | | Note shares | 681,250 | 681,250 | | New investor shares for $5M | 2,500,000 | 2,670,312 | | Existing holders after the round | 75.87% | 74.90% |

When the cap wins, the note holder gets the same shares either way. What changes is who pays for them. Under the quick method the note shares sit on top of the $20M, so the investor is really paying a $21.36M pre-money. Under the term sheet method the note shares come out of the $20M, the price drops to $1.8724, and the existing holders give up about one extra point of ownership.

That point is real money at exit. It is also the most common surprise in a first priced round.

Case B: the discount wins

Now say the round is weaker: $2,000,000 at a $9,000,000 pre-money.

| | Quick method | Pre-money includes the note | |---|---|---| | Round price | $0.9000 | $0.8319 | | Conversion price | $0.7200 (discount) | $0.6655 (discount) | | Note shares | 756,944 | 818,933 | | Note holder after the round | 5.83% | 6.19% | | Existing holders after the round | 77.05% | 75.63% |

Here the formula choice changes the note holder's shares too. The discount price follows the round price down, so the note gets 61,989 more shares than the quick method shows. The check cell confirms it: $0.831875 times 10,818,933 shares is exactly $9,000,000.

Four mistakes to avoid

  1. Converting the face amount. Leaving out accrued interest undercounts the note. In Case A that is 56,250 missing shares.
  2. Using MAX instead of MIN. The holder gets the lower price, which means more shares. A flipped function silently hands the founders a better deal than the note allows.
  3. Turning on iterative calculation to fix the circular reference. It works until someone opens the file with iteration off, and then the cells break. The closed form needs no iteration.
  4. Forgetting maturity. If the note matures before a round, it may convert at the cap, get repaid, or get extended, depending on its terms. Model the maturity date next to the close date so you can see which comes first.

Setting it up in a sheet

Put one note per row with columns for principal, rate, issue date, cap, and discount. Put the round inputs in a separate block. Build Accrued, CapPrice, and NoteShares_cap per row, then sum the note shares for the round price. With several notes and a discount case, the per-row closed form above no longer applies exactly, because each note's discount shares feed the same round price. The usual approach is to solve assuming every cap binds, then flag any note whose discount price beats its cap so you can check it by hand.

If you would rather start from a working file, the Numbersmith cap table workbook already has note and SAFE conversion, the option pool top-up, a post-round cap table, three raise scenarios, and an exit waterfall built with open formulas: Cap Table and Dilution Calculator.

This guide was written with AI assistance and its figures were re-checked by formula.

For education and planning only, not financial, legal, or tax advice. Have counsel review your actual note and term sheet.