Hi to all,

I am new user, have an urgent question and I wonder if you help me by. I am going to convert different costs during life time of a product to Net Present Worth (NPW), consisting Maintenance and Rehabilitation costs (M&Rj) and Salvage Value (SV), according to the following formula:
https://www.dropbox.com/s/38aeqvrz0w...%20Formula.jpg

SV occurs only once at the end of life time, it´s NPW in Excel can be calculated as following:

=PV(i discount, AP,0, SV,1)

but M&Rj costs occurs J time during life cycle, and formulating manually their mathematical formula most times takes much time. Exactly what should I write in the cell to get their total NPW? In other words, how can I get the following formula by Excel functions:
https://www.dropbox.com/s/nrcgo41j0b...ormula%202.jpg

---------------------------------------------------------------------------------
Reminder: NPW= FV( 1/(1+i Discount))^n
FV: future value
i Discount: discount rate
n: numbers of years (periods)


Thanks in advance

As M&R costs occur every J period thus the Excel PV function may not be of help

The PV function and other 4 TVM functions in Excel make use of this equation

FV (1+RATE)^-NPER + PMT * (1+RATE*type) * [1 - {(1+RATE)^-NPER}] / RATE + PV = 0

You were able to solve for SV as this was the FV in the TVM equation

M&R are periodic but then occur each J period thus the PMT part of the TVM equation is unable to solve for this

However there are 3rd party Excel add-in programs such as tadXL that offer their own TVM functions. Such TVM functions have added values and one of which is the value for PERIOD

Here you can specify the length of the period and in this case N is the length of the period

This will let you to find present value for M&R using tadPV function

If M&R costs is in amount of $1000 and occurs every 5 years and the total number of periods were 20 then the following call to tadPV function would find the present value at 3% rate

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

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