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.
Friday, September 26, 2008
Wednesday, September 10, 2008
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.
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."
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."
Subscribe to:
Posts (Atom)
Posts
