Think & Done

ThinkAndDone.com offers TADXL custom Excel functions add-in that provides functionality not currently available in Excel built-in financial functions. Accountants and bookkeepers use Excel for analyzing accounting data, building financial models, perform bookkeeping operations, reporting and visualizing financial data. Excel is also used by scientific, engineering, and medical professionals to build applications and perform research analysis. Excel provides a set of built-in functions in many categories such financial functions, engineering functions, math and statistical functions. These standard Excel functions provide ample functionality yet some of these functions are rather limited. For example, Excel's five TVM or time value of money functions FV, PV, NPER, PMT and RATE assume that interest is compounded discretely. This would suffice in most situations yet in a good number of finance and banking applications, interest rates are compounded continuously as is the case with most bank savings accounts. To make Excel perform TVM calculations using continuous compounding, one has to resort to creating long hand formulas since the built-in Excel TVM functions do not offer such an option for continuous compounding of interest.

5 TVM functions in TADXL Add-in

Excel add-in provides the five Excel TVM functions with the added capability of selecting compounding of interest whether it is discrete or continuous. The following five TVM functions in TADXL Excel add-in use the same format for use as their Excel counterparts with the extra argument for compounding that either takes a value of 0 for discrete compounding of interest or a value of 1 for continuous compounding of interest. You can find more details about each of these 5 TVM functions in our functions reference link on top of this page.

• =tadPV(rate,nper,pmt,fv,type,compounding)
  tadPV performs the same calculations as Excel's PV function to find present value of a lump-sum or that of annuity. tadPV permits you to select whether interest is compounded discretely or continuously
• =tadFV(rate,nper,pmt,pv,type,compounding)
  tadFV performs the same calculations as Excel's FV function to find future value of a lump-sum or that of annuity. tadFV permits you to select whether interest is compounded discretely or continuously
• =tadNPER(rate,pmt,pv,fv,type,compounding)
  tadNPER performs the same calculations as Excel's NPER function to find number of periods of a loan, annuity, savings term deposit. tadNPER permits you to select whether interest is compounded discretely or continuously
• =tadPMT(rate,nper,pv,fv,type,compounding)
  tadPMT performs the same calculations as Excel's PMT function to find constant payment of a loan or that of a pension fund, savings account. tadPMT permits you to select whether interest is compounded discretely or continuously
• =tadRATE(nper,pmt,pv,fv,type,guess,compounding)
  tadRATE performs the same calculations as Excel's RATE function to find interest rate per period due on a loan or periodic savings account deposits offered by banks. tadRATE permits you to select whether interest is compounded discretely or continuously

Interest factors for discrete & continuous compounding

Interest factors for discrete compounding can be calculated in Excel using the FV and PV functions. Such interest factors include PVIF, PVIFA, PVIFAD, FVIF, FVIFA, and FVIFAD. tadXL provides 12 custom functions that calculate such interest factors along with SFF, CRF, CAF, PWF, PVIFGA and FVIFGA when interest is either compounded discretely or continuously. We have decided to include the following 12 interest factors functions in tadXL add-in:

• =tadPVIF(rate,nper,compounding)
  tadPVIF calculates present value interest factor of a $1 at an interest rate for number of periods. tadPVIF permits you to select whether interest is compounded discretely or continuously
• =tadFVIF(rate,nper,compounding)
  tadFVIF calculates future value interest factor of a $1 at an interest rate for number of periods. tadFVIF permits you to select whether interest is compounded discretely or continuously
• =tadPVIFA(rate,nper,compounding,type)
  tadPVIFA calculates present value interest factor of an ordinary annuity or annuity due worth a $1 at an interest rate for number of periods. tadPVIFA permits you to select whether interest is compounded discretely or continuously
• =tadFVIFA(rate,nper,compounding,type)
  tadFVIFA calculates future value interest factor of an ordinary annuity or annuity due worth a $1 at an interest rate for number of periods. tadFVIFA permits you to select whether interest is compounded discretely or continuously
• =tadPVIFAD(rate,nper,compounding)
  tadPVIFAD calculates present value interest factor of an annuity due worth a $1 at an interest rate for number of periods. tadPVIFAD permits you to select whether interest is compounded discretely or continuously
• =tadFVIFAD(rate,nper,compounding)
  tadFVIFAD calculates future value interest factor of an annuity due worth a $1 at an interest rate for number of periods. tadFVIFAD permits you to select whether interest is compounded discretely or continuously
• =tadPVIFGA(rate,growth_rate,nper)
  tadPVIFGA calculates present value interest factor of a growing annuity worth a $1 at an interest rate and growth rate for number of periods.
• =tadFVIFGA(rate,growth_rate,nper)
  tadFVIFGA calculates future value interest factor of a growing annuity worth a $1 at an interest rate and growth rate for number of periods.
• =tadCAF(rate,nper,compounding,type)
  tadCAF calculates compound amount factor of an ordinary annuity or annuity due worth a $1 at an interest rate for number of periods. tadCAF permits you to select whether interest is compounded discretely or continuously
• =tadCRF(rate,nper,compounding,type)
  tadCRF calculates capital recovery factor of an ordinary annuity or annuity due worth a $1 at an interest rate for number of periods. tadCRF permits you to select whether interest is compounded discretely or continuously
• =tadPWF(rate,nper,compounding,type)
  tadPWF calculates present worth factor of an ordinary annuity or annuity due worth a $1 at an interest rate for number of periods. tadPWF permits you to select whether interest is compounded discretely or continuously
• =tadSFF(rate,nper,compounding,type)
  tadSFF calculates sinking fund factor of an ordinary annuity or annuity due worth a $1 at an interest rate for number of periods. tadSFF permits you to select whether interest is compounded discretely or continuously

Investment Analysis Functions

Another example where Excel's built-in financial functions omit a necessary ability is the NPV function that works only for series of cash flows with end of period payments. To make Excel's NPV function to calculate net present value when series of cash flows form an annuity due with start of period payments, one has to skip the first cash flow as the function argument and add it to the results of the NPV calculation. TADXL add-in fixes this issue with calculation of net present value with tadNPV function that let's you select whether the series of cash flows occur at end of period or start of period. Excel has a couple of functions that are used in capital budgeting or for investment analysis: these include internal rate of return IRR function and modified internal rate of return MIRR function. Here too, Excel makes an assumption that interest rate is compounded discretely; tadXL addresses this issue with similar functions tadIRR and tadMIRR that permit you to specify whether the interest rate is compounded discretely or continuously.

IRR, MIRR and NPV are three of the various capital budgeting methods or metrics used in investment analysis. Other popular methods include NFV to calculate net future value of an investment, PP or payback period to find the time period required to recover the initial cost. DPP or discounted payback period takes time value of money into consideration when finding time period needed to recoup the costs of a project. PI Profitability index or B/C ratio is the ratio of discounted benefits over discounted costs. Excel does not provide functions to address payback period, discounted payback period, profitability index, B/C ratio and net future value of a project. Here TADXL add-in provides all four functions to perform the tasks. The following lists shows the eight investment analysis function that are part of the TADXL add-in for Excel.

• =tadNPV(discount_rate,type,compounding,cash_flows)
  tadNPV adds extra capabilities to Excel's NPV function to find net present value of an ordinary annuity with end of period cash flows and net present value of annuity due with start of period cash flows. tadNPV permits you to select whether interest is compounded discretely or continuously
• =tadNFV(discount_rate,type,compounding,cash_flows)
  tadNFV has no counterpart Excel function, it finds net future value of an ordinary annuity with end of period cash flows and net future value of annuity due with start of period cash flows. tadNFV permits you to select whether interest is compounded discretely or continuously
• =tadIRR(compounding,cash_flows)
  tadIRR performs the same calculations as Excel's IRR function to find internal rate of return from series of cash flows and an optional guess rate. tadIRR permits you to select whether interest is compounded discretely or continuously
• =tadMIRR(finance_rate,reinvestment_rate,compounding,cash_flows)
  tadMIRR performs the same calculations as Excel's MIRR function to find modified or adjusted internal rate of return from series of positive cash flows (benefits) and series of negative cash flows (costs). tadMIRR permits you to select whether interest is compounded discretely or continuously
• =tadPP(type,compounding,cash_flows)
  tadPP finds payback period from series of cash flows. Excel does not have a payback period function thus tadPP is the only function available for Excel that can calculate payback period.
• =tadDPP(discount_rate,type,compounding,cash_flows)
  tadDPP finds discounted payback period from series of cash flows and the discount rate. Excel does not have a discounted payback period function thus tadDPP is the only function available for Excel that can calculate discounted payback period. tadDPP permits you to select whether interest is compounded discretely or continuously
• =tadPI(discount_rate,type,compounding,cash_flows)
  tadPI finds profitability index from series of positive and negative cash flows along with the discount rate. Excel does not have a profitability index function thus tadPI is the only function available for Excel that can calculate profitability index. tadPI permits you to select whether interest is compounded discretely or continuously
• =tadBCR(discount_rate,type,compounding,cash_flows)
  tadBCR finds B/C ratio or benefits to cost ratio from series of benefits (positive cash flows) and costs (negative cash flows) along with the discount rate. Excel does not have a B/C ratio function thus tadBCR is the only function available for Excel that can calculate benefits to costs ratio. tadBCR permits you to select whether interest is compounded discretely or continuously

The list above included capital budgeting methods when the cash flows followed a periodic timeline, yet for irregular cash flows a variation of these methods are employed. Excel only caters to two such situations for NPV with a XNPV function and XIRR for IRR with irregular cash flows. TADXL includes not only these two methods but also all the other methods including XNFV, XMIRR, XPP, XDPP, XBCR, and XPI. These functions are included in the TADXL add-in functions library and it's usage is listed below:

• =tadXNPV(discount_rate,compounding,cash_flows,date_schedule)
  tadXNPV performs the same calculations as Excel's XNPV function to finds net present value from a series of irregular cash flows. tadXNPV permits you to select whether interest is compounded discretely or continuously
• =tadXNFV(discount_rate,compounding,cash_flows,date_schedule)
  tadXNFV has no counterpart Excel function, it finds net future value from a series of irregular cash flows. tadXNFV permits you to select whether interest is compounded discretely or continuously
• =tadXIRR(compounding,cash_flows,date_schedule)
  tadXIRR performs the same calculations as Excel's XIRR function to find internal rate of return from a series of irregular cash flows. tadXIRR permits you to select whether interest is compounded discretely or continuously
• =tadXMIRR(finance_rate,reinvestment_rate,compounding,cash_flows,date_schedule)
  tadXMIRR has no counterpart Excel function, it finds modified or adjusted internal rate of return from a series of irregular cash flows. tadXMIRR permits you to select whether interest is compounded discretely or continuously
• =tadXPP(type,compounding,cash_flows,date_schedule)
  tadXPP finds payback period from a series of irregular cash flows. Excel does not have a payback period function thus tadXPP is the only function available for Excel that can calculate payback period.
• =tadXDPP(discount_rate,compounding,cash_flows,date_schedule)
  tadXDPP finds discounted payback period from a series of irregular cash flows and the discount rate. Excel does not have a discounted payback period function thus tadXDPP is the only function available for Excel that can calculate discounted payback period. tadXDPP permits you to select whether interest is compounded discretely or continuously
• =tadXPI(discount_rate,compounding,cash_flows,date_schedule)
  tadXPI finds profitability index from a series of irregular cash flows and the discount rate. Excel does not have a profitability index function thus tadXPI is the only function available for Excel that can calculate profitability index. tadXPI permits you to select whether interest is compounded discretely or continuously
• =tadXBCR(discount_rate,compounding,cash_flows,date_schedule)
  tadXBCR finds B/C ratio or benefits to cost ratio from a series of irregular cash flows and the discount rate. Excel does not have a XB/C ratio function thus tadBCR is the only function available for Excel that can calculate benefits to costs ratio. tadXBCR permits you to select whether interest is compounded discretely or continuously

The finanical metrics I listed above process or operate on single series of cash flows, yet on occasions a financial analysts analyzes multiple projects at the same time. This usually requires finding incremental internal rate of return and incremental net present value from such cash flows. Furthermore a crossover rate helps an analyst find an interest rate at which both projects have the same NPV. To cater to this, TADXL provides three additional functions for finding incremental IRR, incremental NPV and crossover rate. The following explains the usage for such functions in TADXL Add-in.

• =tadINCNPV(discount_rate,type,compounding,cash_flows1,cash_flows2)
  tadINCNPV function finds incremental net present value from two series of cash flows. tadINCNPV permits you to select whether interest is compounded discretely or continuously
• =tadINCIRR(compounding,cash_flows1,cash_flows2)
  tadINCIRR function finds incremental internal rate of return from two series of cash flows and an optional guess rate. tadINCIRR permits you to select whether interest is compounded discretely or continuously
• =tadCORATE(compounding,cash_flows1,cash_flows2)
  tadCORATE function finds crossover rate from two series of cash flows and an optional guess rate. tadCORATE permits you to select whether interest is compounded discretely or continuously

When the capital budgeting projects have unequal lives the aforementioned methods are not suitable to make a sound decision about the approval of the projects. Here a financial analyst can make use of alternative techniques such as replacement chain method, equivalent annual annuity method and equivalent annual cost method. Excel does not have any functions for these capital budgeting methods yet TADXL Add-in for Excel provides functions that help find Replacement chain analysis NPV, EAA, and EAC.

• =tadRCNPV(discount_rate,type,compounding,cash_flows1,cash_flows2)
  tadRCNPV function finds replacement chain net present value from two series of cash flows with unequal lives. tadRCNPV permits you to select whether interest is compounded discretely or continuously
• =tadEAA(discount_rate,type,compounding,cash_flows)
  tadEAA finds equivalent annual annuity from a series of cash flows. tadEAA permits you to select whether interest is compounded discretely or continuously
• =tadEAC(discount_rate,type,compounding,cash_flows)
  tadEAC finds equivalent annual cost from a series of cash flows. tadEAC permits you to select whether interest is compounded discretely or continuously