Net Present Worth of reptitive costs?

[dexoey]

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:

NPW Formula.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:
NPW Formula 2.jpg

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


Thanks in advance

[MoneyMaker]

Hi to all,

I am new user, have an urgent question and
I wonder if you help me by.

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:

Thanks in advance

Part I of reply (due to short limit of 10,000 letters in editor)

To answer your question in brief would be that Excel has no function of it's own that can find the present value of repetitive values that occur every J period. I will offer you a way to solve your problem by making use of Excel PV function offered by 3rd party add-ins. But before I present you with a solution, it would be required of me to explain the shortcomings of financial functions found in Excel and how to address such problems by using 3rd party solutions.

Excel has 5 time value of money functions namely RATE, NPER, PMT, PV & FV. All these function solve for the particular value using a TVM equation listed below

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

If you look closely, the equation is setting the sum of discounted future value, discounted periodic payment and the un-discounted present value to zero. This leads us to define our five TVM functions as f(RATE), f(NPER), f(PMT), f(PV) and f(FV)

Each of these functions can be solve for except f(RATE) which requires making use of iterative methods to approximate the actual rate.

But then the TVM equation that I have listed is the trivial form of time value of money equation as it leaves out options that are desired when performing TVM Calcs. For example, in finance, banking and business you would come across periodic payments that are not in uniform series of amount. Such payments have a GRADIENT meaning that these payments may grow or shrink by a growth or shrink rate. It may also be that such payments increase or decrease by a constant money amount.

The growth rate in such an equation would usually represent inflation and interest rate would represent the rate of return. But then the growth in annuity payments or increase in annuity payments may not be periodic meaning the growth occurs every J period or increase in annuity payment occurs every K period.

But the story continues and you want even more out of your TVM equation to consider a series of never ending annuity payments. Such payments go on for ever and we would want to find the net present value of such a never ending stream of payments. This is called finding the worth of a perpetuity. And to ask for more you would want the ability to consider a never ending payment that may have a growth rate or it may increase by a constant money amount.

As you have seen with your problem at hand that there would be times when the annuity payment that is in uniform amount (constant payment) may not be periodic and the payments occur each J period rather that each single period.

Conclusion: Throw out Excel in trash bin and get yourself a copy of tadXL add-in

[MoneyMaker]

Hi to all,

I am new user, have an urgent question and I wonder if you help me by.

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:
Attachment 227771

Thanks in advance

Part II of reply (due to short limit of 10,000 letters in editor)

Solution:

The collection of 95 financial functions in tadXL would extend the functionality of existing Excel financial functions and offer a wide range of new financial functions. Take for example the 6 TVM functions in tadXL as compared to 5 TVM functions of Excel itself. The new TVM function found only in tadXL is called a GRADIENT and it permits you to find the growth or shrink rate by which an annuity payment grows or shrinks. It may also help you find the constant money amount by which an annuity increases or decrease each period or each J periods.

It is now possible to value the never ending series of payments (perpetuity) and find the internal rate of return on such never ending stream of payments using the tadPV, tadRATE and tadPMT functions. To do so, you would use INF (short for infinity) as a value for the NPER variable.

The NPER variable in Excel stands for number of periods where we have no way to tell Excel what this particular period may be such as Year, Quarter, Month, Week, or Day. With tadXL you would have access for new options in it's TVM function that will permit you to specify the period as a Year by using a value of 1 for function arguemnt called PERIOD. You may use just about anything as a value for period for example 1/365 for day, 2 for biennial, 1/2 for half-year and so on

The most frustrating aspect of using Excel TVM functions has to do with the lack of options to specify compounding frequency of interest rate. This issue too has been address by tadXL with an added arguments in its TVM function called COMPOUNDING. Now using this you can tell Excel that compounding of interest is annual with a value of 1. If the compounding of interest were to be semi-annual you would use 6/12 as the value. For daily compounding a value of 1/365 would be used. And if the compounding of interest were to be infinite or continuous then you can specify that with a value of 0.

Original problem stated by OP may be better understood if we look at the series of cash flows required to solve the problem. The PV function in Excel would only work when annuity payment is periodic the second column in the following table represents such cash flows. The 3rd and 4th column in the following table shows how the cash flows look like when annuity payments occur each J period

N	CF1	CF2	CF3
1	1000	1000	0
2	1000	0	0
3	1000	0	1000
4	1000	1000	0
5	1000	0	0
6	1000	0	1000
7	1000	1000	0
8	1000	0	0
9	1000	0	1000
10	1000	1000	0
11	1000	0	0
12	1000	0	1000
13	1000	1000	0
14	1000	0	0
15	1000	0	1000

The annuity payments under CF2 begin at the start of each J period and the annuity payments under CF3 begin at the end of each J period.

Have a look now at how the net present value differs from each of the three sets of cash flows listed in the prior table

N	CF	CF x PVIF	DCF	PV
1	1000	1000 x (1+2%)^-1	1000 x 0.98039	980.39
2	1000	1000 x (1+2%)^-2	1000 x 0.96117	961.17
3	1000	1000 x (1+2%)^-3	1000 x 0.94232	942.32
4	1000	1000 x (1+2%)^-4	1000 x 0.92385	923.85
5	1000	1000 x (1+2%)^-5	1000 x 0.90573	905.73
6	1000	1000 x (1+2%)^-6	1000 x 0.88797	887.97
7	1000	1000 x (1+2%)^-7	1000 x 0.87056	870.56
8	1000	1000 x (1+2%)^-8	1000 x 0.85349	853.49
9	1000	1000 x (1+2%)^-9	1000 x 0.83676	836.76
10	1000	1000 x (1+2%)^-10	1000 x 0.82035	820.35
11	1000	1000 x (1+2%)^-11	1000 x 0.80426	804.26
12	1000	1000 x (1+2%)^-12	1000 x 0.78849	788.49
13	1000	1000 x (1+2%)^-13	1000 x 0.77303	773.03
14	1000	1000 x (1+2%)^-14	1000 x 0.75788	757.88
15	1000	1000 x (1+2%)^-15	1000 x 0.74301	743.01
		NPV	T= 1 to N	$12,849.26

N	CF	CF x PVIF	DCF	PV
1	1000	1000 x (1+2%)^-0	1000 x 1.00000	1000
2	0	0 x (1+2%)^-1	0 x 0.98039	0
3	0	0 x (1+2%)^-2	0 x 0.96117	0
4	1000	1000 x (1+2%)^-3	1000 x 0.94232	942.32
5	0	0 x (1+2%)^-4	0 x 0.92385	0
6	0	0 x (1+2%)^-5	0 x 0.90573	0
7	1000	1000 x (1+2%)^-6	1000 x 0.88797	887.97
8	0	0 x (1+2%)^-7	0 x 0.87056	0
9	0	0 x (1+2%)^-8	0 x 0.85349	0
10	1000	1000 x (1+2%)^-9	1000 x 0.83676	836.76
11	0	0 x (1+2%)^-10	0 x 0.82035	0
12	0	0 x (1+2%)^-11	0 x 0.80426	0
13	1000	1000 x (1+2%)^-12	1000 x 0.78849	788.49
14	0	0 x (1+2%)^-13	0 x 0.77303	0
15	0	0 x (1+2%)^-14	0 x 0.75788	0
		NPV	T= 0 to N-1	$4,455.54

N	CF	CF x PVIF	DCF	PV
1	0	0 x (1+2%)^-1	0 x 0.98039	0
2	0	0 x (1+2%)^-2	0 x 0.96117	0
3	1000	1000 x (1+2%)^-3	1000 x 0.94232	942.32
4	0	0 x (1+2%)^-4	0 x 0.92385	0
5	0	0 x (1+2%)^-5	0 x 0.90573	0
6	1000	1000 x (1+2%)^-6	1000 x 0.88797	887.97
7	0	0 x (1+2%)^-7	0 x 0.87056	0
8	0	0 x (1+2%)^-8	0 x 0.85349	0
9	1000	1000 x (1+2%)^-9	1000 x 0.83676	836.76
10	0	0 x (1+2%)^-10	0 x 0.82035	0
11	0	0 x (1+2%)^-11	0 x 0.80426	0
12	1000	1000 x (1+2%)^-12	1000 x 0.78849	788.49
13	0	0 x (1+2%)^-13	0 x 0.77303	0
14	0	0 x (1+2%)^-14	0 x 0.75788	0
15	1000	1000 x (1+2%)^-15	1000 x 0.74301	743.01
		NPV	T= 1 to N	$4,198.56

We can only solve for the present value of 2nd and 3rd series of annuity payments using tadPV function which would not be otherwise possible use Excel PV function

Case 1:
=tadPV(2%,,,15,-1000)
$12,849.26

Case 2:
=tadPV(2%,,,15/3,-1000,,1,,,3)
$4,455.54

Case 3:
=tadPV(2%,,,15/3,-1000,,0,,,3)
$4,198.56