Forum Discussion
Name range in formula
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.