Introduction

In the article entitled The Dividend Discount Model interest rates were examined together with a short introduction to discounting a stream of cash flows and determining a value for an investment opportunity.  This value depended on the rate of return required by a willing buyer.  In this blog, the goal is to turn this effort on its head and learn how to figure out an interest rate if we only know the stream of cash flows and the future value.  The effort borrows from areas with which most people are already familiar: a savings account.  It shows how the mechancis of calculating interest payments in such an account can be adapted to figuring out how well a portfolio is performing.  The method that Quicken and other similar packages and sites uses will be demonstrated and shown to be equivalent to an Excel function called XIRR.  The limitations of this approach will be touched on.  Be forewarned: this article is long!

Later, we’ll look at this interest rate of 16% and investigate terms called a nominal and an effective rate of interest.

Things are going to be a little hectic in this account because each day the owner either makes deposits and/or withdrawals, so the balance of the account changes each and every day.  Nonetheless, whatever the balance may be after the bank closes, it receives an interest payment of 0.0438% which is tacked on.

The table that follows shows what happened during the month of January 2010.

Table 1: Daily Activity in a Savings Account in January 2010

The first column, A, lists the date; column B shows the starting balance for the account; transactions for the day are listed in column C: a number in red is a withdrawal and a number in black is a deposit; Column D shows the interest credited to the account that day: that interest is paid to the total balance in the account, which includes the starting balance in column B and the deposits for the day in C (nice); finally, column E holds the ending balance.  The ending balance consists of the starting balance plus transactions plus interest or the sum of columns B, C and D.  This ending balance in column E on any given day becomes the opening balance in column B for the next day—and on the calculation goes, day in, day out.   

• Column A Transaction date
• Column B Starting balance
• Column C Deposits and withdrawals
• Column D Interest on new balance of the account (B and C)
• Column E Final balance

It’s a bit of an unwieldy table, but the calculation is pretty straightforward.  Each day, the balance of the account is determined; interest is calculated on that balance and is then tacked on to form the opening balance for the next day.

If you look at the table above and think what if there interest rate were high or lower for those thirty-one days; how would that change anything?  In fact, if you think about it, the only thing that would change would be the daily balances.  As long as the starting balance remained the same and there was no difference either the amount or timing of each of the cash flows (the deposits and/or withdrawals), the only thing that would change would be the account balance on each day.  Even if the interest rate had changed in the middle of the month—on the fifteenth—once again it would only impact the daily balances.

So, since the balance in this hypothetical savings account is dependent on the interest rate, if you don’t know that rate, is there a way to figure it out?  In other words, you might know when and in what amounts you made deposits and withdrawals; you could also know the starting and ending balance of the account, but if you’re missing the rate of interest, can you calculate it?  The answer is yes.  Since the final balance, indeed any balance, depends upon the rate of interest it is possible to figure it out.

The first thing a person might do is consider looking at the initial balance of $10,000 on January 1 and the final balance of $19,878.60 on January 31; however, it’s clear that simply using this difference doesn’t provide the answer.  While the balance of $19.878.60 does contain contributions from regular payments of interest over the month, it also contains deposits (and withdrawals) made to the account.  These interest payments are the only contributions from the bank or financial institution and they’re really what you’re trying to get your arms around.

So the next step might be determining what the net result was of those deposits and withdrawals.  If you sum them up (remembering that figures in red in column C above are negative numbers with a value that should be subtracted), you get $9,850.  Similarly, adding up all the interest payments gives you $193.93.  So you earned $193.93 on your investment of $10,000, but if you divide $193.93 by the initial investment of $10,000, you end up with 1.9369%; if you multiply that by 12 as an initial attempt to come with an annual interest rate, you get a figure of 23.2427%, which is much higher than the interest rate paid by the bank.  You may not have expected to have calculated the rate of interest precisely, since a few shortcuts were taken, but to be over seven percentage points off is really missing the target.

So to get a handle on where the problem might lie, you might look at when the deposits were made and realize that if they were all made early in the month (and the withdrawals occurred later), this change might increase the amount of interest generated.  So to see if changing the dates of deposits and withdrawals makes a difference, you add up the total amount deposited during the month ($38,885.00) and the total amount withdrawn ($29,500).  The results are tabulated below.

Table 2: Details about deposits and withdrawals in a savings account

The deposits are listed in column F above, while the withdrawals are in Column G.  The last row sums up the contents each of the three columns.  Finally, $29,500 is subtracted from $38,885 to confirm that the net impact during the month was deposits of $9,385.

Next, let’s look at the same bank account, but this time let’s make all the deposits ($38,885) on January 2 and have all the withdrawals ($29,500) occur on January 30.  So if we revisit the account first presented in Table 1 and change the dates on which the deposits and withdrawals occur to that they’re in lump sums on January 2 and January 30, we get the following.

Table 3: Changing the timing of deposits or withdrawals into a savings account

The results quite dramatic in that if you add up all the interest payments received in January, you’d get $625.12, which is much larger than the total of $193.69 determined when these deposits/withdrawals were more evenly spread out.  If you were to try and use the simple approach described earlier to come up with an annual rate of return, you’d end up over 75%, which is way off the mark.

Let’s see what happens if all the deposits occurred on January 29, followed by a withdrawal of $29,500 the next day.

Table 4: More changes to the timing of deposits or withdrawals into a savings account

Now, the total interest earned drops to $161.97.

What’s clear is that it’s not only the amount of the deposits and withdrawals that impact the fortunes of an account, but the timing too.  So any attempt to calculate the interest rate earned that doesn’t consider when the deposits occur will come up with an incorrect answer.

What do you need to know to calculate a return? 

So if you don’t know the rate of interest, what do you need to figure it out?  You certainly need to know how much is in the account at the start; you’ll also need to know how much is there at the end.  We’ve just shown that you also need to have information on the amount and timing of deposits and withdrawals (cash flows) into the account.  If you had a daily balance, as in the first table, you could figure it out right away, so let’s imagine you don’t have that information.  In summary you know the following

• The starting balance
• The timing and amounts of deposits and withdrawals
• The closing balance

That’s it.  With this information, you can figure out the interest rate for the account.  It involves a numerical solution or an iterative method, which in this case means making a guess, refining it and converging on the answer.

So here goes.  Let’s way you don’t know the answer, but you did that quick and dirty estimate described at the start of the discussion and decided the rate of return was 20%.  Since interested is compounded daily, in practice you’re saying that the daily rate of interest is 0.0548% (dividing 20 by 365.25).  You run the calculations and get the results presented below.

Table 5: Iterative method for calculating an interest rate with a guess of 20%

With a guess of 20%, you end up with a final balance ($19,927.45) that’s higher than the balance of $19,878.69 reported on January 31, 2010.  Since your guess is too high, you have to lower it.  This time you try 10% to see what happens (or 0.0274% a day).  Once again, the results are tabulated below.

Table 6: Iterative method for calculating an interest rate with a guess of 10%

Since the calculated ending balance of $19,805.80 is lower than the actual reported balance, you know your guess is too low.  But something important has happened: you’ve corralled the correct answer.  You now know it lies somewhere between 10 and 20%, so it’s now a matter of closing in on it.  So, in the next attempt you split the difference and try 15%.

Table 7: Iterative method for calculating an interest rate with a guess of 15%

That’s better; the final balance calculated with a return of 15% is $19,866.52, which, while still too low, is the closest you’ve been to the actual balance of $19,878.69.  People normally close in on the answer by continually splitting the difference, to the next guess is half way between the answer you know to be too high (20%) and the one that is now too low but closest to the correct answer (15%).  The results are shown in the next table.

Table 8: Iterative method and converging on the correct answer

The goal is to move closer and close to the answer: the bracket the surrounds the correct answer should get smaller with each guess.  In this way, within fourteen attempts, you converge on the correct interest rate of 16%.

After the third guess, 15%, you’re still too low, so you take the average of the closest low guess, 15% and the closest high guess, 20%.  That new guess, 17.25%, is too high, so this time, you average the closest high guess, 17.25% and the closest low guess, 15%.  You must repeat this process, over and over, until you get to the point where you’re happy with the answer.  By guess 13, the result was within a penny of the correct answer and it was an exact match by the fifteenth estimate.

These are obviously the kinds of calculations designed for computers and there are number of ways to use not only the information from the last guess to converge on the correct answer much more quickly than 12 guesses.

Closing in on the answer

As an aside, and if you don’t like math, just jump to the next section, the process of “surrounding” the true value, then closing in on it by taking steps that are half-way between the most recent high and low values of the rate of interest is a little laborious.  There’s clearly some unused information provided, because 10% is too low by $72.89 and 20% is too high by only $48.76.  You know the “correct” answer is $19.878.69.  So here’s a way to get to the answer more quickly.  It’s not a novel approach; in fact, you can go as far back as Sir Isaac Newton to look at these intercalative methods.
So, looking at table 8, the new boundaries—the upper and lower limits—are $19,927.45 on the high side and $19,805.80 on the low.  The real answer lies between those two limits; the actual interest rate lies between the high and low guesses.  Since the answer of $19,878.69 is closer to the higher limit, you could figure out the fraction of the distance it is between the high and low values, then take that fraction and figure out what percentage value is that same distance between the high and low percentage estimates.  If you carry out these steps, you end up with your third guess being 15.9917%.  You get almost as close to the correct answer in one guess than the previous method did in nine attempts.

Using 15.9917% as your next guess returns $19,878.59, which is too low, but very close to the answer of $19,878.69.  Once again, you figure out the fraction of the “distance” that these new answer is from the low value as the low value is from the high; use that same fraction to increase the guess.  The next guess becomes 16.0000%, which is the correct answer.

What if the interest range keeps changing?

Let’s mix things up with that savings account a little and assume that the interest paid changes every day.  You can find out the daily balance and you know the cash flows into and out of the account; you also know when they occur, but what’s missing is the annual interest rate.  You might want to know this number so you can compare the performance of this savings account with a dynamic interest rate that changes daily and a more traditional account like the one portrayed in Table 1.

In Table 9 below you see the day-to-day results for the savings account.  The deposits and withdrawals are the same as in Table 1, but the interest paid each day changes.

Table 9: A savings account with daily changes to the interest rate

Now the final value is lower, $19,874.06, so you’d expect the new interest rate to be smaller than the 16% in the initial example.  You could run through the same iterative process to come up with the effective annual interest rate of this account of 15.62%.  The table below shows the process carried out, starting with the two same initial guesses of 20 and 10% per year.

Table 10: Iterative process to determine the actual interest rate

This time the answer was reached after sixteen attempts.

Let’s try and simplify this approach even more.  When figuring out the interest on this savings account, as mentioned earlier, there are a number of factors that have to be considered: the initial balance, the cash flows and their timing (deposits and withdrawals) and the final balance.

Table 11 below just summaries the pertinent information and includes the initial balance, together with the deposit make that day; all the other deposits or withdrawals into the account right up to January 31, 2010.  In fact, the balance on January 1 is treated like a cash flow into the account on that day.  Keep in mind that in this wacky bank or financial institution, any deposit received attracts an interest payment for the entire day.  So, the $100 that was deposited on the first day will receive the same consideration as the balance of $10,000 that rolled over from December 31, 2009.

Table 11: Initial balance and cash flows into the savings account

We know that to reach a value of $19,874.06 on January 31, 2010 these cash flows and dates on which they occurred together and the opening balance of $10,000 have to be invested at an annual rate of 15.62%, that’s 0.0428% a day.  In other words, the cash flow of $10,100 on January 1, 2010 (the balance that rolled over plus the deposit of $100) would be invested for 31 days at a rate of 0.0428% a day.  At the end of those 31 days, that balance, a fraction of the total, would become $10,234.76.  Similarly, the deposit on January 2 of $300 is invested for thirty days at 0.0428% a day meaning that it has a value of $303.87 at the end of the month.

So what happens on January 3?  There’s a withdrawal of $500.00.  It a deposit is compounded forward at the given rate of interest, a withdrawal should be too.  If you compound this withdrawal forward at 0.0428% a day, you end up with -$506.24.

Compounding a loss forward is often a little tricky to grasp, but think of it this way: if a person deposits $400 on January 1 then, to calculate the value of that investment thirty days later, you add interest each day at the rate of 0.0428%, the rate that’s being used in the most recent example.  What fhappens if the person withdraws $400 the very next day?  The funds will no longer be in the account, so you should no longer credit interest.  Well, in fact, the way to cancel out the thirty of the thirty one days of interest from the funds deposited on January 1 is to compound the withdrawal forward too.  In this way, the interest accrued on the first deposit on January 2 will be exactly canceled by the interest lost on the first withdrawal on January 2.  This point will be discussed further in a moment.

For now, let’s look at the effect of compounding deposits and withdrawals forward at the rate of 0.0428% a day from January 1 through January 31, 2010.  The results are tabulated next.

Table 12: Cash flows into an account and compounding them forward

Column A details the date of the cash flow; note that the initial balance and the January 1 deposit of $100 are tallied together; column B contains the “cash flow,” the deposit (a positive number) or the withdrawal (a negative number); finally, column C contains the value of the cash flow in column B compounded forwarded to the January 31.  (Remember too that the deposit on January 31 gets one day of interest!).  When all these future values (the value compounded forward to January 31) are added together, the final balance is $19,874.06: the balance shown in table 9, the value reported in the bank statement.

Compounding the cash flows forward at the correct rate of interest yields the appropriate final balance.

This process can clearly be mimicked to calculate an average annual return for an investment portfolio.  The only issues that are of relevance are, as before, an initial balance at some specified dates; cash flows into and out of the account with dates and amounts and a final balance at some specified point in time.  A similar iterative process to that described earlier can be used to determine the rate of return.

An example is provided next.  A hypothetical account is opened on January 1 and there are sixteen deposits or withdrawals in total during the year.  The final balance on December 31 is determined by reading the “Total Equity” from the Trading Center.  Table 13 below shows the opening balance, each of the cash flows into or out of the account and the final balance at the end of the year.

Table 13: Example of a hypothetical brokerage account

The account has a starting balance of $8,523.16 on January 1 and a final balance of $21,443.26 on January 31.  During the course of the year, there are various deposits (positive numbers displayed in black) and withdrawals (negative numbers displayed in red).  Now we treat these balances and cash flows as if they’re in a savings account and determine the effective rate of interest that these investments generated.

Table 14 shows how the information to take a guess at the right answer is displayed on an Excel spreadsheet.

Table 14: Iterative approach to getting a brokerage account return

Cell B1 contains an attempt at guessing the interest rate that would have been earned had these funds been invested in a savings account.  In fact, this value of 24.10% was the final guess that converged on the correct answer.

• Column A contains the dates corresponding to specific account balances (green background) or cash flows (deposits and/or withdrawals).
• Column B contains the value of the account balance (green background) or of the cash flow, which is either a deposit (dollar figure in black) or withdrawal (dollar figure in red).
• Finally, Column C contains the value of the amount in column B compounded forward to the morning of January 1, 2011.  So, for example, the opening balance of $8,523.16 is invested at an annual rate of 24.10% (daily rate of 24.10% divided by 365.25 days), it would be worth $10,843.33, which is the value in cell C4.

Here’s what the formula in cell C4 in the Excel spreadsheet looks like

=B4*(1.+$B$1/365.25)^($A$21-A4)

What it’s saying is increase the value you find in cell B4 by the annual interest rate you find in cell B1 (but convert it into a daily rate of interest) and repeat this process for a year.  Cell B1 has dollar signs (hit F4 when you click on B1 in a formula) which is telling Excel that if you copy this formula, don’t change B1, leave this reference exactly the way it is.  Similarly, cell $A$21 should always remain the same if this formula is copied.  So if you copy the contents of cell C4 and put them into cell C5, B4 will become B5 and A4 will increment to A5 in the formula, but B1 and A21 won’t change.

When you sum up these values, you get what should be the account value on January 1 (or at midnight on December 31), which is $21,443.26.
By the way, if once again you started with guesses of 30 and 15% and found that the real value lay somewhere in between, then started the iterative process of taking an intermediate guess, which is halfway between the nearest high and low guesses, it would take nineteen additional guesses to converge on the correct answer.  Table 15 displays those guesses.

Table 15: Iterative guesses in figuring out a return

Had the interpolative method been used as describe earlier, you could have converged on the correct answer within four additional guesses.  Those guesses are also shown in table 15.

The process for calculating the return can be described as follows:

I know math is an unpleasant experience for some, but the expression above just illustrates what’s been talked about previously.  CF stands for a cash flow (a specific deposit or withdrawal as discussed earlier).  The letter “r” with the subscript guess is just the interest rate: the annual rate that was estimated.  It changes as the guess is refined and the final balance gets closer to the actual value.  The interest rate is divided by the number of days in the year, since we compound daily.  Finally, the last term in parentheses is the number of compounding periods—the number of days for which the cash flow (CF) gets interest.  The first term is the final day (January 1, 2011) and the last term is the current day.  So, if it happens to be January 1, 2010, interested is compounded for 365 days—an entire year.  It’s a shorter period for all the other cash flows.

I like this way for calculating the rate of return because it’s conservative.  There are justifiable criticisms for the process, which will be addressed in a moment.  You’ve probably figured out from the first sentence in the paragraph that there are other ways to calculate a return.  They’ll be looked at in a moment.  We’ll first take a look at how Quicken does it and then touch on the XIRR function in Excel.

This generalized expression for showing how the future value of each cash flow is calculated is presented for a reason: so that it can be compared to Quicken!

Nominal and Effective Rates of Interest

At the start a hypothetical savings account was introduced that paid interest at a rate of 16% a year and a caveat was presented about nominal and effective rates of interest.  I want to touch on this issue now, because when the method that Quicken (and the XIRR function) is introduced in the next sections, the rates may look higher than were presented previously.  There’s a good reason for this difference.

An interest rate is fully explained unless the compounding frequency is also provided.  Earlier, when the savings account was discussed, a 16% annual rate was presented that compounds daily at 1/365.25 of the annual rate or 0.0438% each day.  You might expect given that the annual rate is 16% that if you invested $100 at this rate, you’d have $116 at the end of a year.  In fact, you’d have more than $16 in interest.  The final balance at the end of the year would be $117.33.  If the interest compounded quarterly at a rate of 4% every three months, you’d have $116.99 at the end of the year.  This difference is caused by the “interest on interest”: the interest is added to the principle and the interest earned itself also compounds.  If you invested $1,000 at 10% compounded annually, you’d generate $100 in interest.  If it compounded every six months, you’d earn 5% at the end of six months—that’s $50 in interest bringing the balance to $1050.  At the end of the year, you’d earn the other 5% on the balance of $1,050, which is an additional $52.50 in interest.  The more frequently the interest compounds, the more you get in return.  So, in the example of a 16% annual rate, the effective rate for daily compounding is 17.33%; for quarterly compound it’s 16.99% a year; if interest compounded twice each year, the effective annual rate is 16.64%.

This issue of compounding and the effective rate will be touched on in the next few sections.

How Quicken does it

The difference between how Quicken computes a return and what was presented previously is subtle but it can lead to profound differences.  These are discussed next.

Earlier the “savings account” approach was generalized in simple expression.  Here’s a similar expression for how Quicken performs the calculation on determining a rate of return.

If look back at the previous expression, there are two subtle differences—maybe one of them is subtle.  First Quicken (and for that matter the XIRR function) has 365 days a year.  No big deal; it’s just as valid, but I will explain later, when I get a soapbox, why I am stuck on the 0.25 issue.  The next is how the interest rate is used.  In the “savings account” example, the annual rate of interest was converted into a daily rate.  In the expression above, an annual rate is used and the compounding occurs at a fraction of a year (if number of days in the exponent is less than 365, you’ll get a fraction).  Again, it’s not a big deal, but it does make a difference in the rate that’s calculated.

It was shown in the last section that for any annual rate of interest, the more compounding periods you have the higher will be the effective rate that you get.  The consequence is that if you have the same investments in different accounts with the same annual rate of interest, but each of them compounds at a different frequency, the investment with the highest number of compounding periods will have the highest rate of return.
If you then say, well I am now going to fix the final balance to some set amount: how does that impact the nominal rate of interest?  Let’s see.
What Quicken is doing is compounding annually—and at a fraction of a year for investments that haven’t been in the account for longer than a year.  Let’s go back to the hypothetical brokerage account presented in table and carry out the same process; however, this time using the expression that’s used by Quicken.

Table 16: Iterative process with annual compounding

For completeness the guesses used in the interpolative approach are provided at the bottom of the table.

The rate of 27.32% is higher than the 24.1% calculated earlier.  The lower rate was calculated with a method that had daily compounding of interest; this higher rate uses annual compounding.  We saw earlier that for a given fixed annual rate, the one with the higher compounding frequency gave a higher effective rate and, thus, a higher overall return.  However, in the examples we’re using, the starting and ending balances are fixed.  So, if you think about it, an interest rate with a higher compounding frequency needs a lower annual rate to hit a fix target than does an interest rate with the same annual rate but a lower number of compounding periods.  Hence, the Quicken approach with its annual compounding should provide a higher rate of return when used to analyze a given portfolio.

Let me run through the discussion that we had for the last Excel presentation.

•  
  • Column A contains the dates corresponding to specific account balances (green background) or cash flows (deposits and/or withdrawals).
  • Column B contains the value of the account balance (green background) or of the cash flow, which is either a deposit (dollar figure in black) or withdrawal (dollar figure in red).
  • Finally, Column C contains the value of the amount in column B compounded forward to midnight on December 31, 2010; however, the formula in this column is different than the one used before.

It’s different because it uses the Quicken approach.  So, for example, the opening balance of $8,523.16 is invested at an annual rate of 27.32%, it would be worth $10,844.64, which is the value in cell C4.

Here’s what the formula in cell C4 in the Excel spreadsheet looks like

=B4*(1.+$B$1)^(($A$21-A4)/365)

The difference is how the compounding is treated.

Just in case you preferred the iterative method where the distance between the closest high and low estimates was halved to slowly converge on the answer, here’s the table of those results too.

Table 17: Annual compounding and the slower iterative approach

Just to make life a little easier, if you use this method, here’s a formula that you can put in column H, the first one going into cell H4.

=IF(F4<>"",IF(G4<0,"L","H"),"-")

It’s saying that if the contents of cell G4 are less than zero show the letter “L”; if they’re greater than zero, show the letter “H”; however, before you do any of that, check for what’s in cell F4 and if it’s empty, just put a dash into cell H4 to show that the calculation hasn’t started yet.  You could then copy this formula for every cell in the column where this information is required.

Which compounding period is correct?

I think that you get a more accurate answer if you use an annual compounding period, since, for most cases, you’re looking for an annual rate of return for your portfolio.  It’s difficult to demonstrate an annual rate of return for a portfolio that’s less than a year old or, frankly, that has cash flows at intervals other than a year: if I suggested compound for 0.834 of a year or 1.3345 years, it makes no sense to many people.  So, to recognize what’s happening, drawing the analogy with a savings account where interest is paid every day is a good place to start.

Excel and the XIRR function

The IRR bit in the term XIRR stands for Internal Rate of Return; it’s an expression that you’re likely to run across when you’re involved in budgeting in a corporation.  In a nutshell, if you have two or more comparable projects and you’re deciding to which one should financial resources be dedicated, you look at the cash flows that each is likely to generate, then discount those cash flows back for each project, sum them up and the one that has the highest value is the project considered.  The process is essential identical to what has been described earlier except that instead of compounding forward to come up with a value that matches the current equity in a portfolio, you’re discounting back from cash flows, including the final balance, to the current value.  So, if you had string of cash flows, you’d want a rate of return that matched your initial balance.

Excel has a number of functions that work on the concept of IRR, but the one that’s useful to evaluate a portfolio is the XIRR function, since it allows for what’s called “irregular cash flows.”  It’s not a big deal really, but in many real-life situations, cash flows occur quarterly or annually; XIRR allows them to occur at irregular periods in time—like for people investing in a portfolio.

Let’s put the XIRR function to work using the fictitious portfolio described in the previous few examples.  Recall in the last example, where we used the most widely adopted approach to determine the annual return for a portfolio, we compounded the return annually and, in an iterative process, came up with a value of 27.32%.  The next table shows a similar calculation using the XIRR function in Excel.

Table 18: Using the XIRR function on the example brokerage account

Here's a summary of the data in each column. 

• Once again, column A contains the dates corresponding to specific account balances (green background) or cash flows (deposits and/or withdrawals).
• Column B contains the value of the account balance (green background) or of the cash flow, which is either a deposit (dollar figure in black) or withdrawal (dollar figure in red).  We don’t need a column C this time, since XIRR does all the work.

The XIRR function returns a value of 27.32%, which is identical to the compounding approach, described earlier, using an effective annual rate of interest.  It shouldn’t be surprising, since the methods use the same algorithm.  The expression in cell B2 is as follows:

=XIRR(B4:B21,A4:A21,0.1)

The term B4:B21 corresponds to the cash flow values in the corresponding cells; A4:A21 contain the dates and the 0.1 entry is a guess at the rate of return.

Issues with the XIRR function 

XIRR can be quirky: when providing a range of dates and numbers, the very first date in the range must be the earliest date.  Also, as was demonstrated earlier, we’re trying to match a final balance, so the latest value—which is the total equity value of the portfolio—should be treated as a cash flow out; that is, it should be written as a negative number.

A few other issues with the function: make sure that the date column is in the proper format; that is, make sure it’s not “text” or a numeric.  Cutting and pasting may not necessary solve this problem.  You may have to highlight the range, right click and select format cells, then on the tab that says “Number,” select “Date,” which is usually the fifth entry.  You can select the type of date from the window that will appear on the right.  Do the same thing for the values column, which isn’t as sensitive, but may as well be in the format of a currency.

When using XIRR, make sure the range of values is entered first, then the range of dates.

If the return is large and negative, the function may not be able to converge on the answer without a guess that’s a negative number.

If you don’t have the Excel XIRR function—and you do have to activate it in many cases—follow this link to find out what you do next.

Things to keep in mind about the current approach

Recall that this approach is only concerned about balances and cash flows into and out of your portfolio; it provides a method of making a determination on how well your portfolio is doing based that allows you to compare with other types of investments.

The approach isn’t concerned with what’s going on inside your portfolio; in other words, it’s not concerned with when and how you buy or sell shares; how much you pay in commission, if you get interest form idle cash, if you have fees, if you get funds entered into your account for referrals, if you pay extra fees for bouncing checks: none of these is an issue.  They certainly impact the balance of your portfolio at any point in time, but this information is tied up in the final balance, which is treated as a cash flow out in the XIRR approach or as a target to match in the approaches that used compounding—they actually use the same approach.  You don’t need to account for fees, dividends, etc.  If you do try and include them as a cash flow, it will give you an erroneous return as to how you’re investments are doing based on the amounts that you funded the account.

The only issue that probably should be considered, but isn’t, is the issue of taxes.  If you incur taxes on dividends or capital gains, you have a real cost for managing your investments.  If you want an accurate metric of how you’re performance, any relevant portfolio-related costs that aren’t included in the swell of activities inside your portfolio should be considered.  If you incur a tax on capital gains and pay for it from another account, a correction should be made.  The simplest way to accomplish this goal is to change the final balance of your portfolio.  For example, if you incurred, say, $400 in costs during the year that were paid somewhere else, the final balance should be adjust from $21,443.26 to $21,043.26; the rate of return will decline, but it’s a better reflection of what actually occurred during the year.  (There is a way you can also make the adjustment in a way that includes the timing of these activities, but I’ll discuss that point some other time.)

So do these methods give the correct return?

The short answer is that they give a correct answer.  The process behind calculating the return is accurate, but before making a determination on what your portfolio is doing, you really must first ask the question: what information do you seek?

The process described in this blog is attractive because it mirrors what occurs in many other financial assets with which people are familiar; it calculates a return in a manner that’s similar to an interest bearing savings account or CD.  In this sense, the approach is fairly intuitive.  There are other ways to make a determination as to how well a portfolio is performing; they will come up with slightly different results, but it doesn’t make them any less valid.
Some information does get lost.  For example, let’s say that there are two portfolios which are identical in every respect regarding cash flows and their timing and the starting and ending balances.  The difference is that one person incurs lots of commissions and bounced check fees, while the other does not.  It could be argued that the person that incurred fees actually performed better because he or she has a lower cost basis for investing, since some of the cash flows were gobbled up in fees, which don’t contribute to performance.

It’s also possible that two otherwise identical portfolios could have had different timing of the same investments: same cash flows, balances, even identical companies and number of shares, but person A bought the holdings earlier in the year than did person B: are these two portfolios identical? 

The Leap Year issue

(Don't take this section too seriously!) In most cases, having that 0.25 trailing at the end of 365 doesn’t make sense; I used it in discounting as it adds a little buffer to the final result.  The other reason you don’t really need it is that if you’re compounding on a daily basis, the calculation takes care of the number of days.  Even when years are involved, with the exception of 1900, Microsoft handles the issue of Leap Years for you when using Excel.

I started doing it when I discovered that Microsoft, which is proud of its method of having dates start on January 1, 1900, allowed February 29, 1900 to exist.  Most years ending in 00 aren’t Leap Years.  If the first two digits are divisible by 4, it’s a Leap Year; so, 1600 and 2000 were Leap Years, but 1900 was not.  Since Microsoft can get away with creating Leap Years, I thought I’d start using them too.  End of sermon!

In Summary

In this blog an effort was made to unravel one approach to calculating the return of a portfolio on Zecco.  The approach mirrored how returns are calculated on many types of investments with which people are already familiar: savings accounts, CDs, etc.  It showed how interest could be calculated on these instruments.  The interest rate calculation was used as a basis for demonstrating how compounding cash flows could be used as a basis for calculating the return on a portfolio.  The issue of interest rates and how regularly they are compounded was introduced and this discussion led to an introduction to the most common way for calculating returns—the approach showed how Quicken, for example, does it.  Finally, this method was compared to the XIRR function in Excel and a demonstration was provided as how the XIRR function can be implemented to determine the effective annual return for a portfolio based on cash flows into and out of that portfolio.