#NUM!
Occurs when the numeric inputs for the formula are unacceptable or cause a problem. For example =SQRT(-1) will cause a #NUM! error because the result is not a real number. =10^500 will also cause a #NUM! error because the result is too large for Excel to handle.
#NAME?
Occurs when the text in the formula is not understood. It could be a typo in the name of a named range of cells or a function like =SQT() instead or =SQRT(), omitting a colon (:) in a cell range, or entering text in a formula without enclosing it in ” “. This error also occurs when using a function that has not been loaded in Excel. This is likely to happen if you use XIRR() without loading the Analysis Toolpak.
#REF!
Occurs when a cell reference is missing because the referenced cell was deleted.
#DIV/0!
Occurs when the formula divides by 0. It usually happens when the formula is something like =A1/B1 and B1 is empty.
#VALUE!
Occurs when the wrong type of input is fed into a formula.
For example, formula is expecting numeric inputs and text is entered. =A1*B1, when the contents of either A1 and B1 is not a number. The exception to this rule is TRUE and FALSE which Excel reads as 1 and 0 respectively.
A range of cells is entered when the formula is expecting a single cell.
Typing an array formula without using Ctrl+Shift+Enter.
Circular References
When one or more formulas reference each other such that a logical loop is formed, Excel will trigger an error message telling you that you’ve created a circular reference. For example, if cell A1 has the formula =B1 and cell B1 has =A1, this will create a circular reference and Excel will ask if you are sure you want to use this formula. Excel will allow circular references if you really want them. Circular references are an infinite loop, but they will not crash Excel because Excel caps the number of loop iterations. There are some useful applications of circular references, they can be used to calculate solutions that can only be found through successive iterations. But more often than not, they are errors in a spreadsheet.
Debugging and the Audit Tool
When writing a spreadsheet, typos inevitably occur. The problem is you might not figure out where they are until after you’re done. The trick to spotting an error in your spreadsheet is to test it using simple data with round figures. The data should be easy enough for you to figure out what the formulas should spit out. For example, if you’re debugging financial ratios from an income statement, input $10 M revenue, $5 M cost of sales, $1 M Net Income, & 2 M shares. You can see that Gross Margin should be 50%, Net Margin 10%, and EPS $0.50. Any errors in the spreadsheet will be easy to pick out.
Once you’ve found a formula that has a bug in it, fixing it is usually pretty simple. But, if the spreadsheet is large enough, it may be difficult to trace the references in a formula when you’re debugging the sheet. To make this easier, Excel has the Audit command. It will draw a line and arrow from the cell you select to all precedents (inputs into the formula) or decendants (all cells that use the selected cell as an input).
Click on the buggy cell and go to Tools –> Auditing –> Trace Precedents. Several lines and arrows will appear on the spreadsheet pointing to the inputs in the formula. Similarly, Tools –> Auditing –> Trace Dependants will trace a path to all formulas that use that cell as an input.
These are some of the common statistical functions in Excel that are useful in investing. Again, these are not the only ones to be found in Excel, I’m just filtering the onses most useful to investors.
AVERAGE(number1,number2...) Returns the average or arithmatic mean of the series.
GEOMEAN(number1,number2...) Returns the geometric mean of the series. See Note 1.
for difference between geometric and arithmatic mean.
MEDIAN(number1,number2...) Returns the median (i.e. midpoint) of the series
MAX(number1,number2...) Returns the maximum value in the series
MIN(number1,number2...) Returns the minimum value in the series
COUNT(number1,nubmer2...) Returns the number of values in the series.
FORECAST(X,known_X,known_Y) Extrapolates Y given a series of known X and Y values.
Uses linear regression (i.e. fitting a straight line
through the data) to project forward results.
TREND(known_y,known_x,X,const) Extrapolates Y given a series of known X and Y values.
Similar to FORECAST(), TREND() uses linear regression.
GROWTH(known_y,known_x,x,const) Extrapolates Y given a series of known X and Y values.
Similar to TREND(), but GROWTH() fits an exponential
curve through the data to project forward results.
LINEST(known_y,known_x,const,stats) Returns the statistical information on the line that
best fits the data. This function must be entered as
an array formula. See Note 2.
LOGEST(known_y,known_x,const,stats) Returns the statistical information on the exponential
curve that best fits the data. This function must be
entered as an array formula. See Note 2.
number1, number2, etc. are variables that can be entered directly into the equation or can be replace with a cell reference or range of cells.
known_X, known_Y are variables (usually a range of cells) that are input into the regression analysis.
X is the next X variable. Typically TREND(), GROWTH() etc. solve for an extrapolated Y value given the known_Xs & known_Ys and an assumed X. For example the annual returns on a portfolio are 3%, 17%, 23%, & -15%. These would be the known_Y values, the known_X values are the years 1, 2, 3, & 4. X would be the year for which you want TREND() to project a corresponding Y value.
Const is an optional TRUE/FALSE variable which can be excluded. It is used in the line/curve fitting function to force the y-interect to go through 0. If the flag is set to TRUE, the function will return a slope for a line/curve through the origin.
Stats is an optional TRUE/FALSE variable which can be excluded. If it is set to TRUE, additional statistical information (mn,mn-1,…,m1,b;sen,sen-1,…,se1,seb;r 2,sey) will be provided by the function.
Note 1:
When talking about average growth rates there are two types, arithmatic mean and geometric mean. Arithmatic mean is what is commonly known as the average, and is calculated from summing all the terms and dividing by the number of terms (i.e. (X + Y + Z) / 3). Geometric mean is calculated by multiplying all the terms and taking the n_th root of the product (i.e. (X * Y * Z) ^ (1/3) ).
The significance of this is in growth rates. Growth rates fluctuates from year to year and trying to nail down an average growth rate over a number years is a bit difficult if you use arithmatic mean. Take the following example of a portfolio’s 10 year performance.
Now, according to the law of compounding, the ending value of the portfolio could be calculated given the starting value, growth rate and time period. Since there were no deposits or withdrawls to the portfolio, the compound interest equation can be use. Starting_Value * (1 * Growth_Rate) ^ Time. But if you plug in the values from this example you get
$10k * (1 + 10.2%) ^ 10 = $26,413.
$1,375 higher than the actual ending value.
To get the true average growth rate, also known as the compounded annual growth rate (CAGR), you need to use the geometric mean. It is calculated a bit differently because only positive numbers can be input into the equation.
Using the geometric mean in the compound interest equation we get
$10k * (1 + 9.6%) ^ 10 = $25,038
which matches the actual results. For further discussion on the difference and applicability of the geometric mean, see this article
Note 2:
Array formulas are for functions that return several pieces of data. LINEST() return two pieces of information, the slope and y-intercept of the best fit line. Since two separate numbers cannot occupy the same cell, the function needs to be spread across two cells. Example:
To enter the LINEST() function, highlight cells A11 & B11. Then type
=LINEST(B1:B10,A1:A10)
but instead of hitting ENTER at the end, hold down SHIFT + CTRL + ENTER.
The results should be 8.31 and -22.60.
Similarly, =LOGEST(B1:B10,A1:A10) should return 1.63 and 0.69.
To project values of Column B for values of 11, 12, 13, 14 & 15 in Column A, either the TREND() or FORECAST() functions can be used for linear regression or the GROWTH() function can be used for exponential growth projections.
To get an idea of how well the TREND() and GROWTH() projections work, I created some charts plotting the projected values against the real values. In this example I used Fibonacci numbers as a pattern to fit a regression line.
You can see from the charts that the exponential regression works much better than linear regression for fitting this data set. If LOGEST() had some more data points to work with, the slope of the regression line would go to phi.
Say you want to sort this table by ticker symbol. Highlight the entire table (A1:G7) and start the sort command (Data –> Sort…). A popup window will appear and Excel should have anticipated that you have a header row (Row A). Excel will then ask by which column you wish to sort by. Excel, at least in the ‘97 version, allows up to three levels of sorting (i.e. start w/ TICKER, then any duplicates by EPS, and then a third tier of duplicates by ROE).
Each sorting criteria can sort by ascending or descending entries. If your data was entered such that you wanted to sort the rows, the pop-up window has an Options button that allows you to change the methodology from sorting columns to sorting rows.
When Excel sorts numeric and text data at the same time (for example if you wanted to sort the above table by P/E), Excel will list the numeric data first then the text.
Filtering
Using the same example table, let’s try filtering it. Highlight A1:G7 and start the Autofilter command (Data –> Filter –> Autofilter). The header row will become a drop down box. You may filter the table using any column’s drop down box. You may choose to filter for:
1. A specific entry in the column.
2. The top X entries. By default, the X is 10, but it is customizable.
3. A custom filter to search through the data that meets specific criteria.
Say you wanted to filter for all ROE > 10%. Click on the drop down box in cell C1 and choose (Custom…). This will bring up a pop-up window. Choose “Is greater than…” in the first drop down box and type 10% in the second box. Click enter to execute the custom filter. Notice that Excel has not deleted any of the data, it has merely hidden the rows that do not meet the filter criteria. If you wish to remove any filter, click on the appropriate header drop down box and choose (All).
To remove all filtering use Data –> Filter –> Show All.
Importing Data
Excel can open more than spreadsheet files. It can also read in text files, word documents, HTML, etc. However, Excel may have trouble integrating the data into the cell structure. When Excel has such a problem, it will ask how it should parse the data. There are two ways that the data can be split:
1. Delimited - Excel will look for a special character in the text and break the text into a new column whenever it encounters the charcter. These are usually tabs, commas, or spaces but Excel allows just about any delimiter if it is instructed to do so.
2. Fixed width - Excel will count a fixed number of characters and put them into a column regardless of where that break will fall in the text file. Excel generates a preview of the document and shows where the fixed width column break will cut the text.
Generally if you can control how the data file is created, it is easier to use Delimited to separate the data into columns.
I’m reposting some of my articles I wrote during a spreadsheet education project on The Motley Fool. The complete thread can be found here (Guest registration required for non-members):
Here are some of the most commonly used financial functions in Excel for investing. They are not the only financial functions that can be found in Excel. I am just filtering the ones I think are most useful for investors. All of the financial functions are basically derived from the compound interest formula:
Future_Value = Present_Value * (1 + Interest_Rate) ^ Time
For example, $1000 is deposited in a 5-year CD earning 5% compounded annually. Over the next five years the balance would be:
Year 1: $1000.00 * (1 + 5%) = $1050.00
Year 2: $1050.00 * (1 + 5%) = $1102.50
Year 3: $1102.50 * (1 + 5%) = $1157.63
Year 4: $1157.63 * (1 + 5%) = $1215.51
Year 5: $1215.51 * (1 + 5%) = $1276.28
Now, instead of going through the calculation for each year, the ending value at Year 5 could have been calculated from the compound interest formula:
$1000 * (1 + 5%) ^ 5 = $1000 * 1.27628 = $1276.28
The compound interest formula can be tweaked to work with more frequent compounding (say monthly or quarterly) by adjusting the Interest_Rate and Time values accordingly.
Incidentally, if the splitting of compounding periods is taken to its extreme (i.e. continuous compounding), the compound interest formula becomes the exponential growth formula e ^ Interest_Rate * Time.
$1000 * e ^ (5% * 5) = $1000 * 1.28403 = $1284.03
In Excel, the compound interest equation is made into several related functions. Each function will solve for a missing variable if given enough of the other variables. Think of it as a family of functions where one function will solve for C when given A, B & D. Another function can solve for B if given A, C & D, and yet another will solve for D given A, B & C.
=PV(rate,nper,pmt,fv,type) Present Value - calculates the principal amount of a loan.
=FV(rate,nper,pmt,pv,type) Future Value - calculates the final value of a savings account.
=PMT(rate,nper,pv,fv,type) Payment - calculates the periodic payment/deposit.
=NPER(rate,pmt,pv,fv,type) Number of Payments - calculates the number of payments to
pay off a loan or the number of deposits to an account.
=RATE(nper,pmt,pv,fv,type,guess) Interest Rate - calculates the interest rate of a
loan/savings account.
The boldfaced items are the minimum required inputs for the function to work.
“Type” is optional and refers to when the payments are made, 0 for end of the period, 1 for the beginning. If the type is omitted, it is assumed to be 0. The rate and nper must be consistent with the timeframe. If annual payments are assumed, the straight NPER and RATE values can be used. If monthly payments are assumed, the RATE must be divided by 12 and the NPER multiplied by 12.
This family of functions is used to calculate, for example, the respective parts of a series of equal cashflows, such as monthly payments on a loan or the ending balance of a savings account.
Cash outflows (such as a withdrawal of funds from an account) are listed as negative, cash inflows (such as deposits) are positive. Keep in mind that the negative values are needed to offset the positive for the function to operate. Say you write a FV() function to calculate the expected balance of a savings account.
Annual $1000 deposits, 2% interest and 5 years are plugged in as
=FV(2%,5,1000)
The output is -$5,204.04, but don’t panic, this does not mean you lost money or have a bug in your formula. The negative value is necessary to the function because it needs to balance positive and negative cashflows. Your ending balance will be $5,204.04, but Excel will report the output of the FV() function as -$5,204.04. You will have to remember how the sign convention works.
Examples:
You are considering a 30-year mortgage at 6% interest. If you wish to keep the monthly payment below $1000, how much can you borrow?
=PV(0.06/12,30*12,-1000)
=$166,792
If you needed $250,000, how much would the monthly payment be under the same terms?
=PMT(0.06/12,30*12,250000)
=-$1,499
If you deposited $100/month into a savings account at 2% interest, how long would it take to save $5000?
=NPER(0.02/12,-100,0,5000)
=48.1
If you were to save $125 instead of $100 how much would the account be worth at the end of 48 months?
=FV(0.02/12,48,-125)
=$6,241
If you bought a $100k annuity that paid $12k a year 10 years, what is the interest rate?
=RATE(10,-12000,100000)
=3.46%
Internal Rate of Return
IRR(values,guess)
XIRR(values,dates,guess)
These functions are often used in determining the performance of a portfolio with cash inflows and outflows. A portfolio that is setup with a single deposit and is allowed to grow without additions or withdrawals can be measured using the PV/FV family of functions. When multiple cashflows are involved, it gets a bit trickier. Excel can solve these types of problems with IRR() and XIRR(). These functions are iterative, meaning the solution is calculated through trial and error.
IRR() is used for cashflows that fall into regular intervals, usually annual.
XIRR() is used for irregular cashflows the corresponding date for each cashflow must be entered. XIRR() may not be installed in the default Excel installation. If you get #NAME? as the result of the XIRR() function, it means that XIRR() has not been installed. To install XIRR(), go to the Tools menu, pick Add-Ins… and make sure Analysis Toolpak is installed.
The guess part of the function is optional. If it is not entered it is assumed to be 0.1.
Excel calculates the IRR/XIRR by trial and error and the guess is only to give Excel a starting point.
Example: A $10k stock portfolio is bought on 1/1/2004. Dividend payments are received at the start of each subsequent year according to the table below.
On 1/1/2009 the portfolio is liquidated for $13k. The return on the portfolio is calculated with the IRR() & XIRR() functions. The dates are listed in Column A, cashflows are in Column B.
Col A Col B
Row 1 1/1/2004 -10000
Row 2 1/1/2005 500
Row 3 1/1/2006 750
Row 4 1/1/2007 650
Row 5 1/1/2008 700
Row 6 1/1/2009 13750
=IRR(B1:B6)
=XIRR(B1:B6,A1:A6)
The result of the IRR() function is 11.38%, the result of XIRR() is 11.37%. The difference is due to leap years. Below is the same example with different dates for dividend payments.
Note that IRR() cannot be used at all in this example.
Col A Col B
Row 1 1/1/2004 -10000
Row 2 2/14/2004 500
Row 3 3/15/2005 750
Row 4 4/1/2006 650
Row 5 7/4/2007 700
Row 6 10/31/2008 750
Row 7 1/1/2009 13000
=XIRR(B1:B7,A1:A7)
The result is 11.81%.
Net Present Value
NPV(rate,value1,value2,…) & XNPV(rate,values,dates)
Similar to the PV() function, these functions calculate a series of unequal cash flows. NPV() calculates the cashflows assuming a regular interval, XNPV calculates them at irregular intervals. “rate” in this case refers to the discount rate and is assumed to be constant throughout the calculation. In practice the discount rate should be your assumed rate of return + a safety factor. This is the main function used in discounted cash flow analyses.
Examples:
The intrinsic value (IV) of a security is the discounted sum of all future cashflows. This is usually done with a two-stage growth model. The first stage is the defined or explicit growth period where the cashflows and growth rates can vary from year to year. The second stage is the terminal or residual growth period where a constant growth rate is assumed. Typically the first stage runs for 10 years and all later years use the 10th year’s cash flow as a base for the future projection.
The cashflows for our example company from Years 1 - 10 are explicitly determined and shown in the table below. Years 11 and on are estimated by taking Year 10’s cashflow and growing it into perpetuity at a constant rate. A 12% discount rate is assumed.
Year Cashflow
Col A Col B
Row 1 1 $1.00
Row 2 2 $1.20
Row 3 3 $1.33
Row 4 4 $1.45
Row 5 5 $1.28
Row 6 6 $1.56
Row 7 7 $1.68
Row 8 8 $1.82
Row 9 9 $1.75
Row 10 10 $2.00
The cashflow from each year could be discounted individually in Column C using =B1/(1+.12)^A1,
=B2/(1+.12)^A2, etc. but Excel has a function, NPV(), that can sum the cashflows in a single equation.
The NPV of the 10 years is:
=NPV(0.12,B1:B10)
=$8.00
For Years 11+, a 5% growth rate is assumed. Excel does not have a function to calculate a perpetuity but luckily the algebra for this infinite series is very simple, lol. Here’s a link (Note: Guest registration required) to a post that goes through the math.
Year 10’s FCF/share is $2.00. The Year 10 value of the perpetuity is
=2.00*(1+0.05)/(0.12-0.05)
=$30.00
This is not the PV of the perpetuity. The equation used to solve the perpetuity calculates an equivalent single cashflow at Year 10. This needs to be discounted back to the present using the following equation.
=30/(1+0.12)^10
=$9.66
Adding it to the discounted cashflows from Years 1 - 10 yields 9.66 + 8.00 = 17.66
It may be easier to visualize the two parts of the DCF analysis with this chart.
The residual growth period theoretically goes on into infinity and so does the growth, but as you can see the discounted value of the cashflow shrinks to an infinitesimal amount. In practice the cashflows from the first dozen years or so will be equal to about half the IV.
The first 20 years captures about two-thirds to three-fourths and the first 50 years captures 90%+ of the IV depending on the discount rate chosen.
Yield - YIELD (settlement,maturity,rate,pr,redemption,frequency,basis)
This function calculates the yield of bond. Settlement is the purchase date, maturity is the maturity date. Rate is the annual coupon rate (the interest rate you get if you buy the bond at par value). Redemption is the redemption price per $100 face value (i.e. a $99 redemption price is equal to $990 on a $1000 bond). Frequency refers to how many interest payments are made per year. Basis is optional and refers to how days our counted (i.e. 30 days per month, 360 per year).
The true yield of a bond over its lifetime (otherwise known as the Yield to Maturity or YTM) is somewhat difficult to calculate because the price of a bond fluctuates and the bond has a limited lifespan. For example take a bond w/ $1000 face value and 5% coupon rate paid semi-annually has a 5-year maturity. Half way through the first year the bond’s price on the open market is $1025 because interest rates have changed since the bond was issued. A bond investor buying at $1025 may have a current yield of 50/1025 = 4.88% but when the bond matures the investor will only get back $1000, not the $1025 paid on the open market. The YTM is somewhat lower and can be calculated with the YIELD().
I’m reposting some of my articles I wrote during a spreadsheet education project on The Motley Fool. The complete thread can be found here (Guest registration required for non-members):
Arithmatic
=SUM(A1:A6) Adds the contents of cells A1 thru A6.
=SUM(A1:A3,-A5) Adds the contents of cells A1, A2, A3 & subtracts A5.
=PRODUCT(A1:A6) Multiplies the contents of A1 thru A6.
=PRODUCT(A1:B3) Multiplies the contents of A1, A2, A3, B1, B2, & B3.
=QUOTIENT(A1,A2) Divides A1 by A2.
=POWER(A1,A2) Raises A1 to the A2 power.
=SQRT(A1) Takes the square root of A1.
Irrational numbers. Excel also has some special functions for common irrational numbers.
=PI() pi (3.14...), returns pi accurately to 15 digits
=EXP(power) e (2.71...), returns e raised to the specified power
Trigonometry functions. Note that the inputs for all trig functions must be in radians, not degrees. Excel has functions that can convert degrees to radians and vice versa.
Hyperbolic trig functions are similar, just add an H to the end of the function name.
Rounding. Excel has an large selection of rounding functions.
=ROUND(number,decimals) Rounds a number to the specified number of decimals.
Negative decimal places are possible if you want to round
to the nearest 10, 100, etc.
=ROUNDUP(number,decimals) Rounds up instead of to the nearest decimal.
=ROUNDDOWN(number,decimals) Rounds down instead of to the nearest decimal.
=INT(number) Rounds down to the nearest integer.
=EVEN(number) Rounds up to the nearest even integer. If a negative number
is entered, it rounds away from 0.
=ODD(number) Rounds up to the nearest even integer. If a negative number
is entered, it rounds away from 0.
=MROUND(number,multiple) Rounds to the desired multiple.
=TRUNC(number,decimals) Cuts off or truncates all decimal places after the specified number
Other functions
=FACT(number) Factorial (example: =FACT(5) is 5 * 4 * 3 * 2 * 1)
=ABS(number) Returns the absolute value of a number
=LOG(number,base) Returns the logarithm of a number to a specified base
=LN(number) Returns the natural log (i.e. to the base e) of a number
=LOG10(number) Returns the log to the base 10 of a number
