Dividends Issued & Shares Repurchased Excel Tutorial – JP Morgan Case Study

Once we know JP Morgan’s capital levels, we can back into the amount of dividends they can issue and the share repurchases they can make each year – and that’s what you’ll learn how to do in this lesson.

You’ll see why this calculation is inherently circular and how to link these numbers to the rest of our model.

Dividends Issued & Shares Repurchased – JP Morgan Example Transcript

In this lesson we’re going to calculate our final part of our operating model here, which is JP Morgan’s, shares repurchased and dividends issued. So if you haven’t already done so, go to ‘ALT + T + O’, options and formulas, or calculations in 2003. Make sure your workbook calculations are ‘automatic, except data tables’, and that iterative calculations are ‘enabled’, you can set max iterations to 100 and maximum change to 0.001.

Basically what we’re going to be doing is looking at the minimum amount of Tier 1 common capital that is required by JP Morgan, so we’ll look at how much capital, after that they have available for dividends, and stock repurchases. We’ll estimate how much in dividends we want them to be able to issue, and then how much they can actually issue, and then the remaining funds will be used for stock repurchases.


So that’s the basic idea. That’s not too complicated conceptually, but then after that we also need to calculate how their basic, and average basic shares, and diluted shares will be changing. And for that we need to project what their share price is going to look like to get to the share counts.

And for that, of course, we need to estimate what their future P/E multiple, in forward-years is going to be here. So let’s get started with the easy part first, and linking in some of these items from the capital calculations page that we just created. So risk-weighted assets can come in directly from the capital page, the risk-weighted assets right here for 2010, we can link that in directly. I’m going to copy this across.

Now the minimum Tier 1 common required here, so you might be wondering, “Okay, so how can we actually tell, how much in Tier 1 common capital is required by the bank?” We don’t really know.


We do know that according to the rules and regulations, the minimum Tier 1 capital is 4.0%, and the minimum total capital ratio is 8%. But as we saw on our capital calculations page, most of the time banks actually maintained ratios far above that. Here in 2008, JP Morgan had actually a Tier 1 common ratio of 8.8%, a Tier 1 capital ratio of 11%, so usually it’s far above the 4.0% minimum.

If we look at the equity research report I’ve linked to by Morgan Stanley, they actually have estimates here. And they say normalized earnings 2012 our target price is based on normalized return equity of 13%, cost of equity of 10%, and a 9.0% common Tier 1 ratio.

So the first two here ROE, and cost of equity will come up, of course, in our dividend discount model and our residual income valuation model. The 9.0% common Tier 1 ratio will also appear there.


But what we’re going to use it in this context is we’re going to use Morgan Stanley’s estimate, and we’re going to be using it here, to estimate how much in Tier 1 common capital, JP Morgan actually has to keep on hand.

So the first thing I want to do is actually go back to the inputs page, and I’m going to change the minimum Tier1 capital, to minimum Tier 1 common capital. I’m going to footnote this, ‘SHIFT + F2’, and say “MS equity research”, and just give the date here, so we know where we got this from. So that’s what we’re guiding to here.

So how do we determine this? Well we take our risk-weighted assets, remember these are actually average risk-weighted assets, the average is implied whenever you have risk-weighted assets. We’re going to take this number, and we’re going to multiply by the minimum Tier 1 common capital right here, this is a named cell, ’Min_Tier_1’, so we don’t need to anchor it.


So that gives us our minimum Tier 1 common, we have to maintain at all times. We have these two numbers, we have our minimum, and then how do we figure out how much is actually to be available? Well, we need to look at what our Tier 1 common would be in 2010, if we assumed the old 2009 balance, and then took into account all the changes, all the additions, and all of the subtractions from Tier 1 common.

So how do we do this? Well let’s start with the old balance here, so let’s go up to 2009, I’m going to enter a SUM formula, we’re going to name it the balance sheet Tier 1 common. So we’re going to start with shareholder equity, excluding preferred stock here, so common stock down to treasury stock, so that’s step one.

We’re looking at the old balance here. The next thing you need to do is look at how this is going to change in future years, so let’s think about the items on here. Common stock stays the same, so no changes there.


Capital surplus, well we’re going to add in common stock issuances, which we’ve projected elsewhere. Retained earnings, well we’re going to add-in our net income right here, and our dividends paid right here. Accumulated and other comprehensive income, that’s just going to be the exchange rate effect; restricted stock constant, treasury stock, stock-based comp, and stock repurchased.

So some of these variables, we’re going to be trying to solve for in this section, and others we’ve already defined. So let’s just think about this in more detail, and start from the very top with the income statement.

So for the income statement we could pull in the net income right here, I’m actually going to pull in the net income to common here, because again, we’re looking at common shareholders equity, so we want to leave out the preferred stock dividends. We want to make sure that these are being subtracted, before we even get to our common equity.


And if you think about it again, this is effectively what’s flowing in anyway, because the preferred stock appears in the dividends paid section of the cash flow statement, so this is the number I’m going to pull from the income statement right here.

Then on the cash flow statement, so we’re going to add in stock-based comp right here, nothing else under cash flow from operations, nothing on cash flow from investing. Then we’re going to add in common stock issuances right here, that’s going to increase our share count.

And then dividends paid and stock repurchased, we’re not going to be adding in either one of these, because remember, stock repurchased is what we’re solving for. Dividends paid; well we’ve actually already figured this in, because above on our income statement, we are looking at net income to common, so effectively we’re already subtracting preferred stock dividends here, so we don’t need to do that.


And I’m avoiding it here, because again, it would create an extra circular reference. Circular references are okay, we’re going to have them, but if we don’t absolutely need it, we like to avoid it. So, that’s why I’m not figuring in dividends paid, or the stock repurchased here for right now.

The other thing, I actually forgot this, is we also want to take into account the exchange rate effect very small, but we do want to a have it here just in case. And then remember our definition of Tier 1 common that we also need to take out these items; the goodwill, fair value, investments and subsidiaries, and non-qualifying intangibles.
For the accumulated and other comprehensive income adjustments, technically we’d want to remove that, or actually add that in this case. But first off its zero, so we’re not going to worry about it.

And second off if you think about it, this one’s only going to add to our Tier 1 common capital. It’s never going to subtract from it, and it’s zero to begin with, so we’re not going to worry about it here. So I’m going to say SUM these numbers, and so we have that in place. I’m going to copy this across. So what is this really telling us?


Well, actually, very similarly to the federal funds sold and repurchased calculation, we’re basically looking at the prior year and saying, “Okay, so what happens if we take the prior year, and then take into account all the changes over this next year, but use one of the prior year numbers, what happens?”

And with the federal funds calculation we saw how this led to an increase in fed funds sold or purchased. With this calculation here by contrast it just tells us how much capital we have available, before we actually issue dividends, and before we repurchase any stock.

So to figure out the capital available here, we can just subtract. We can take our available capital minus the minimum required. One thing I can do here to make this formula a little bit safer is to actually take the MAX of this subtraction and zero.


Because we could potentially have a situation where, somehow our capital falls below the minimum required. It’s honestly not going to happen with the numbers we’ve chosen, but just to be extra safe, we can do this. And so if its zero that’s telling us, we cannot issue dividends, we cannot repurchase any stock here.

So that’s just adding an extra safeguard in there. So we figured out how much is actually available for dividends and stock repurchases. The next thing we want to do is look at how many dividends they’re actually going to be issuing each year here.

The reason that we’re looking at dividends before stock repurchases is that stock repurchases are really optional. Companies and shareholders don’t necessarily expect them, but dividends on a financial services stock, by contrast are almost expected.

We saw in 2005 to 2007 they had very stable dividends per share, in 2008 as well, and then of course, they slashed the dividends in 2009. So we want to make sure that we’re taking into account dividends first, because shareholders expect to receive dividends.


Stock repurchases can be another way to return cash to shareholders, but they’re more of an extra feature, as opposed to something they’re always going expect. So to do this, we’re going to make some simple assumptions for the payout ratio, and then we’re going to do a check to make sure our dividends fall within the allowed range.

So first off with the basic EPS, I’m going to link up to our income statement, take the base EPS from here. Remember that when we look at dividends, we’re looking at basic earnings per share and not diluted, because the options holders, and the restricted stock unit holders, do not actually get dividends. Only the owners of the common equity of the company receive dividends.

Now for the payout ratio, so for this one what we’re going to do is refer back to the Morgan Stanley Equity Research Report, and see what they’re saying about dividends and stock repurchases here.


If you scroll down to pp. 6 of 17 here, they have a graph here; $45 billion of capital deployment estimated between 2011 and 2012, so they have dividends of $2.5 billion in 2011 going to $5.9 billion in 2012, and then $22.6 going to $13.9 in 2012.

So we want to try to get our numbers, such that we get roughly equal to what they have for the dividends, for the share repurchases these are going to be hard-coded and constant, so that’s a little bit easier.

So what we’re going to say here is that our dividend payout ratio is gradually improving over time, and getting back to the level that we saw in ‘05 to ‘07 there. So I’m going to say 9.0% – 12% – 25% – 30%, and 35%, so that it is gradually rising back up to the 35%, we saw in 2006. So now for the dividends per share, remember, this is just the EPS time the payout ratio. So we have that.


And then for the common dividends so in absolute dollar terms not on a per share basis, but just in absolute dollar terms, how much in common dividends do we get here? Well, for this one what we’re going to have to do is look at the average, basic shares. Now we’re going to be calculating this separately, after we finish these other calculations.

For right now though, just to get some kind of numbers in here, I’m going to link to the income statement, and just copy this formula across. We’ll be changing this later this is just something I’m doing now, so we can get actual numbers here.

So what I’m going to say is that common dividends, equal to the dividend per share, times the average basic shares (NOT diluted shares), because again, option holders and RSU restricted stock unit holders, do not actually get dividends. So we have this, let’s look at our numbers here.

So we have $2.1 billion. Morgan Stanley has $2.5 billion, going to $5.9. We have $2.1 going to $5.1, our numbers are a little bit low, but keep in mind that our share count here is not yet correct.


We have to change this, once we take into account the repurchases, and the stock issuances. So we will be changing this, for now our numbers do look a little bit high though. And then for the allowed dividends here, so what we want to do is look at how much we could potentially issue, and then how much we can issue according to the capital available for dividends and stock repurchases.

So this formula is going to be very simple – we’re just going to say MIN of the capital available and the common dividends. Why are we using the minimum? Well, if the capital available is less than the common dividends here, we only want to use the capital available.

If on the other hand it’s greater, then we can issue everything that we want to issue right here. This also handles the case where we have no capital available, because if our formula here, the MAX formula that defaults to zero, if we have no capital available for dividends and stock repurchases. I’ll copy this across.


So this is giving us in cash terms what our dividends each year will be, the allowed dividends here. In this case we have assumed very mild dividend payout ratios, so we’re not coming up against the upper capital limits, right here.

When we finish this model, I’ll go through again and change around some of these numbers, so you can see exactly how our model works if, for example the common dividends here exceed the capital available. So now for the stock repurchases we’re going to refer back to the Morgan Stanley report.

They have $22.6 billion in 2011, $13.9 billion in 2012. So we’re going to go with those numbers first, $22.6 billion, and then $13.9 billion, so they’re assuming that JP Morgan is using a whole lot of cash flow, excess cash flow here for the stock repurchases. Now in earlier years, ramping up to this what I’m going to say is $5.0 billion for stock repurchases.


And then after this we’re going to assume a trailing-off, because presumably, at this point their capital ratios will be a bit more normalized. They won’t have quite as much excess to return to shareholders, so I’m going to say $10 billion for 2013, and $10 billion for 2014.

So we have how much we actually want to be able to repurchase. Of course, the relevant question is how much is available for stock repurchases, right here. So for this one, I’m just going to take our capital available, and subtract the allowed dividends.

Remember that this handles the case if we have no capital available allowed dividends could never be greater than the capital available, so we can never end up with a negative here.

And if this is zero, the capital available is zero, then the allowed dividends will also be zero, so no matter kind of numbers we have, we don’t need to do anything more complex than simple subtraction here.

Copy that across, and then once again, for the actually allowed stock repurchases, we want to use the MIN function, so MIN of the capital available and the stock repurchases.


I’m using ‘Stock Repurchases / Planned’ here because in historical years, I’ve pulled in the actual stock repurchase numbers. So, we’re going to take the MIN, again I’ll change around the numbers after we’ve finished this part, to show you what would happen, if we had something weird going on here.

But basically again if we had say $15 billion here, well it would come out to only $12.8 billion, because that is all we have available, in terms of capital here. Copy this across, and so now have our allowed stock repurchase and allowed dividends numbers here.

Of course, we’re not quite done yet. The next step here is to figure out how our share counts will be changing from year-to-year. Now if you think about how we have to do this, basically what we have to do is look at our shares repurchased, and our shares issued, and then look at the net change in basic shares.

We’re going to modify the ending basic shares, by that net change in basic shares, and then we’re simply going to average those numbers, and then once we’re done with that we’re going to go up to the income statement, and link in the weighted average basic shares, and the weighted average diluted shares.


So to get started with this to figure out what their share price is going to be, we’re going to base it off of the diluted EPS, and then also the trailing P/E multiple. We’re just going to make simple assumptions for the P/E multiple in future years. These will probably be way off.

But again, we have no better way of really predicting what the share price is going to be, so we’re just going to use projected trailing P/E multiples here. So, for the P/E multiples I’m going to assume a gradual decline, say 12x, 11.5x, and then 11x, 11x and then 10.5x.

Then for the diluted EPS so what I’m going to do is take this number, and copy this across. I’m going to un-bold that, so we have the diluted EPS coming in from the income statement.


Now we’re going to start delving into circular reference territory, because this EPS is going to depend on how much stock we’ve repurchased or issued, but how much we repurchase or issue in terms of shares is going to depend on our share price.

So this is where the circular references start creeping in. So now for the actual or implied stock price the blue numbers here the hard-coded ones are actuals that I just pulled from historical data from Google Finance, if you want to get them yourself.

We’re getting the share prices at the end of the year, because we’re looking at trailing P/E multiples over the course of a year here. So now, for the implied stock prices in future years, we’re going to take our trailing P/E multiple times the diluted EPS. Carry this across, and this gets us our projected implied stock price.

Again this is a total shot in the dark. We might be completely off on this, but generally if a bank’s assets are increasing, then it share price is going to be increasing. A bank’s market cap is very closely linked to its book value in most cases.


So actually for a normal company I would feel totally wrong about trying to project a share price, which is in fact why in many of the basic models in this course, we just leave this blank and assume nothing for share repurchases or issuances.

But for a bank actually, if you can know their book value in the future, usually the market cap is closely linked to the book value, which is why you look at price to book value multiples in many cases.

So I feel in this case, it’s not quite as off as for example, if we are working with a manufacturer or technology company. Plus we need to get something for our numbers for the stock repurchases and issuances, even if it’s not quite right, just to make our model complete.

For the numbers here first for the stock repurchases, we’re going to go right up here for our allowed stock repurchases. I’m going to put a negative sign in front. For the stock issuances, it’s going to have a positive sign, because this creates more shares.

The stock repurchases reduces the amount of common share outstanding in the market. I’m going to go to the cash flow statement drivers and pull this in from there.


And I’ll copy both of these across. Now for the number of shares repurchased what we want to do here is assume that the repurchasing and issuing occurs over the course of a year. So what we’re going to do is look at the absolute dollar amount, and then divide by the average share price, over the course of a year here.

So I’m going to take the stock repurchased right over the course of a year, 2010 in this case, and then divide by the average share price during that time. Taking the ending balances in both cases, and making a simple average assumption, like that.

I’m going to anchor the 313 row here, because we don’t want this to shift around, even when we copy it down for the shares issued. So I’m anchoring it in both of those. I’m going to copy this down to shares issued, because again, our stock issuances are right below it, the implied stock prices are both anchored in this formula.


And then net change in basic shares, I’m simply going to sum these up, so we have this in place, and we’re going to copy it across. Magnitude-wise this makes sense, we have a huge increase in 2011 and 2012, corresponding to Morgan Stanley’s projections for what JP Morgan is going to do with its capital here, so that make sense intuitively.

Then the next thing I want to do is figure out what the diluted shares here are going to look like. So RSU’s outstanding, this is very easy, we’re just going to link to our inputs page to the RSU account right here.

Remember that this isn’t even really an exercise price for the RSU, these are just grant prices. But these are just really just normal shares, except that the shareholders don’t receive dividends, so these will have to be calculated.

These we’ll have to figure in to our diluted EPS calculation, but they’re not going to receive dividends, which is why we left them out of that part of this. All that matters here is the absolute amount, the exercise price, it’s really not even an exercise price, is irrelevant for restricted stock units. Copy that across.


Now for the dilution from options, so let’s go back to our inputs page, remember what the formula for option dilution is here. If the option price is greater than the share price it’s zero, because out of the money. Otherwise we take our options, and we subtract our options, times the option price, over the share price.

Basically we’re subtracting how much the company repurchases here; basic treasury stock math. What I’m going to do to save time, and because I’m being lazy here I’m going to take the IF statement right here, copy this, then go back to the operating model right here, and then for dilution from options, I’m going to press ‘F2’ to paste this in.

And what I’m going to do is use the same formula. Remember these are all named cells. These are actual variables in our Excel workbook here, so we don’t need to change these. What we do need to change is the share price, because we’re going to be assuming here that this dilution happens, over the course of these future years. So we’re going to be using the future share prices of JP Morgan.


And specifically, again we want to be using the average share prices, because we’re assuming that it changes over the course of a year. So I’m going to say average of the 2009 and the 2010 share prices right there. So we have the average here, in place of the old share price variable.

And then I want to do the same thing for the second part here, where we divide by however much JP Morgan is repurchasing, or according to the treasury stock method.

Now you could make the argument here that the options for JP Morgan are going to change in future years, and they probably will. But again as with many other items we have no way of predicting what the change is going to be, or projecting the future numbers.

So we’re just going to assume that the total options and the exercise price for those options stays, the same here. So now we’ll copy this across, and we see that as the share price rises we start getting dilution from the options, make sense intuitively, as the share price goes up you should start to see more dilution from the options.


They’re out of the money in the first year, but then become in the money after that. So now what we can do is actually start filling in some of this data; for the ending basic shares, the average basic shares, and the average diluted shares outstanding; based on all our calculations above for the stock repurchases.

So for ending basic shares what I want to do here is take the old balance, and then add in the net change in basic shares, so we have that. Then for the average basic shares what I’m going to do is average the ending basics shares numbers for 2009 and 2010 here. And then in future years we’re going to be doing it for future years, I’m going to copy this formula across.

So we see here overall, it’s going down quite a bit. Primarily because, we have a lot in the way of share repurchases going, on in this model. Then for average diluted shares outstanding, so remember this calculation is very simple.


We’re going to take our average basic shares, and then add-in the dilution from options, and restricted stock units outstanding. To make the formatting consistent, I’m just going to change this to have a single decimal place. So we have that.

And so this brings us to the end of this calculation section for dividends and stock repurchases, and how the share count changes. Of course we’re not done, because what we need to do now is actually go through, and link all of this together.

So let’s go to the income statement first. For basic and diluted EPS, we’re going to leave these the same, because nothing here changes. The only thing that changes is the weighted average basic and diluted shares. So for basic shares what I’m going to do is go down here and link to average basic share.

And then for average diluted shares I’m going to go down to our calculations section and link to average diluted share outstanding, and I’m going to copy these formulas across.


And then for dividends per common share, what I’m going to do is go down to this section and link to our dividends per share right here. Remember this is basic EPS, so we’re excluding the diluted shares, as we should be for dividends, and I’ll copy this across, so that’s how we link together our income statement.

Then on the cash flow statement, we still have a few of these items to fill in. Nothing under cash flow from operations, this is already complete. Under cash flow from investing activities, this is actually already complete as well, so what I’m going to do is here is under financing activities for stock repurchased, this should be a negative, so I’m using a negative sign.

I’m going to go down to the dividends and stock repurchased section right here, and link to the allowed stock repurchases. Copy this across. And then for dividends paid, so this is almost correct.


We have taken into account the preferred stock dividends that appear on the income statement right here, so we have that taken into account, but now we need to add in the common stock dividends as well, and specifically the absolute dollar amount of dividends here.

So, I am going to take this formula that sums up the preferred stock dividends, and I’m going to add in the allowed dividend on common shares, from right there. And I’ll copy this across. And so now we have everything in place, and our cash flow statement and the rest of our statements here are complete and linked together. We have dividends paid right here, linked to the common and the preferred.

We have stock repurchased, based on the Morgan Stanley Equity Research, in large part. And of course, we see that our ending cash on the balance sheet, still matches our ending cash balance right here, because of all the reasons I mentioned before with the federal funds calculations, so this looks to be correct.


And then of course, now we have our complete calculations for dividends, and stock repurchased and stock issuances. Now just to check ourselves, let’s go back to the Capital page. And we see here that the Tier 1 common ratio it’s always staying above the 9.0% right here, as it should be, because we’ve set this as the minimum Tier 1 common ratio.

We see that overall, the Tier 1 capital ratio is much more reasonable now, before it was going up to a much higher number. Total capital similarly before it was going up to around 20% which is way too high for a commercial bank like JP Morgan. Tier 1 leverage staying at around the 7.0% or 8.0% level.

So overall, once we’ve taken into account our share repurchases and dividends, the ratios are a lot better. Let’s say that I went into our calculations section here, and let’s say that I just made completely ridiculous assumptions, for the dividend payouts right here, and for the stock repurchases. So, let’s say I said 100% for all of these, for example.


So what would happen in this case? Well, let’s go back to our Capital page see right here, Tier 1 common ratio, even with these ridiculous assumptions stays at 9.0%. It stays at 9.0%, because that’s the assumption that we’ve set up, on the input page right here.

So no matter what kind of ridiculous assumptions we have, this always stays correct. And then the other ratios, of course follow from Tier 1, so these are declining which is a little bit concerning, but again they’re still above the minimum threshold.

If you look at how this is working more specifically, you see that the common dividends exceed the capital available, so we’re just using the available capital here, for the allowed dividends.

There’s no capital available for stock repurchases except in Year 2 here, and then the same applies in future years. There’s just no capital available, so we’re defaulting to zero for all of these.

And that’s how our checks work, that’s why we need to use these MIN and MAX formulas, and subtraction, and actually take into account regulations, in our operating model like this. I’m just going to undo these changes.


Another quick thing we’d like to do is to go up, and check our diluted earnings per share calculations here, and see what equity research says. So 2010, we have $3.49, $4.52, and $5.40. Let’s check Morgan Stanley, so for 2010 they have $3.63, consensus is $3.45. We have $3.49, pretty close; $4.66 consensus, we have $4.52.

Then $5.46 consensus, we have $5.40. So overall, our EPS here is actually very, very close to Morgan Stanley. Of course this is no coincidence, it’s because we have been following equity research, the RBC report, and the Morgan Stanley report all along.

So we’ve been kind of following it, and making sure that our numbers stay relatively close, to what Wall St. analysts expected. If we want to be extra certain we can also check the RBC report, and see what they have for EPS. So they have $3.99 for 2010, going to $4.83 in 2011.


We have $3.49, so $0.50 lower than theirs going to $4.52, a bit lower for 2011, but clearly in the right range, this is definitely what Wall St. analysts we’re expecting at the time for JP Morgan, over the next three to five years here. So that really takes us to the end of our operating model now, we’ve done all of the heavy lifting, all of the difficult parts.

And the only remaining thing to do here is add in a summary page that sums up some of the key ratios; the revenue, the EPS, the Tier 1, total capital, leverage ratios and so on.

And make it in an easy to read format, for example, if your Managing Director or someone else is looking at it, and wants to get a quick overview of what’s going on with this company over the next five years.

For more tutorials on Bank Modeling click here.