NPV for annual operating costs and reoccurring fixed cost every X years

[jonexcel88]

Hi all,

I am developing a forecast of costs using the NPV formula which include operating and fixed costs however, the fixed costs reoccur every X years (depending on the type of equipment). The model looks at costs of switching from equipment 1 to equipment 2. Equipment 1 only has operating costs and no fixed costs whereas equipment 2 has operating costs and reoccurring fixed costs every (C104) years.

I currently have:
=NPV($C$105, $E$34:E34, $C$103+F87,OFFSET(G87,0,0,1,$C$104-1),$C$103+OFFSET(G87,0,$C$104-1,1,1),OFFSET(G87,0,$C$104,1,$C$104-1))

Where:
C105 = discount rate
E34 = operating costs of equipment type 1
C103 = fixed cost of equipment 2
G87 = first year of operating costs of equipment type 2
C104 =useful life of equipment type 2

I don't think the NPV formula is working correctly here. Happy to explain further if you need more information to help.

Thanks in advance!

 

[Devo182]

Not able to test it rn, but do keep in mind cash outflows need to be entered as negative values.

Is it returning odd figures or an error?

 

[jonexcel88]

Thanks for replying Devo182.

The negative values is a good idea but since we don't have any cash coming in for this forecast I don't think that would make a difference.

To answer your questions, the formula is not returning an error, just odd figures. The numbers are strangely lower than they should be. I'm wondering if it has to do with placing ranges in the Value 1, Value 2, etc. slots for the NPV formula combined with the offset function.

Maybe there is a better way to use NPV with regular operating costs and reoccurring fixed costs every x years?

 

[jonexcel88]

It turns out the sheet I was using to check the calculations was wrong and my formula was right. So I'll mark this as solved.