npv with dynamic range

Hi,
See attached,

I have assumed that when you say 'After the year in A1' you mean starting with the next year. So if 2018 is in A1 the NPV is for years 2019-2024

Yes, your method is perfectly OK and indeed better than mine since it's shorter - always a good thing.

i ran into a problem. using the formula in the attachment I provided (post 4), if i delete a column (say I don't need column K) or I need to add columns the formula does not adjust. Am i missing something or is this limitation of my syntax????

Hi,

With the column delete I'm not sure what you mean by the the formula doesn't adjust. Take the column B formula
=NPV(0.05,B2:K2)

deleting column K, or indeed any of the other columns changes the formula to
=NPV(0.05,B2:J2)

Similarly if you were to Add a column before column K the formulae would adjust to reflect the larger range, i.e. column B formula would be
=NPV(0.05,B2:L2)

The only case where the formula wouldn't adjust would be adding a column after column K.
You can get round this by changing the B3 formula to
=NPV(0.05,OFFSET(B2,0,0,1,COUNTA(2:2)))

and copying this across.

I think if you go into this file you will see what I am talking about. if you delete K you get a #ref error. Also row 3 is mostly irrelevant. it was mainly to check formula in A2. The formula in A2 is the one that needs to adjust when a column is deleted (or added)

NPV with dynamic range.xlsx

OK,

I thought you were talking about the row 3 formulae.

Use instead in A2

Please Login or Register  to view this content.

okay thanks. that works in the prior attached example. However, when i tried to put it into my big model it didn't work. I tried to replicate what is going on in my model and I backed into the formula but I don't understand why it is working. Can you please help me understand? Mainly why the col adjustment of -7???

Hi,

The -7 (or -2 in the original example) is necessary since your data starts in column 7.

The MATCH(C2,3:3) bit of the formula returns the column number for the year in question. i.e. column 11 (K) if 2019 is selected. Hence we need to deduct 7 to bring the OFFSET (third column parameter) back to a value of 4 meaning the Offset start point is G + 4 = column K

Originally Posted by ExcelForumOG

how would you avoid if people plug in zeroes, before the start of the project since zeroes will change the NPV.

Right, but only for zeros in the earlier periods.

And I wouldn't "avoid" it. Zeros in earlier periods have meaning, when used correctly. It means that "t0" (the year of present value) is before the first non-zero cash fllow. For example, you might want to compare the NPV to 2019 of Project 3 with delaying the project until 2021 (Project 1). In that case, we might want to account for the time-value of the delay.

So I guess I would "avoid" it by telling such people: GIGO! But if they insist on seeing meaningless zeros, tell them to enter it as text; e.g. ="0" or '0 (apostrophe zero). Again, Excel NPV ignores text as well as empty cells.

Of course, if you insist, we could complicate the NPV formula to ignore early zero periods. But that can be messy.

PS.... You are "responding" to a discussion that ended 7 years ago. I responded when I thought the question could be resolved quickly. If you continue to have follow-up questions, I think you should start a new discussion.

Originally Posted by ExcelForumOG

HI Everyone, thank you for the solutions presented.

I have a question that I hope you can help me with (this is a variant of the problem statement original posted)

I have an excel file with each row representing different projects, each project starts at different periods (assume we want to measure NPV for 6 years each)...
I want a formula to do NPV for each project. But need to be able to do it in each row with different time frames (start and end date)

How would you set this up?

Thank you in advance

(Attached a file with the example)

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Originally Posted by ExcelForumOG

My apologies, first time posting.

Thanks

Not a problem