No XNFV Excel Function?

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:

FV

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:

PV=SUMPRODUCT((1+APY)^((MIN(MyDates)-MyDates)/365)*MyData)

FV=SUMPRODUCT((1+APY)^((MAX(MyDates)-MyDates)/365)*MyData)

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:

Factors

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.

Download “Present_and_Future_Value_dates”

Download

http://www.pistulka.com/Excel_Shared/

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *