NPV, IRR, MIRR, payback period

NPV, IRR, MIRR, payback period are some of the tools that are available to an analyst to find an investor's ROI - return on investment. The IRR and MIRR are percentage interest rates, whereas the NPV is a money amount. In contrast payback period is the time required to recover the initial cost incurred in undertaking the project.

There are several tools at the disposal of an analyst to help her find an investor's return on investment - ROI. Some of these measures include the IRR, MIRR, NPV, NFV, EAA, EAC, PP, DPP, Financial PI, and the B/C ratio. All these tools have there pros and cons such as the IRR happens to be the interest rate at which the investor breaks-even. The NPV - net present value is the sum of discounted cash flows that shows the money to made or lost from the investment as of present. The net future value is the sum of compounded cash flows that shows the money to be made or lost from the investment at the end of investment period. The EAA - equivalent annaul annuity is the uniform amount of money that is made or lost per period over the life of the investment. The EAC - equivalent annual cost if the uniform amount of cost that is paid per period over the life of the investment. The PP - payback period is the time period required to recover just the initial cost of the investment. The DPP - discounted payback period is the time period required to recover the initial cash outlay when the future incomes are discounted to reflect their present worth. The Financial PI - profitability index is the ratio of discounted profits over the discounted expenditures. The BCR - benefit to cost ratio is the ratio of discounted benefits over discounted costs. All these measures over the analyst insights into finding the worthiness of an investment.

The tadXL add-in offers financial functions to analyze investments including the Excel functions for IRR MIRR NPV and payback period. Out of these four methods Excel offers functions for only IRR MIRR and NPV yet all such functions have severe limitations. First of all, the native Excel functions do not allow for compounding frequencies of interest, further in Excel there is no way to specify the length of the payment periods. And the functions in Excel default to the use of full-year discounting convention. In contrast the tadIRR, tadMIRR tadNPV and tadPP period function offer options to use almost any type of compounding frequency of interest, and payment periods of almost any length, it permits you to use any discounting convention of your choice such as mid-year discounting. For example, let us now find internal rate of return, modified IRR, net present value and payback period for a series of cash flows that are discounted at 10%. Such an investment cost $100 (in millions ) making quarterly returns in amounts of $60, $40, $35 and $15 using weekly compounding of interest. Now to find the IRR we use the Excel formula as =tadIRR({-100, 60, 40, 35, 15}, , 1/52, 1/4, 0.5 ) and the function will return an internal rate of return that is compounded weekly for quarterly payments that use mid-year discounting convention. Similarly we find the MIRR with the formula in Excel as
=tadMIRR(10%, 10%, {-100, 60, 40, 35, 15}, 1/52, 1/4, 0.5 )
and the net present value with a formula such as
=tadNPV(10%, {-100, 60, 40, 35, 15}, 1/52, 1/4, 0.5 )
and the payback period using the formula as
=tadPP({-100, 60, 40, 35, 15}, 0.5 )