1. Guide To Excel For Finance: Introduction
  2. Guide To Excel For Finance: Linking Yahoo! Finance and Other Outside Financial Data To Excel
  3. Guide To Excel For Finance: PV And FV Functions
  4. Guide to Excel for Finance: Annuities
  5. Guide To Excel For Finance: Valuation Methods
  6. Guide To Excel For Finance: Goal Seek
  7. Guide To Excel For Finance: HLookup And VLookup
  8. Guide To Excel For Finance: Ratios
  9. Guide To Excel For Finance: Technical Indicators
  10. Guide To Excel For Finance: Advanced Calculations
  11. Guide To Excel For Finance: Conclusion

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):

 


Guide to Excel for Finance: Annuities
Related Articles
  1. Investing

    Time Value Of Money: Determining Your Future Worth

    Determining monthly contributions to college funds, retirement plans or savings is easy with this calculation.
  2. Small Business

    Calculating Net Present Value at Different Points Using Excel

    Calculating the net present value (NPV) of your investment projects using Excel.
  3. Investing

    Calculating the Present Value of an Annuity

    The present value of an annuity is the current, lump sum value of periodic future payments as calculated using a specific rate.
  4. Retirement

    How to Calculate the Value of Annuities

    Here's everything you need to account for when calculating the present and future value of annuities.
  5. Trading

    Improve Your Investing With Excel

    Excel is a useful tool to assist with investment organization and evaluation. Find out how to use it.
  6. Financial Advisor

    Value Investing Strategies in a Volatile Market

    Volatile markets are a scary time for uneducated investors, but value investors use volatile periods as an opportunity to buy stocks at a discount.
  7. Investing

    Calculating Present Value Interest Factor

    The present value interest factor is a number that makes it easier to calculate the present value of a payment or value to be received in the future.
  8. Investing

    What is Present Value?

    Present value tells us how much a future sum of money is worth today, given a specified rate of return. This is an important financial concept based on the principle that money received in the ...
Frequently Asked Questions
  1. What is a good annual return for a mutual fund?

    Learn the key factors that determine if a mutual fund's return is "good" for you and your needs?
  2. How are industrial goods different from consumer goods?

    Understand the difference between industrial goods and consumer goods, and learn the different types of industrial goods ...
  3. What causes inflation, and does anyone gain from it?

    In this article, we will examine the fundamental factors behind inflation, different types of inflation and who benefits ...
  4. What is the difference between a buy-side analyst and a sell-side analyst?

    The main difference between a buy-side analyst and sell-side analyst is the type of firm that employs them and the people ...
Trading Center