Discounted payback period in Excel

As there aren't any financial functions in Excel itself to find the discounted payback period thus it would take 3rd party programs such as moneyXL to bridge the gap. Excel has been around since 1990 when Windows v3.0 was first launched and GUI based operating systems became commonplace. Even the graphical user interface offered by Windows v3.0 had already been around with programs such as Mac OS. Now speaking of the upcoming Excel 2015 or 2016, the word has it that Microsoft is planning to release its own set of financial functions to the Excel users. But if the story is infact true then more than likely most if not all of the new financial functions will be based on or taken from the moneyXL add-in for Excel 2007, 2010 and 2013. I got to hear about Microsoft plans while answering a question about the Excel XIRR function on Bytes magazine site where a high ranking contributor justified the copied work that Microsoft is undertaking by saying that author of the original work, yours sincerely does not have the authority on the subject of finance as he lacks a degree. But you know those who steal will cook up excuses to justify the deed they had done. Having or not having a degree is not an issue here, what is at stake is one persons intellectual copyrights and you would think a company such as Microsoft who cries foul that it loses out on millions of dollars of revenue due to piracy would at least think twice before trampling upon someone else's work. But who cares I suppose when you have one billion installation of Microsoft Excel around the world, cooking up new financial functions for hungry masses brings in quite a lot of dough. Who loses out in the end is the one who invested his time and efforts to research, design and develop the set of financial functions that is called moneyXL add-on.

Now back to the topic of finding discounted payback period in Excel, the DPP as it is called is the time period required to recover the initial cost incurred in undertaking the capital investment. If you were to be concerned about recovery of all costs incurred in undertaking an investment then you are seeking the real discounted payback period instead. The task of finding the payback period is quite cumbersome as there are no formulas to find it and analsyt has to make use of tables to find it. Excel is a good fit product for crunching numbers and here one can exploit the programming capabilities of Excel engine to create the financial functions that are not native to Excel. 3rd party solutions such as moneyXL offer series of financial functions to find the discounted payback period depending upon how much of information is available about the investment. There are 8 different financial functions in moneyXL to find the discounted payback period namely moneyDPP, moneyDPPSchedule, moneyTDPP, moneyTDPPSchedule, moneyXDPP, moneyXDPPSchedule, moneyXTDPP and moneyXTDPPSchedule. So which one of these functions should you use, the answer depends upon how much information do you have about the cash flows from the investment. For the simplest of payback period calculations, you would be using the moneyDPP function that will take a series of cash flows and the discount rate. Yet if you have a term structure for interest rates then finding the payback period is made easy by using moneyDPPSchedule that not only accepts the series of cash flows but also a schedule of discount rates. But if you have access to a schedule of transaction dates for the cash flows then the moneyXDPP function would take care of the payback period calculations. However if you happen to have a schedule of discount rates along with schedule of transaction dates then the moneyXDPPSchedule is the function required. There are four other financial functions the list that allow you to find the real discounted payback period that ensure recovery of all the costs as opposed to just the initial cost.

In addition to the 8 different financial functions mentioned thus far, there are even more financial functions found in moneyXL add-in to find the discounted payback period when comparing investments that have unequal life spans. For example finding incremental and decremental discounted payback period is possible by using the moneyIncDPP and moneyDecDPP functions found in moneyXL. On the same lines it is also possible to find the incremental and decremental real discounted payback period using the moneyIncTDPP and moneyDecTDPP functions. And if you haven't already guessed the other variants of incremental and decremental discounted payback period functions will allow for schedule of discount rates and transaction dates. And for sensitivity analysis, a number of financial functions in moneyXL will allow for expected values when discounted payback period is considered as a random variable. An example of such as financial function in moneyXL is the moneyeDPP and moneyeTDPP functions that accept the values for discounted payback periods of series of investmenst and their respective probability values to find the mean or average discounted payback period.

The moneyXL add-in is available for 32-bit Excel 2007, 2010 and 2013 and for 64-bit Excel 2010 and 2013. You will find the links to download the latest version of moneyXL v2.5 from the following download cart that will permit you to find the discounted payback period using all of the financial functions that were discussed earlier. Please feel at home and get yourself copy of this unique, quality Excel add-in.

If you thought that was the last of the financial functions in moneyXL to find the discounted payback period then you are dead wrong, there are financial functions in the latest version of moneyXL that permit you to find the discounted payback period for a porfolio of investments. This would require you to enter the series of data for each of the investments and their respective discount rates to find the discounted payback period of a portfolio of investments. The version of moneyXL offering incremental, decremental discounted payback period and the options for portfolio analysis is currently under development and will be released soon.

  1. moneyDPP ( discount rate, cash flows, compounding, period, concentration )

Data input


Data output

Discounted payback period =