Nest NPV function within PMT formula as the argument for "PV" .


New Member
Mar 6, 2019
I've been given a particularly diabolical challenge, and for the life of me, I cannot make it work. The challenge is to execute within a single cell a formula that will calculate "EAC: equivalent annual cost" within a single cell. There are two distinct steps in the calculation; first, one calculates the "net present value" [in reality a present value incorporating all negative cash flows, but using the functionality of the NPV() function]. Having done that, one uses the PV thus obtained as the PV argument in the PMT() formula. I have tried various strategies, and none has worked. I get a "too many arguments" or "too few arguments" error message when I try. It would take the form of:

PMT({Cell containing interest rate},{cell using count of a column listing cash flows},{NPV()formula evaluating to PV},[FV],[TYPE]). If you want to see the two steps, done separately, explained better than I have ever seen it, watch this guy Patrick from Ryerson University at . Even if you're not a business or math geek, the explanation is done so well that it's worth your time. Anyway, I hope one of the Excel gurus on here can help me do this "all in a single cell" challenge. Thanks.


Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Latest member