template Archives - Page 2 of 7

Free Stock Trading and Tracking Template in Excel

November 15, 2022

by howtoexcel investing stocks template

***UPDATE: There is a newer version of this template available here****

For a limited time, I have a free stock trading template that is available for you to download in Excel. If you buy and sell stocks often, this is a template that will help you easily enter in your trades and track your performance.

How to use the template

There are three main buttons on this template that you will need to use, all of which are located within the ribbon, under the Trading Journal group:

Ribbon buttons on the stock trading template.

The Enter Transaction button will allow you to post and trades you make. Clicking on it will show the following pop up:

Dialog box allowing a user to enter a transaction.

At the very top there is a Date Picker button which will allow you to select the date of the transaction, rather than manually entering it. Here is an overview of the other fields;

Ticker: this is simply the name of the stock you are buying.
Action: indicate whether you are buying, selling, or adding to an existing position.
Strategy: select your strategy for this transaction. If you want to add or remove available strategies, click on the Update Strategies button.
Shares this is the number of shares you are buying or selling.
Price: this is the price per share for the transaction.
Fees: the amount of any fees you have incurred on the transaction.

Once all this is entered, click on the button to Post Transaction and the details of the transaction will be posted to the Log sheet and an entry will be opened up on the Transactions tab. The Transactions tab will show you a summary of your positions, including any profits and losses, and whether they are open or closed.

There is a Settings tab where you can manually adjust strategies. You can also specify different holding categories should you want to track that (e.g. what constitutes a short, medium, or long holding period).

Once you’ve entered some transactions, you can head over to the Summary tab which will give you a visual breakdown of your investments. To be sure the data is updated correctly, click on the Refresh button in the ribbon button (not the default refresh option in Excel).

Stock template showing a summary of performance.

You can use slicers in the report to filter by different dates, strategies, and holding periods.

You can download the Stock Trading Template here.

If you like this Stock Trading and Tracking 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.

Free World Cup 2022 Prediction Template and Schedule

October 11, 2022

by howtoexcel with No Comment free free template template

The World Cup is starting next month and I have a free template for you to use whether you want to track the matches or make predictions with a group of friends. The entire schedule is downloaded within the file, and you can enter the the scores — both actuals and predictions.

How the template works

There are four tabs on the template:

Actuals: This sheet name needs to remain intact as when comparing your predictions, this is the source data that the prediction results will compare against.

Prediction.Blank: This is a blank sheet that is the same as the actuals that you can use for making predictions.

Prediction.Results: This is where you should post your final prediction results. Column A (name) is for the name of the person whose predictions they are. In columns B:D, you just need to copy those fields from the Predction.Blank page for any predictions you or someone else makes. It’s important the values are in the correct fields for everything to be recorded correctly. Also, make sure to paste them as values to ensure the formulas aren’t being copied over.

I’ve also added a section below the main prediction table that shows the teams you predicted to advance to each elimination stage:

This should also be copied over to the Prediction.Results tab. The stage should go in column B and the team will go in column C. I’ve left an example of how the Prediction.Results tab should be filled in within the template so that you can follow along. The table will automatically calculate and compare against the actuals.

Scoring.Rules: Here you can set up how points are allocated if the score, result, and total number of goals are correct. You can also specify if the teams need to be correct (applicable for knockout rounds) and how many points to assign if you’ve got the right team in the right elimination stage.

The basic idea is that you can make a copy of either the Actuals or Prediction.Blank sheet and give to someone to fill in. Once you get back their predictions, you can paste them into the Prediction.Results table. Then, once the tournament begins, you populate the Actuals. After that, it’s just a matter of updating the pivot table on the Prediction.Results page to see who has the most points.

Enter scores in the 0:0 format

One important item to note is that when entering scores, there should be a number followed by a colon, then by another number. There should be no spaces any no different characters, otherwise you will get an error and the value won’t be read properly.

In the case of shootouts and extra time, there is no extra field to enter these values in. For the purpose of minimizing the complexity of formulas, only one column is used for scores. The workaround for this is if a team wins in extra time or a penalty shootout, simply add 1 to their score so that instead of entering it as 1:1 (5:4) it would just be 2:1.

Tables will update automatically

The benefit of using the template is that the tables will automatically update based on your selections. This will help you in determining which teams play one another in the knockout rounds.

The template does factor in tiebreakers but in the event that a rare situation comes up where the tiebreaker doesn’t calculate correctly (e.g. it comes down to fair play points) then you have the option to override the values.

Next to the tables, there is another table for overrides:

In this example, I have overridden the results so that these will be the standings for Group B regardless of what the spreadsheet has calculated. You don’t need to list every team in a group and only need to list the teams that are tied. You also don’t need to specify the group letter.

Adjust the times for your time zone

Another feature that I’ve added here is to adjust the game times for your particular time zone. On the left-hand side you can specify how many hours you need to adjust for GMT. Remember that the value here will need to factor in for any daylight savings that may be in effect for your location (Qatar doesn’t adjust its time).

For example, in my region, my GMT adjustment is -7 but after factoring in for daylight savings in November, it becomes -8. So please remember to adjust for any daylight savings adjustment that might happen between now and the start of the tournament. For GMT -8, the local time would adjust by 11 hours (Qatar is +3), and so a match starting at 7pm local time would be playing at 8am in my time zone.

Highlight teams with a Watchlist

You can also highlight teams you want to track on the schedule by adding them to a Watchlist next to the schedule. For example, suppose I want to follow any games involving Canada and the USA:

World cup schedule shown with matches highlighted.

Any values that are entered under the Watchlist will highlight corresponding matches on the schedule. For this to work, you need to enter the team names the same way that they are spelled out on the match schedule.

Download

This template is available free of charge, and you can download it here. It is locked to ensure that the formulas remain intact and nothing gets accidentally erased or overwritten.

If you like the Free World Cup 2022 Prediction 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.

Free Excel Template to Track Berkshire Hathaway’s Portfolio

September 13, 2022

by howtoexcel with No Comment free free template investing stocks template

Warren Buffett fans like to track the billionaire investor’s moves, and a good way to do that is through Berkshire Hathaway’s 13f filings. His company reports its holdings every quarter, showing where there were changes in its positions. By looking at multiple filings, investors can track the changes from one period to another. With this free template, you can do that quickly and easily all on your own. All you have to do is specify the specific filings that you want to compare to one another.

How the template works

This template uses PowerQuery to grab the data from the 13f filings. It will then compare the two to find the changes in share count.

There are only two inputs you need for the template, both are on the Current.Holdings tab. You’ll need to paste the URL for the current 13f filing and the previous one (or whichever filings you want to compare against one another). It can sometimes be a bit tricky to get exactly what you’re after. Here’s how you can find the 13f filings for Berkshire Hathaway to use in this template:

Click on the following link, which has company’s filings on the SEC website: https://www.sec.gov/edgar/browse/?CIK=1067983 (this link is included within the template).
Click on the View filings button on the page.
Do a search for 13f so that you can see just the 13f filings on that page:

Berkshire Hathaway's 13f filings on the SEC website.

4. Find the reporting period you want and click on the Filing button — don’t click on the actual description next to it.

5. When you open up the link, you should see multiple files you can open. Select the information table that is in html format:

Selecting the information table in HTML format from the SEC website.

When you open the file, you should see the holdings in a table format:

If this is what you see, then the link you’ve downloaded will work. Note, on older 13f filings (e.g. 2013 and older), the format is in a text file and they won’t work with this template.

6. Copy the link and enter it into one of the fields on this template, either for the new filing or the old one.

Entries for the old and new 13f filings.

7. Once you’ve filled in both the new and old links, then go to the Data tab in Excel and click on Refresh All. This will update the queries that the template relies on, and calculate the changes.

Now the different sheets will update:

Current.Holdings: this will show you the current holdings as per the New13f file
Change.In.Holdings: this will show you the change between the two filings. The change will be reflected in total shares and as a % of change in shares.
Old.Holdings: this will show the number of shares held per the Old13f file.

Summary of Berkshire Hathaway's holdings per their latest 13f filing.

Download the template

This template is completely free to use and you can download the Berkshire Hathaway 13f Template here.

If you like the Berkshire Hathaway 13f 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.

Manage Your Subscriptions With This Free Template

July 26, 2022

by howtoexcel with No Comment free template vbafree

Nowadays, everything is available on a subscription. Even BMW now offers heated seats on a recurring subscription. It can be a challenge to keep on track of all your subscriptions, including when they renew and when trials end. However, I have a free template just for these purposes. With my free template, you can stay on top of your subscriptions and see just how much you are spending on them on a monthly and annual basis.

How the subscription manager template works

This template has no macros and is designed to be easy to update and track. Here’s how it looks:

There are multiple charts to show you the annualized cost of your subscriptions and the monthly cost (which is shown by category). This can be effective for budgeting purposes to know how much all of these subscriptions are costing you.

To add a subscription, simply add it to the bottom of the table. You can enter a category name, a frequency, amount, as well as subscription and renewal dates. The columns that have headers highlighted in grey don’t need to be updated as there are formulas there. This is where the amounts for the monthly and annual costs get calculated. Whether your subscription is paid monthly or annually, these columns will work out the calculation to determine what it costs you both on an monthly and annual basis. That way, you don’t need to worry about grouping monthly and annual subscriptions separately.

There’s also another column called Days to Action — this will calculate the days between today’s date and the renewal date. It will highlight in yellow when you are within 30 days of the renewal date, and it will turn red when you’re within two weeks of it. The point here is to give you a warning that a renewal is coming up (or a trial is ending). This can help you prevent forgetting about it and incurring a surprise fee. If you don’t care to track this, you can just leave the renewal date blank.

Updating the data in the template

When you enter a new line for a subscription or want to make modifications to one, the charts won’t automatically update as there are no macros within the file. In order to trigger an update, go to the Data tab and click on the Refresh All button. Upon doing so, your charts will be updated.

If you want to remove a subscription, simply right-click on the row and delete it. If you’re within the table, then while you’re on the row, right-click and select Delete. You’ll see an option to remove Table Rows. Either method will work fine.

Download the subscription manager template

The subscription manager template is available here

If you like the Subscription Manager 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.

How to Create an Inflation Calculator in Excel

June 22, 2022

by howtoexcel with No Comment index match template

Want to know how much something was worth decades ago? Or how much something costs in today’s dollars? Using inflation data, you can estimate that. And in this post, I’ll show you how you can create your own inflation calculator template in Excel. I’ll also provide you with my free template.

Getting the data

You can get inflation data going back to 1913 from the U.S. bureau of Labor Statistics. There’s an xlsx file there that I’m going use that will be the source for my calculations.

Once in Excel, you’ll see the data is neatly formatted by both year and month:

This data will get updated so over time you may want to get the latest figures so that your calculations are as accurate as they can be. The data has 1st half and 2nd half numbers but one thing I will do is also add the 12-month average. I’ll add a new column so that it just averages the values. In most cases, you’re probably just going to want to compare data from one year to another.

Next, I’ll convert the data into a table. To do this, click anywhere on the data set and under the Insert tab, click on the Table button. Excel should auto-detect the range but if it doesn’t, you can adjust it. In my template, I’ve named this table tblInflation. It includes the average, which will auto-update as new data is included.

Setting up the calculations

The next step involves creating the inputs, doing the lookups, and then calculating the value. There are three inputs I’ll set up: the base value, base year, and the calculation year. The base year and value will act as the starting points and will convert to a calculated value based on what the calculation year is.

To determine the impact of inflation, I’ll use the base and calculation years to find their respective index values. To do that, I’m going to use a formula that includes INDEX & MATCH. Here’s what it looks like for the base year:

=INDEX(tblInflation[Average],MATCH(BaseYear,tblInflation[Year],0),1)

In the table, I’m extracting the value from the Average column and I will be matching the BaseYear (the named range for my input) against the values in the Year column. I’ll use a similar formula to extract the index value for the calculation year. I’ve put these index values next to my inputs but will hide them later:

In 1913, the index average was 9.9 and for 2022 it was around 286.8 (based on the data that’s available thus far). If I take the index value from the calculation year and divide it by the index value from the base year, that tells me the prices are approximately 29 times what they were back then. That comes out to a percentage change of 2,797%. This leads me to the next part of the equation: determining the new price, or as I’ve referred to it in my template, the ‘Calculated Value.’ The formula for this output is as follows:

=CalculationIndex/BaseIndex*BaseValue

In the case of the above inputs, it’s doing the following calculation:

=9.88/286.75*100

This gives me a value of $2,901.40. That means something that was worth $100 in 1913 would be worth $2,901.40 in 2022. I can also do the reverse calculation. I can work backward and answer the question of how much would something in today’s dollars be worth back then. To do that, I would enter the following inputs:

The calculated value is the $100 that I started with in the previous calculation.

My templates is complete and all that’s left at this point is just to add a header and modify some formatting:

You can download the inflation calculator template here.

If you liked this post on How to Create an Inflation Calculator 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.

How to Convert a Table From an SEC Report Into Excel

April 7, 2022

by howtoexcel with No Comment formatting functions iferror investing parse stocks substitute template

If you want to download a company’s financial statements or data, the easiest place is often straight from the source: the Securities & Exchange Commission (SEC). You can download financials in Excel format if there is an interactive option within the SEC filing, but that won’t give you all the tables contained in an earnings report. In this example, I’m going to use Adobe’s most recent earnings report to show you how to get a table into Excel

Downloading the data

Adobe’s earnings report is found here, with the following financials on page 4:

Adobe's income statement for the quarter ending March 4.

Copying it into Excel

Copy the table and then go to paste it data into Excel. But when you right-click in Excel, make sure to select the option to paste it so it matches the formatting on the sheet, as shown below:

Paste with a format matching the spreadsheet.

Now, the data pastes without any of the colors and formatting onto my Input sheet:

Adobe's earnings report downloaded into Excel.

If when you paste it doesn’t show up like this and it looks like just a few lines, re-try copying the data. It may help not to include the header that says “three months ended” and simply start copying from the first line item (“revenue” in the above example”) to ensure that Excel picks it up as a table.

Formatting the data

It looks pretty good except that I have many extra columns. And numbers that have dollar signs have been pushed out by one column. What I will do here is create a template in a separate sheet that will automatically pull in what is needed. The new tab, called Output, will be where I create my formulas. My assumption is that the spacing will be consistent and that the current period values are in columns D and E, and the ones from the prior-year period are in columns J and K.

Starting in cell A1, I’ll create a simple formula that checks if the same value on the other sheet is blank. If it isn’t, then it will pull in the value, otherwise, it will remain blank:

=IF(Input!A1="","",Input!A1)

I will do the same thing for column B, except this time I am looking at values from the Input tab in column D. And I will need to adjust for if there is a $ sign. If there is, I need to pull the value from column E instead. Here’s what that formula looks like:

=IF(Input!D1="","",IF(Input!D1="$",Input!E1,Input!D1))

That gets me a bit closer to where I want to get to:

Financial figures pulled into a separate tab with formatting applied.

There are still a couple of issues. The first is that on row 30, there is a symbol that isn’t a dash that I need to remove. This is character code #151. And there’s also a trailing blank space behind the numbers that needs to be removed. This isn’t your ordinary blank space and it is character code #160. I need a couple of SUBSTITUTE functions to remove those character codes:

=IF(Input!D1="","",SUBSTITUTE(SUBSTITUTE(IF(Input!D1="$",Input!E1,Input!D1),CHAR(160),""),CHAR(151),0))

For character 151, I want to replace this with a 0 value since that’s what the symbol is in place of. Next, I need to convert these values to numbers. I can do this by multiplying them by a factor of 1. I’m going to use the IFERROR function as well so that in case it’s text, it will return the original value in column D. Here’s my completed formula:

=IF(Input!D1="","",IFERROR(1*SUBSTITUTE(SUBSTITUTE(IF(Input!D1="$",Input!E1,Input!D1),CHAR(160),""),CHAR(151),0),Input!D1))

Now, I can repeat this formula in the adjacent column. Except this time instead of referencing D and E, I’ll refer to columns J and K. Now, my output tab looks as follows, after applying some formatting to it:

This can be re-used over for other tables in an SEC report, as they generally follow the same pattern. For example, this is Adobe’s table showing sales by segment:

By dropping this into my Input tab, this is what my Output now shows:

Output tab in Excel showing Adobe's segmented financial information.

All that I needed to do was to copy the formulas and just adjust the columns they referenced on the Input tab. If you’d like to use the file I’ve created for your own use, you can download it for free, from here.

If you liked this post on How to Convert a Table From an SEC Report Into 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.

Free Add-In to Organize Your Tabs

March 30, 2022

by howtoexcel with No Comment add-ins template

Do you have a spreadsheet that has more than 10 tabs? If so, you probably know it can be challenging and time-consuming to organize and move all those sheets around if you want to re-arrange them. Oftentimes it’s just easier to leave them as they are. In this post, I’ll share with you a free Excel add-in I’ve been working on that will make it easy to move tabs around and alphabetize them.

Download and install the add-in

You can download the add-in from this link. Once you’ve downloaded it, go into Excel and select File and then Excel Options. There, you’ll see a section for Add-ins. Click on that and click on the Go button next to Manage Excel Add-ins near the bottom:

You should see a list of available add-ins. To add the one you just downloaded, click on the Browse button on the right-hand side and then select the add-in that you just downloaded. Then you should see it added to your list and the box should be checked off. Once you press OK, you should see the add-in within your Excel spreadsheet, in the middle of your Home tab:

Excel ribbon with the Tab Manager Add-In installed.

The add-in will now be available on all of the Excel files that you open.

Using the add-in

To start using the add-in, click on the Tab Manager button. A form will populate that displays all the tabs in your spreadsheet:

The tab manager launched and ready to use.

To move sheets around, select a sheet and use the arrow keys on the right to move them either up or down. Once you have the order you want, click on the button to Update List and your sheets will be re-arranged.

There’s also an option at the top of the form to Alphabetize the list. Clicking on the button will arrange the sheets in alphabetical order. You can make changes afterward, or you can click on Update List. The point of the add-in is to make it easy to make changes before running the update. If you don’t want to make any changes, simply click on the ‘X’ in the top-right-hand corner.

If you are moving a sheet far enough on the form that you end up moving onto another page/view and notice that the tab names don’t appear to be refreshed, simply click the up and down arrows to refresh the list.

Don’t need the add-in anymore or want to uninstall it?

If you just wanted to try out the add-in or only needed it for one use and want to uninstall it, you can easily do that. Repeat the steps you took to install the add-in, except this time, since it’s already available, simply uncheck the box for the Tabmanager. This will make it unavailable and it will no longer show up on the ribbon.

If you liked this Free Add-In to Organize Your Tabs, 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.

Free Credit Card Payment Calculator Template in Excel

March 15, 2022

by howtoexcel with No Comment calculator personal finance template

Are you wondering how much you should put down on your credit card each month to ensure you aren’t taking decades to pay off your balance? With this free credit card payment calculator template, you can plug in your balance, interest rate, and your expected monthly payment (either fixed or as a percentage of the balance) to determine just how long it may take you to pay off your debt.

How the template works

The cells that require data entry are highlighted in yellow on the template. At a minimum, the balance, interest rate, starting date, and one of the two fields for the monthly payment need to be entered in:

Empty credit card payment calculator template.

For the monthly payment field, you can enter in both a fixed dollar amount and a percentage of the balance. How it works is that if you enter in both values, the higher payment calculation will be used.

For example, if you had a $10,000 balance and planned to pay 5% of it, the monthly payment would be $500 and decline along with the balance. If you also set a fixed payment of $400, then that would ensure your monthly payment would never fall below $400, even as the balance declines. In effect, the payment will be the greater of these two values. If you only enter a percentage of the balance, then only a percentage will be used. Similarly, if you only have a value for the fixed amount, then that’s what your payment will remain at.

Once you’ve entered in these fields, the rest of the template will populate. This includes calculations on how many years it will take for you to pay off your balance, the end date, how much your payments will total, and the amount of interest you will have paid:

Credit card payment calculator template with fields filled in.

If you enter a payment that is so small it won’t even cover your interest, then the payment amount will be highlighted in red:

Credit card payment calculator where the payment is insufficient to cover the interest.

In the above example, the years and end date will not be accurate since, under this example, the balance will grow rather than decline over time, and thus, will never be paid off.

If you liked this free Free Credit Card Payment 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.

How to Calculate Taxes in Excel

July 13, 2021

by howtoexcel with No Comment accounting budgets personal finance template

Want to estimate how much you might owe in taxes next year? If you are self-employed or have other income besides what you get from an employer, then you may find it useful to plan ahead of time and determine how much you might owe to ensure that you are putting aside enough money for taxes. It’s not a fun process but it can save some headaches later on. The good news is that Excel can make that process easy. Below, I’ll show you how you can calculate and estimate your taxes in Excel. And if you’d just prefer to download the file that I have created, scroll to the bottom of this page.

Determining your marginal tax rate

To estimate your taxable income and marginal tax rate, the first thing you’ll need is a table for the tax brackets. For this, I will use the schedule for federal income tax brackets 2021 found here.

I can’t simply copy the table into Excel as I will need to format it a little differently (the values contain text and won’t be helpful if I need to do a lookup). The table needs to be organized by income threshold rather than tax rate. This is how I have set it up in Excel:

To make this table easier to reference to, I am going to create named ranges for these tax brackets plus the income I am going to enter in. This will make it easier to follow along.

If I want to look up the incremental tax bracket for a given level of income, I can accomplish this using a VLOOKUP formula. This is the formula I would use to accomplish that:

=VLOOKUP(Income,TaxBrackets,2)

What it is doing is taking the income number, and looking up the tax bracket table, and pulling in the second column (the tax rate). The VLOOKUP formula doesn’t look for an exact match (as I have left the last argument empty) and it will pull the closest number without going over. This is where it’s important to put in the numbers that the tax bracket start at, rather than a range. Using this formula, it correctly tells me that income of $100,000 would be at the 24% tax bracket as it does not yet reach the minimum amount for the next bracket — $164,926:

Determining the marginal tax rate in Excel.

That tells me the correct tax bracket but I still need to calculate the taxes that are due at each level, which I will cover in the next section.

Determine how much you owe at each tax bracket

For the first tax bracket, I will need to determine if the income level reaches the second tax bracket. If it does and the income is at least $9,951, then I can multiply that by the tax rate of 10% as that would be the maximum that can be taxed at the first bracket — 9,951 x 10%. If the income is not at least $9,951, then I just multiply the total income by the tax rate. Here is what the formula looks like using named ranges:

=IF(Income>=IncomeLevel2,IncomeLevel2-IncomeLevel1,Income)*TaxRate1

For the second tax bracket calculation, I can follow similar logic. I will multiply the difference between the start of the third and second income levels. Here’s how that calculation looks:

=IF(Income>=IncomeLevel3,IncomeLevel3-IncomeLevel2,MAX(0,Income-IncomeLevel2))*TaxRate2

I also use the MAX function just in case there is a negative number (where the income doesn’t even reach the next level). The same logic can now be applied for all of the remaining tax brackets except for the last one. Like the first one, it needs to be calculated differently. In that case, I just need to know if the income is above that threshold. And if it is, I take the difference between it and the total income, and multiply it by the highest rate:

=IF(Income>=IncomeLevel7,Income-IncomeLevel7,0)*TaxRate7

If the income isn’t above the last level, then I put a 0 and multiply that by the tax rate. Now, when I’m all finished, I can sum up the tax owing at each level and come to a total tax number that would be due based on a given income number:

At this stage, you could now decide to deduct how much you may have already paid in taxes and any deductions or credits that you are entitled to.

But I’m not going to go any deeper here because there are too many different variations from one country and jurisdiction to the next when it comes to taxes. However, this should at least give you a good starting point for doing the rest of your estimation, however detailed you want it to be. But by at least estimating the taxes owing and deducting how much you have already paid, you should have a good idea of how much you might owe come tax time, under a worst-case scenario.

If you’d like to just download the file that I created when making this post, you can do so here.

If you liked this post on How to Calculate Taxes 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.

Average Down Calculator Template

June 1, 2021

by howtoexcel with No Comment average calculator financial analysis investing stocks 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.

Get the Full Version

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 there are more scenarios:

Get the Full Version

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.