Excel does not offer any of its financial functions to calculate the simple payback period. The good news however is that you can use tadXL add-in a collection of financial functions that offers its version of the PP Excel function. Here, me Abraham A. will walk you through steps to required to find the payback period using the Excel tadPP function.
All tad Finance products are available for download at this link.
Payback period in finance refers to the time period required to recover just the initial cost incurred in undertaking an investment. This sounds simple but it is not so simple to find the payback period since there are no formulas to find one. This makes us to look for easy and quick solutions in Excel but there are no financial functions in Excel to find the payback period. However third party tools such as tadXL add-in for Excel 2007, 2010, and 2013 offer tadPP function to find the payback period in Excel. This tadPP function takes the cash flows and returns the simple payback period; you can also specify the length of the period with this function and the discounting convention such as mid-year discounting. All these features make tadXL add-in a must have tool for the professional financial analyst.
Before we proceed to find payback period in Excel, you would need the tadXL add-in which is available for download here. There are different versions of this add-in i.e. v0.5, v1.0, v2.0 and v2.5 and you would have to select one of them for installation.
The payback period Excel function requires the following values as input:
=tadPP ( cash_flows, type, period, distribution )
You can download a copy of payback period template here.
Finance tutorials on a variety of topics ranging from finding ROI using IRR, NPV, Payback period, etc. TVM calculations i.e. present value & future value of loans and bank deposits. Valuation & yield on stocks and bonds i.e. cost of equity, & debt. Rates of return on investments i.e. ARR, GRR, holding period return & yield.
Excel 4 finance to perform DCF analysis NPV, xNPV, IRR, xIRR, MIRR, xMIRR etc. Time value of money calculations i.e GRADIENT, INTEREST RATE, NPER, PMT, etc, yields and prices of bonds and stocks. Rates of return i.e. HPR, HPY, AHPR, and AHPY, etc.
TI BA II plus guide to analyze investments using capital budgeting methods i.e IRR, NPV, MIRR etc. TVM calculations i.e. RATE, NPER, PMT, PV, FV. Interest rate factors i.e. PVIFA, PVIF, FVIFA, FVIF, etc .
Finance tables to find interest factors for present value and future value of $1 and of annuities with start of period and end of period payments in amount of $1. Interest rates for growing and shrinking annuities are available as well.