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. Retirement

    Present and Future Value of Annuities

    Do you want to invest in annuities that get you a series of payments over a period of time. Here's everything you need to account for when calculating the present and future value of annuities.
  2. Investing

    The Difference Between Enterprise Value and Equity Value

    Enterprise value calculates a business’s current value, while equity value offers a snapshot of that business’s current and potential future value.
  3. Investing

    Learn simple and compound interest

    Interest is defined as the cost of borrowing money or the rate paid on a deposit to an investor. Interest can be classified as simple interest or compound interest.
  4. Financial Advisor

    Is Texas Instruments a Good Value Play? (TXN)

    Find out whether investors and analysts believe that Texas Instruments would make a good value play at its current valuation, and learn more about its outlook.
  5. Investing

    What Is The Value In Value Investing?

    Value investing has its advantages, but it also has significant drawbacks. We look at the pros and cons.
Frequently Asked Questions
  1. What will happen to my SEP IRA if I leave my current employer?

    Because the funding vehicle for the SEP is a Traditional IRA, the same rules that apply to a Traditional IRA also apply to ...
  2. I want to close my IRA account. What percentage will I lose to tax?

    You can move the amount by means of a trustee-to-trustee transfer to another IRA, or roll over the amount to your 401(k). ...
  3. Can a spouse who is not named as a beneficiary receive assets from an IRA?

    Generally no: The designation of beneficiary form dictates who receives the assets – unless the named beneficiaries choose ...
  4. Can the non-spouse beneficiary of an IRA name a successor beneficiary?

    Whether an Individual Retirement Account beneficiary can name a successor beneficiary is determined by the provisions of ...
Trading Center