Tracking Investment Returns: A Practical Guide Using XIRR

Any investment platform eventually has to address one uncomfortable topic: a real return on investment (ROI). Comparing ROI across different platforms is rarely simple. There are several reasons for this.

Different platforms use different methods to calculate returns. These methods are usually documented and publicly available, but users still often complain that the results shown by platforms are too optimistic, for example:

• Not adjusted for defaults or overdue loans.
  A platform may say “only one payment is late”, meaning they do not treat the entire loan as potentially defaulting — even though for the investor, the full principal is at risk.
• Missing negative data or hiding losses.
  Some platforms ignore fees or other costs (transfer fees, service fees, currency losses). These still affect your true return, even if the platform excludes them from its calculations.
• Calculated in a way that flatters the platform rather than reflects reality.
  Platforms rarely start their presentations with pessimistic scenarios.

Because of this, it’s up to us — the investors — to calculate realistic and comparable returns ourselves.

Why You Need Your Own ROI Tracking

Things are simple if you invest using only one platform. But sooner or later, most of us end up with multiple investment types running in parallel: deposits, funds, P2P lending, trading, crypto, or other projects. At that point, we need:

• A way to compare the performance of different investments (e.g., Investment A vs. Investment B)
• A way to calculate the cumulative result of our entire portfolio

With more experience, we also want to look at returns from different angles — by time period, by strategy change, or by risk scenario. Many investors also like to track optimistic, realistic, and pessimistic outcomes.

To perform these calculations, I rely on Excel or LibreOffice Calc and specifically the XIRR function. XIRR calculates the internal rate of return based on the actual dates of deposits and withdrawals (cash flows).

In simple terms:

If I invest $5 three months ago and receive $10 today, XIRR is higher than if I invested the same $5 twelve months ago and received $10 today.

---

A Simple Example & How XIRR Works

How XIRR works:
To calculate the result, the XIRR function uses:

• All deposits and their dates
• All withdrawals and their dates
• The current value of the portfolio

This combination allows XIRR to determine the effective rate of return, taking into account both timing and amount of cash flows.

Example:

• February 2, 2014: Deposit $1,000
• May 10, 2014: Add another $500
• May 12, 2014: Withdraw $200
• November 12, 2015 (today): The platform shows a current value of $2,600

Using XIRR on these numbers gives a return of 49.73%.

A More Realistic Scenario: Including Risk

Real life is rarely as simple as the example above. When investing in P2P lending, for example, some loans will be late, and some may default. Late loans may still recover, while defaults usually recover only partially — if at all.

Most platforms display something like this:

• Free cash
• Current (well-performing) loans
• Late loans
• Defaulted loans

Using this data, you can build your own valuation of the portfolio based on your assumptions about recovery.

For example, you might assume:

• 50% of late loans will recover
• only 10% of defaulted loans will recover

Then the value of your portfolio today can be estimated as

Current_Portfolio= Well_performing_loans_amount
+ Recovery_Overdue% x Overdue_loans_amount
+Recovery_Defaulted% x Defaulted_loans_amount
+ Free cash

or in Excel:

This allows you to calculate realistic XIRR instead of relying on the platform’s potentially optimistic view.

Three Scenarios: Realistic, Optimistic, Pessimistic

To make analysis more comprehensive, you can create three scenarios with different assumptions:

• Realistic: Your best estimate of recovery percentages
• Optimistic: Assume higher recovery from late or defaulted loans
• Pessimistic: Assume lower recovery

For each scenario, you get a separate portfolio value and therefore a separate XIRR. This helps you understand the range of possible outcomes and choose a risk profile that matches your preferences.

Tracking Performance Over Time

Once your formulas are ready, you can create a “history section” in your spreadsheet. Each time you update portfolio data, copy the current line of calculations to the history section. Over time, this gives a timeline of XIRR values.

With this history, you can:

• Track portfolio performance over time
• Build graphs showing trends
• Compare strategies or platforms side-by-side
• Calculate additional metrics, e.g., growth of overdue loans vs total portfolio

Excel  gives you complete flexibility. You can:

• Track different investments on separate sheets or even in different columns within the same sheet.
• Combine all cash flows to get a consolidated view of your portfolio.
• Build joint graphs to compare the performance of multiple investments side by side.
• Add calculated columns in the history section, for example, to track the growth of overdue loans relative to the total portfolio.

Excel Sample File

I’ve included a sample Excel file that demonstrates these calculations. You can use it as a starting point for building your own tracking system.

Download there:  xiir-sample

Thank you for reading!
Happy investing!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Disclaimer: The information provided in this post is for general informational purposes only and should not be construed as investment advice. It is not intended to be used as a basis for any investment decisions. The views expressed are solely those of the author and do not constitute an offer or solicitation to buy or sell any financial instruments. The author is not a licensed investment advisor and does not provide personalized investment recommendations. Readers should consult with a qualified financial professional before making any investment decisions.