Dynamic Periods NPV

[ctindell]

Is it possible to calculate a Future Value that has an NPV of 0 without using goal seek or VBA?

I have an investment in stock quantity (D3) and price (D4) to calculate a cash outflow (D5). I have a return rate (D8) of 10% and a holding period (D7) in months and there are no other cash inflows until I sell the stock. I am trying to calculate the price or cash inflow, I need the stock to rise to, in order to have a zero NPV. When I calculate this currently, the FV is 10% higher, however, due to time value of money in NPV, this value returns a negative NPV. I have a table below with the following formula to calculate FV based on holding period (D7):

=IF(C16=$D$7,((1+$D$8)^(C16/12))*-$D$5,0)

Maybe I don't even need to discount this on an NPV basis because there are no period cash flows - any help would be appreciated.

 

[gmhumphr]

Assuming you use the same interest rate to calculate the FV as you do to evaluate NPV, it FV returns exactly an NPV 0 result.

Get back to the theory: NPV calculates value from a stream of cash flows discounted back to day 0 at a given investment rate. For each period, it discounts the cash flow amount to PV, and aggregates those values. NPV returns a non-zero result when the sum of those discounts vary from the required rate of return you feed into the calculation (assuming constant reinvestment at that rate).

If you applied the NPV calculation to stock returns where you simply buy at T0 and sell at T12, it would see a bunch of 0 value cash flows that would discount to 0, and be aggregated with the investment amount and the PV of the future amount at T12. 0 discounts to 0, so you can ignore it. In effect, even if you ran NPV with a bunch of 0 periods, you are only discounting the final cash from the sale. This is EXACTLY the same thing that the FV function does, it simply skips the middle of discounting 0 amounts in the intervening periods.

 

[gmhumphr]

Try using the built in formulas instead of calculating your own (you can still nest it within the IF statement if you need)

Example:

Outflow of 100
Rate of 10% (APR) - passed to function divided by 12
Hold for 12 periods (1 year)

=FV([rate]/12,[holding period],0,-[cash outflow],1); this returns $110.47
=NPV([rate]/12,-[cash outflow],{0,... 11 times},[cash inflow]); given a cash inflow of $110.47, this returns an NPV of 0

 

[gmhumphr]

Clearly I am still obsessing over this...

I think your issue is in the understanding of the difference between calculating NPV and FV using APR and APY. When there are multiple periods in a year, APR<apy to="" achieve="" constant="" returns.="" that="" means="" if="" you="" calculate="" with="" one="" method="" forward="" (i="" think="" are="" using="" a="" formula="" for="" apy="" find="" fv="" in="" the="" posted)="" and="" then="" discounting="" another="" (npv="" assumes="" apr),="" would="" mistakenly="" assume="" there="" is="" positive="" npv.
and APY are not the same to achieve the same return. APR divides the rate by 12 and reinvests each period at that rate, APY gives you the equivalent rate you would need to have the same return if you only had 1 period in the year (you lose the 12 compoundings, thus the need for a higher rate).

Your formula should instead be:

(1+$D$8/12)^D7*D5</apy>