How to Calculate the Payback Period With Excel

How to Calculate the Payback Period With Excel

Theoretically, longer cash sits in the investment, the less it is worth. In order to account for the time value of money, the discounted payback period must be used to discount the cash inflows of the project at the proper interest rate. To calculate the cumulative cash flow balance, add the present value of cash flows to the previous year’s balance. The cash flow balance in year zero is negative as it marks the initial outlay of capital. Therefore, the cumulative cash flow balance in year 1 equals the negative balance from year 0 plus the present value of cash flows from year 1.

  1. Look at past data, market research, or expert forecasts to estimate these figures accurately.
  2. First, it ignores the time value of money, which is a critical component of capital budgeting.
  3. Monica Greer holds a PhD in economics, a Master’s in economics, and a Bachelor’s in finance.
  4. Last, a payback rule called the payback period calculates the time required to recover the investment cost.

The payback period tells you how quickly an investment will earn back the money spent on it. It’s key in capital budgeting to compare which projects or purchases might be worth the cash. The payback period is favored when a company is under liquidity constraints because it can show how long it should take to recover the money laid out for the project. If short-term cash flows are a concern, a short payback period may be more attractive than a longer-term investment that has a higher NPV. As the equation above shows, the payback period calculation is a simple one.

For example, a firm may decide to invest in an asset with an initial cost of $1 million. Over the next five years, the firm receives positive cash flows that diminish over time. As seen from the graph below, the initial investment is fully offset by positive cash flows somewhere between periods 2 and 3. It is a rate that is applied to future payments in order to compute the present value or subsequent value of said future payments. For example, an investor may determine the net present value (NPV) of investing in something by discounting the cash flows they expect to receive in the future using an appropriate discount rate. It’s similar to determining how much money the investor currently needs to invest at this same rate in order to get the same cash flows at the same time in the future.

According to payback period analysis, the purchase of machine X is desirable because its payback period is 2.5 years which is shorter than the maximum payback period of the company. Keeping in mind the formula mentioned above, let’s take a closer look at how to calculate the payback period. My Accounting Course  is a world-class educational resource developed by experts to simplify accounting, finance, & investment analysis topics, so students and professionals can learn and propel their careers.

Analyst Certification FMVA® Program

Thus, at $250 a week, the buffer will have generated enough income (cash savings) to pay for itself in 40 weeks. As you can see, using this payback period calculator you a percentage as an answer. Multiply this percentage by 365 and you will arrive at the number of days it will take for the project or investment to earn enough cash to pay for itself.

Payback Period Formula in Excel (With Excel Template)

According to payback method, the project that promises a quick recovery of initial investment is considered desirable. If the payback period of a project is shorter than or equal to the management’s maximum desired payback period, the project is accepted, otherwise rejected. For example, if a company wants to recoup the cost of a machine within 5 years of purchase, the maximum desired payback period of the company would be 5 years. The purchase of machine would be desirable if it promises a payback period of 5 years or less. Let’s look at a real-world investment example to understand how to calculate the payback period. It helps quickly sift through potential projects to find ones that return the initial investment swiftly.

In Excel, you divide the total invested money by the yearly cash flow to get the payback period. The payback period for this project is 3.375 years which is longer than the maximum desired payback period of the management (3 years). Once the payback period has been completed, the business will enter the profitability period, ceteris paribus, which means that “other things being equal or held constant”. In other words, nothing has changed in terms of the demand for the firm’s product, or the costs of supplying its product. The situation gets a bit more complicated if you’d like to consider the time value of money formula (see time value of money calculator). After all, your $100,000 will not be worth the same after ten years; in fact, it will be worth a lot less.

Payback Period

The appropriate timeframe for an investment will vary depending on the type of project or investment and the expectations of those undertaking it. Investors may use payback in conjunction with return on investment (ROI) to determine whether or not to invest or enter a trade. Corporations and business managers also use the payback period to evaluate the relative favorability of potential projects in conjunction with tools like IRR or NPV. Project Beta shows a faster recovery of the initial investment, indicating a shorter payback period compared to Project Alpha. Accountants must consider this metric along with others such as IRR and NPV to ensure a comprehensive financial analysis. Despite its limitations, payback period analysis remains a key tool for initial screening of investment opportunities.

In other words, we fix the profitability of each year, but we place the valuation of that particular amount over the period of time. As a result, the payback period fails to capture the diminishing value of currency over increasing time. For example, if a business purchased a coupon-yielding bond, it would be easy to look at the coupon rate and determine when the breakeven point will occur.

Oftentimes, cash flow is conveyed as a net of the sum total of both positive and negative cash flows during a period, as is done for the calculator. The study of cash flow provides a general indication of solvency; generally, having adequate cash reserves is a positive sign of financial health for an individual or organization. The Payback Period Calculator can calculate payback periods, discounted payback periods, average returns, and schedules of investments. Payback period is the amount of time it takes to break even on an investment.

For example, if it takes five years to recover the cost of an investment, the payback period is five years. The term payback period refers to the amount of time it takes to recover the cost of an investment. Simply put, it is the length of time an investment reaches a breakeven point. According to payback method, machine Y is more desirable than machine X because it has a shorter payback period than machine X. According to payback method, the equipment should be purchased because the payback period of the equipment is 2.5 years which is shorter than the maximum desired payback period of 4 years. While calculating the payback period, we ignore the basic valuation of 2.5 lakh dollars over time.

You don’t see future cash flows or how the value of money can change over time. Despite these issues, many people use this method because it’s straightforward and does a fast job at sizing up an investment’s risk. The payback period is an essential assessment during the calculation of return from a particular project.

Related AccountingTools Courses

Unlike net present value , profitability index and internal rate of return method, payback method does not take into account the time value of money. A modified variant of this method is the discounted payback method which considers the time value of money. Payback focuses on cash flows and looks at the cumulative cash furniture invoice template flow of the investment up to the point at which the original investment has been recouped from the investment cash flows. Obviously, the longer it takes an investment to recoup its original cost, the more risky the investment. In most cases, a longer payback period also means a less lucrative investment as well.