Preferred Stock Yield Calculator Excel Template
Preferred Stock Yield Calculator Excel Template
Interactive Excel Financial Model
Download, customize, and integrate into your own analysis
What's Included
- Interactive financial model with live Excel formulas
- All formulas visible and fully editable
- Professional formatting with color-coded inputs & outputs
- Formula reference sheet with variable definitions
- Step-by-step instructions sheet
- Compatible with Microsoft Excel 2016 and later
Download Template
Price range: $0.00 through $20.00
Calculate current yield, yield to call, yield to worst, and tax-equivalent yield for preferred stocks with this professional Excel template.
Online Calculator vs Excel Template
| Feature | Online | Excel |
|---|---|---|
| Instant use — no download | ✓ | — |
| Works offline | — | ✓ |
| Customize formulas & layout | — | ✓ |
| Save & share with colleagues | — | ✓ |
| Integrate into your own models | — | ✓ |
| Print-ready formatting | — | ✓ |
About This Template
Calculate preferred stock yields instantly with this professional Excel template. Designed for income investors analyzing dividend-paying preferred securities, this calculator computes current yield, yield to call, yield to worst, and tax-equivalent yield—all with live formulas you can customize.
What You Can Calculate
This template delivers seven key metrics for preferred stock analysis:
- Current Yield — Annual dividend return at today's market price
- Coupon Rate — Fixed dividend rate based on par value
- Yield to Call (YTC) — Total return if the issuer redeems at the call date
- Yield to Worst (YTW) — Conservative estimate using the lower of current yield or YTC
- Premium/Discount — How far the price is above or below par value
- Call Risk Assessment — High, Medium, or Low based on price vs. call price
- Tax-Equivalent Yield — Compares qualified dividends to taxable income
Required Inputs
Enter these values to analyze any preferred stock:
- Current Price — Market price per share
- Par Value — Liquidation value (typically $25 or $100)
- Annual Dividend — Total yearly dividend payment
- Call Details — Whether callable, call price, and years to call
- Tax Rate — Your ordinary income rate for tax-equivalent comparison
Methodology
Current Yield is calculated as Annual Dividend ÷ Current Price. Yield to Call uses the IRR of cash flows from purchase to redemption (equivalent to Excel's RATE function). Yield to Worst takes the minimum of these two yields for conservative analysis. The tax-equivalent yield formula adjusts for the 20% qualified dividend rate versus ordinary income.
Assumptions
- Perpetual preferred stock with no maturity date
- Fixed annual dividend payments
- YTC calculated using whole-year cash flows
- Qualified dividend tax rate of 20% (federal)
Frequently Asked Questions
Yes. Copy the Calculator sheet to create additional tabs, or add columns to compare several preferreds in one view. All formulas remain live, so you can quickly evaluate which security offers the best risk-adjusted yield.
Change the 'Years to Call' input to test different call scenarios. For example, compare yields if called in 3 years versus 7 years to understand how call timing affects your total return.
The template uses federal rates only, but you can modify the tax-equivalent yield formula to include state taxes. Unprotect the sheet, then adjust the formula to factor in your combined marginal rate.
A fully interactive financial model with live Excel formulas, an Instructions sheet with usage guide, and a Formula Reference sheet with variable definitions and model assumptions. All formulas are visible and editable.
Microsoft Excel 2016 or later. The template uses standard Excel formulas only — no macros, VBA, or add-ins required.
Yes. All cells are fully editable. You can modify any formula, add your own calculations, change formatting, or integrate the model into your existing spreadsheets.
The online calculator runs in your browser for quick calculations. This Excel template gives you a portable, offline financial model you can customize, save, share with colleagues, and integrate into your own analysis.
This template is provided for educational and personal use. You may use it in your own professional analysis and presentations. Redistribution or resale of the template itself is not permitted.
You can re-download the latest version from your account or by requesting a new download link. Free downloads are limited to 5 per email address per month.
