NPV / PV Calculation / Formula with Cumulative / Compounding Cash Flow

[donkeybusiness]

Dear all,

Good day.

Kindly see my attached file, just wondering is there a formula or any way that could calculate the present value for year 4 - 8 (as shown as ?? in the Template, which should total 14,199.85, based on the Formula Proofing) instead of breaking the years into each individual columns, which would end up showing the correct value.
Reason being I have a cash flow that could last up to 30 years and it would be tedious to expand those.

There are 2 elements in this cash flow:
1) Income for 1st 3 years is based on actual no. and from 4th year onwards, it's a 10% constant growth;
2) Income for 1st 3 years is based on actual no. and from 4th year onwards, it's a 10% of sales.

The second table below shows the expected correct no. if they're broken down into individual columns.
Many thanks in advance!

[Ace_XL]

Try

=SUMPRODUCT(C3*(1+$C$1)^ROW(A4:A8),1/(1+C1)^(ROW(A4:A8)+1))

where Row(A4:A8) signifies Year 4-8

[bart hoekstra]

that is 5*909.09
(8-4+1)*909.09 I guess this is not what you want!??

[Richard Buttrey]

...or another way

Please Login or Register  to view this content.

[joeu2004]

Kindly see my attached file, just wondering is there a formula or any way that could calculate the present value for year 4 - 8 (as shown as ?? in the second green table, which should total 4,545.45) instead of breaking the years into each individual columns
Attachment 368862

Conceptually, you want to find the amount to deposit in the beginning of period 1 to fund withdrawals at the end of periods k through n. The deposit earns interest at the NPV discount rate. The withdrawal amount grows at a specified rate starting with a base amount for period 1.

Suppose you have the following data:

	A	B
1	pmt1	1,000.00
2	%pmt incr	10.00%
3	%discnt rate	10.00%
4	k	4
5	n	8

In the simple case (your example) where the %pmt increase is the same as the %discount rate, the PV (beginning of period 1) of the withdrawals starting at the end of period k is simply:

Please Login or Register  to view this content.

More generally, if the %pmt increase is different from the %discount rate, the PV is:

Please Login or Register  to view this content.

[donkeybusiness]

Apologies, I think I didn't make my worksheet to be clearer, there appears to be further complications (please see my attached reworked file)

[Richard Buttrey]

Apologies, I think I didn't make my worksheet to be clearer, there appears to be further complications (please see my attached reworked file)

Hi,

You don't appear to have taken notice of my suggestion in post #4.

If you use this, with the row references changed to reflect the new position of your data than you'll find

Please Login or Register  to view this content.

gives you the 14199.85 value you seek.

[donkeybusiness]

Hi Richard, I've seen your suggestion earlier, however, the table below (ie row 22) is only for 'proofing' purpose; therefore cannot be use. The formula that I require should only be restricted to whatever available in b12:f16.

[Ace_XL]

Try

=SUMPRODUCT(E13*0.9*(1+$C$1)^ROW(A1:A5),1/(1+C1)^(ROW(A4:A8)))

The 0.9 depicts less expenses of 10%. The Row numbers are for referencing number of years

[donkeybusiness]

Hi Ace, your formula works, do you mind explaining the logic behind that formula? Will this only work if the expenses is a % of sales?
Thanks!!

[Ace_XL]

Let me break this down

The first part i.e. E13*0.9*(1+$C$1)^ROW(A1:A5) creates an array of values increasing with E13 as the base at the given rate (C1) for 5 years (Row(A1:A5)). The 0.9 of course is net of expenses. Alternatively, you could have a separate array for the expenses too assuming they increase at a given rate and have a base year too. It'll just be a longer formula

The second part i.e. 1/(1+C1)^(ROW(A4:A8)) creates an array of the discounting years beginning Year 4 to year 8

The SUMPRODUCT multiplies the first and second arrays and sums them up to give you desired results

[donkeybusiness]

I've have a thorough look at it again, thanks all for help!