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

[jimcarroll]

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 https://www.allthingsmathematics.com/courses/134034/lectures/3812802 . 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.

Jim