When you have created formulas in Excel, you may want to extend and increment them down to other cells. In many cases, you can just drag down a cell that contains the formula and pull it down so that it automatically gets copied. However, there’s a problem if you need to increment a formula or number in an argument. Below, I’ll show you an example of this and how you can address it in multiple scenarios.
Let’s start with just pulling the largest value in a data set. Using the LARGE function, you can grab the largest value with a formula of =LARGE(A:A,1) — that will give you the largest value in column A. However, you’ll notice quickly that just copying this formula down won’t work as expected, it’ll keep copying the 1 in the second argument down; you will continue extracting the largest value. Sometimes Excel just isn’t good at detecting patterns. But there are ways you can adjust the formula to ensure your formula is incrementing correctly.
Option 1: Using an Additional Column
In a separate column, you could list the numbers 1 until 100 or whatever range you needed to and reference those. In the example below, I can pull the largest 10 items using the LARGE function and a referencing the cells to the left:
The problem with this approach is that it isn’t always practical within a spreadsheet, especially if you don’t have room to spare and don’t want to be creating extra columns for the sake of what should be a simple calculation. It may also be difficult to determine what the upper limit should be. That’s where the second option is going to be a better one.
Option 2: Using the ROW Function
There’s an easy way to adjust the above calculation so that you don’t need to reference the actual number, nor do you need to manually enter it. The trick is to use the ROW function. It returns the row value of a specified cell. If I entered =ROW(A20), it would return a value of 20 for the row value. You don’t need to enter an argument and using =ROW() will just return the row of the cell that contains the formula. The function is particularly useful when you want to increment values because as you drag it down, the results will increase by one each time.
In calculating the largest values, we’ll use the ROW function as the argument to determine which value we want, whether it’s the fifth largest or 20th largest number. Here’s how the initial formula would look like, assuming we wanted to start with the largest value:
=LARGE(D:D,ROW(A1))
In the above formula, the calculation will return the largest value since ROW(A1) will return a value of 1. Now, if the formula is copied down, here’s what it will look like:
By copying down the formula, ROW(A1) changed to reflect the next row. Excel does a better job of detecting patterns when they’re part of a function as opposed to just standalone numbers. Using the ROW function eliminates the need for the extra column. And as you’ll notice, the results are the same as in the earlier example.
This is just one example and there are many others where you can use the ROW() function to your advantage and increment values down. If you need to increment as you’re going to the right instead of down, then you’ll want to use the COLUMN function, which effectively does the same as the ROW function, only it’ll pull the column number that a cell is in.
If you liked this post on how to extend and increment formulas 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.
Merged cells are great for presentation purposes but not so ideal when you’re creating a model or a complex spreadsheet. It can make copying cells or formulas painful as you can get an error or unintended results when doing so. However, there’s a way that you can achieve the same result as a merged cell without actually having to merge anything.
Align cells rather than merge them
When merging cells, you would select the cells you want to merge, and then click on the following button:
What happens in that case is that the cells you selected now become one. In the below example, if I click on Merge & Center, the text would get spread across multiple cells:
And become this:
By doing so, I can no longer enter a value individually in cells B1:F1 as the merged cell effectively takes over. If you never plan to copy over these cells then that’s fine but if you do, then you could end up with some annoying errors along the way.
However, there’s an easier, less intrusive way of accomplishing the same result. I would still select cells A1:F1 but instead of using the Merge & Center button I’ll go into the cell formatting menu (CTRL+1) which will give me the following options:
From here, I’ll adjust the Horizontal Alignment and select Center Across Selection:
Now, my text is spread across those cells but it isn’t merged, it only looks that way. For example, I can still click individually on cell B1 and enter data there:
And if I do that, it takes over the center alignment and pushes back the other text into cell A1:
The advantage of using an alignment rather than merging cell is this avoids the errors that can come when you’re modifying or copying data and you get notifications that the cells are not the same size.
By using the horizontal alignment, you have the flexibility of making your text appear as if you’ve merged it across multiple cells without you needing to unmerge data later on if you want to change the text or add data.
If you liked this post on an alternative to merged cells 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.
Are you looking for an easy way to log and track your time in Excel? Below, I’ll show you how you can keep track of the time you spend on tasks without the need for a complicated template or to even open up Excel every time to enter in your time. With a combination of Excel and Notepad, you have all the tools you need to quickly and easily track your time and create a log in Excel.
An easy trick to turn Notepad into a log
To make the process of logging time easy, you probably don’t want to have to open up an Excel spreadsheet each time. There’s an easier way to do so and that’s by using Notepad. Open up a new instance of Notepad and write the following in the first line:
.LOG
Save the file as whatever you want, and then close it. Open it up again and you’ll notice there is now a timestamp when you open the file. Because you entered .LOG at the start of the Notepad file, it will now automatically create a timestamp each and every time that you open the file.
Now, when you’re working on a task, just enter in some text, such as “working on Excel,” click save, and close the file. Now, when you’re switching over to another task or want to say that you’ve finished the task, open up the text file again and enter in a new entry. You probably don’t need to say that you’ve ended a task since the start of a new task would effectively tell you that the previous one is over.
The key thing to remember when you’re logging your tasks in Notepad is that you’ll want to save the file once you’ve made an entry, and then close it out. A good place to store the file might be online or on a shared folder, somewhere that you can access it from any computer and that you can easily update from wherever you are. As you keep adding to the log, you’re essentially creating a database of all your entries.
You can create multiple log files depending on what you’re tracking or you can just keep one big list in a single text file. Either way, once you’ve made some entries, what you can do is now extract that time log in Excel, which brings us to the next step:
Pulling the data into Excel
The text file, while useful, isn’t going to be terribly helpful if you want to easily see the time you’ve spent on a given task. This is where Excel can be incredibly useful. To get the information into Excel, go onto the Data tab and import data using the From Text/CSV button.
You can leave the default settings and Load the data as is as it’ll likely leave all your text entries in vertical form, which will still work for our purposes. Here’s a sample of what my log file looks like after importing it into Excel:
If you’re using one of the newer versions of Excel that includes PowerQuery, a connection is created when you import the text file into your spreadsheet. This prevents you from having to re-import the file manually each time to check for changes. You only need to refresh the data and it will pull in the changes for you.
And if you make additional entries to your text file, save it, and refresh the data in the spreadsheet and it will update. Just simply right-click on one of the entries in column A, select Refresh, and the data will update from the file — as long as it remains saved in the same place.
If, after an hour I make another entry to make log file and click on update in the Excel file, the information is up-to-date without having to initiate another import process:
This is where Excel is very powerful and effective in making it easy to pull data from another file. However, the data isn’t in a form that’s terribly useful to us in the form that it’s in now. Let’s move on to the next part: setting up the template in Excel so that the time log will be a lot more user friendly.
Creating a template to populate the information correctly
The data is in column A, and what I’ll do is create headers in columns C:F for the Start Time, the Task itself, the End Time, and the Duration (in minutes). Here’s what that looks like:
Now, I’ll need to enter in formulas to populate all those fields. The start time field will initially pull from the third row in column A, and then it will grab every second row after that. So let’s start with building out that logic.
I’ll start with using an INDEX() formula to pull a value from column A. Since there’s only one column I’ll be extracting data from, the key argument is going to be the row number. The third row is where my first entry is, so for the row number I’ll start with the number three. Here’s what my formula looks like thus far:
=INDEX(A:A,3,1)
I select row 3 and column 1. This will only work for the first value. I need to adjust the formula so that it will automatically adjust based on which row I’m on, so that it knows to take either the first time entry, the second, the third, and so on. The ROW() function is helpful in this case because it will return the row number of the current cell. And since my first entry in the table will be on the second row, I’ll want to remove the first two rows. My row calculation looks like this right now:
3+ROW(C2)-2
For the first entry (on the second row), this will evaluate out to 3, since ROW(C2) will equal 2 and it will minus 2 from that. This still works for the first entry, but if I were to copy this formula down it would not give me the correct result for other entries. For instance, in row 3, the formula would be as follows:
3+ROW(C3)-2 this would evaluate to 3+(3)-2 = 4
But row 4 contains my task description, not the next timestamp. I need to double with each row I go down. I need to adjust my formula for the row calculation back in C2 to be as follows:
3+(ROW(C2)-2)*2
Now, the row number minus 2 will then multiply by 2. If I copy this formula down to cell C3, it’ll look as follows:
3+(ROW(C3)-2)*2 : this would evaluate to 3+(3-2)*2 = 3+(1)*2 = 5
This returns row 5, which is the next timestamp in column A. If I copy the formula down to row 4, then it will return the 7th item in the column, which is again the next timestamp. Now that the formula is correctly returning each odd-numbered row, I can use this formula for the template I’ve created. My full formula in column C2 looks as follows:
=INDEX(A:A,3+(ROW(C2)-2)*2,1)
This will work not only for the initial timestamp but it will also extract entries that come after it. All you need to do is copy the formula down.
I can replicate this for the Task field in column D. The only change I need to make is to use row 4 as my starting point rather than 3. And so my formula for the task column looks as follows:
=INDEX(A:A,4+(ROW(C2)-2)*2,1)
For the end time, I’ll use row 5 as my starting point. The end of one task will be the same as the start of the next task. And then all that’s left is to calculate the task duration in column F. The calculate the difference in times, I’ll start by taking the end time and subtracting the start time. However, this will give me a decimal that isn’t very easy to interpret:
The reason is that Excel converts this into a fraction of a day. A two-minute interval is less than 1% of the 1,440 minutes that are in each day, which is why the number is so low. To convert the duration into hours I can multiply it by 24, and then the number changes to 0.033, which is the fraction of an hour that two minutes represents. But if I want to go further and convert this into total minutes, I’ll multiply this again by a factor of 60. Now my formula looks as follows:
=(E2-C2)*24*60
Now, after rounding off the decimal points, my duration calculation in column F correctly gives me the number of minutes between the start and end time of a task:
The table is now set up and you can just copy these formulas down to accommodate more entries. You’ll end up with a series of zeroes if there’s not enough data in column A. If you want a cleaner solution, what you can do is use the COUNTA() function to determine the number of rows that are in column A and determine whether to apply a formula or not. For instance, in my example, my data goes until the 8th row and so my formulas look fine for the first two entries but after that, there is no end time for the third task and the subsequent entries are full of zeroes:
It’s not a terribly elegant solution at this point. To get around this, I’ll create a rule for each column to say that if there is no entry, it will be blank. For the start time, I’ll add the following to the beginning of the formula:
IF(COUNTA(A:A)<(3+(ROW(C2)-2)*2),””
This will check if there are enough rows in column A to extract a value for the current cell. If not, the value will be blank. Here’s how the full formula looks in cell C2:
For the duration calculation, I will check to make sure there are values in both the start and end time, otherwise, the value will be blank:
=IF(OR(C2=””,E2=””),””,(E2-C2)*24*60)
With these formulas now set up, I can copy them down hundreds of rows down if I want and they won’t result in a series of zeroes or errors:
The data in Excel will now auto-populate as I add more entries to the time log and at the same time it won’t be an eyesore if there is incomplete data.
If you liked this post on how to create a time log 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.
Spreadsheets can allow you to analyze data and create reports efficiently. But sometimes the tasks that are involved can be difficult or appear to be time-consuming. The good news is that there’s a lot of automation you can achieve in Excel, and it isn’t always necessary to know how to code in order to do so. Below, I’ll show you 10 types of tasks that you can automate in Excel either on your own or with our help.
1. Cleaning and parsing data
One of the more challenging things in Excel is when you’re dealing with a dataset that may not be easy to manipulate. For instance, if you’ve got text mixed in with numbers or dates that aren’t in the right format, Excel may not interpret or recognize the data properly. But there are many formulas that can help you with that. Rather than manually fixing the data, you can use functions like TRIM, CLEAN, LEFT, MID, and RIGHT to extract what you need while also getting rid of extra, unnecessary spaces and other characters.
If you’re looking for more of a walkthrough of the process, there’s a detailed explanation in this post of how to parse data.
Through the use of formulas, you can save hours that you might otherwise spend trying to clean up your spreadsheet. And the best part is that once you’ve set it up, you can re-use the formulas as you add more data. You don’t need to use macros or complicated coding to clean up; a well-structured template can be enough to do the job for you.
2. Creating simple reports
One of the best features of using Excel is that once you’ve entered data into a spreadsheet, it’s even easier to create a report from it. One example is through the use of a pivot table, where through just a few clicks you can easily summarize your data and split it along different categories. Slicers can make filtering and summarizing data even easier in pivot tables, especially for users who aren’t very familiar with Excel. Forget any manual work here; just a few clicks and you’ve got a report that can quickly summarize information in a table for you!
Alternatively, you can also insert charts easily and Excel will try and select the best one based on your data set. There’s also lots of formatting you can apply to charts so that they have the look and feel that you’re after. And once you’ve got a look that works, you can re-use it over and over again.
3. Creating dynamic dashboards
Dashboards are incredibly popular but they can be complex to set up. Then there’s also the challenge of updating it and making sure the data is up-to-date. It can easily take you hours every time to make sure the information is accurate.
However, in this post, I show you how to create a dynamic dashboard that not only won’t take you hours but that will automatically update as you add data to it. And then, you end up with a report that looks great to send to management to easily review and update.
4. Routine data entry
One of the biggest headaches people can face when using spreadsheets is when they hard-code calculations. A hard-coded calculation is where you don’t reference any cells and just put the result in the cell; it can make it nearly impossible to decipher how that number was calculated (especially if you’re not the person who entered the value). If you go to re-calculate it or update it, you could spend a lot of time just trying to figure out the calculation.
However, by using a formula, there’s no ambiguity as to how a value was calculated. Not only does that save you the time of entering in data but it also makes it easy to correct and update the figure. Ideally, you should minimize the number of places you’re manually entering data into. By doing that, you’ll have a much more robust template where your inputs are kept to a minimum which will eliminate the need for a lot of data entry and your other calculated fields will update automatically. This type of automation doesn’t require complex coding and just needs an Excel spreadsheet to be carefully constructed so that it is efficient and makes the most of formulas.
5. Conditional formatting
Oftentimes you’ll want to color-code your data to highlight things you should be paying attention to. If you’ve got an aged accounts receivable schedule, it is useful to highlight which accounts are more than 90 days overdue. You could manually filter the data and highlight all the cells or rows in red that are overdue, but you can just use conditional formatting to do that for you.
Through conditional formatting, you can create rules to determine when a cell or row should be highlighted in red, when you may want it to be in yellow, or when you may just want to hide the text so that you can easily skip over it. For example, hiding zero values can make it easy to focus on more important numbers.
You can apply many different formatting rules and can even put in a hierarchy to determine if you want to keep applying formatting rules or whether you want to stop if a specific criteria is met. Conditional formatting can be complex but it can be a huge time-saver by allowing you to focus on just the items that are important to you. And once you’ve set up the rules, you don’t need to worry about making changes every time you add new data.
If you use multiple workbooks, then another area where you can avoid re-entering data is by linking both workbooks. There are numerous ways that you can do this. One approach involves just linking directly to another worksheet where data will automatically pull from another table.
You can also use the INDIRECT function to reference another worksheet or workbook. Just like with a template, once you set up these formulas and connections, they are there to stay and you can avoid having to manually make changes by yourself.
7. Audit tracking by logging changes
One of the neat features of many Office products is they allow you to track changes that are made. This is normally when you share a workbook with other users. However, through the use of macros, you can have a separate sheet that can tell you which values were changed, when, and by who.
Rather than manually noting these changes or relying on people to make the updates themselves, it doesn’t take much effort through a macro to create a log of what’s been changed.
8. Generating PDFs
One thing many advanced Excel users like to do is to use automation to export reports into PDFs. While there is a way to print to PDF, and it’s particularly easy on the newer versions of Excel, it can be a time-consuming process especially when you need to print out multiple sheets. Here again, with a simple macro, you can auto-generate PDFs and save them in a predefined folder all with the click of a button.
9. Sending emails
Another feature many users like is the ability to use automation to send out emails right from Excel. Through the use of macros, this is also possible. You can create a macro that will enter in the email of the recipient, attach a file, enter the body of the message, and even send the email itself. This can be even set up on a large scale, such as sending out invoices to dozens or hundreds of customers, a process that could easily save you hours worth of work.
10. Just about anything else with VBA
The power of programming in Excel can unlock many different possibilities with what you can automate. Whether it’s using automation to help import data and then manipulating it, creating custom reports, or following a series of complicated steps, there are many tasks in Excel that can be expedited with a few clicks of a button. As long as there’s some logic to the process that you can break into steps, then you can also build that into the code and automate it.
Don’t know where to start? Contact us!
There is significant potential in Excel but not everyone knows how to use automation to make the most of it and to make a spreadsheet as efficient as it can be. You can contact us if you have a certain Excel issue that you need help with or if one of the tasks above has perked your interest and you’d like to learn more. We can help create solutions for you that work efficiently and that can save you many hours, perhaps even days every month.
If you liked this post on 10 Tasks You Can Automate Today, 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.
Do you want to know how to calculate how many hours and minutes have elapsed between two times? Below, I’ll show you how to do that with Excel formulas. The difference won’t be in just fractions of days or hours but real minutes and hours that make it easy to measure. To make this work, however, the first step is entering in the time correctly.
How to enter time in Excel
Excel has a built-in time function called TIME where you can enter the hour, minute, and second. For example, if you wanted to enter a time of 6:30 AM, you could enter a formula of TIME(6,30,0). Alternatively, you could type in 6:30 AM — the key is leaving the space between the time and the AM/PM indicator.
However, the one clear limitation here is that this won’t help you if you want to calculate the hours and minutes between times that span more than just one day. By using the TIME function, or entering just the hours and minutes, Excel is always going to assume you’re talking about the current day.
The best way to enter in time is to also factor in the date. Depending on your regional settings you might enter this differently, but this is how I’d enter a time of 8:00 PM on Nov. 20 on my computer:
2020-11-20 8:00 PM
I can also use a 24-hour clock and type in the following:
2020-11-20 20:00
Either way, Excel knows what time I’m talking about. If you always want to return the current time, you can use the NOW function.
For the start time, I’ll set it to the start of the year: 2020-01-01 0:00
Calculating the difference
Just using the minus operator, I can get the difference between these two dates as follows:
By default, Excel will return the number of days, including the fractional days as well. To convert days and calculate the hours instead, we will multiply the difference by 24, since that’s how many hours are in a day. That gives us the following:
That’s 7,796 hours between those two dates and times. It’s a nice round number but what if we changed it so that the end date was at 8:30 pm, or 20:30, this is what the updated calculation would look like:
Now I’ve got that residual 0.50 which indicates half an hour. But I want minutes, not fractions of an hour. The easiest way to do this is to create one calculation for hours, and another for minutes. Then, afterwards, you can concatenate them together. To get the total hours, I’ll adjust my formula to include the ROUNDDOWN function so that it does not include the 0.5. It looks something like this:
=ROUNDDOWN(datedifference*24,0)
Where datedifference is that raw calculation between the two dates and times. In my calculation, I’m still multiplying the time difference by 24 to get to hours, and then I round that to 0 spots, which is indicated by the 0 in the second argument. Now it will only show 7,796.00 for hours.
To calculate the number of minutes, I’ll need to multiply the datedifference by 24 and then again by 60, to convert the difference into minutes. This is what my calculations look like thus far:
My hours are nicely rounded but my minutes include the total minutes, which is not what I want. I only want the minutes that are left over after the hours are factored out. Here I can make use of the MOD function which will tell me the remainder after division. I’ll adjust the minute calculation to calculate the remainder after I’ve divided the total minutes by 60. This will determine what’s left over after pulling out full 60-minute hours, which is that residual 0.50 that I’m after. Here’s what this formula looks like:
=MOD(datedifference*24*60,60)
That gives me the following result for minutes:
Now I get a nice and round 30 minutes. There is the potential that I can also get partial minutes if I have seconds in my calculation. This could be the case if I’m using the NOW function. To correct for this, I can again use the ROUNDDOWN function as I did for hours.
However, let’s assume that you also want to track seconds. We can do that as well. I’ll break out another column for seconds. There, I’ll multiply the difference by another factor of 60, to get the following:
I added 25 seconds to my end date, which is why you’ll notice there’s a slight change in the difference column from this screenshot and the earlier one. Right now, total seconds tells me there are 28,067,425 seconds between these two times. If I want to get the raw number of seconds, then I’ll again use the MOD function and again use 60 as a divisor, since now I want to factor out the minutes:
I now have a clean breakdown between hours, minutes, and seconds between these two times. But if you want to calculate more than just hours between two times, you can also incorporate the number of days as well.
Breakdown of days, hours, minutes, and seconds
If I wanted to take a different approach and break the difference down by days, and then by hours, minutes, and seconds, I’ll first need to break out the days. Since that’s the default calculation for Excel, all I need to do is use the ROUNDDOWN function on the difference. The formula is as follows:
=ROUNDDOWN(difference,0)
And that gives me this:
If I wanted to get the hours that are remaining, what I can do is take the difference, use the MOD function, but this time I’m using a divisor of just 1, since I really only want the decimal place after the full number. Then I’ll multiply that by 24 hours, and again, use the ROUNDDOWN function:
=ROUNDDOWN(MOD(difference,1)*24,0)
Now my hours total looks like this:
I’ve got a nice round 20 hours, which makes sense since 8:00 PM is 20:00 on a 24-hour clock. To calculate the difference in minutes, I can revert back to the earlier calculation where I used the MOD function to determine what’s left over after multiplying the difference by 24 and 60, and then dividing it by 60 minutes:
The seconds calculation will work the same way as well. The only difference in the way to break out hours and days was to adjust the hours calculation to ensure it isn’t taking in the full hour difference, only the residual amount that pertained to the current day.
Now that you’ve got all the chunks broken down between days, hours, minutes, and seconds, you could concatenate that into one large formula, Something like this might work:
=CONCATENATE(daydifference,” Days “,hourdifference,” Hours “,minutedifference,” Minutes “,seconddifference, ” Seconds”)
That produces the following:
For this not to be a messy result, you’ll want to ensure you’re using the ROUNDDOWN function in each of those calculations so that you aren’t keeping any trailing numbers.
If you liked this post on how to calculate hours between two times, 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.
An amortization table is a useful tool when you need to calculate interest payments, principal payments, and to track the balance that’s owed on a loan. However, you don’t have to create a full schedule to get these values and below I’ll show you two functions that can get you that information quickly and easily. First, let’s start with what a typical amortization schedule looks like.
Creating the amortization schedule
When you set up an amortization schedule, you’ll track the balances, interest, and principal payments. It often looks something like this:
You could use the table to determine what the balance is at the end of period 10 or to add up all the interest payments up until that point. However, there’s another way to arrive at those totals, and that’s using two functions that are available in the newest version of Excel: CUMIPMT and CUMPRINC.
Using the functions
In the amortization schedule, we can see that the ending balance of the $100,000 loan by the end of period 10 is $85,016.67. We can use the CUMPRINC function get to that total as well. The function takes on the following arguments:
To calculate the cumulative principal payments, I’ll enter the formula with the following arguments:
=CUMPRINC(0.05/12,60,100000,1,10,0)
This gives me a total of -$14,983.36. When added to $100,000, it nets out to a balance of $85,016.64 — within just a few cents of the amount on the amortization table. The function gives you the flexibility to specify which periods you want to extract and so you aren’t limited in just tabulating the totals for the first 10 periods or starting from the beginning. You can start from period 13, or the second year, and so on.
If you want to calculate the total interest payments, then that’s where you can use the CUMIPMT function. It has the same arguments as the CUMPRINC calculation, so the formula will look very similar to what’s above:
=CUMIPMT(0.05/12,60,100000,1,10,0)
This tells me that the cumulative interest payments during the first 10 payment periods is $3,887.87. This matches what I would get by adding the interest payments in my amortization table over the same period, this time to the penny.
Should you use these functions instead of an amortization table?
On older versions of Excel, you won’t have access to these functions but if you’re using Microsoft 365 or Excel 2019, then these functions are available and can potentially serve as replacements for an amortization table. Now, if you need the table for audit purposes it may not be possible for you to do without an amortization table completely. But if you’re only generating the table just to determine how much you’ve spent on interest or what your balance will be at some point in the future, then these functions can certainly replace doing a full-blown amortization table.
If you liked this post on 2 Excel functions that can eliminate the need for an amortization table, 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.
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:
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:
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:
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:
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:
Then, after clicking on OK, my values look like this:
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:
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:
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:
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.
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:
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:
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.
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:
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:
Now, if I look at my template, I see a different discount rate and my total present value is netting out to 0:
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.
Do you need to calculate the present value of future cash flows or assess two options that will impact your cash flow over many years? Excel’s a great place to do that and below I’ll show you how you can easily set up a template to calculate discounted cash flow that you can adjust for changes in the discount rate and cash flow. And if you don’t want to create your own template, you can download mine at the bottom of this post.
In this example, I’ll compare a lump sum lottery win versus a scenario where you receive an annual amount for 25 years. Step one is knowing to calculate present value, which is what I’ll cover next:
Calculating the preset value
To calculate the present value of future cash flow, you need to know what discount rate to use. What you can use is the rate that you can earn on a typical investment. For instance, if you invest in stocks and assume you can make 5% per year, on average, then you might want to use that as your discount rate. If you want to be more conservative, you could use a rate of 2%. Below, you’ll see how the discount rate can play a big impact in your calculations.
That’s because when calculating today’s present value, you have to use the discount rate to bring the future value back to what it would be worth today. For example, suppose you were to receive a $10,000 payment a year from now, and your discount rate was 5%. An easy way to calculate this is as follows:
You might see other formulas on the web involving fractions to calculate present value but just using a negative power does the trick. This calculation yields a result of $9,523.81. Because you’re not getting the payment today, the value of that money is worth less than the full amount. Consider that if you were to receive $10,000 today and invest it and earn 5%, then a year from now it would be worth $10,500 — more than if you were to receive the $10,000 in a year.
Now, suppose you used a discount rate of just 2%. In that scenario, the $10,000 payment a year from now would be worth $9,803.92 today. Since the discount rate is lower, there’s less of a cost associated with waiting for your payment. If the discount rate was 0%, then there would be no incentive for you to invest your money since a year from now it would still be worth the same value it is today. That’s why when interest rates fall and get closer to zero, people will be less inclined to keep their money at the bank and there’s more demand for gold — since that can be a better way to store wealth at that point.
Creating a template to calculate discounted cash flow in Excel
Now that we’ve gone over how to calculate discounted cash flow in Excel, we can set up the template. All that’s really necessary here is to map out the payment schedule, including how much cash you’ll receive every year. Here’s an example scenario of receiving $100,000 for 25 years:
All the payments don’t have to be the same, but for the lottery example, I’m going to keep them that way. What I can do is create another column that will tell me the present value of each one of those payments. To do that, I’ll use a formula that takes the cash flow value, multiples it by the discount rate (I’ll use 5%) raised to a negative power (the year). Here’s how that looks:
I created a discount rate named range so that it’s easy to reference the percentage and to change it. The only thing left here is to calculate the total of all these payments, to arrive at the present value of all of them:
The total present value of the payments comes in at just over $1.4 million. Even though the total of all the payments over 25 years is $2.5 million, we’re losing a lot of that value because of the time value of money, at a rate of 5% per year.
However, let’s prove this out, and to do that let’s look at the future value of all these payments. Let’s assume that these funds will be reinvested and earning a rate of 5% every year. Here’s how much we’d have by the end of year 25:
In this situation, we’re benefitting from compounding and earning 5% on each year’s ending balance, which includes the prior-year return. By the end of year 25, if we were to invest all of these $100,000 payments at a rate of 5%, we’d have a future ending value of $4,772,709.88.
Now, remember, the equivalent of these annual payments is a present value of $1,409,394.46. Let’s assume that rather than receiving annual payments of $100,000, we simply receive a lump sum payment of this and invest it and also earn 5% every year. Here’s how that will look like:
The ending value after 25 years is the same, $4,772,709.88. This tells us that if you’re given the option of 25 annual payments of $100,000 or a lump sum of $1,409,394.46 today, there’s no difference to you (if the discount rate you’re using is 5%). If the discount rate is 2%, then the present value climbs to $1,952,345.65.
As you can see, depending on which discount rate you use, it can have a significant impact on your present value calculations. This template will allow you to quickly change the discount rate and see how the calculation looks under different scenarios. You can also add more years to this calculation by just extending the formulas down. The amounts also don’t need to be identical, they were only set up this way purely for the purpose of comparing lottery winnings in a scenario where you earn one lump sum amount versus equal payments over multiple decades.
If you’d like to download this template to follow along, the free version is available here, which goes up to year 15. For the full and unlocked version, which has no ads and goes up to 30 years, please refer to the product page here.
If you liked this post on how to calculate discounted cash flow 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.
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:
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:
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:
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:
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:
Another thing I can do is add conditional formatting. Color scales can be really helpful here, such as these ones:
Now it’s even easier to see the progression and how it relates from one dividend yield to the next:
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:
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:
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.
