Help with NPV!

[Guest]

Please Help ASAP!
I have been working with the NPV function in excel, and I have run into a problem with regards to the "Frequency" of cash flows. I need to build an automated worksheet that will derive the NPV of 6 different cash flows, each with a variable number of years. i.e. on a financial calculator, after you enter an amount for a cash flow you also enter the number of years that cashflow remain constant for. Excel wants me to enter each cash flow, but if i want Cashflow 4 to last for 2 (or 3 or 99) years instead of 1 (before the other payments are calculated!), I want it to calculate it WITHOUT modifying the function. Please Help!

 

[Norman Harker]

Hi Jared!

Interesting! You seem to want to enjoy the financial calculator
approach of being able to insert CFi and Ni (Number of repeated
flows).

I'll send you the workbook if you like but here's a layout of one that
seems to work sweetly.

A1: Label: Cash Flow
B1: Label: Amount
C1: Label: Number

A2:A8 Labels: Cf0, Cf1, Cf2, Cf3, Cf4, Cf5, Cf6

B2:B8 Sample Amounts: -250000, 20000, 26000, 38000, 48000, 60000,
80000
C2:C8 Sample No's of Flows: 1, 10, 10, 10, 10, 15, 20

A11: 0
A12: 1

A13:
=IF(A12="","",IF(A12=SUM($C$2:$C$8),"",A12+1))
Copied down to A161 (allows 150 years if flows are annual but you'll
need more for monthly)

B11:
=B2
B12:
=IF(A12<$C$2,$B$2,IF(A12<SUM($C$2:$C$3),$B$3,IF(A12<SUM($C$2:$C$4),$B$
4,IF(A12<SUM($C$2:$C$5),$B$5,IF(A12<SUM($C$2:$C$6),$B$6,IF(A12<SUM($C$
2:$C$7),$B$7,IF(A12<SUM($C$2:$C$8),$B$8,"")))))))
Copied down to B161

F1: Label: Discount Rate
G1: 6%
F2: Label: NPV
G2: =NPV(H1,B12:B162)+B11
(Note principle due to Excel treating first flow as receivable after 1
period)

On my data, G2 returns: 246653.54



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Please Help ASAP!
I have been working with the NPV function in excel, and I have run

into a problem with regards to the "Frequency" of cash flows. I need
to build an automated worksheet that will derive the NPV of 6
different cash flows, each with a variable number of years. i.e. on a
financial calculator, after you enter an amount for a cash flow you
also enter the number of years that cashflow remain constant for.
Excel wants me to enter each cash flow, but if i want Cashflow 4 to
last for 2 (or 3 or 99) years instead of 1 (before the other payments
are calculated!), I want it to calculate it WITHOUT modifying the
function. Please Help!

 

[Ron Rosenfeld]

Please Help ASAP!
I have been working with the NPV function in excel, and I have run into a problem with regards to the "Frequency" of cash flows. I need to build an automated worksheet that will derive the NPV of 6 different cash flows, each with a variable number of years. i.e. on a financial calculator, after you enter an amount for a cash flow you also enter the number of years that cashflow remain constant for. Excel wants me to enter each cash flow, but if i want Cashflow 4 to last for 2 (or 3 or 99) years instead of 1 (before the other payments are calculated!), I want it to calculate it WITHOUT modifying the function. Please Help!

If the cash flows are for the same amount, but only the number of years is
changing, then perhaps the PV function would be the one to use. Number of
Periods is one of it's arguments.


--ron

 

[Norman Harker]

Hi Jared & Ron!

Re: If the cash flows are for the same amount, but only the number of
years is changing, then perhaps the PV function would be the one to
use. Number of
Periods is one of it's arguments.

You can use a "nested" PV approach to resolve an NPV without a cash
flow. In my example the direct NPV can be obtained using:

=PV(H1,C2,-B2,-PV(H1,C3,-B3,-PV(H1,C4,-B4,-PV(H1,C5,-B5,-PV(H1,C6,-B6,
-PV(H1,C7,-B7,-PV(H1,C8,-B8,0,1),1),1),1),1),1),1)
Returns: 246653.537841094

Suspiciously similar to the NPV approach that yielded 246653.537841093

In many ways I prefer this algorithm of analysis because it
facilitates variation of interest rates over time. I don't know if
you've noticed, but interest rates and yields do vary over time. The
NPV method assumes a constant rate. But that the start of a long
debate rather than a solution to your problem.

But both my previous approach of EITHER building the cash flow and
taking NPV OR using nested PVs suffer the limitations of nesting of
functions. The following sum of PVs approach doesn't suffer that
limitation:

=PV(H1,C2,-B2,0,1)+PV(H1,C3,-B3,0,1)*(1+H1)^-C2+PV(H1,C4,-B4,0,1)*(1+H
1)^-SUM(C2:C3)+PV(H1,C5,-B5,0,1)*(1+H1)^-SUM(C2:C4)+PV(H1,C6,-B6,0,1)*
(1+H1)^-SUM(C2:C5)+PV(H1,C7,-B7,0,1)*(1+H1)^-SUM(C2:C6)+PV(H1,C8,-B8,0
,1)*(1+H1)^-SUM(C2:C7)
Returns: 246653.537841094

With this approach I just keep summing the PVs for each income tranche
discounted by the term to commencement of that tranche.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
into a problem with regards to the "Frequency" of cash flows. I need
to build an automated worksheet that will derive the NPV of 6
different cash flows, each with a variable number of years. i.e. on a
financial calculator, after you enter an amount for a cash flow you
also enter the number of years that cashflow remain constant for.
Excel wants me to enter each cash flow, but if i want Cashflow 4 to
last for 2 (or 3 or 99) years instead of 1 (before the other payments
are calculated!), I want it to calculate it WITHOUT modifying the
function. Please Help!