Excel offers a few simple formulas for determining the value of projects over time. In this article, I will cover PV and FV. For most financial applications, some subset of the formulas listed above will be more than enough to determine the value or rate of return of a particular project.

The PV and FV formulas are very similar in construct, but give the opposite answers. PV, which is present value, will tell you what the value of a string of future payments is today. Conversely, FV, which is future value, tells you what the value of your current holdings will be in the future.

As a brief primer, the time value of money is an important concept in finance. The simplest way to explain this concept is to understand that if you had $1 dollar today, it is worth more then $1 in a year for any interest rate greater than 0%. Let us assume that the current annual interest rate is 3%. If you invested the $1 you have today, it would be worth $1.03 at the end of the year (annual payment, no continuous compounding). Clearly, that is more than merely $1.00. Now, if you were to take this example to the next level and assume that the interest rate of 3% holds for five periods, the future value is now $1.16. Notice in this simple example, the future value is $1.16 and not $1.15, indicating the power of compounding. For an example that has a much larger difference, consider $10,000 today with an annual interest rate over 10 years of 8%. The future value is $22,589.25 versus the non-compounded total of $18,000.00 – a difference of $3,589.25. This is a bit of a digression and will be explained in further articles, but the summary of all of this is that the time value of money is a fundamental concept in financial theory and practice, and Excel makes it easy to calculate.

The formula for PV is =PV(rate,nper,pmt,[fv],[type]), where rate is the interest rate per period, nper is the total number of period in the calculation, pmt is the periodic payment, if any, future value of the project (optional) and type of payment stream (optional). For type of payment stream, use 1 for beginning of period payments and 0 or blank for end of period payments. In virtually all financial applications I have done, I have dealt with end of period payments, so the "type" is left blank. The reason the "fv" is optional is that for a project producing periodic payments, the fv may be zero and the calculation is just for determining the present value of periodic payments over some time period. For example, if there is an annual interest rate of 6%, periodic payments of $10.00 over 30 periods, the present value is $137.65. When there is no specific future value identified, fv = 0.

Similar to PV, the formula for FV is =FV(rate,nper,pmt,[pv],[type]). The same explanations hold for FV as in PV, and PV in this formula is optional. For example, in the case where there is no identifiable present value, pv is equal to zero. Assume that the pv equals zero, the annual interest rate is 6%, there are 30 periods and the payment is $10.00 per period. If you plug this into an Excel formula, you will get a future value equal to ($790.58). Why would you have a negative future value? To understand the answer to this question, it is important to consider what you are trying to determine. Present value calculations return negative values (under normal circumstances) because that is the "cost" of achieving a stated future value. The cost, in this case, is really the investment needed to generate the future value calculated using the formulas. If you think back to the first example, investing $1.00 is what it takes to get to $1.03 one year later. You do not have access to that dollar once it is invested, so from your perspective, it is a cash outflow, or a negative cash event to you. Coming back to future value, when you have positive payments, the formula interprets that as you paying out that amount, not receiving, over the defined period of years (this should be reasonably intuitive, since you must enter a present value, if known, as a negative number for purposes of calculating future value). When you input ($10.00) for the payment, the future value is correctly calculated as $790.58.

In summary, the PV and FV formulas save time and effort when using spreadsheets that require many calculations for present and future values. In the next article, I will discuss NPV and IRR, two formulas critical in evaluating projects. For now, just remember that cash in your hands today trumps the same amount of cash in the future because you can take the money now and invest.

IS Formulas in Microsoft Excel – Part II

This is continuation of a prior article covering several similar formulas within the Information category that allows a modeler to extract TRUE or FALSE information, based on the design of the model. This article covers the functions ISEVEN, ISODD, ISLOGICAL and ISBLANK. These formulas, too, can play a big role in simple financial models and I think that many individuals who are normally familiar with Excel do not know how to incorporate them in a way that may make the modeling process easier.

ISEVEN, ISODD

These are simple functions to determine whether or not a reference is an even number odd number. The form of the functions are =ISEVEN(value) or =ISODD(value) and the formulas return a TRUE statement if the value is even for ISEVEN or odd for ISODD. These are the types of formulas that could be used for storing products in a warehouse (like addresses, even on one side and odd on the other) or developing a spreadsheet to determine prime numbers (after number 2, since it is the only even prime number).

ISLOGICAL

This formula merely checks to see if the reference is TRUE or FALSE, and its form is =ISLOGICAL(value). At this point in the article, you have seen how these IS statements work, and this one is straightforward. If you are interested in taking a total tally of all of the TRUE or FALSE statements, you can use this formulas for each of the individual cells and then use =COUNTIF(Range_of_Data,FALSE) to count all of the FALSE references. That is one example of a scenario in which you might actually utilize in your own model.

ISBLANK

This formula checks the reference cell to see if there is any data in it, and its form is =ISBLANK(value). This is a formula that I have used many times and while a bit clunky on more complex spreadsheets, its simplicity makes it easy to incorporate. For example, when you are modeling anything with a waterfall structure, this formula comes in handy. You can create a series of formulas that will only kick in after a specific circumstance is reached. So, instead of having to manually adjust formulas across rows or columns, incorporating the ISBLANK language allows you to copy and past across the time horizon of your model. Let us say that you have a model where an investor has invested $10 in a company, and you want to calculate the return based on a specific sale date. Further, if a sale does not occur at the end of a period, some interim cash flow to an investor is paid. So, if the sale date is the variable, you may have a formula that looks to see if there was any activity in the prior cell to determine if any action should take place. Using the $10 investment, assume that you have years across the top and years in the column on the left. If the years across the top represent when cash flow will take place, the years on the left indicate when the sale of the investment occurs. Of course, of the year in the left column is greater then a year in the row (across the top), there would be no activity. This would be an example of when to use ISBLANK.

(As an aside, a waterfall is something that has a payoff structure that resembles water falling over a cliff. You would see this if you need to determine the returns to an investor over a period of years. If there was a sale of an investment in the first year, the investor might be getting only one payment – the initial sale. If the sale of the investment occurred in year 5, there may be smaller annual payments with a final larger payment at the end of year 5, thus the payoff stream would look like a waterfall when you model it. This will be explained in better detail in the financial articles to come.)

In summary, these simplistic formulas may not seem like terrific methods by which to establish your financial model, but a subset, particularly the ISBLANK function, can be very helpful in making a more complex situation easier to manage.

IS Formulas in Microsoft Excel – Part I

Excel offers several similar formulas within the Information category that allows a modeler to extract TRUE or FALSE information, based on the design of the model. This article will briefly describe the various functions, including: ISERR, ISERROR, ISNA, ISNONTEXT, ISNUMBER, ISREF, and ISTEXT. These formulas can play a big role in simple financial models and I think that many individuals who are normally familiar with Excel do not know how to incorporate them in a way that may make the modeling process easier.

ISERR, ISERROR

These two formulas will check to see if the reference is an answer or an error. The form for each is simply =ISERR(value) or =ISERROR(value) (Note that in each of these the form includes a "value" component, which should be thought of as the cell reference you are targeting). The only difference between these two formulas is that ISERROR can include the error of #N/A, and if the reference is #N/A, the result of TRUE is returned. For ISERR, the value of FALSE is returned for #N/A because it does not recognize that particular error. For that reason, I use the ISERROR only. This formula can come in handy when evaluating a formula designed to lookup data in an area (like VLOOKUP or HLOOKUP). It can act as the MATCH formula in this capacity, treating an error as a non-match and thereby allowing the modeler to have a means by which to check if the required data is actually contained in the data range. For example, if you have a list of inventory products and you need to know the amount sold on a specific day, you could do the following to get the exact amount or return an error message:

=IF(ISERROR(VLOOKUP(Product,Range,Number,False)),"No Data", VLOOKUP(Product,Range,Number,False)).

ISNONTEXT, ISTEXT, ISNUMBER

These functions are fairly straightforward, with one determining if a reference is text, number or not text at all. The short of this section is that ISNONTEXT and ISTEXT are opposite, not surprisingly, and ISNONTEXT and ISNUMBER are the same EXCEPT FOR a blank cell, which ISNONTEXT states TRUE and ISNUMBER states FALSE. There is no real mystery in these formulas, whose form takes the place of =ISNONTEXT(value), =ISTEXT(value) or =ISNUMBER(value). You could use these to check the names of employees in a long list, addresses in a database or some combination that requires confirmation of text status.

ISNA, ISREF

These two formulas are designed to specifically determine is there is an #N/A or #REF! in a reference cell. The form is either =ISNA(value) or =ISREF(value). I have not found a good time to use either of these formulas, and you may never find a good time either. Having said that, if you have a long list of data and want to do a quick copy and paste of a simple formula to find the bad data, one of these two Excel formulas could do the trick. Let us say that you have 4,000 entries of products sold last year and you need to know how much was sold each day of the week. If you were checking to see if any results were #N/A (indicating no sales on that particular day of the week), you could use =ISNA(Range_of_Friday_Sales) for each cell next to the product to see if it is truly #N/A. Frankly, that is cumbersome and I doubt you would ever use something like that, but Excel has made the formula available to use, so if you are in a bind, use it. The same would apply for use with ISREF, but instead of finding #N/A, you would be searching for bad formulas. Let us say that you have a model with a lot of data in a row and you begin to delete some of those rows. Of each row was dependent upon the next to provide the right answer, you will end up generating a #REF! That is when you can use ISREF, but once again, I cannot see a real life scenario where the ISREF will trump another formula you create in your financial model.

Additional Information formulas are covered in the next article entitled "IS Formulas in Microsoft Excel – Part II."

Weighted Average Calculations in Microsoft Excel

There are many analyses you will undertake that will require the need to calculate a weighted average instead of a simple average, and there are a few different methods by which to do so. This article will first define the concept and then illustrate the methods used to calculate the correct answer. Once you understand the shortcut that Excel provides in determining the result, you will likely not return to the longer, more cumbersome approach.

First, what is a weighted average ("WAVG")? The WAVG uses a parameter, like market capitalization or shares traded, to modify a simple average calculation. As an example, let's assume that you have five data points that are stock prices: $3.00, $4.00, $4.50, $5.00 and $5.50. If these are the closing prices over the last five trading days, the average price over this period would be $4.40. But, if we assume that these prices were intraday prices, we could say that the average price for the day was $4.40. This is misleading, however, as it does not consider the volume of shares at each trade. The volume at the time of each trade provides a clearer picture of what the market thinks of valuation. The calculation used to weight these trades is the volume weighted average price ("VWAP") and is a common practice in private capital transactions involving equity or equity-linked securities, like convertible debt (note that VWAP and WAVG are calculated in the same fashion, and I use VWAP to illustrate a real life application).

In keeping with the prior example, let us assume that we have volume information associated with the prices as follows: 1,000 shares, 1,500 shares, 1,000 shares, 10,000 shares and 500 shares. You can see from the data that many more shares traded at $5.00 – 71.4% of the total daily volume to be exact. How does one incorporate that information to determine the VWAP? There are two approaches for doing the calculation: the step-by-step method or the SUMPRODUCT method.



Assume that in the first column of your spreadsheet contains the stock prices and the next column contains the shares traded at each price. The step-by-step approach would dictate that you create two new columns: weighting and contribution. The weighting column would contain the result of that day's shares divided by the total shares traded for the day for each of the data points. For example, the weight for the $4.00 price would be 10.7% (1,500 divided by the total shares traded that day of 14,000 shares). Once you have the weightings completed, you can do the contribution column, which would be the weighting value multiplied by the actual stock price. In the $4.00 example, you would take the 10.7% and multiply by $4.00, yielding $0.43 – the contribution to the total VWAP. Summing the contribution column yields the VWAP, which, in this case, is $4.73, higher than the simple average of $4.40. The result would indicate that the actual value of this stock is closer to $4.73 than $4.40.



To avoid the need for two additional columns, Excel provides the SUMPRODUCT function. The form of this function is =SUMPRODUCT(array1,[array2],[array3]….). This formula takes an array (row or column) and multiplies it by one or more other arrays of the same size. To calculate the VWAP from our prior example, all you would need is =SUMPRODUCT(prices,volume)/total volume. This would result in the same answer as above, $4.73. The answer is calculated without the additional columns. When you look at the formula and break it down, you can see that it takes the form of (A x B)/C, which can be rewritten as A x (B/C). The B/C component is the same as the "weighting" column described in the step-by-step approach, with A being the stock price used in determining the "contribution" column in the former method.

Finally, you can also utilize an array method to calculate the VWAP using the form of ={SUM((prices)*(volume))/total volume}, which is entered using ctrl+shift+enter. You can clearly se that 1) this is not much of a time saver and 2) SUMPRODUCT is similar to this form. I only show this to illustrate that there are several ways to accomplish the WAVG calculation, and depending upon space, modeling skill or other personal factors, you can use whichever works for you. I recommend minimization of superfluous data and err towards using the functionality contained in the Excel formulas, and would use SUMPRODUCT over the lengthier method.