• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for dividends distribution optimization

F

Fadisaw

New Member
Hello Guys.... hope all is well...


Currently working on a financial model, whereby, I need to calculate how much I can distribute dividends to my shareholders based on complex assumptions and calculations.

The maximum amount to be distributed needs to take into consideration the expected cash flows in following periods and without having a negative cash balance.

I.e. In Dec'18, the first distribution will take place, where i have x amount that will be distributed, after that, i will end up with some negative and positive cash outflows based on those complex calculations - therefore, is there a macro that enables me to automatically calculate the maximum amount that I can distribute without having negative balance and of course without the unwelcomed circular references? :)

Thanks indeed in advance
 

Attachments

  • forum - Copy.xlsx
    173 KB · Views: 2
I'm bit confused. Dividend should be based on earnings and not cash balance. Also, company/business should keep portion of net income to reinvest in growth, to pay off debt, or to cash reserve (retained earnings).

There are 3 main approaches to dividends: residual, stability and hybrid. Each having advantages and disadvantages. Majority using hybrid method to give some stability to dividends as well as giving companies long term stability in debt/equity ratio.

I'd suggest you start out by building residual model, then introduce stability model into the mix to achieve balance.

Edit: Also, keep in mind. Most companies do not start dividend payout until, company has matured to a point (i.e. past rapid growth stage).
 
Last edited:
thanks for your reply... indeed dividends are based on earnings. what you mentioned earlier have been assumed as residual approach.

My issue is a bit technical with excel, where I cannot make it read how much is needed from future periods assumptions and calculations, to reach a current residual amounts .

of course in the most dynamic and efficient way.
 
Residual calculation don't typically use forecasting, but based on what's on hand. Only forecasting need is future capital expenditure. That's more business decision than Excel function.

Your worksheet doesn't have enough info to arrive at your desired result.
 
You must log in or register to reply here.
Back
Top