AgedARReport1

Create an Accounts Receivable Aging Report in Excel

by accountingaccounts receivable

An aged accounts receivable (AR) report can help you identify accounts with overdue receivables. It can help you stay on top of your AR to ensure that you are prioritizing collection efforts based on their relative ages.

Here’s a sample of what your AR data might look like, showing customers, invoice dates, due dates, and amounts:

Using a pivot table, we can summarize these details to determine how old the AR is, and create a report to help us stay on top of overdue accounts.

Creating the accounts receivable table

First, I’ll convert this data into a table, and call it tblAR. By creating a table, it will make it easy to add to the data and update it, and for the pivot table to refresh easily as new data is entered. Next, I’ll add a column to calculate the number of days past due. The formula for that is as follows:

=IF([@[Due Date]]>TODAY(),0,TODAY()-[@[Due Date]])

Now my table shows the number of days past due. And since it uses the TODAY() function, it will automatically update.

A table containing accounts receivable data.

Next, I need to create a lookup table for the aging, which I’ve setup as follows:

Then, I’ll create a lookup formula to determine what the description should be based on how old the receivable is. In my spreadsheet, the lookup table is in columns H and I. The following formula will pull in the ‘description’ field:

=VLOOKUP([@[Days Past Due]],H:I,2,TRUE)

Now I have a complete table that shows the correct aging category based on the days past due:

Setting up the pivot table

Once the data is ready to go, the next step is to create the pivot table. When putting the Aging Category in the Rows section and the Amount in the Values section, this is what the pivot table looks like:

A pivot table summarizing invoices by age.

This gives me a broad high-level overview of the overall aging. However, I can also set this up so that it breaks it down by customer. For that setup, I can put the Customer Name field in the Rows section, and the Aging Category going across in columns:

A pivot table breaking down aged receivables by customer.

I have dragged the ‘Current’ value to the front to ensure that the text value is at the start. Next, I will create a chart to help visually show the aged AR data. Since there are multiple aging categories per customer, a stacked column chart is ideal in this situation. Then, after applying custom formatting colors to each aging category, I have a visual representation of the AR aging chart:

An aged accounts receivable report displayed using an Excel chart.

If you have a lot of customers and can’t fit all of them on a single chart, you could insert a slicer to make it easy to select one or multiple customers.

An aged accounts receivable report displayed using an Excel chart and slicers.

To help visualize this further, I can also add a data table that shows the breakdown of the different aging categories. To enable this, select the chart, click on the Design tab, select Add Chart Element and select a Data Table With Legend Keys:

An aged accounts receivable report displayed using an Excel chart and slicers and a data table.

If you like this post on How to Create an Accounts Receivable Aging Report 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Amortization calculator which calculates values for a specific period.

Create an Amortization Calculator in Excel

by accountingamortizationcalculatorfunctions

Amortization schedules are essential tools for anyone managing a loan or mortgage, providing a clear breakdown of how payments are allocated towards interest and principal over the loan’s term. Typically, these schedules detail each payment’s date, the interest portion, the principal portion, and the remaining balance, offering borrowers insight into the precise cost of borrowing over time. Creating an amortization schedule in Excel can be a time-consuming process, but with the help of some advanced payment functions, you can expedite the process and even eliminate the need for creating an entire amortization table.

Generating the key components of an amortization schedule

Whether you’re creating a full-blown amortization schedule or just want to calculate the balance at a future point in time, you’ll need to know the following values before you can begin:

  • the loan value,
  • the interest rate and compounding,
  • the number of periods, and
  • the start date of the loan

Suppose you have a $500,000 loan with a 5% interest rate, which is for a period of 10 years, and that payments are made on a monthly basis. Given this information, you can start by calculating the monthly payment amount. Here’s what the inputs would look like for the PMT function:

=PMT(0.05/12,10*12,-500000,0) = 5,303.28

This assumes compounding to be monthly, hence the need to divide the interest rate by 12. And the negative 500,000 balance tells the function that this is an amount owing and that it will reduce over time. The following argument, 0, is to signify that the future value is 0.

There is also an additional argument, for whether the payments are made at the beginning or the end of the period. The default is set to the end of the period. If payments are made at the beginning, then the final argument is set to 1. If the payment is at the start, then the payment value would change to $5,281.27, assuming all the other values remain the same. But for the purposes of these examples, we’ll assume payments are made at the end of the period.

Creating a calculator for specific month’s calculations

Now that you have all the necessary variables, including the payment amount, you can start to create the calculator.

In this first case, we’ll look at how to calculate values for just a specific period. Let’s assume the loan’s start date is January 1, 2024. And let’s assume we want to calculate what the balance, interest, and principal payment amount will be for the month of Dec 2025.

How to calculate amortization amounts for a specific period:

1. Start with calculating the period number. One function that can help with this is the DATEDIF function. By using it, you can quickly calculate the difference between two time periods. Here’s how it would work in this example:

=DATEDIF(“1/1/2024″,”12/1/2025″,”m”) = 23

Two full years have not fully elapsed until we get to 1/1/2026. But since we want to calculate the values for the 24th month, we’ll need to add 1 to the equation. The formula to calculate the current period will be as follows:

=DATEDIF(“1/1/2024″,”12/1/2025″,”m”)+1

2. Calculate the interest payment for the period. To get the interest amount, use the IPMT function:

=IPMT(0.05/12,24,120,-500000) = 1,760.19

3. Calculate the principal amount paid down during the period. In this case, we can use the PPMT function. It is the same setup as the previous formula:

=PPMT(0.05/12,24,120,-500000) = 3,543.08

4. Calculate the ending value as of that period. Using the FV function, here’s the formula to get the ending balance as of the end of Dec 2025:

=FV(0.05/12,24,5303.28,-500000) = 418,902.68

By using these functions, you no longer need to make an entire amortization schedule. You can simply do a calculation for the period you need to sum up the values for. By creating inputs for the start of the loan, loan amount, interest rate, # of periods, and the period you want to calculate for, you can setup an amortization calculator as follows in Excel:

Amortization calculator which calculates values for a specific period.

Calculate amortization values for a specific range

Suppose you wanted to calculate the interest and payment values for all the months in 2025. This can be a bit trickier since we aren’t calculating amounts for just a single period anymore. Instead, we need to get a range of values.

How to calculate amortization amounts for a specific multiple periods:

Step 1. Get the correct period numbers. Let’s assume the loan began on June 1, 2023. We need to get the starting period number, which is Jan. 1, 2025. This can be done with the DATEDIF function:

=DATEDIF(“6/1/2023″,”1/1/2025″,”m”)+1 = 20

This tells us that January is the 20th month of the loan. If January is the 20th month, then that also means if we add 11, that December will be the 31st month of the loan. Thus, our range of periods is 20 through 31. You could, however, use the DATEDIF function to do the calculation for that period as well.

Step 2. Calculate the interest payments for the year. Now that we know the periods we need to calculate the interest for, we can use the CUMIPMT function:

=-CUMIPMT(0.05/12,120,500000,20,31,0) = 20,851.88

The present value cannot be set to negative for the cumulative function, or else you will get an error. To adjust for this and avoid a negative value, simply add a dash before the function to ensure the result is flipped from a negative value to a positive one.

Step 3: Calculate the total of the principal payments made during the period. To calculate the principal paid during the period, we can use the CUMPRINC function. The inputs are the same as that of the cumulative interest payment calculation:

=-CUMPRINC(0.05/12,120,500000,20,31,0) = 42,787.43

Step 4. Get the ending period’s balance. Just as with the previous section, we can use the same FV calculation to get the ending balance. This time, it will be for period 31.

=FV(0.05/12,31,5303.28,-500000) = 393,684.23

Here is how the calculator could be setup to make these calculations based on a range.

Amortization calculator which calculates values for a specific range.

You can download the amortization calculator I have created from these examples here.

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

BankRecGeneric

Bank Reconciliation Template 2024: Use Auto-Matching Rules to Speed Up Your Recs!

by accountingbank reconciliationtemplatevba

Introducing the Ultimate Bank Reconciliation Excel Template – your time-saving companion for hassle-free financial management!

Are you tired of spending hours manually matching transactions and dealing with duplicates during your bank reconciliations? Look no further! This downloadable Excel file is here to revolutionize the way you handle your finances. Packed with powerful features and user-friendly functionalities, this template will streamline your reconciliation process like never before.

Key features

Automated Transaction Matching: Say goodbye to tedious manual matching. This Excel template is equipped with an intelligent algorithm that automatically matches transactions, making your reconciliation process a breeze. Experience unmatched efficiency as the template swiftly identifies and pairs up corresponding transactions, freeing up your valuable time.

Effortless Manual Matching: Whatever the template doesn’t end up matching, you can do so manually using the Reconciler. It’s a much easier process than the manual approach as once you select a transaction, it will find related transactions that you can match the transaction to. Simply review the related items displayed, and with a few clicks, you’ll have your transactions matched accurately and swiftly.

Using the bank reconciliation template to easily match transactions.

Duplicate Detection: This template also checks for duplicates and will be careful not to match any items where there is a duplicate entry. You can still match these transactions manually using the Reconciler, but you won’t have to worry about the template automatically matching items where there are duplicates. This helps ensure accuracy and integrity when doing the automatic matching.

Flexible Matching Rules: Take full control of your reconciliation process with customizable matching rules. The Excel template empowers you to create rules to automatically match transactions on a 1-to-1 basis, 1-to-many, or even many-to-many, tailored to your unique needs.

Matching rules in the bank reconciliation template.

By creating these rules, you can specify how a transaction should be classified.

In the above example, if a description contains CK#, then it will belong to the ‘Checks’ category. For the key, which is used to help match a transaction, it will take the next 4 numbers. So if you have CK#1234, then 1234 would be the key. For a transaction to automatically match, it will need to be part of the same category, have the the same key, and amount. In the case of 1-to-1 matches, it will only need to find another transaction that matches this criteria. The one exception is if there is a duplicate; in that case, the transactions won’t automatically match. The auto-match is designed to minimize false matches.

For the Wire Transfer example above, it will simply use the date as the key. Since it’s a 1-to-1 match, it will look for another Wire Transfer on the same date with the same amount.

The deposit example shown above is slightly different in that it relies on the date but it is a 1-to-Many match type. However, this can also work as a many-to-many matching type as well. That’s because it will look for all of the deposits on that date, across the book, bank, and previous outstanding items. Only if the total of all the deposits on that date are a match will the auto-match rules kick in and say that everything is a match. You might use this type of matching if you have multiple deposits on the GL side that total just a single deposit amount on the bank side, or vice versa.

Easy Overrides. If you need to force a match, you can do so by entering any value in the ‘Manual Override’ column. This will clear the O/S status and indicates that the transaction has been matched. In the example below, just entering an ‘X’ is enough to mark off a transaction as being reconciled:

Overriding a transaction on the bank reconciliation template.

Quickly Generate Reports and Start a New Month: With just a click of a button, this Excel template generates a comprehensive report summarizing outstanding items. Gain valuable insights into your financial status and easily identify discrepancies that require attention. Stay on top of your bank reconciliations with accurate, up-to-date information at your fingertips. Clicking on the Reconcile Month button will summarize your outstanding items. You can also clear all the data with the Clear Data button. Click the New Month button when you’re done reconciling and want to close out the month. It will transfer all your current outstanding items to the previous outstanding items tab.

Buttons on the bank reconciliation file.

Try the bank reconciliation template for yourself!

Whether you’re a small business owner, a financial professional, or an individual managing personal finances, this Bank Reconciliation template is your go-to solution. Experience unparalleled ease, accuracy, and efficiency in your reconciliation process. Save time, reduce errors, and take control of your financial management like never before.

Best of all, you can try it out for free to see how you like it. Download the trial version here. If you decide you want to buy the full version without restrictions and full VBA code available, click on the following button:

If you like this Bank Reconciliation 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

NPVIRR

How to Calculate Net Present Value (NPV) and Internal Rate of Return (IRR) in Excel

by accountingfinancial analysis

What is Net Present Value (NPV)?

Net Present Value (NPV) is a financial metric used to determine the current value of a series of cash inflows and outflows. It takes into account the time value of money, which means that a dollar received in the future is worth less than a dollar received today due to factors like inflation and the opportunity cost of not having that money available to invest in other projects.

The calculation of NPV involves discounting the expected future cash flows of a project or investment back to their present value using a specified discount rate. The result is the difference between the present value of the expected cash inflows and outflows.

NPV is an important calculation because it helps you evaluate the profitability and feasibility of an investment. It can also allow you to compare the expected returns of different investment opportunities, and to make informed decisions about which projects to pursue.

If the NPV is positive, it means that the project is expected to generate more cash inflows than outflows, and thus, it’s a profitable investment opportunity. However, if the NPV is negative, the project is expected to result in a net loss and is therefore not considered a viable option.

The NPV calculation is an important tool in finance as it can help decision makers determine whether to move forward on a project.

What is the Internal Rate of Return (IRR)?

The Internal Rate of Return (IRR) is used to measure the profitability of an investment project or opportunity, often in conjunction with calculating NPV. It is the discount rate where the present value of expected cash inflows equals the present value of expected cash outflows, or when NPV is equal to 0.

IRR represents the rate of return at which an investment will break even over its lifetime. It is shown as a percentage. And if you use the IRR percentage as your discount rate in the NPV calculation, the result will be an NPV of 0.

With Excel, you can quickly calculate the IRR through a simple formula, rather than having to go through a time-consuming process that might otherwise involve trial and error.

Calculating NPV and IRR in Excel

To illustrate how to calculate NPV and IRR, I’ll use the following example. Suppose that you are investing $1,000 into a project that will generate the following cost savings:

  • Year 1: $50
  • Year 2: $100
  • Year 3: $250
  • Year 4: $300
  • Year 5: $600

In total, that is $1,300 in cost savings. Although that’s more than the original $1,000 investment, those savings are spread out over a period of five years. To get a true picture of whether the project is worthwhile, you need to adjust for the time value of money and adjust those amounts and calculate their present values — what their values are today. This is where the NPV function comes into play.

However, before using the NPV function, you need to determine the discount rate that you are going to use. The discount rate is important as it tells you the interest rate that you will be using when adjusting the cost savings back to today, and to calculate the present value. If the discount rate is high, then it’ll be more difficult for the NPV calculation to be positive (and hence, suggest that the investment should be taken on). And if the discount rate is too low, then it could be too easy to clear the bar and for the NPV formula to suggest the project is worthwhile.

The discount rate should be higher than the risk-free rate since you are taking on some risk, and thus, you should be compensated for doing so. If you were to use the same rate as what you could earn on a treasury bill or a bank deposit, there would be little incentive to go ahead with the project even with a positive NPV. After all, what’s the point of taking on the risk if you’re not getting a better return?

In this example, I’m using a discount rate of 5%. This is what the NPV formula will look like with all of the inputs:

=NPV(0.05,50,100,250,300,600)-1000

As you can see, the order of the values is important as that will determine how many periods each value will be discounted by. The result of this formula is a value of $71.21. It’s a positive amount, indicating that the project should be undertaken as the present value of the future cost savings offset the current investment.

To prove that calculation out, I’ll show you how this calculation could be done manually. Here, for example, is how the present value would be calculated for the $50 in cost savings that is achieved in year 1:

=50*(1+0.05)^-1

One plus the discount rate is raised to a power of negative one to bring the value back one period, using the discount rate. That returns a value of $47.619. Here are the other present value calculations:

  • Year 2 ($100) : $90.703
  • Year 3 ($250) : $215.959
  • Year 4 ($300) : $246.811
  • Year 5 ($600): $471.116

If you add all of these present values up, they total $1,071.21. And that is $71.21 more than the $1,000 initial investment, which is the same result as the NPV formula.

One thing you may be wondering is at what point does the value equal 0 — where is the breakeven? This can be calculated using the IRR formula. In Excel, this is a simple formula that just takes all the inflows and outflows. For example, if you had the negative investment amount of $1,000 in cell A1 followed by the cost savings in the the adjacent columns (until column F), then the formula for IRR would be as follows:

=IRR(A1:F1)

The end result is a value of 6.8576%. If you use this as the discount rate in the NPV calculation, you will get an NPV value of 0. This tells you that if you use a discount rate higher than this percentage, your NPV value will be negative as the level of discounting will be too high for the project to have a positive NPV value. On the other hand, anything below the IRR rate will result in a positive NPV value and thus indicate that the project should move forward.

If you liked this post on How to Calculate Net Present Value and Internal Rate of Return 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

15ExcelAccounting

15 Excel Functions Accountants Should Know

by accountingadvancedaverageaveragesbeginnercountcountifdatesfunctionsindexintermediatelookuplookupsmatchsumsumifsumifsuniquevlookup

If you’re an accountant, you know that working with large amounts of data can be a daunting task. But with Excel, that work can get a whole lot easier and more efficient. Understanding Excel’s advanced features and functions can improve productivity, reduce errors, make your work more accurate, and most importantly — save you time. Below, I’ll go over some of the most important Excel functions that accountants should know, and provide examples of how to use them. For this example, I’ll use the following spreadsheet. Feel free to download it and follow along with the calculations.

1. SUM

The SUM function is a basic but essential function in Excel. It allows you to add up a range of values, which is helpful when calculating totals, such as revenue, expenses, and profits. Suppose you have a spreadsheet with sales data. In the above example, the total sales are in column G. If you wanted to sum up the entire column, the formula would be as follows: =SUM(G:G)

2. AVERAGE

The AVERAGE function calculates the average of a range of values. It is useful when analyzing data and preparing financial statements. In the above example, suppose you wanted to calculate what the average sale was. To do this, you can just use the AVERAGE function on column G, similar to the SUM function before. Here’s the formula: =AVERAGE(G:G)

3. IF

The IF function allows you to test a condition and return one value if the condition is true and another value if the condition is false. This can be useful because it can send your formulas to the next level. By knowing to use the IF function, you could also use SUMIF, AVERAGEIF, and many other functions that involve an if statement. In the above example, let’s say you only wanted to know if a value in cell M2 was part of the Motorcycles product line. The formula would be as follows: =IF(M2=”Motorcycles”,1,2). If it is part of Motorcycles, you would have a value of 1, otherwise, it would be 2.

4. SUMIF

By knowing the SUM and IF functions, you can combine them together with SUMIF, which is an incredibly popular function. It gives you a quick way to tally up the totals that meet a criteria. For example, let’s say you want all sales that relate to the Motorcycles category. The formula for that would be as follows: =SUMIF(M:M,”Motorcycles”,G:G). If the criteria is met in column M, then the formula will sum up the corresponding values in column G. There’s also the super-powered SUMIFS function, which allows you to combine multiple criteria.

5. EOMONTH

The EOMONTH function calculates the last day of the month for a specified number of months in the future or past. It is useful when working with data that is organized by date. For accountants, this can be useful when you’re calculating when something is due. Let’s say in this example, we need to calculate the date orders need to go out on, and that needs to be the end of the next month. Using the ORDERDATE field in column H, here’s how that calculation would look in the first cell, which would then be copied down for the rest: =EOMONTH(H2,1)

6. TODAY

The TODAY function is helpful for accountants in calculating deadlines and knowing how many days are remaining or past a certain date. Suppose that you wanted to know how many days have past since the ORDER DUE DATE that was calculated in the previous example. Rather than entering in a static date that every day you would need to change, you can just use the TODAY function. Here’s how a formula calculating the days since the deadline for the first cell would look like, assuming the due date is in column N: =TODAY()-N2. The next day you open up the workbook, the calculations will update to reflect the current date; there’s no need to make any changes. There are many more date calculations you can do in Excel.

7. FV

The FV function calculates the future value of an investment based on a fixed interest rate and a regular payment schedule. You can use it to calculate the future value of an investment or savings account. Let’s say that you wanted to save $10,000 per year and expect to earn a return of 5% per year on that investment. Using the FV calculation, you can do that with the following formula: =FV(0.05,5,-10000). If you don’t enter a negative for the payment amount, the formula will result in a negative value. You can also specify whether payments happen at the beginning of a period (1) or end (0 — this is the default) with the last argument in the function.

8. PV

The PV function lets you do the opposite and work backwards from a future value to the present. Knowing that the calculation in example 7 returns a value of $55,256.31, that can be used in the PV calculation to check our work: =PV(0.05,5,10000,-55256.31). The formula returns a value of 0, which is correct, as there was no starting value in the FV calculation.

9. PMT

The PMT function calculates the periodic payment required to pay off a loan with a fixed interest rate over a specified period. It is helpful when determining the monthly payments required to pay off a loan or mortgage. Let’s take the example of a mortgage payment where you need to pay down $500,000 over the period of 30 years, in monthly payments. At a 5% interest rate, here’s what the payment calculation would be: =PMT(0.05/12,12*30,-500000,0). Here again the ending value needs to be a negative to avoid a negative value in the result. And since the payments are monthly, the periods need to be multiplied by 12 and the interest rate is dividend by 12.

10. VLOOKUP

The VLOOKUP function allows you to search for a value in a table and return a corresponding value from another column in the same row. It’s one of the most common Excel functions because of how useful and easy to use it is. It is helpful when working with large data sets and performing data analysis. Let’s suppose in this example that you want to find the sales related to order number 10318. The formula for that calculation might look like this: =VLOOKUP(10318,C:G,5,FALSE). In a VLOOKUP function, you need to specify the column number you want to extract from, which is what the 5 represents. If you’re using Office 365, you can also use the newer, flashier XLOOKUP function. I put VLOOKUP on this list because it’ll work on older versions of Excel — XLOOKUP won’t.

11. INDEX

The INDEX function allows you to return a value from a data set by specifying the row and column number. It’s also helpful if you just want to return data from a single row or column. For example, the sales column is in column G. If I know the order number is on row 20 (which relates to order number 10318), this formula would do the same job as the VLOOKUP in the previous example: =INDEX(G:G,20,1).

12. MATCH

The MATCH function allows you to find the position of a value within a range of cells. Oftentimes, Excel users deploy a combination of INDEX and MATCH instead of VLOOKUP due to its limitation (e.g. VLOOKUP can’t extract values to the left of the lookup field). In the previous example, you had to specify the row belonging to the order number. But if you didn’t know it, you could use the MATCH function within the INDEX function. The MATCH function would look like this: =MATCH(10318,C:C,0). Placed within an INDEX function, it can replace the argument where in the previous example, we set a value of 20: =INDEX(G:G,MATCH(10318,C:C,0),1). By doing this, you have a more flexible version of the VLOOKUP function. You can also create dynamic formulas using INDEX and MATCH that use lookups for both the column and row.

13. COUNTIF

The COUNTIF function allows you to count the number of cells in a range that meet a specified condition. Let’s count the number of values in the data set that are Motorcycles. To do this, you would enter the following formula: =COUNTIF(M:M,”Motorcycles”).

14. COUNTA

The COUNTA function is similar to the previous function, except it only counts the number of non-empty cells. With no criteria, it is helpful to just the total number of values within a range. To calculate how many cells are in this data set, you can use the following formula: =COUNTA(C:C). If there are no gaps in data, then the result should be the same regardless of which column is used. And when combined with the UNIQUE function, you can have an easy way to count the number of unique values.

15. UNIQUE

The UNIQUE function returns a list of unique values within a range, and it’s a much easier method than the old-school way of extracting unique values. If you wanted to extract all the unique product lines in column M, you would enter the following formula: =UNIQUE(M:M). If, however, you just wanted to count the number of unique values, you could embed it within the COUNTA function as follows: =COUNTA(UNIQUE(M:M)). You can adjust your range if you don’t want to include the header.

This is just a sample of some of the useful Excel functions that accountants can utilize. If you are familiar with them, you’ll put yourself in a great position to improve the efficiency of your workflow and make your spreadsheets easier to use. Plus, you can confidently say that you are highly competent with Excel, which can make your resume more attractive and make you better suited for accounting jobs that require advanced Excel skills — and there are many of them that do!.

If you liked this post on 15 Excel Functions Accountants Should Know, 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

H2Etaxes1

How to Calculate Taxes in Excel

by with No Comment accountingbudgetspersonal financetemplate

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:

Income tax brackets set 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:

Tax owing calculation in Excel.

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.

H2Etax

How to Calculate Tax Included in an Invoice

by with No Comment accountingaccounts receivablebeginnerinvoice

If you are creating an invoice and need to account for taxes, usually you just need to multiply the subtotal by the percentage due for taxes. However, it gets trickier when the tax amount is already included within the invoice total and you need to work out what the amount relating to tax is. This is important if you need to determine how much in taxes you need to claim on an expense or how much you need to collect if you’re the seller. Below, I’ll go over a sample invoice calculation to show how can determine the tax amount whether it is included in the total or not.

Calculating taxes on an invoice

Let’s start with the basic calculation. This is how you might normally determine the taxes on an invoice and the total invoice value:

Sample invoice calculation including taxes.

The calculation is straightforward as what you do is just take the subtotal, multiply that by the tax rate, and add that back to the subtotal. Another way is to just take the subtotal and multiply it by a factor of 1 + the tax rate. In this case, it would $100 x 1.10. But let’s pretend we don’t know the subtotal and just know that the invoice total is $110.00 and the tax rate is 10%. In order to calculate the pre-tax amount, we need to do the steps in the opposite order. To prove this out, let’s use a bit of algebra:

$100 + ($100 x 10%) = $110

This can be simplified as follows:

$100 (1 + 10%) = $110

Now let’s solve for $100 which I will assign a variable of ‘y’ to:

y (1 + 10%) = $110

To solve for y, all we need to do is move the factor of 1 + the tax rate and divide $110 by that:

y = $110/(1 + 10%)

Taking $110 and dividing by 1.1 will give us a value of $100. And so what our end result comes out to is essentially this:

invoice total / (1 + tax rate) = pre-tax amount

To calculate the tax, all that’s needed then is to take the total and subtract the pre-tax amount.

Now that the logic is set up, let’s convert this into an Excel formula:

Invoice calculation when the tax amount is included.

Similar to how multiplying by a factor of the pre-tax amount by 1.1 (when the tax rate is 10%) would get you to the invoice total, dividing the total by 1.1 would get you to the amount before taxes. If the tax rate were 5%, then you would use 1.05, etc.

If you liked this post on How to Calculate the Tax Amount When it Is Included in the Total, 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.

amort

2 Excel Functions That Can Eliminate the Need for an Amortization Table

by with No Comment accountingfunctionsintermediatetips & tricks

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:

Amortization table in excel.

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:

Cumprinc 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.

IRR

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

by with No Comment accountingcashflowfinancial analysisforecastinggoalseekintermediate

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.

DCF

How to Calculate Discounted Cash Flow in Excel

by with No Comment accountingcashflowintermediatetemplate

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:

Calculating discounted cash flows one year out.

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:

Receiving 100,000 every year 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:

Discounted cash flow template.

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:

Present value calculation of all the payments over 25 years.

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:

Calculating future value of payments over 25 years.

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:

Lump sum payment earning 5% every year.

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.