# Theory behind XIRR computation

The XIRR answers a basic question : given a number of deposits / withdrawals and the current value of the portfolio, at which interest rate would I need to invest the deposits in order to reach the current value ?Note that it doesn’t mention anything about the actual investments taking place in your portfolio. Indeed, it values an*theoretical*portfolio in a uniform way, which allows us to compare the returns from different portfolios just by knowing the deposits, withdrawals and current value.

# Sample computation : Swaper XIRR

I’ll use my Swaper account as an example of XIRR computation.## Find the required data

Log on to your Swaper account. If you don’t have one, don’t worry; I’ll display the necessary data below.Go to “Overview” -> “Account Statement”. First enter a “From Date” earlier than your first deposit. Under “Transaction Type”, select “Deposits” and “Withdrawals” then click “Search”.In my case, it displays my initial deposit and three withdrawals.The current value of your account can be found under the “Overview” -> “Summary” tab. As I’m writing this article it’s 517€.Not that we have all necessary data, let’s move on to the dirty part !## Input the data on Google Sheets

The sample spreadsheet I created can be found at Google Sheets.Go to Google Drive, create a new spreadsheet.When inputing the data, we must follow some basic conventions. Deposits as well as the current portfolio value should be entered as negative values, while withdrawals should be positive.On my spreadsheet I sorted the lines according to the date, but it’s not necessary for the computation to work. I only did so in order to enhance readability.I inputted the date for the last line (current value) in a special way : instead of typing today’s date manually, I use the formula “=now()”.On the next column, I finally entered the formula for the XIRR : “=XIRR(D2:D6;C2:C6)” where D2:D6 is the amounts range and C2:C6 the dates range.The XIRR is initially displayed as a decimal number. Select the cell and choose “Format” -> “Number” -> “Percent” to switch to a more user-friendly format.Similarly, I also formated all dates using “Format” -> “Number” -> “Date”.Here’s the resulting sheet : (ignore the rightmost part which we’ll use in a moment)Note that the result differs slightly from the one computed on Swaper’s website : 11.32% on Google Sheets versus 12.09% on Swaper’s website.## Checking the result

We’ll check this result by using the definition of XIRR I provided earlier. We need to actualize each amount using the FV function, which computes the future value of an invested amount.In a column at the right of the first amount, enter the following formula : “=FV($D$8/12, int((now() – C2)/30), 0, D2)“.The parameters are :- the interest rate; in this case I referenced the cell containing the XIRR computed by Google Sheets. Note that I escaped “D8” into “$D$8” so that it will be immutable when I copy this formula
- the number of repayments : I divided the number of days between the date and today by 30, which once truncated is a rough estimate of the number of months elapsed since this date
- the payment amount, which is 0 as we don’t withdraw or add money
- the initial value