excelexcel-formulafinancequantitative-financecomputational-finance

Calculate the annual cash flows given a target NPV (net present value)

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.

Solution

  • 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