Dividend Distribution Planner
Plans dividend distributions based on available profit, retention targets, and share structure. Calculates dividend per share, payout ratio, and yield to help balance shareholder returns with retained earnings needs.
Accounting - Dividend Distribution Planner.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Determining how much profit to distribute versus retain
- Calculating the per-share dividend amount for each shareholder
- Ensuring the payout ratio stays within sustainable limits
- Modeling the impact of different distribution amounts on retained earnings
- Communicating dividend decisions with clear financial backing
Who This Is For
- CFOs planning annual or quarterly dividend declarations
- Board members reviewing proposed distribution amounts
- Small business owners distributing profits to partners or shareholders
- Accountants preparing dividend documentation
Inputs
- $Net profit available for distribution
- %Target retention ratio
- #Total shares outstanding
- $Current share price
- textDistribution frequency
Outputs
- Total dividend pool
- Dividend per share
- Payout ratio
- Dividend yield
- Retained earnings after distribution
How Calculations Work
The dividend pool is net profit multiplied by one minus the retention ratio. Dividend per share is the pool divided by total shares outstanding. The payout ratio is the dividend pool divided by net profit, expressed as a percentage. Dividend yield is the annualized dividend per share divided by the current share price. Retained earnings equal net profit minus the dividend pool.
Example Use Case
Scenario: A company has $500,000 in net profit, targets a 40% retention ratio, has 100,000 shares outstanding, and the current share price is $25. Distribution is annual.
Result: Dividend pool is $300,000. Dividend per share is $3.00. Payout ratio is 60%. Dividend yield is 12%. Retained earnings are $200,000.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
What is a sustainable payout ratio?
It depends on the industry and growth stage. Mature companies often pay 40-60%. High-growth companies typically retain more. The CONFIG sheet lets you set guardrails.
Can I model quarterly distributions?
Yes. Set distribution frequency to quarterly on the INPUT sheet. The template divides the annual dividend pool by four for per-period amounts.
Does this handle different share classes?
Not by default. If you have preferred and common shares, calculate the preferred dividend first, subtract it from the pool, then run the remainder through the template for common shares.
How does dividend yield relate to share price?
Yield equals annual dividend per share divided by share price. A higher share price with the same dividend produces a lower yield. The template recalculates automatically when you change the share price.
What if net profit is negative?
The template will show a zero dividend pool. Distributing dividends from reserves or prior retained earnings requires overriding the pool manually.
Download Dividend Distribution Planner
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.