Elearnmarkets - Financial Market Learning
  • Courses
  • Webinars
  • Stories
  • Language
    • English
    • Hindi
    • Bengali
No Result
View All Result
Get Free Course
  • Basic Finance
  • Derivatives
    • Futures
    • Options
  • Financial Planning
  • Fundamental Analysis
  • Technical Analysis
  • Mutual Funds
  • Marketshala
  • Miscellaneous
Elearnmarkets - Learn Stock Market, trading, investing for Free
  • Courses
  • Webinars
  • Stories
  • Language
    • English
    • Hindi
    • Bengali
No Result
View All Result
Get Free Course
Elearnmarkets - Learn Stock Market, trading, investing for Free
No Result
View All Result
Home Fundamental Analysis
How to Calculate the Payback Period in Excel Sheet with formula? 1

How to Calculate the Payback Period in Excel Sheet with formula?

Elearnmarkets by Elearnmarkets
April 19, 2024
in Fundamental Analysis
Reading Time: 8 mins read
0
80k
VIEWS
Share on FacebookShare on XShare on WhatsApp

The payback period helps us to calculate the time taken to recover the initial cost of investment without considering the time value of money.

This means that it will not evaluate the project based on the present value of money but on the basis of the actual investment made. Also, the shorter the payback the better it is as we are recovering the initial investment deployed in the business.

Table Of Contents
  1. Types of Payback Period
  2. Features of the Payback Period Formula
  3. Advantages and Disadvantages of Payback Period formula
  4. How to calculate in excel?
    • Learn advanced excel concepts with Advanced Excel Tutorial Online by Market Experts
  5. Frequently Asked Questions
    • What is a payback period?
    • What is the difference between payback period and return on investment?
    • How do you calculate the payback period for a business?
    • How is a payback period calculated?
  6. Conclusion

This method is mostly used by private companies because they are more concerned about liquidity. Once they invest in a huge project their capital is blocked.

Hence, they want to know the time duration in which they will get back the initial investment made so that they can deploy the same in other ventures.

Types of Payback Period

There are two types of payback period that are popularly used –

Non-discounted payback period – This is the general payback period. It does not take into account the time value of money while calculating the time taken to recover the initial cost of investment.

Discounted Payback Period – Discounted payback period is the time taken to recover the initial cost of investment, but it is calculated by discounting all the future cash flows. This method of calculation does take the time value of money into the account.

Features of the Payback Period Formula

The payback period formula has some unique features which make it a preferred tool for valuation. Some of these are –

  1. This formula is one of the easiest ways for businesses to understand the time it’ll take for their operations to reach break-even point (where there is no profit and no loss).
  2. This method is very suitable for small businesses with small cash balances, as it is a simple formula that requires minimal and basic information.
  3. Out of the various factors which affect a business’s ability to reach the break-even point, it focuses most on the speed of recovery of investment.

Advantages and Disadvantages of Payback Period formula

Advantages

  1. As mentioned before, it is the most straightforward way to calculate the time taken to reach the break-even point.
  2. This formula can be applied towards any kind of project, irrespective of time duration, capital size, etc.

Disadvantages

  1. The biggest disadvantage of this formula is that it originally does not take the time value of money into the account. This disadvantage is countered by the discounted payback period formula.
  2. The formula does not take into account any risk. It only considers the speed with which we can recover our initial capital.
  3. Since the formula does not consider the capital size, it can only be used in combination with other techniques when it comes to capital budgeting.

How to calculate in excel?

Just like many other valuation techniques, the payback period can be calculated with the help of MS Excel. Calculating it through this method is the easiest way to do it for finance and non-finance managers.

We will understand the process of calculation with the help of an example:

Payback period

In the above excel sheet, you will see that the company earns a cumulative cash flow of Rs. 832 at the end of the fifth year.

Learn advanced excel concepts with Advanced Excel Tutorial Online by Market Experts

Hence, the amount yet to be recovered –

Initial cash flow invested (outflow) – total cumulative cash flows (inflow) = 900-832 = Rs. 68

Now, the time taken to recover the balance amount of Rs. 68 i.e the time taken to generate this amount will be 0.22 years (68/308).

Hence, the total pay-back period will be 4+0.22 = 4.22 years, as below:

Calculate Payback Period in Excel: (with formula) 2

So now we know that 4.22 is the payback period in which we will recover our initial cost of investment of Rs. 900/-

The calculation of the payback method is not an accurate method as it ignores the time value of money, which is a very important concept. Also, this period does not consider the cash flows received after the pay-back period. Hence, it is not a very appropriate method on a stand-alone basis.

The time value of money, which is an important element, can be taken care of while calculating the discounted payback period.

Let us understand the concept of the discounted payback period with the help of the previous example. In the case of discounted payback period, we need to calculate the present value of the cash flows.

payback period in excel
payback period excel formula

In the above figure you will see that the cumulative amount (ignoring the decimal) aggregates to Rs. 851, which means that even till 5 years, we have not been able to recover the initial investment made.

Hence, we will get the negative of Rs. 49, as shown below:

NPV = Outflow – Inflow = [-900 – (-851)] = (49)

So in this case, as the project is not recovering my initial cash flow, we are not able to calculate the payback period, but it will surely be 5+ years. So that is the difference when taking the discounting payback period.

Once you do not take the time value of money into consideration, the results are positive as we are able to complete the project within the life of the project. On the other hand, if we consider the time value of money, the project goes negative. As seen in the above example, we will not be able to recover the money and will have a loss of Rs. 49/-.

Frequently Asked Questions

What is a payback period?

It helps investors to understand the component which is helping the company to drive profitability. It helps to know if the company is running on debt or more Equity funding or genuine cash flows.

What is the difference between payback period and return on investment?

The payback period is the time calculated in months or years by which time your initial investment into any business is expected to yield returns anticipated by you

How do you calculate the payback period for a business?

Payback period refers to the time taken by an initial investment to yield returns whereas return on Investment means the percentage yield delivered by the initial investment.

How is a payback period calculated?

It is calculated by calculating the time period over which the Initial Capital investment is returned by the business and the business by itself starts generating more capital. Thus the time frame between these two is known as the calculation of the payback period.

Conclusion

The discounted payback period will always be lower than the payback period because we are using the discounted cash flow which will always be lower than the normal cash flows.

So by now, we have learned the steps for estimating cash flows, few methodologies for estimating our project results. Apart from these, there are also other accounting methodologies that can be used.

Learn more about various concepts of excel using the advanced excel course.

Tags: discounted cashflowsenglishintermediate
ShareTweetSend
Get Kotak Offer Get Kotak Offer Get Kotak Offer
Previous Post

Interpretation of MACD Indicator

Next Post

15 Ways to Detect Fake 2000 Rupees Notes

Elearnmarkets

Elearnmarkets

Elearnmarkets (ELM) is a complete financial market portal where the market experts have taken the onus to spread financial education. ELM constantly experiments with new education methodologies and technologies to make financial education effective, affordable and accessible to all. You can connect with us on Twitter @elearnmarkets.

Related Posts

Top 5 Valuation Ratios in the Stock Market 2
Fundamental Analysis

Top 5 Valuation Ratios in the Stock Market

May 5, 2025
828
price to sales ratio
Fundamental Analysis

Price to Sales Ratio: A Key Metric for Understanding Company Value

May 5, 2025
447
Gearing Ratio
Fundamental Analysis

What Is the Gearing Ratio? Formula & Calculation

December 3, 2024
1.2k
Top 5 Fundamental Analysis Tools 3
Fundamental Analysis

Top 5 Fundamental Analysis Tools

January 31, 2025
4.1k

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Disclaimer

Elearnmarkets (Kredent InfoEdge Pvt. Ltd.) is a SEBI-registered Research Analyst (RA) entity (SEBI Registration No.: INH300007493). The information provided in this article is for educational and informational purposes only and should not be considered as an offer to buy or sell any securities or investment products.

The stocks, securities, and investment instruments mentioned herein are not recommendations under SEBI (Research Analysts) Regulations, 2014. Readers are advised to conduct their own due diligence and seek independent financial advice before making any investment decisions.

Investments in securities markets are subject to market risks. Please read all related documents carefully before investing. Investing in Equity Shares,
Derivatives, Mutual Funds, or other instruments carry inherent risks, including potential loss of capital. Elearnmarkets (Kredent InfoEdge Pvt. Ltd.) does not provide any guarantee or assurance of returns on any investments. Past performance is not indicative of future performance.

Elearnmarkets Logo

Follow Us

Facebook-f X-twitter Instagram Linkedin-in Youtube Telegram

Register on Elearnmarkets

Continue your financial learning by creating your own account on Elearnmarkets.com

Register Free Account

Download App

Playstore logo
Download on app store

Categories

  • Basic Finance
  • Derivatives
  • Financial Planning
  • Fundamental Analysis
  • Technical Analysis
  • Mutual Funds
  • Miscellaneous

Popular On Elearnmarkets

  • Market Superheroes:
  • Vivek Bajaj
  • Chetan Panchamia
  • Ashish Kyal
  • Premal Parekh
  • Abhijit Paul
  • Jegan
  • Sivakumar Jayachadran
  • Jyoti Budhia
  • Vivek Gadodia
  • Vishal Mehta
  • Piyush Chaudhry
  • Santosh Pasi
  • Gomathi Shankar
  • Market Superheroes:
  • Vivek Bajaj
  • Chetan Panchamia
  • Ashish Kyal
  • Premal Parekh
  • Abhijit Paul
  • Jegan
  • Sivakumar Jayachadran
  • Jyoti Budhia
  • Vivek Gadodia
  • Vishal Mehta
  • Piyush Chaudhry
  • Santosh Pasi
  • Gomathi Shankar
  • Courses:​
  • Options Trading
  • Dow Theory
  • Momentum Trading
  • Stock Investing
  • Harmonic Chart Patterns
  • Algo Trading
  • Elliot Wave Theory
  • Advanced Excel
  • Cryptocurrency
  • NSE Certification Course
  • Courses:​
  • Options Trading
  • Dow Theory
  • Momentum Trading
  • Stock Investing
  • Harmonic Chart Patterns
  • Algo Trading
  • Elliot Wave Theory
  • Advanced Excel
  • Cryptocurrency
  • NSE Certification Course
  • Webinars:
  • Bank Nifty Scalping
  • Intraday Trading Strategies
  • Options Trading Strategies
  • Options selling
  • Price Action
  • Relative Strength
  • Tax Planning
  • Options Buying
  • Growth Stocks
  • Portfolio Management
  • Risk Management
  • Renko Charts
  • Crude Oil
  • Traders Psychology
  • Moving Average
  • Multibagger Stocks
  • Webinars:
  • Bank Nifty Scalping
  • Intraday Trading Strategies
  • Options Trading Strategies
  • Options selling
  • Price Action
  • Relative Strength
  • Tax Planning
  • Options Buying
  • Growth Stocks
  • Portfolio Management
  • Risk Management
  • Renko Charts
  • Crude Oil
  • Traders Psychology
  • Moving Average
  • Multibagger Stocks
  • Free Learning Modules:
  • Intraday Trading
  • Options Scalping
  • Swing Trading
  • Financial Modelling
  • RSI Indicator
  • Bollinger Bands
  • Pricing of Futures
  • Personal Finance
  • Initial Public Offerings (IPO)
  • Value Investing
  • Technical Indicators
  • Candlesticks
  • Chart Patterns
  • Option Greeks
  • ELSS Funds
  • Banking and Insurance
  • Real Estate
  • Gold
  • Free Learning Modules:
  • Intraday Trading
  • Options Scalping
  • Swing Trading
  • Financial Modelling
  • RSI Indicator
  • Bollinger Bands
  • Pricing of Futures
  • Personal Finance
  • Initial Public Offerings (IPO)
  • Value Investing
  • Technical Indicators
  • Candlesticks
  • Chart Patterns
  • Option Greeks
  • ELSS Funds
  • Banking and Insurance
  • Real Estate
  • Gold
  • Book Summaries:
  • Rich Dad Poor Dad
  • Psychology of Money
  • The Intelligent Investor
  • The Richest Man in Babylon
  • Think and Trade Like a Champion
  • Value Investing and Behavioural Finance
  • Trading in the Zone
  • Learn to Earn
  • Book Summaries:
  • Rich Dad Poor Dad
  • Psychology of Money
  • The Intelligent Investor
  • The Richest Man in Babylon
  • Think and Trade Like a Champion
  • Value Investing and Behavioural Finance
  • Trading in the Zone
  • Learn to Earn
  • Tools:
  • CAGR Calculator
  • SIP Calculator
  • eLearnOptions
  • Future Value Calculator
  • Present Value Calculator
  • Atal Pension Yojana
  • Cost of Delay Calculator
  • Become a Crorepati
  • Tools:
  • CAGR Calculator
  • SIP Calculator
  • eLearnOptions
  • Future Value Calculator
  • Present Value Calculator
  • Atal Pension Yojana
  • Cost of Delay Calculator
  • Become a Crorepati

© 2025 Elearnmarkets. All Rights Reserved

  • Visit Elearnmarkets
  • Courses
  • Webinars
  • Financial Guides
  • Get Free Counselling
  • Visit Elearnmarkets
  • Courses
  • Webinars
  • Financial Guides
  • Get Free Counselling

Download Our App

No Result
View All Result
  • Article Categories
    • Basic Finance
    • Derivatives
    • Financial Planning
    • Fundamental Analysis
    • Technical Analysis
    • ETFs & Mutual Funds
    • Marketshala
    • Miscellaneous
  • Language
    • Hindi
    • Bengali
    • English
  • Courses
  • Webinars
  • Stories
Get Free Course

© 2024 Elearnmarkets All Rights Reserved

Trading Mentorship Program