You might have already used the XNPV built-in Excel function for calculating the present value of a stream of cash flows with dates. How about XNFV for future value? Wait a minute. There is no XNFV. It would have been easy to include an XNFV function, but maybe they couldn’t think of a reason to use it. I needed one a few years ago, when my firm won a lawsuit against a handful of other financial institutions. It involved the sale of commercial real estate participations. After a few years of litigation, we had accumulated a lot of expenses and upkeep on the property. Since we won we wanted to recapture those expenses, including interest. That is when a XNFV function would have been handy. There were hundreds of payments we paid out, on various dates, but lets just make up a small example:
The problem was, how much do we want from the other parties, including interest at an APY of 4.0%, if they pay us on 2/15/2014? The answer is the future value of $118,343.78. I created the two dynamic names, MyDates and MyData, because more expenses may have come in before we came to an agreement. The last cash flow on 2/15/2014 must be zero, not a blank cell. The interesting thing is that the future value and the present value calculations are the same, except for one thing:
The difference is that the present value uses the starting date and future value uses the ending date. This makes sense when you consider that the PV lump sum calculation is the reciprocal of the FV lump sum calculation:
Where R is rate (in this case APY) and T is time (in this case years)
The sumproduct formula calculates the factors at each date, multiplies the factor by the cash flows, and sums the results. I don’t use the built-in function XNPV anymore. I use the formulas in a template.