Guide To Excel For Finance: PV And FV Functions

Valuation Models

Time Value of Money

The time value of money generally relates to the concepts of present value and future value.  The PV Functions and FV Functions in Excel will be explained below and are fundamental to nearly all finance matters. The basic forms of the time value of money, which consists of multiplying an initial present value by an interest rate to get to a future value, can easily be calculated via a single cell calculation in Excel. (Related: Understanding The Time Value Of Money - Video)

The more complicated theories, including annuities, DCF, DDM and RIM, require more sophisticated modeling techniques in Excel and will also be explained in more detail below.

This guide to Excel provides an overview of some of the more useful tools for investors to utilize, but really just touches on a few of the many applications available. Additionally, as will be touched upon in the tutorial, it is also important to take note of third-party cell "add-ins" to Excel, which represent separate programs that can link directly to Excel and have pre-built formulas and other applications, to further improve on the utility of Excel as a financial tool.

Present Value

Present value refers to an amount of money needed today to get to an amount of money needed in the future.  The Present Value or "PV Function" in Excel helps the user determine the current value of a financial asset.  Take a child’s future college expense as an example.  If a child, "Jimmy," has been born, he has approximately 18 years to save for college. Assuming $50,000 annually for a four year university education, $200,000 will be needed in the future.  Assume that a mix of investments will earn a return of 5% annually.  (Related: Present Value - Video)

Below is the present value formula that Excel will help us calculate:

PV = FV / (1 + r)^t

Below is the formula, and calculation detail in Excel:

\

You can see the formula cell above the word problem, as well as above the calculation result.  To have $200,000 in 18 years, a family will need to start with $83,104.13 dollars today and earn 5% annually to reach their savings goals.  The PV formula in Excel will link to the stated amounts (e.g. “rate” is first at 5%, “nper” is number of periods at 18 years, 0 is the “pmt”, or payment (this comes into play when dealing with annuities) and “fv” is the $200,000 future value needed for college).  “PV”, or present value, is the variable we are solving for.

Here is another example of using the concept of present value.  For instance, analysts can value a stock by forecasting its future profits, or cash flows, and discounting them back to today to get a current, or present value, for the stock. If it differs much from the value listed in the stock market, it may be either a buy or a sell. 

Let's look at an example. Assume a company earns a profit of $100,000 annually over a period of 10 years. The "Type" is a "1" or "0" and details whether the earnings would occur at the beginning of each year, or end of each year, respectively. If an analyst has a price target of $100 per share, or a $1 million firm value assuming 10,000 shares outstanding, then the estimated present value of the stock is $29.04. If the current market price is above this level, the stock would not be worth buying. If it traded at less than $29 per share, then it would be worth an investment, based off of the inputs the analysts used in his analysis.

Solving for Future Value, Rate, Number of Periods

Using the above present value basics, Excel can be used to solve for the other variables as well.  Below is a brief overview.

FV = PV(1 + r)^t

You may have already noticed that the Future Value or "FV Function" in Excel was part of the discussion for the analyst valuing the stock, above. To compute the future value, the present value, discount rate (“r”), and amount of time are known.  In the below example, a worker has received a $75,000 bonus (the present value) that he will invest for 5 years (the time period) in the stock market at an estimated annual return of 10% (the discount rate):

FV Function

If we keep all of the variables the same in the earlier stock valuation example, but assume we know the current firm value is $350,000, or $35 per share, then the future value of the stock in 10 years would be $84.53 per share. Here is what the inputs would look like: 

r = (FV / PV)^1/t – 1

To return to the employee who received the $75,000 bonus, Excel can help determine returns needed to reach goals.  If the same worker would prefer to have $150,000 saved for a house in five years, this formula calculates the annual return he will need to achieve (it is 14.87% per year):

t = ln(FV / PV) / ln(1 + r)

As a final example, assume the worker realizes that earning more than 10% a year will be difficult.  Assuming a 10% stock market return, here is how many years it will take to achieve $150,000 in savings (we are solving for the number of periods here):