Cash secured put calculator results are easy to produce and easy to misread. Type in a strike, an expiration and a premium, and any web widget will hand back a percentage that looks like income. The problem is the denominator. A cash secured put does not cost you the premium, it commits you to a purchase, and the cash behind that commitment is not idle while you wait. It earns the Treasury bill rate with no equity risk at all. A calculator that reports the premium yield without that benchmark is not wrong, it is incomplete in the exact way that makes a thin trade look attractive. This guide builds the calculator in Excel from live option data, and adds the column that settles the question. Two finished workbooks are attached at the end.
Cash Secured Put Quick Reference
The worked example uses a real Intel put captured on 2026-08-15, with the stock at 102.50 and the 2026-09-18 expiry 34 days out.
| Metric | Value | Where it comes from |
|---|---|---|
| Underlying | INTC at 102.50 | =QM_Last("INTC") |
| Put sold | 2026-09-18 95 strike, 34 days out | Sold at the bid, 4.40 |
| Collateral required | $9,500 | Strike times 100 |
| Premium received | $440 | Bid times 100 |
| Net cash committed | $9,060 | Collateral minus the premium credit |
| Static return if it expires worthless | 4.86% | Premium divided by net cash |
| Annualized, simple | 52.1% | Static return times 365 divided by 34 |
| 13 week Treasury bill | 3.70% | The same cash, no equity risk |
| Excess yield over cash | 48.4% | The part that pays for the risk |
| Breakeven and cost basis if assigned | 90.60 | Strike minus premium per share |
| Cushion below spot before a loss | 11.61% | Distance from 102.50 down to 90.60 |
| Chance of finishing in the money | 37.4% | N(-d2), the assignment estimate |
| Maximum loss if the stock goes to zero | $9,060 | The full net commitment |
Two numbers in that table do the real work. The 4.86% is the best case. The 90.60 is the case where you own 100 shares of Intel. Both are true at the same time, and a calculator that shows only the first one is describing half the trade.
The Denominator Problem
Start with what a cash secured put actually is. You sell a put, which obligates you to buy 100 shares at the strike if the buyer exercises. To secure that obligation with cash rather than margin, your broker reserves the full purchase price, which is the strike times 100. On this Intel trade that is $9,500.
The premium credit lands in your account immediately, so the cash you genuinely commit is $9,500 minus $440, or $9,060. That is the denominator for every honest return figure in the calculator.
Now look at the ways this gets computed badly.
- Return on premium. Some tools divide the premium by itself or by the margin requirement, which produces a number in the hundreds of percent. It describes nothing.
- Return on the stock price. Dividing $440 by the $10,250 that 100 shares cost at spot gives 4.29%. It is close, but it is measuring a position you do not have. You have not bought the stock.
- Return on collateral, gross. Dividing $440 by the full $9,500 gives 4.63%. This ignores that the premium reduces your outlay the day you sell.
- Return on net cash committed. $440 divided by $9,060 gives 4.86%. This is the figure the workbook reports, because it is the only one that matches the cash actually leaving your control.
The gap between 4.29% and 4.86% is not large on one trade. Repeated across a ladder of strikes it changes the ranking, and ranking is the entire point of a calculator.
Annualizing Is a Comparison, Not a Forecast
The static return of 4.86% covers 34 days. Multiply by 365 and divide by 34 and you get 52.1% annualized. That number is useful and it is also routinely abused.
Simple annualization assumes you can repeat this trade back to back for a year, roughly 10.7 times. Assignment breaks that assumption on the first bad month. When you are assigned, the collateral converts into stock and the put selling stops until you decide what to do with the shares. The 52.1% describes the rate of pay while the trade is working, not a yearly outcome you can bank.
Treat annualized yield as a ratio for comparing a 34 day put against a 62 day put against a bill. Do not treat it as a projection. The workbook labels the cell exactly that way, and the covered call calculator post makes the same point from the other side of the option chain.
The Column Almost Every Calculator Omits
Here is the part that changes decisions. The $9,060 backing this put is not doing nothing. In a brokerage sweep account or a short Treasury bill it earns the bill rate, which closed at 3.70% on 2026-08-14. That return carries no assignment risk, no single stock exposure and no timing decision.
So the compensation for selling the put is not 52.1%. It is 52.1% minus 3.70%, or 48.4 points of excess yield. That spread is the price the market is paying you to agree to buy 100 shares of Intel in a decline.
On this trade the spread is enormous, because Intel implied volatility is running above 62%. The market is quoting a wide distribution and paying accordingly. High excess yield is never free money. It is the market's estimate of how much the stock can move against you.
The framing matters most when the spread is thin. Below is the same structure across nine underlyings on the same 2026-09-18 expiry, each about 7% out of the money, sorted from worst to best.
| Ticker | Spot | Strike | Bid | IV | Collateral | Static | Annualized | Over cash | Cushion | P(assign) |
|---|---|---|---|---|---|---|---|---|---|---|
| JPM | 362.84 | 335.00 | 0.98 | 20.8% | $33,500 | 0.29% | 3.15% | -0.55% | 7.94% | 10.0% |
| KO | 87.71 | 82.50 | 0.34 | 19.2% | $8,250 | 0.41% | 4.44% | 0.74% | 6.33% | 14.2% |
| BAC | 64.49 | 60.00 | 0.28 | 21.7% | $6,000 | 0.47% | 5.03% | 1.33% | 7.40% | 13.4% |
| DIS | 106.85 | 100.00 | 0.58 | 24.4% | $10,000 | 0.58% | 6.26% | 2.56% | 6.95% | 18.5% |
| WMT | 115.27 | 105.00 | 0.64 | 28.3% | $10,500 | 0.61% | 6.58% | 2.88% | 9.46% | 14.1% |
| AAPL | 305.93 | 285.00 | 1.76 | 23.3% | $28,500 | 0.62% | 6.67% | 2.97% | 7.42% | 15.7% |
| XOM | 160.10 | 150.00 | 1.63 | 28.6% | $15,000 | 1.10% | 11.79% | 8.09% | 7.33% | 22.9% |
| AMD | 514.39 | 480.00 | 19.00 | 55.2% | $48,000 | 4.12% | 44.25% | 40.55% | 10.38% | 36.4% |
| INTC | 102.50 | 95.00 | 4.40 | 62.6% | $9,500 | 4.86% | 52.14% | 48.44% | 11.61% | 37.4% |
Look at the top row. The JPMorgan put pays an annualized 3.15% while the same $33,402 of committed cash earns 3.70% in bills. A standard calculator displays 3.15% next to the word "return" and the trade reads like income. Measured against the alternative it is negative carry of roughly half a point, and it hands you a 10% chance of being assigned $33,500 of a single bank stock in exchange.
That is not a judgment about JPMorgan. It is arithmetic about a specific contract on a specific afternoon. Two weeks later, after an earnings report or a volatility move, the same row can look completely different. The point is that you cannot see it at all unless the calculator carries the benchmark column.
The Strike Ladder
Once the return math is right, the useful view is the whole expiry at once. Here is every Intel strike in the 2026-09-18 chain, priced at the bid.
| Strike | Bid | IV | % OTM | Collateral | Premium | Static | Annualized | Over cash | Cushion | P(assign) | Delta |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 80.00 | 0.90 | 64.3% | 21.95% | $8,000 | $90 | 1.14% | 12.2% | 8.5% | 22.83% | 11.9% | -0.084 |
| 82.50 | 1.17 | 64.0% | 19.51% | $8,250 | $117 | 1.44% | 15.4% | 11.7% | 20.65% | 15.1% | -0.110 |
| 85.00 | 1.59 | 63.3% | 17.07% | $8,500 | $159 | 1.91% | 20.5% | 16.8% | 18.62% | 18.7% | -0.139 |
| 87.50 | 2.09 | 63.0% | 14.63% | $8,750 | $209 | 2.45% | 26.3% | 22.6% | 16.67% | 22.8% | -0.175 |
| 90.00 | 2.75 | 62.7% | 12.20% | $9,000 | $275 | 3.15% | 33.8% | 30.1% | 14.88% | 27.4% | -0.214 |
| 92.50 | 3.50 | 63.3% | 9.76% | $9,250 | $350 | 3.93% | 42.2% | 38.5% | 13.17% | 32.5% | -0.259 |
| 95.00 | 4.40 | 62.6% | 7.32% | $9,500 | $440 | 4.86% | 52.1% | 48.4% | 11.61% | 37.4% | -0.305 |
| 97.50 | 5.40 | 62.6% | 4.88% | $9,750 | $540 | 5.86% | 62.9% | 59.2% | 10.15% | 42.7% | -0.354 |
| 100.00 | 6.55 | 62.8% | 2.44% | $10,000 | $655 | 7.01% | 75.2% | 71.5% | 8.83% | 48.0% | -0.404 |
| 105.00 | 9.30 | 63.4% | -2.44% | $10,500 | $930 | 9.72% | 104.3% | 100.6% | 6.63% | 58.1% | -0.504 |
Read this table across, not down. Moving from the 80 strike to the 105 strike multiplies the annualized yield by more than eight, from 12.2% to 104.3%. Over the same span the cushion collapses from 22.83% to 6.63% and the assignment estimate rises from 11.9% to 58.1%. The 105 strike is already in the money, which is why its assignment odds exceed a coin flip.
Nothing in that progression is a free lunch, and no column tells you which row to pick. What the ladder does is make the trade explicit: every extra point of yield is bought with cushion. A calculator that shows yield without cushion is showing you the price without the product.
The delta column is worth a note. Traders often use the absolute value of delta as a quick assignment proxy, and at the 95 strike delta is -0.305 while the modeled probability of finishing in the money is 37.4%. Delta is a reasonable shortcut but it runs low, and the gap widens as you move out of the money. The workbook computes both so you can see the difference rather than assume it away. For the full treatment of these inputs, see the option Greeks calculator guide.
What Happens at Expiration
The payoff on a cash secured put is not symmetric and it is not smooth. Above the strike every outcome is identical. Below the strike each dollar costs you 100 dollars per contract. Here is the 95 strike across a price ladder.
| INTC at expiry | Move | Profit or loss | Return on cash | vs. bills | What happens |
|---|---|---|---|---|---|
| 56.38 | -45.0% | -$3,422 | -37.77% | -$3,453 | Assigned, own 100 shares |
| 66.62 | -35.0% | -$2,398 | -26.47% | -$2,429 | Assigned, own 100 shares |
| 76.88 | -25.0% | -$1,372 | -15.14% | -$1,403 | Assigned, own 100 shares |
| 82.00 | -20.0% | -$860 | -9.49% | -$891 | Assigned, own 100 shares |
| 87.12 | -15.0% | -$348 | -3.84% | -$379 | Assigned, own 100 shares |
| 90.60 | -11.6% | $0 | 0.00% | -$31 | Breakeven, assigned at 95.00 |
| 92.25 | -10.0% | $165 | 1.82% | $134 | Assigned, own 100 shares |
| 95.00 | -7.3% | $440 | 4.86% | $409 | Expires worthless, keep premium |
| 102.50 | 0.0% | $440 | 4.86% | $409 | Expires worthless, keep premium |
| 112.75 | 10.0% | $440 | 4.86% | $409 | Expires worthless, keep premium |
| 117.87 | 15.0% | $440 | 4.86% | $409 | Expires worthless, keep premium |
Three details deserve attention.
First, the flat top. Intel can rise 15% or 50% and the trade still pays exactly $440. You sold the upside when you sold the put, which is the structural cost of collecting premium.
Second, the assignment band. Between 90.60 and 95.00 the position is profitable and you still end up owning the shares. Assignment is not the same event as a loss, and conflating the two leads people to close winning positions early out of nerves.
Third, the vs. bills column. At breakeven the trade nets zero, but the same cash in Treasury bills would have earned $31 over those 34 days. The position can finish positive and still lose to cash. Any outcome below roughly 90.91 leaves you worse off than having done nothing at all.
If You Are Assigned
Assignment is the outcome the calculator exists to prepare you for, so plan it before you sell.
| What you inherit | Value |
|---|---|
| Shares acquired | 100 |
| Purchase price, the strike | 95.00 |
| Premium already received per share | 4.40 |
| Net cost basis per share | 90.60 |
| Total capital in the shares | $9,060 |
| Discount to the 102.50 you started at | 11.61% |
Your basis is 90.60, not 95.00, because the premium is yours regardless. That is a genuine discount to where the stock traded when you opened the position. It is not a guarantee of anything, since the stock reached that level for a reason, but it does mean assignment at your strike is a better entry than buying at spot on the day you sold.
From there you have three choices, and the workbook lays them out on its own sheet: hold the shares, sell a covered call against them to collect a second premium, or close the position and stop the exposure. Selling the call is the step that turns this into the wheel, and the older walkthrough on finding the right cash secured put to sell covers the screening side of the same decision.
Two risks sit outside the model. American style puts can be assigned early, at any time, and the odds rise when the put moves deep in the money or when a dividend is about to be paid. Intel currently pays no dividend, which removes one trigger, but it does not remove the possibility. Separately, the next Intel earnings report lands on 2026-10-22, which is after this expiry. Had it landed inside the holding period, the volatility the model assumed would not describe the distribution you were actually facing. Check the date every time.
Position Sizing From Collateral
The most common sizing mistake with cash secured puts is anchoring on the premium. The premium here is $440 and the obligation behind it is $9,500. Those are not the same order of magnitude.
Size from the collateral instead. On a $250,000 portfolio, capping any one underlying at a share of total capital produces this:
| Cap on one underlying | Collateral budget | Contracts | Premium | Shares if assigned | Portfolio at risk |
|---|---|---|---|---|---|
| 5% | $12,500 | 1 | $440 | 100 | 3.8% |
| 10% | $25,000 | 2 | $880 | 200 | 7.6% |
| 15% | $37,500 | 3 | $1,320 | 300 | 11.4% |
| 20% | $50,000 | 5 | $2,200 | 500 | 19.0% |
| 25% | $62,500 | 6 | $2,640 | 600 | 22.8% |
| 30% | $75,000 | 7 | $3,080 | 700 | 26.6% |
Restate the bottom rows in share terms. At a 30% cap you are agreeing to buy 700 shares of one company, about $66,500 at the strike. If that purchase would be too concentrated to make outright today, then the puts are too large as well. The premium column is small enough to feel harmless, and that is precisely why it is the wrong anchor.
Building It With MarketXLS Formulas
Every data point above comes from a formula, so the workbook updates instead of going stale. The chain of calls is short.
Start with the contract symbol, because every contract level function needs it:
=OptionSymbol("INTC", "2026-09-18", "Put", 95)
That returns the QuoteMedia contract symbol. Feed it into the quote and liquidity functions:
=QM_Bid(<option symbol>) Bid, the price you actually sell at
=QM_Ask(<option symbol>) Ask, so you can see the spread you cross
=QM_OpenInterest(<option symbol>) Open interest, a liquidity screen
=OPT_DaysToExpiration(<option symbol>) Calendar days, drives the annualization
Underlying context comes from the ticker directly:
=QM_Last("INTC") Spot price
=ImpliedVolatility30d("INTC") 30 day implied volatility, as a decimal
=ImpliedVolatilityRank1y("INTC") Where that volatility sits in its own year
=StockVolatilityThirtyDays("INTC") Realized volatility, to compare against implied
=earnings_date("INTC") Next earnings date, check it against your expiry
=Beta("INTC") Beta versus the market
=FiftyTwoWeekLow("INTC") Context for the basis you would inherit
=AverageDailyVolume("INTC") Liquidity in the shares you might be assigned
The Greeks take eight arguments in a specific order, and the dividend yield sits between the strike and sigma. Getting that order wrong is the most common error in option spreadsheets:
=opt_Delta(spot, marketOptionPrice, expiryDate, "Put", strike, riskFreeRate, dividendYield, sigma)
=opt_Theta(spot, marketOptionPrice, expiryDate, "Put", strike, riskFreeRate, dividendYield, sigma)
=opt_Vega(spot, marketOptionPrice, expiryDate, "Put", strike, riskFreeRate, dividendYield, sigma)
To build the ladder rather than one row, pull the strikes and expirations directly:
=Strikes("INTC", "2026-09-18")
=ExpirationNext("INTC", 4)
One deliberate choice: the risk free rate is a yellow input cell, not a formula. MarketXLS offers TreasuryRate10y(), but a cash secured put commits cash for weeks, not ten years. The correct benchmark is the short bill, so the workbook asks you to type it and update it when it moves. The full function library is documented at marketxls.com/functions.
What Is in the Template
The workbook has eight sheets, and each one carries a MarketXLS Functions Used box at the bottom listing the exact formulas that power it.
- How To Use. What a cash secured put is, what each sheet answers, and why the bill rate belongs in the calculation.
- Calculator. The main dashboard. Yellow inputs for symbol, expiry, strike, contracts, rate and dividend yield. Outputs grouped into the cash you commit, the return on that cash, what happens if the stock falls, and assignment risk including delta, theta, vega and the N(-d2) probability.
- Strike Ladder. Every strike in the expiry with yield, excess over cash, cushion and assignment odds side by side.
- Scenario Analysis. Profit and loss across a price ladder at expiry, with the vs. bills column and a share count for each outcome.
- Assignment Plan. The position you inherit, your basis against the 52 week range, and the covered call step if you choose it.
- Position Sizing. Contracts, collateral and share obligation from your portfolio value and a concentration cap.
- Yield Vs Cash Screen. The nine ticker cross section, sorted by excess yield.
- Formula Reference. Every MarketXLS function used, with exact argument order.
The sample workbook carries the 2026-08-15 quotes frozen so the arithmetic is reproducible, and each data cell has a comment showing the live formula behind it. The template workbook has those formulas already wired to the input cells.
Download the templates:
- - Pre-filled with the 2026-08-15 data
- - Live-updating formulas
Frequently Asked Questions
How do you calculate the return on a cash secured put?
Divide the premium received by the cash you actually commit, which is the strike times 100 minus the premium. On the Intel example that is $440 divided by $9,060, or 4.86% over 34 days. Multiply by 365 and divide by the days to expiration for the annualized figure, and treat that annualized number as a comparison ratio rather than a forecast.
What is the breakeven on a cash secured put?
The strike minus the premium per share. Selling the 95 strike for 4.40 gives a breakeven of 90.60. Below that price the position loses money, and 90.60 is also your cost basis per share if you are assigned. The two are the same number viewed from different sides.
How likely am I to be assigned?
The workbook reports N(-d2), the risk neutral probability of finishing in the money, which is 37.4% for the 95 strike. Absolute delta is a common shortcut and reads 0.305 here, slightly lower. Both estimate the chance of being in the money at expiry. Neither estimates the chance of trading below the strike at some point during the holding period, which is meaningfully higher.
Is a cash secured put safer than buying the stock?
It has a lower maximum loss than buying 100 shares at spot, because your basis is 90.60 rather than 102.50, and it profits if the stock is flat. It also caps your gain at the premium and still exposes you to the full decline below your breakeven. It is a different risk profile, not a smaller one, and this article is educational rather than a recommendation.
Why compare the premium to Treasury bills?
Because the collateral would earn the bill rate anyway with no equity risk. The excess yield over cash is the part of the premium that actually pays you for accepting assignment risk. When that spread is near zero or negative, as it was on the JPMorgan row above, the structure is taking single stock downside for no measurable premium over doing nothing.
Can I sell cash secured puts on any stock?
You need a listed option chain, an account approved for the strategy, and enough cash to buy 100 shares per contract at the strike. Liquidity matters as much as approval. The open interest and bid to ask spread columns in the workbook exist so you can screen out contracts where the exit costs more than the edge.
The Bottom Line
A cash secured put calculator has one job: tell you what you are being paid and what you are agreeing to buy. Most tools do the first half. They report a premium yield, often on the wrong denominator, and stop there.
The version built here computes the return on the cash you genuinely commit, states the breakeven and the basis you would inherit, estimates the assignment odds from the option's own implied volatility, and then measures all of it against what the same collateral earns in Treasury bills. That last step is what turns a percentage into a decision. It is also what showed that one of the nine contracts in the screen was paying less than cash for the privilege of taking equity risk.
Build it once, wire it to live data, and the ranking updates itself every time the chain moves. Explore the full function library at marketxls.com/functions, see how the same machinery handles the other side of the chain in the options calculator dashboard, or book a demo to see MarketXLS running in your own workbook.
This article is educational and is not investment advice. Options involve risk, including the loss of the entire amount committed, and are not suitable for every investor. All quotes are as of 2026-08-15 and will have changed.
