NPV Help!

[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 calculate!), I want it to calculate it WITHOUT modifying the function. Please Help!

 

[Norman Harker]

Hi Jared!

No need to post to more than one group.

Here's the answer I gave over the road at misc:

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 calculate!), 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 calculate!), I want it to calculate it WITHOUT modifying the function. Please Help!

Try the PV function
--ron

 

[Norman Harker]

Hi Jared & Ron!

And keeping replies together for both threads:

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.

 

[Harlan Grove]

...
...

Interesting! You seem to want to enjoy the financial calculator
approach of being able to insert CFi and Ni (Number of repeated
flows). ...
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 ...
F1: Label: Discount Rate
G1: 6%
F2: Label: NPV

...

Quibble: the cashflows span 75 periods. Few if any business applications that
involve that many years, so if the periods are months, 6% would be an unlikely
interest rate. I'll use your sample data, but I'll assume the 6% is APR while
the cashflows are monthly, so the monthly effective interest rate is 0.5%.

Not a quibble: the superstructure is unnecessary unless the goal is to be
pedantic. Given just the entries above, the present value is given by the single
array formula (in G2)

=SUM(PV(G1/12,C2:C8,-B2:B8,,1)
/(1+G1/12)^MMULT(--(ROW(C2:C8)>TRANSPOSE(ROW(C2:C8))),C2:C8))

 

[Norman Harker]

Hi Harlan!

Nice solution.

"Few if any business applications that involve that many years"

I real estate we have lots of 99 and 125 year leases that need
analysing. For low yields (sub 6%) it starts to lead to significant
errors if you truncate too early. Also in those instances we often
want to value the reversionary interest after expiry in (say) 80
years. Admittedly we would be better of using a PV approach but some
like to keep it all in the same matrix and formula.
--
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.