Forum Discussion
Name range in formula
Instead of images, is it possible for you to post the actual spreadsheet you have? My suspicion is that you haven't actually named the range but have just put a heading up there....but without the spreadsheet itself one can only guess.
I think what you were missing is the absolute reference for the Rate portion of the formula. I didn't check the results but see if this works.
=NPV($B$2,@benefit-@cost)
The "$" dollar signs make each formula, all the way down the column, continue to refer to B2 even though the other references are changing.
mathetes thank for the advice, I tried it but sadly it didn't work.
The formula npv(rate, value1, value2,...) in itself only accept one "rate" so $ is unnecessary, that why I'm convinced the problem lied in name range.
You were right. I hadn't used NPV before. THis morning I looked at this resource and learned how it's to be used. You would benefit from the same. https://exceljet.net/excel-functions/excel-npv-function
The way to name that range is to highlight cells D4 through D13 and then use "Insert....Name...Define" from the main menu.
The resulting formula then can read =NPV(B2,ben_cost) or it could just as effectively read =NPV(B2,D4:D13)
I highly recommend reading that ExcelJet reference, however. It explains well the limitations of the NPV function as well as showing how to use it.