Calculating the PV & FV of Investments with Excel

microsoft-excel-logoMost of the stock valuation methods we use as value investors require some form of calculating the present value of a future investment, or determining what a dollar amount today will be worth X number of years in the future. What we are talking about here are present value and future value calculations. The equations themselves are not that complex, but Microsoft Excel has both functions built-in, so why not take advantage of this.

First a quick review. The fact that the value of money differs over a period of time is due to the time value of money. It’s based on the premise that there is always at least some small rate of return that can be made on an investment, so $100 today is worth more than that same $100 in the future because I can invest that money today and earn interest.

Future Value (FV)

The future value equation looks like so:

F = P(1+R)N

where:

  • F = the future value
  • P = the principal (starting amount)
  • R = interest rate
  • N = number of years in the future

To work backwards and calculate the present value of a future amount we simply solve for P instead of F algebraically. Fortunately, Excel has two built in functions that simplify things a bit. Future values are calculated using the FV function, and present values are calculated using the PV function. Each contain a number of parameters. Let’s look at FV first:

=FV(rate, nper, pmt, [pv], [type])

where:

  • rate = the interest rate for the investment
  • nper = the number of periods (ie. years)
  • payment = the payment that will be made each period, if any
  • pv (optional) = present value of the investment
  • type (optional) = 0: payments due at end of the period (default) or 1: payments due at the beginning of the period

The most confusing aspect of these equations is the cash flow convention that an outgoing payment is negative, and an incoming payment is positive. Let’s look at an example.

Example: What is the future value of a $5,000 initial investment, with an additional $500 invested at the end of every year for 10 years at an interest rate of 7.5%?

Solution: =FV(0.075,10,-500,-5000,0) which results in an answer of $17,378.70

Notice how both the $5,000 initial investment and the $500 annual contribution are both represented as negative numbers in the function, since they are both outgoing payments.

Present Value (PV)

Now let’s look at the PV function:

=PV(rate, nper, pmt, [fv], [type])

where:

  • rate = the interest rate for the investment
  • nper = the number of periods (ie. years)
  • payment = the payment that will be made each period, if any
  • fv (optional) = future value of the investment
  • type (optional) = 0: payments due at end of the period (default) or 1: payments due at the beginning of the period

Example: An investment is worth $50,000 in 20 years. What is the present value of that investment, assuming an interest rate of 10%?

Solution: =PV(0.1,20,0,-50000,0) which results in a present value of $7,432.18

Again, notice that the future value is represented as a negative number.

Download

I have made a simple excel spreadsheet available for download that will let you experiment with these two functions. Enjoy!

PV FV Calculator.xlsx

The image, “Excel Icon” is copyright © 2012 Vincent Garnier and made available under a CC Attribution Noncommercial NoDerivs 3.0 license.

Tagged , ,

Leave a Reply

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