DoublingTimeLambda

Calculating the Doubling Time Formula in Excel

Do you want to calculate how quickly it will take for something to double in value? In this post, I’ll show you how to calculate that using the doubling time formula. By utilizing variables, it can also be easily updated in Excel to factor in different growth rates, making it easy to do what-if calculations.

What is the doubling time formula?

The doubling time formula utilizes logarithms and takes an assumed growth rate to determine how long it will take for a value to double in value. For example, if your investment were to rise at a rate of 10% per year for 10 years, it would be worth roughly 2.59 times what it is now. But rather than doing trial and error to try and determine exactly at what point it will double in value, you can use a formula to do that for you.

In essence, all the doubling time formula involves is taking the logarithm of the change in value you’re trying to get to (e.g. 2) and dividing that by the logarithm of the current growth rate plus 1 (e.g. 1 + 0.1 = 1.1). By doing this calculation, you get an answer of 7.27 for this example. You can plug that into the following formula to check:

1.1^7.27

And the result will 1.9995. The more decimal places you keep in the above calculation, the closer you will get to precisely 2. This formula can also be adapted if you want to calculate how long it will take to triple, or quadruple. In those cases, you can just change the numerator so that instead of taking log 2, you’re taking log 3 or log 4, if you want to calculate tripling or quadrupling time, respectively.

Setting up the formula in Excel

As you can see, this isn’t a terribly complex formula. The key is really just using logarithmic functions in Excel. And whether you use a natural log or not doesn’t matter, your results will be the same. You can use the LOG function for these purposes. In Excel, the earlier formula would be calculated as follows:

=LOG(2)/LOG(1.1)

To make it more versatile, I’ll also add some variables here. One for the current growth rate, and one for the target growth (this is where you can specify if you want to double, triple, quadruple, etc.). Here’s how that looks:

Doubling time formula in Excel.

A value of 2 will read as 200% in Excel. The formula to calculate the years to double will simply need to be adjusted to factor in for these variables, which I’ve named TargetGrowth and GrowthRate in my file:

=LOG(TargetGrowth)/LOG(1+GrowthRate)

By utilizing these variables, I can now easily update my calculations.

Creating a LAMBDA function to make it even easier

Another thing you can do is to create your own LAMBDA function. If you’re on the latest version of Excel, these are custom functions you can ease, without the need to even set up a template and separate cells. All this involves is going to the Name Manager in Excel as if you were creating a new named range (the long way). Except when you create it, the name you’re assigning is the name of the function. And rather than referencing cells, you’re entering in a formula.

This particular function should contain two variables, one for the current growth rate, and one for the target. It will then plug them into the formula I referenced above. Here’s what the formula will need to look like within the Name Manager:

=LAMBDA(current,target,LOG(target)/LOG(1+current))

You’ll notice it needs the LAMBDA prefix so that Excel knows to treat this differently. Here’s how it looks within the Name Manager:

Doubling time lambda function in Excel.

I called it DoublingTime even though it can do more than just calculate that. You can of course call it whatever you prefer. Now, this formula can be used in Excel to do the exact same calculation as above, without the need for extra cells:

You’ll notice here I’m just entering in raw values as opposed to percentages. This is just because of how I structured the formula and to keep it as simple as possible.


If you liked this post on Calculating the Doubling Time Formula in Excel Functions, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2Efinancials

How to Import Financial Statements Using Power Query

In this post, I’ll show you how you can import a company’s financial statements into Excel using Power Query. Previously, I’ve covered how to get stock prices from both Yahoo Finance and Google Sheets. But to get financial statement information, I’m going to use a different source: wsj.com. The reason being, is it’s in an easy format to export and that makes the import process very easy for Power Query.

Downloading the data

I’m going to use Walmart’s financials for this example. And if you navigate to the following URL, you will get a summary of Walmart’s quarterly financial statements:

https://www.wsj.com/market-data/quotes/WMT/financials/quarter/income-statement

What’s convenient about this URL is that it contains both the ticker, the statement type, and indicates that the financials are quarterly. That makes it easy to alter in case you wanted to look for annual statements or a balance sheet rather than an income statement. Just changing the URL will get you to the right page. The above link is what I’m going to use for this example.

To load the data into Power Query, go to the Data tab and click on From Web:

The data tab in Excel that shows the Get & Transform data section.

Then, paste the URL in the following box:

Entering a URL in the From Web section.

After clicking OK, you can select which table to import. In this case, it’s going to be Table 0:

Selecting which table to import from a Power Query import.

Next, press the Transform Data button to make changes before it gets imported. I’ll start with removing the column at the very end, showing the trend, as it doesn’t contain any information. To remove it, right-click on the header and click Remove:

Removing a column from Power Query.

I’m also going to remove the Changed Type step, which automatically changes the data types. To get rid of the step, click on the X next to the step:

Removing a step from Power Query.

This is important because since the header names change based on the quarter, it isn’t going to be helpful to have this step since it looks for hardcoded values. An optional step you could take is to Demote Headers so that the header names are generic and not tied to a specific quarter. However, this isn’t necessary if you remove the Changed Type step. For more information on changing header names, refer to this post.

Once you’re done making changes, click on Close & Load in the top-left corner, and then your data will load into a sheet.

Close & Load button in Power Query.

The download will work just fine right now. However, let’s also make the file a bit more versatile in case you want to quickly change the ticker symbol.

Setting up the variables

First up, I’ll create a named range for the ticker symbol, called ‘Ticker’ :

Power Query table with a variable for a company's stock ticker off to the right.

I’ll now go back into the query editor to account for this named range. To edit a query, go into the Data tab, click on Queries and Connections, and then off to the right you should see your queries. Right-click edit on the one you want to adjust:

Selecting the option to edit an existing query in Excel.

Then, click on the Advanced Editor button near the top of the Power Query window:

The Advanced Editor button located on the Power Query Home tab.

I’m going to add the Ticker variable under the let section as follows:

Ticker = Excel.CurrentWorkbook(){[Name=”Ticker”]}[Content]{0}[Column1],

Note that Power Query is case-sensitive and you will get an error if what you’ve entered doesn’t match exactly what you’ve set as your named range. Also, make sure to add a comma at the end.

I will also need to adjust the Source variable so that it uses the Ticker variable:

Source = Web.Page(Web.Contents(“https://www.wsj.com/market-data/quotes/”&Ticker&”/financials/quarter/income-statement”)),

The key thing here is to break up the part of the URL that mentions WMT and replace it with the named range. Here’s what the code looks like within the Advanced Editor:

Power Query code in the Advanced Editor.

Now, you can Close & Load back into the worksheet. To test the named range, what you can do is replace the ticker value from WMT to AMZN, and if it works correctly, it should load Amazon’s income statement instead. After changing the ticker symbol, remember to press the Refresh All button under the Data tab:

The Refresh All button in the Data tab.

If it works, you should see a whole new set of data populate on your spreadsheet:

Amazon's income statement loaded into an Excel spreadsheet using Power Query.

If you liked this post on How to Import Financial Statements Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

h2eVariances

How to Calculate Variances in Excel

In this post, I’m going to show you how you can easily calculate variances in Excel. I will also go over how to group variances and how using pivot tables, charts, and conditional formatting can help save you time in reviewing them.

For this example, I’m going to use data from the S&P 500 as stock prices frequently fluctuate. To start, I’m going to download the data from the past year. I’m going to remove everything except the closing values just to keep this example simple:

Download of the S&P 500 closing prices over the past 12 months.

Calculating the variances

The calculate the variance in these data points, what I need to do is to take the current closing price, and subtract the previous day’s closing price from it. That will tell me how much of a move there was that day. On June 7, for instance, the S&P 500 fell from 4,229.89 on June 4 (the previous trading day) to 4,226.52. If I minus the current day’s close from the previous, I get a value of -3.37.

But we can dig a lot deeper than just looking at the difference in price. Let’s also create a field to indicate whether these variances are positive or negative. To do that, I’ll create another column called ‘Direction.’ For this calculation, I will take a look at the value in column C (where my variance is) and create a simple IF formula:

=IF(C2>0,”Positive”,”Negative”)

Here’s what my sheet looks like now:

Table of variances showing positive and negative values.

Although you can determine whether it is positive or negative from the variance field, by creating another column you can quickly filter if you want to look at all the negative or positive values. Another column I’ll insert here is for the percentage change.

To do this, what I will do is take the variance amount and divide it by the previous day’s closing price. This will tell me how much the price has moved as a percentage of what its value was the day before — which is much more useful than just looking at the raw value. After inserting the column, I have the total variance, variance %, and which direction it went in:

Variances by raw amount, percentage, and positive or negative indicator.

I changed the variance % field to show percentages and I added a few decimal places since the percentages are fairly small. To add decimal places, go to the Numbers group on the Home tab and click the following button on the left:

Button to increase or decrease the number of decimal places.

The one on the left will add decimal places while the one on the right will remove them.

However, what if you don’t care about positives or negatives and are just interested in the absolute value of the changes? I’ll cover that next.

Calculating changes in absolute value

With absolute value, you remove the positive or negative indicator. And to calculate a variance this way, you just need to add a formula to the calculation in the variance field. Rather than this:

=B2-B3

You would enter this:

=ABS(B2-B3)

Now, my variances update and I no longer have a use for the Direction field since all the values will be positive:

Variance table when only calculating absolute values.

Alternatively, you could also just create another column specifically for the change in absolute value.

Now that the variances have been created, what you may want to do next is to group them.

Grouping variances

Why would you want to group variances? The big advantage in doing so is they can make it easier to analyze a large data set by showing you where the bulk of the variances are.

Rather than creating a bunch of IF statements, what I’ll do is create a table to show where the variances belong:

Table grouping the variances.

I’ve created a named range called VarianceTable for this. And now, all I need to is use a VLOOKUP formula to find which category a variance belongs in. Since I’m not using an exact match, I will set the last argument in the function to ‘TRUE’ :

=VLOOKUP(D2,VarianceTable,2,TRUE)

Now I have a category field instead of the Direction:

Table with variances grouped by category.

But this doesn’t tell me a whole lot. I could filter by the category. However, a better approach is to create a quick pivot table that shows me a summary of where the values fall:

A pivot table showing the count of the different variances groups.

And from that, I can quickly display these variances on a chart:

A chart showing variances by category.

Another way you can help identify extreme values in variances is by using conditional formatting. To apply conditional formatting, select either the variance column or the variance % column and under the Conditional Formatting button on the Home tab, you can select either Data Bars or Color Scales. I prefer using Data Bars since there are fewer colors:

Selecting data bars under the conditional formatting section.

Then, my variances are easier to visualize and to see where the highs and lows are:

When you are analyzing variances, using conditional formatting, pivot tables, and charts can help you summarize your findings.


If you liked this post on How to Calculate Variances in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

adc

Average Down Calculator Template

If a stock you invested in dropped in price, it could be a good opportunity to buy more shares and bring your average down. You can use the average down calculator on this page to do a quick what-if calculation to determine how many more shares you would need to be. However, you can also use this template, which will allow you to run through the same scenarios within Excel.

How the average down template works

There are only six inputs on this template:

  • Amount invested
    • This is how much money you have already invested into the stock.
  • Shares owned
    • The number of shares that you own.
  • Current share price
    • What the share price is.
  • Desired average price
    • What price you want to average down to.
  • Budget
    • How much money you can afford to invest.
  • Increment price by
    • This is for the sensitivity analysis and determines by how much you want it to move by. The default is set to $0.50.

Once you’ve entered that data, the rest of the template will populate. Here are the two scenarios that it will show you:

1. Getting to your desired average price

In this scenario, the template will show you how much to invest at different price points to get your average down to your desired average price. You will see up to 20 different data points to show you if the price continues to get lower, how many shares you will need to buy to reach the average price you are targeting.

And any scenarios that fall within your budget will be highlighted in green, and so will the corresponding chart:

Average down calculator showing how to get down to an average desired price.

If all the data points aren’t filled in or it looks like the chart doesn’t go all the way to the right, this is a sign you need to fix your Increment Price by value. Enter a smaller price increment and you’ll see more data points and a more complete chart.

2. How low you can get your average

The second scenario ignores the desired average price and simply tells you the different average prices you can average down to if you buy at the current price. This is good if you don’t have a specific average in mind and just want to see how low you might be able to go.

Average down calculator showing how low you can get your average.

You’ll notice on the x-axis it refers to the average price rather than the share price in the earlier chart.

Please note that the template is locked down and this is to prevent overwriting formulas which could lead to errors in the calculations and the charts.

Download the file

You can download the file for free, from here. The free version is limited to five price points. On the full version, there are 20 different prices, no ads, and the file is unlocked.


If you liked this Average Down Calculator Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Price to Earnings Ratio Calculator

To enter data in the price to earnings ratio calculator, start from top to bottom. Tabbing over or hitting enter will update the calculations.

    Current Stock Information

Price $

EPS   $

P/E      

  What-if Analysis

      Change

       

      to

     

What P/E is and why it’s important for investors

The price-to-earnings (P/E) ratio is a key metric that many investors use when analyzing whether a stock is well-priced and a good buy, given its level of earnings. The calculation takes the current stock price and divides it by the company’s earnings per share, typically over the last four quarters. You can also calculate a forward P/E. This is what the ratio will be in the future, based on estimates of earnings.

This is a particularly useful calculation in a year like 2020 when the coronavirus pandemic has thrown many businesses out of whack and some are over or underperforming. And that means their P/E ratios may not be all that reliable right now.

Using a P/E ratio is particularly useful when comparing one stock against another. If a stock is trading at a very high P/E of 50 or more, it could be a sign that it’s overvalued. However, this can be skewed if a company is coming off a bad quarter where its profits were low. It’s always important to consider the context. And comparing different types of industries may not be helpful, either. A bank stock that is relatively stable and that may not achieve much growth will trade at a much lower P/E than a high-growth tech stock where its sales are climbing by 50% or more.

How to use this calculator

I wanted to create a calculator that could be useful for setting up alerts. For instance, if a stock is trading at a P/E of 50 and you want to set up an alert for when it falls to a lower multiple. You can use the What-if analysis section to plug in the P/E that you want to buy it at. It will then tell you the price it will have to fall to or the EPS that it will need to rise to.

You could also use it as a simple P/E calculator. While many financial websites may give you a P/E number they won’t always update quickly, like when a company reports its earnings. If you know what the new P/E is, you can plug it into the calculator. You can also do a what-if analysis to see what the ratio will be if earnings rises or falls to a certain number.

To enter data into this calculator, you’ll want to start from the top and work your way down. Enter the price and EPS first and then make your selections in the what-if analysis. If you go straight to the what-if analysis then the calculation won’t be correct. As you’re entering data and tabbing over, the formulas will automatically update. Hitting enter after entering in a number will also update the calculation.

Another calculator you may want to try is the average down calculator, which can help you determine how many more shares you’ll need to buy to get your average price down to a specified amount.


If you liked this post on the price to earnings ratio calculator, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Optimized-H2Epayback

How to Calculate Payback Period

In previous posts, I went over how to calculate the internal rate of return and how to discount future cash flows to arrive at a net present value. Today, I’ll go over another way you can evaluate projects, and that’s using the payback period. The payback period calculation is a simpler method than the other two approaches in that it just looks at how long it’ll take for you to recoup your money from an investment, or when you’ll hit breakeven.

Setting up the spreadsheet

To do this calculation, I’ll again use the discounted cash flow spreadsheet from my earlier example. The key difference in calculating the payback period is that you don’t need to worry about present value since this won’t take into account the time value of money.

Let’s assume a scenario where you invest $1,000,000 into a project and generate cost savings of $100,000 every year. Here’s how that might look like over a 25-year period:

Cash flows over the next 25 years.

This is a really simple setup but let’s set up a formula to determine when the investment reaches breakeven. In this scenario, since the cash savings are always $100,000 every year, you can simply take the initial investment and divide it by the annual cost savings. The formula looks as follows:

Payback period calculation.

After 10 years, the investment will be paid back in its entirety and reach breakeven. If your cash flows will vary over the years, what you can do is use an average to try and smooth it out and get to an approximate payback period. Another alternative is to create another column that shows your cumulative savings or cash inflows and how much is left to reach breakeven. To calculate a cumulative sum, just use a regular summation formula but freeze the first cell so that your formula will always start from the same position. Here’s how you might set this up:

Cumulative cash flow over 25 years.

You’ll see that cell C6 is frozen as that’s where my first value is, and that’s where the $1,000,000 outflow of cash is. I’ve also changed my cash flows so that they’re different amounts each year, and under this scenario, you’ll notice that it’s not until year 22 that I reach breakeven. A better way to illustrate this is through conditional formatting, by highlighting the negative values and the positive ones in different colors.

You can do this by selecting all the values in the cumulative field and under Conditional Formatting, selecting Format all cells based on their values, which gets you to this menu:

Conditional formatting rules.

The first thing I’ll do here is to change the color scale so it shows three colors instead of just two. Then, I’ll set it up so that the lowest value is red, the midpoint is set to 0 and white, and the maximum is set to green:

Conditional formatting with a 3-color scale.

Then, after clicking on OK, my values look like this:

Cumulative cash flow with conditional formatting.

Using the conditional formatting, I can easily see the progression of the red into white (breakeven), and then into green. It’s a lot easier on the eyes and allows you to quickly see the progress. If you want to look into more ways you can do this, check out this post on conditional formatting.

Payback period when factoring in time value

If you just want to calculate the payback period using a simple formula and your cash flow / savings is the same every year, then simply dividing your total investment by that amount will suffice. Then, it’s simply a matter of determining whether the number of years in the payback period is acceptable to you. If it is, you can move forward with the project. If the payback period is too far into the future, then you may want to re-consider it.

However, when you’re looking at a longer timeframe, you may want to consider incorporating discounted cash flow to give you a more realistic picture of the payout period over time. And while the typical payback period calculation doesn’t incorporate the time value of money, that doesn’t mean you can’t do it. In this example, I’ll calculate the present value of the cash flows like I did in the earlier post which looked at discounted cash. Using a discount rate and raising the cash flow to a negative power (years in the future), I can arrive at the present value. Here’s how that looks in an additional column, with the respective formulas off to the right:

Present value of future cash flows.

Note that I used a named range for the discount rate. Column B relates to the Year field and Column C is the cash flow value in the future.

This time I use Excel’s built-in present value function, which requires you to enter the rate, the number of periods, payments (not applicable here), and the future value (which needs to be negative for this to calculate correctly). Using a 5% discount rate, I’ve populated the present values of each of the future cash flows.

Now, I can add a column to track the cumulative values:

Cumulative total of all present values.

When factoring in the time value of money, my payback period is now well over 25 years. It’s an important reminder of just how important time value is. Under the previous payback period calculation that didn’t factor in the time value of money, the payback period was 22 years. In order for my payback period in this example to get to breakeven within 25 years, I’d have to set my discount rate to less than 1%. At 0.5%, this is what the schedule looks like:

Cumulative present value when at a 0.5% discount rate.

Only after 24 years does the project attain breakeven in this situation, and that’s with a minuscule discount rate. Normally, in a payback period calculation, you’ll just stick to the investment total divided by the savings or cash flow that the investment will generate. However, there are significant drawbacks to doing so when it may take many years for an investment to breakeven. In that case, it may be worthwhile to consider the time value of money.


If you liked this post on how to calculate payback period in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

IRR

How to Calculate Internal Rate of Return (IRR) in Excel

Last week, I covered how to calculate discounted cash flow. In this post, I’ll build off that worksheet and show you how you can calculate the internal rate of return (IRR) in Excel. IRR tells you the return that you’re making on an investment or project, and at what discount rate the net present value of all the cash flows will be zero. In these scenarios, there’s typically an outlay of cash, usually at the beginning.

In my previous example, I only looked at cash flows coming in. This time, I’ll look at a scenario where you pay money out at the beginning and generate cash flow in future periods. A common example is paying to upgrade a piece of equipment and then generating cost savings from it for x number of years. Knowing the IRR can tell you if you’re making enough of a return off of the investment and whether you should move forward with it. Using IRR can also be helpful when you’re comparing multiple options to see which one is the best one.

Setting up the spreadsheet

This step is about the same as when setting up the discounted cash flow template. You’ll need to enter the different years, the cash you expect to come in or out, and then calculate back what the present value is today.

Here’s what the file looks like setting in a scenario where you pay $100,000 upfront and then generate $10,000 in cash flow for 25 years. At a 5% discount rate, in this example the present value of all that cash flow is a positive $40,939.45:

Discounted cash flow calculation using an interest rate of 5%.

Calculating the IRR

The problem here is the discount rate can be difficult to determine, and that can have a significant impact on your overall returns. And so rather than worry about what your discount rate should be, you only need to determine the IRR — which is to say at what point would your present value be worth $0? If you need a higher return than the IRR the project would be a no-go but if you’re okay with anything up to and including the IRR, then the project or investment would be passable. What it comes down to is the lower the IRR is, the worse the investment is

There are a couple of different ways to calculate IRR in Excel. One way is through a formula called XIRR. It only has two required arguments — dates and cash flow. This is why in this example I entered dates for my cash flows rather than just numbering the years. This makes it easier for me to use the XIRR formula. In my spreadsheet, I enter the following formula:

=XIRR(D6:D31,C6:C31)

Column D contains my cash flow and column C contains the dates. Doing this, Excel tells me the IRR is 9.687% for this specific project. But if I work backwards and calculate the net present value, it doesn’t get me right to 0:

It certainly gets close to 0 and it’s probably close enough that it can help you make a decision about your investment. However, there’s another way to calculate IRR and that’s using Excel’s What-If Analysis. On the Data tab, there’s a drop-down for this option in the Forecast section:

What-if analysis on the forecast tab in Excel.

Depending on which version of Excel you’re using, it may show a bit differently, but what you’re ultimately looking for is Goal Seek.

Selecting goal seek from the What-If Analysis drop-down.

Goal Seek is an accelerated way of doing trial-and-error. Excel’s doing it for you much quicker than you could ever do it by yourself. For IRR, it’s the best solution.

Here’s how it works. You’ll need to enter the cell that you want to get to a certain value, what value that is, and which cell Excel should be changing values in. In my spreadsheet, E2 is where my net present value formula is, and I want that to equal 0. In cell B2 is my discount rate, which is what I want Excel to be changing. Here are what my inputs look like:

Setting the inputs in goal seek.

Then, once I click on OK, Excel goes to work. After a few seconds you should see Excel show you that the target value and the current value are a match (e.g. they’re both 0), meaning it’s done its job successfully:

Goal seek after completion.

Now, if I look at my template, I see a different discount rate and my total present value is netting out to 0:

Discounted cash flow template after using goal seek to calculate the internal rate of return.

As you can see, this is much more accurate than Excel’s XIRR function. You can repeat these steps and make this table for other projects that you can assess side-by-side.

If you’d like to test this out, try downloading the discounted cash flow spreadsheet from my last post and then just using Goal Seek or the XIRR function to determine your IRR. You can remove unnecessary columns from the sheet and then duplicate the table, and then you’ve got a template where you can assess multiple investments against one another.


If you liked this post on how to calculate IRR in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

sensitivityanalysis

How to Do Sensitivity Analysis in Excel

Sensitivity analysis is a powerful way to make your template or Excel model update to reflect changes in variables. It makes it easy to run various what-if scenarios at once. In this post, I’ll show you how you can conduct sensitivity analysis in Excel in a way that’s user friendly and that can make your spreadsheet that much more versatile.

In this example, I’m going to compare two dividend stocks. One that pays a high yield right now versus one that pays a lower yield but that grows its payments over the years. I’ll look at how long it’ll take for the growing dividend to become larger than the one that’s higher today. I’ll also look at what the projections are when I make changes to my assumptions.

Setting up the analysis

First thing’s first, let’s start with the basic analysis. Once that’s setup, then we can move on to adjusting the variables and setting up the visuals. To make things simple, we’ll assume that the investment in both stocks is going to be a nice, round, $10,000.

Let’s say that in our example, Stock A pays a dividend yield of 3% per year and on average it will increase its payouts by 5% ever year. Stock B, however, won’t increase its dividend payments but it currently yields 7%.

Here’s how much dividend income each stock would generate annually over the years:

Comparing two dividend stock yields.

Under these assumptions, it would take 18 years before Stock A begins producing more in annual dividend income.

All that this spreadsheet is doing is just taking the total investment of $10,000 and multiplying it by the dividend yield for the first year. And for subsequent years, it’s adding on the compounded annual growth rate (CAGR). That will determine what the dividend payment will be after factoring in any increase. With Stock B, since there aren’t any increases, the dividend income remains the same. Stock A, however, increases by 5% every year.

To prove the calculations out: 1.05^18 * $300 = $721.99.

Now, suppose we change these assumptions and say that Stock A’s yield is 4% and that it grows by 6%, and Stock B’s yield remains the same. With those assumptions, it would take just 10 years before Stock A’s yield becomes the larger payout:

Comparing two dividend stock yields in excel.

But rather than updating our model each and every time, we may want to have a quick glimpse as to what these differences will look like at different dividend yields.

Adding in the comparables

Instead of repeating these steps over and over for different stocks, to do a sensitivity analysis, I can quickly compare Stock A against a series of other stocks. For instance, I’m going to keep the assumptions for Stock A the same, and now I’ll simultaneously compare it to stocks that yield 5% all the way to 10%. I’m going to create a column for each percentage and then calculate the difference between that column and Stock A. Here’s how that looks:

Sensitivity analysis of multiple stock yields.

All that I’m doing for these different columns is taking the value from Stock A and subtracting from it the dividend income earned at a 5% yield, at a 6% yield, 7% yield, and so on. The difference between a 4% yield and a 5% yield on $10,000 is just $100 (this the first value under the 5% column). But as the dividend rates rise, that delta grows. At a 10% yield, there’s a difference of six percentage points. That means the non-growing dividend stock pays $600 more in year 0.

One thing that helps a sensitivity analysis chart is some formatting. First, I’ll change the format of these numbers so that negatives show up in red. I can select the cells in the other columns and change their formatting to Currency and select the red option for negative numbers:

Formatting cells to show negatives in red.

I also removed the decimals to save space. Now, it becomes easier to see my data and when the numbers flip from positive to negative:

Applying formatting to sensitivity analysis.

Another thing I can do is add conditional formatting. Color scales can be really helpful here, such as these ones:

Using color scales to add conditional formatting.

Now it’s even easier to see the progression and how it relates from one dividend yield to the next:

Applying conditional formatting to sensitivity analysis table.

You can adjust the formatting to how you prefer. These are just some of the ways you can help your numbers pop out.

Changing your data becomes much easier

Now, what if the stock you’re comparing changes? You’ve found one that pays 4.5% and grows by 4%. You can easily change Stock A and now the rest of the values and the formatting will update:

Changing variables in the sensitivity analysis spreadsheet.

By being able to easily update your base stock (Stock A) and then just see the changes update for all your other comparables, you can easily run through various what-if scenarios on the fly without having to update all your other formulas. That’s where a sensitivity analysis becomes very useful; it prevents you from having to repeat steps over and over to compare different scenarios. It does it all at once for you and avoids the inevitable follow-up questions you may receive in your analysis of what about this scenario or that one.

And Another way to visualize the data, is of course, through charts. And rather than a boring line chart, one that I found particularly effective to demonstrate these differences is the 100% Stacked Area Chart. Here’s how it looks like:

Sensitivity analysis in a chart.

I only mapped the first 20 years. That’ because by that rate, it’ll capture the year when Stock A surpasses the 10% dividend yield. The chart does a great job of showing the size of the differences over the years and just how much longer it’ll take for Stock A to overtake a 5% yield versus a stock that’s yielding 10%. It’s certainly not the only chart that might work. However, it definitely has a nice effect that helps it stand out and summarizes the data well.

If you’d like to follow along, you can download the spreadsheet I created for this example here.


If you liked this post on how to do a sensitivity analysis in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2Epercentages

How to Calculate Percentages in Excel

When doing any kind of data analysis, it’s important to be able to pull in not just raw data but also to show percentages. From a period-over-period percent change to how much an item represents of a total, showing a percentage can give readers multiple different viewpoints. Below, I’ll show you how to calculate percentages in Excel and to give your data more context.

In this example, I’m going to use data from Netflix’s most recent quarterly results. The streaming giant always releases its numbers in a friendly Excel format, making it easy to analyze the data. Here’s what its income statement tab looks like, unchanged for the second quarter of fiscal 2020, which includes previous periods:

Netflix income statement in Excel.

Showing period-over-period changes

Netflix’s numbers look impressive — $6.1 billion in revenue for the quarter ending June 30, 2020. However, that number on its own may not be very helpful. One way to add some context is to calculate the percentage change to show the increase or decrease from a previous period, aka its rate of growth.

I’ll add a column next to those quarterly results and add a formula that shows the percentage difference from the previous quarter (ending March 31, 2020). To calculate the percent change, all I need is to take the difference and divide it by the old number, or base amount. A good way to remember this is: (new-old)/old.

In this example, column Q contains the quarterly results for June 30 and column P is the previous period. And the revenue is in row nine. The formula for the first item looks as follows:

=(Q9-P9)/P9

Where Q9 is the new total, while P9 is the old number. This gives me the following:

Quarter over quarter change.

The $0 isn’t really helpful here, and it’s also not a dollar amount. Excel’s just defaulted it to that format based on the other numbers. To properly show it as a percent change, I need to change it to a % format. It’s as simple as selecting the entire column and clicking on the % sign on the Number tab:

Number group in Excel.

That will now give me the following results, after centering the column:

Quarter over quarter change in percent format.

However, this still may not be ideal. If I want a bit more detail, such as to show multiple decimal places, you’ll again want to go back to the format section and select the item to add decimal places:

Add decimal places button.

Clicking on this button twice will now give me a couple more decimal places:

Percent change with two decimal places.

Now, with my percentage change looking correct, I can copy the formula down for the rest of the items:

Quarter over quarter percent change across all items.

Showing the percentage of a base amount, or grand total

Another way you may want to show a percentage is how much an item makes up of a total. One common way to analyze financial statement is to look at items as a percentage of revenue. A company’s profit margin, for instance, takes its total profit and divides it by revenue to determine what percentage of its top line makes it through to the bottom line.

How to calculate percentages in Excel when just looking at how much an item makes up of a grand total is an easier process. In this example, the calculation just takes the current item and divides it by revenue. The key is just freezing the denominator, which in this case is revenue. Here’s how the formula looks like:

Percent of revenue by line item.

Using the % of revenue analysis, it’s easy to see that operating income was 22% of revenue and Netflix’s profit margin was 11.7%. Replicating these formulas for other periods can help compare multiple periods to see the % of revenue trends. Here’s how the current quarter looks against the previous one when looking at the percent of revenue:

Multiple periods percent of revenue analysis.

Compared to the earlier quarter, Q1, the profit margin becomes a bit less impressive in Q2 as it’s declined from the previous period. Despite a stronger overall Q2 performance, a higher tax bill led to a smaller overall profit margin than in Q1.

As you can see, by adding percentages to your analysis you can create very different viewpoints and add a lot more context to the numbers.


If you liked this post on how to calculate percentages in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

wallet-2292428_640

Paying Off Debt vs Adding to Savings: Analysis in Excel

Excel is a great tool for financial questions and analysis, and one popular one is whether someone is better off paying down their debt vs adding to their savings. There are temptations to both, but this is where Excel can help identify a clear winner.

As much as it is a math question, it’s also a question about opportunity cost. If you’re paying down debt then you’ll incur fewer interest charges, and if you add to your savings then you can earn more in interest income. While both options will put someone in a better financial position, that doesn’t mean that one option isn’t better than the other.

Adding to Savings

Typically, banks pay interest rates of around 1% at best, unless there’s a promotional period. But generally, you aren’t making much when it comes to your savings. However, if you’ve got an amount of $5,000 to add to your savings, then that will produce an extra $50 per year at a 1% interest rate, or about $4.17 per month. It’s by no means huge, but it’s still incremental income that can help grow your savings over time.

Paying down debt

If you’ve got credit card debt, however, you’re probably paying anywhere from 15% to 20% in interest. There are exceptions, but most credit cards will likely fall within that range. On a credit card with an interest rate of 20%, that’s going to be an annual interest charge of $1,000 on a $5,000 balance. And so by paying down off a card at that rate, you’ll be saving a lot in expenses.

Difference in interest rates is key

It’s pretty clear that it all comes down to the difference in interest rates, and that’s why paying down debt will always provide you with more of a benefit than adding extra cash to your savings will. While it might be tempting to add to your savings, the reality is that you’ll likely be losing in the long run.

Using a color scale in Excel, I mapped out the difference in interest rates by the size of the possible payment. It’s a very linear relationship: the higher the differential and the higher the payment, the more money you’ll lose by putting money into savings rather than paying off debt:

In the example above, the difference in interest rates was 19% (20% in credit card rate less the 1% in savings rate). Multiplied by a $5,000 payment, that would have resulted in an annual loss of $950. Rather than avoiding $1,000 in interest charges, I would have made just $50 in interest income ($1,000 – $50 = $950 opportunity cost). On the matrix above, you’ll notice this is also the amount that is at the intersection of 19% and $5,000.

Summary

This isn’t the most groundbreaking discovery but it’s one that can be visualized well using Excel. After all, this is a numbers problem, nothing else. While some people may argue there are other reasons to add to savings such as having cash for a rainy day, the reality is that whether you add to your savings or free up room on your credit card, you can access funds from either option. And in the meantime, if you add to savings, you’re still incurring the interest charges. To make matters worse, the interest income you earn may be subject to income taxes, which will further erode the benefit of adding money to savings. Interest expenses on credit cards likely won’t be tax-deductible.

Overall, there really isn’t a strong argument for contributing to savings instead of paying down debt. It’s comes down to what interest rate you’re paying vs what rate you’re earning. And I’ve yet to see anyone earn more in interest than they’ve had to pay on a credit card.

The post is not a substitute for financial advice and is only intended to show how an analysis in Excel can be done when comparing two different options – in this case, paying down debt vs contributing to savings.

You can download the file that I used in these calculations here.


If you liked this analysis on Paying Off Debt vs Adding to Savings, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.