-1

I am building a model that compares scenario 'A' with scenario 'B' by setting the NPV of 'B' equal to NPV of 'A'. I have a series of cash flows in 'A' and calculated the NPV as follows:

NPV Image

Therefore, NPV of A = $130.04

I am now attempting to (reverse) the formula in order to calculate a possible set of cash flows in 'B'. For example, If NPV of 'B' = $130.04 , what are my annual cash flows given that in year 10 I know the cash flow will be $300. The cash flows in years 1 - 9 are equal. Cash flows in year 1- 9 would logically be lower than $200 as seen in previous example.

One technique that would accomplish this would be "goal-seek" to set G21 = B21 by adjusting G10:G18. However, I would rather use a reverse formula if one exists. I have found nothing on google or stackoverflow to indicate that a formula can be reversed, but I believe it may be possible since the relationship already exists.

Is there a way to reverse the NPV formula to make it calculate backwards? Otherwise, my only option would be a macro to automatically goal seek.

ClaireLandis
  • 325
  • 3
  • 18
  • 3
    Are you assuming that the cash flow in years 1-9 are all equal? Because there are many "possible sets of cash flows" that would still give you the same NPV. – BigBen Jul 13 '18 at 15:48
  • Yes cash flows in year 1-9 are equal – ClaireLandis Jul 13 '18 at 15:55
  • 1
    @pnuts In what cell is the second formula meant to go? Why am I counting G9:G30 when the range is only G9:G19? I can't use 130.04 as a hard-coded number as this will be a dynamic model. What is the second formula meant to be calculating in your comment? – ClaireLandis Jul 13 '18 at 16:03
  • 1
    @pnuts THIS IS EXCELLENT! If you will post this as an answer I will happily accept! – ClaireLandis Jul 13 '18 at 16:19
  • You could do this using the solver, making the changing cells G10 to G18 and setting npv ofb must equal npv of A... without any reverse formulae... – Solar Mike Jul 13 '18 at 18:29
  • By using Goal Seek i get 193.96 for the cashflow marked "?" Is this what you expect? – John F Jul 14 '18 at 03:08
  • I have the same question. However cash flows for me are not equal, but can be assumed to follow some simple rules of division between each time period. However, given compound interest/discount rates comes into play, the solution should be numerical and maybe not easy to calculate. – babipsylon Jun 17 '21 at 16:16

3 Answers3

2

OK I managed to backward solve it without Goal Seek.

I am assuming:

  • the first case the cashflows are equal.

  • In the second case, a constant part of those equal cashflows is delayed until maturity.

    The PV of these cashflows have to be equal. If you solve this for the new cashflow you can get your answer. I did it first with goal seek, then with the PV equation solved for the cashflow. I have shown it in this sheet both with ordinary formulas and using Excel functions. Hope this helps.

enter image description here

John F
  • 276
  • 1
  • 6
1

discounted flat coupon table

We can obtain the current value of 1 dollar coupon over years 1-9, which is $5.32825, per the table above (each year is discounted more than the one before).

Then we can balance the change in value of the final payment (extra $32.19732) with as many dollars of repayment as we need: -$32.19732 / 5.32825 = -$6.042758

$200 - $6.04 = $193.96

Phil H
  • 19,928
  • 7
  • 68
  • 105
1

Answer received from another user in comments which has since been deleted:

Use the formula : 

=PMT(C4,COUNTIF(G10:G19,0),G9+NPV(C4,G10:G19)-B21

in cell G24 to find the value of each cash flow

Answer

ClaireLandis
  • 325
  • 3
  • 18