Payback Period Formula in Excel (With Excel Template) Here we will do the same example of the Payback Period formula in Excel. The formula for payback period = Initial investment made / Net annual cash inflow For Investment A, the payback is = $100,000 / $20,000 = 5 years.
For Investment B, the payback is = $150,000 / $50,000 = 3 years.
The payback period helps us to calculate the time taken to recover the initial cost of investment without considering the time value of money. Learn how to calculate it with Microsoft Excel. Reading Time: 7 min Share on Facebook Share on Twitter Share on LinkedIn Share on WhatsApp. It is very easy and simple.
When the cash flow remains constant every year after the initial investment, the payback period can be calculated using the following formula: PP = Initial Investment / Cash Flow For example, if you invested $10,000 in a business that gives you $2,000 per year, the payback period is $10,000 / … So by adding INDEX(F19:M19,,COUNTIF(F17:M17,”<0″)+1) and COUNTIF(F17:M17,”<0”), you get a formula for payback period that will change dynamically based on the values you input. You need to provide the two inputs i.e Initial Investment and Cash Inflows. For Investment C, the payback is = $120,000 / $60,000 = 2 years. The formula to calculate the payback period of an investment depends on whether the periodic cash inflows from the project are even or uneven.If the cash inflows are even (such as for investments in annuities), the formula to calculate payback period is:When cash inflows are uneven, we need to calculate the cumulative net cash flow for each period and then use the following formula:Where,A is the last period number with a negative cumulative cash flow;B is th… The payback period is the amount of time needed to recover the initial outlay for an investment. For example, a $1000 investment which returned $500 per year would have a two year payback period.
How to Calculate Payback Period in Excel Sheet(with formula) November 10, 2016. This means that it will not evaluate the project … Excel, Techtites Featured, Tips and Tutorials Payback period in capital budgeting refers to the period of time required for the return on an investment to “repay” the sum of the original investment. Bonus tip: whenever possible link your tables to a single input so that updating the data is much simpler. You can easily calculate the Payback Period using Formula in the template provided.