Excel payback period function in tadXL v2.5
- tadPP ( cash flows, period, concentration )
- tadTPP ( cash flows, period, concentration )
- tadXPP ( cash flows, dates )
- tadXTPP ( cash flows, dates )
Payback period =tadTPP ( cash flows, period, concentration )
Real Payback period =tadXPP ( cash flows, dates )
Payback period =tadXTPP ( cash flows, dates )
Real Payback period =
Excel has none of its own financial functions to find the payback period, and it has been this way since 1990 when Excel was made available to Windows v3.0 users. In the time since the release of Windows v3.0 in 1990, there have been a number of upgrades to the Excel spreadsheet program yet most if not all of its financial functions have stayed the same. The financial functions found in Microsoft Excel up till Excel 2013 were those that were taken or based upon now defunct Lotus 123 spreadsheet program. In March of 2012, we at thinkanddone.com released tadXL add-in for Excel 2007, 2010 and 2013 that offered its own set of financial functions that extended the functionality of existing Excel functions and introduced a large number of new financial functions including those that find the payback period. A couple of months ago while answering a question about Excel XIRR function on the online Bytes magazine site, we come to know that Microsoft is planning to release a new set of financial functions in upcoming Excel 2015 or 2016. The person on Bytes magazine who ranks quite high on their forum seemed to justify the copied work that Microsoft is undertaking by giving arguments such as wasn't the Windows GUI based on existing programs such as Mac OS and Amiga. Wow, these people really have a nerve and Chutzpah to defend something that borders illegality and infringement of other peoples copyrights. His argument about our tadXL program was that the author of tadXL is not an authority on the subject of finance since he lacks a college degree. I am not sure if the jury would buy such an arguement if this matter goes into litigation, but lets not jump to conclusions and we will take a wait and see approach as to what will be presented by Microsoft when they do release the next version of Excel 2015 or 2016.
Now lets come back to the topic of finding payback period using Excel 2007, 2010 and 2013 with the financial functions found in tadXL add-in. The tadXL add-on offers a number of financial functions to find the payback period depending on how much of information is available about the investment. For example, if you have only the cash flows then finding the payback period is possible using the tadPP function. The tadPP function will take the series of cash flows and return the simple payback period. Now there is a difference between the definition of a payback and a real payback period. The former is concerned only with recovery of the initial cost whereas the latter would ensure recovery of all costs incurred in undertaking the investment. To find the real payback period of your investment when there are interim costs you will have to use the tadTPP function where T represents the true payback period. Yet if more of information about the investment becomes available such as a schedule of transaction dates for the cash flows, then finding the payback period is possible by invoking the tadXPP function. The tadXPP function will accept a series of cash flows and a schedule of transaction dates to return the date when the payback period occurs. Similarly if you are seeking a real payback period when date schedule is at hand then you would be using the tadXTPP financial function found in tadXL add-in.
If you thought that was all that tadXL has to offer then you are dead wrong, there are even more financial functions in tadXL that allow you to find the incremental and decremental payback period of an investment. This is applicable when comparing more than one investment where the life span of the investments differs so does the size of the cash flows. Functions such as tadIncPP and tadIncTPP find the incremental payback and real payback period. And decremental payback and real decremental payback period is found using tadDecTPP and tadDecTPP functions. Similarly if you have access to a schedule of transaction dates then finding the incremental and decremental payback periods is possible using tadXIncPP, tadXIncTPP, tadXDecPP and tadXDecTPP series of financial functions.
So far we have discussed finding payback period of a single investment, how about finding the payback period of a portfolio of investments. Finding payback period of a portfolio too is possible using the financial functions found in tadXL add-on. Please note that the financial functions to find payback period for incremental, decremental and portfolio will be available in the next release of tadXL.