If the numbers in tadPV formula listed in my last post seem meaningless, then let me show you what each of the values in the formula represent.

If the M&R costs begin immediately then the following values would be placed in the Excel PV function http://tadxl.com/excel_pv_function.html that is part of tadXL add-in http://tadxl.com/

RATE: 3%
GRADIENT: 0%
TAXRATE: 0%
NPER: =20/5
PMT: $(1,000)
FV: 0
TYPE: 1
GTYPE: 1
COMPOUNDING: 1
PERIOD: 5
DISTRIBUTION: 1
GPERIOD: 1

=tadPV( 3%, 0%, 0%, 20/5, -1000, 0, 1, 0, 1, 5, 1 )
$3,248.56

Yet if the costs begin at the end of first period then the following values would used in the formula to find net present worth.

RATE: 3%
GRADIENT: 0%
TAXRATE: 0%
NPER: =20/5
PMT: $(1,000)
FV: 0
TYPE: 0
GTYPE: 1
COMPOUNDING: 1
PERIOD: 5
DISTRIBUTION: 1
GPERIOD: 1

=tadPV( 3%, 0%, 0%, 20/5, -1000, 0, 0, 0, 1, 5, 1 )
$2,802.24


In both instances we are defining the period having a length of 5 years by giving value of 5 to the variable called PERIOD.

We are then telling the tadPV formula that the number of periods are 4 by giving NPER a value of 20/5. That is twenty years divided by the lenght of each period.

This sort of calculation is not possible with Excel's own PV function due to the reasons mentioned in my last post. Only tadXL offers extended TVM functions of RATE, GRADIENT, NPER, PMT, PV and FV that permit performing time value of money calculations in various scenarios.

We can confirm the results produced by Excel PV function http://tadxl.com/excel_pv_function.html by performing online NPV calculation http://njinstruments.com/financial/npv-calculator.html

For the first example when costs are incurred immediately

=tadPV( 3%, 0%, 0%, 20/5, -1000, 0, 1, 0, 1, 5, 1 )
$3,248.56

NPV = 3,248.56
How net present value was calculated
Present value of cash flows
DCF1 = #### 1000 x (1+3%)-0 #### 1000 x 1.00000 #### 1,000.00
DCF2 = #### 0 x (1+3%)-1 #### 0 x 0.97087 #### 0.00
DCF3 = #### 0 x (1+3%)-2 #### 0 x 0.94260 #### 0.00
DCF4 = #### 0 x (1+3%)-3 #### 0 x 0.91514 #### 0.00
DCF5 = #### 0 x (1+3%)-4 #### 0 x 0.88849 #### 0.00
DCF6 = #### 1000 x (1+3%)-5 #### 1000 x 0.86261 #### 862.61
DCF7 = #### 0 x (1+3%)-6 #### 0 x 0.83748 #### 0.00
DCF8 = #### 0 x (1+3%)-7 #### 0 x 0.81309 #### 0.00
DCF9 = #### 0 x (1+3%)-8 #### 0 x 0.78941 #### 0.00
DCF10 = #### 0 x (1+3%)-9 #### 0 x 0.76642 #### 0.00
DCF11 = #### 1000 x (1+3%)-10 #### 1000 x 0.74409 #### 744.09
DCF12 = #### 0 x (1+3%)-11 #### 0 x 0.72242 #### 0.00
DCF13 = #### 0 x (1+3%)-12 #### 0 x 0.70138 #### 0.00
DCF14 = #### 0 x (1+3%)-13 #### 0 x 0.68095 #### 0.00
DCF15 = #### 0 x (1+3%)-14 #### 0 x 0.66112 #### 0.00
DCF16 = #### 1000 x (1+3%)-15 #### 1000 x 0.64186 #### 641.86
DCF17 = #### 0 x (1+3%)-16 #### 0 x 0.62317 #### 0.00
DCF18 = #### 0 x (1+3%)-17 #### 0 x 0.60502 #### 0.00
DCF19 = #### 0 x (1+3%)-18 #### 0 x 0.58739 #### 0.00
DCF20 = #### 0 x (1+3%)-19 #### 0 x 0.57029 #### 0.00
NPV (Annuity Due) T= 0 to N-1 #### $3,248.56

For the second example when costs are incurred at the end of 5 year period

=tadPV( 3%, 0%, 0%, 20/5, -1000, 0, 0, 0, 1, 5, 1 )
$2,802.24

NPV = 2,802.24
How net present value was calculated
Present value of cash flows
DCF1 = #### 0 x (1+3%)-1 #### 0 x 0.97087 #### 0.00
DCF2 = #### 0 x (1+3%)-2 #### 0 x 0.94260 #### 0.00
DCF3 = #### 0 x (1+3%)-3 #### 0 x 0.91514 #### 0.00
DCF4 = #### 0 x (1+3%)-4 #### 0 x 0.88849 #### 0.00
DCF5 = #### 1000 x (1+3%)-5 #### 1000 x 0.86261 #### 862.61
DCF6 = #### 0 x (1+3%)-6 #### 0 x 0.83748 #### 0.00
DCF7 = #### 0 x (1+3%)-7 #### 0 x 0.81309 #### 0.00
DCF8 = #### 0 x (1+3%)-8 #### 0 x 0.78941 #### 0.00
DCF9 = #### 0 x (1+3%)-9 #### 0 x 0.76642 #### 0.00
DCF10 = #### 1000 x (1+3%)-10 #### 1000 x 0.74409 #### 744.09
DCF11 = #### 0 x (1+3%)-11 #### 0 x 0.72242 #### 0.00
DCF12 = #### 0 x (1+3%)-12 #### 0 x 0.70138 #### 0.00
DCF13 = #### 0 x (1+3%)-13 #### 0 x 0.68095 #### 0.00
DCF14 = #### 0 x (1+3%)-14 #### 0 x 0.66112 #### 0.00
DCF15 = #### 1000 x (1+3%)-15 #### 1000 x 0.64186 #### 641.86
DCF16 = #### 0 x (1+3%)-16 #### 0 x 0.62317 #### 0.00
DCF17 = #### 0 x (1+3%)-17 #### 0 x 0.60502 #### 0.00
DCF18 = #### 0 x (1+3%)-18 #### 0 x 0.58739 #### 0.00
DCF19 = #### 0 x (1+3%)-19 #### 0 x 0.57029 #### 0.00
DCF20 = #### 1000 x (1+3%)-20 #### 1000 x 0.55368 #### 553.68
NPV (Ordinary Annuity) T= 1 to N #### $2,802.24