Excel Tip of the Week #253 - Calculating the effective interest rate of an investment

Hello all and welcome back to the Excel Tip of the Week!  This week we have a Creator level post in which we're taking a look at a clever use of the XIRR function - to create an automatic and simple interest calculator for calculating the effective interest rate on a pension or other investment that changes in value but does not actually pay interest.  XIRR was most recently covered in detail in TOTW #169 if you'd like more background.

The concept of an internal rate of return

If you're not familiar with the terminology, here's a brief overview of the idea of an internal rate of return.

The idea comes from net present value calculations - i.e. the thought that money loses value over time due to inflation, and hence when evaluating a project, you need to consider not only money that comes in and goes out, but also when those things happen.  NPV calculations usually assume some target "discount rate" that reflects the presumed speed at which the anticipated future cash flows lose their value compared to the present day.

NPV calculations often then want to find the internal rate of return.  This is the actual interest rate we would need to achieve for our cash flows to make the project break even.  If the rate is achievable, then the project is worthwhile.

While many real-world calculations are considerably more complicated, simple NPV and IRR calculations can be performed in Excel using the functions XNPV and XIRR.

Turning this on its head

Let's see we want to get a measure for how well our pension fund is performing over a period of time.  We have records of the contributions we made over time and when we made them, and of course we have the current fund value.  How can we summarise all of that into an effective annual interest rate?

We can model this by treating the whole exercise as a net present value calculation - but one that happened in the past, rather than the future.  The IRR of that calculation is the effective interest rate that our fund has experienced.

Let's get to layout.  Here's the start of our table: There are several things to note about the layout we've chosen:

1. The data is formatted as an Excel Table to allow for easy extension (see TOTWs #163 and #164 for more details)
2. The current value of the fund is input as a negative number, even though it's actually a positive amount - this represents us "closing" the account today in order to make the calculation work
3. The date of the current value is entered using a =TODAY() function so that it automatically updates to the current date
4. The dates are not in order in this screenshot - XIRR will only work if the dates are sorted into order so after our data entry is done we will need to sort the Date column

Once this is all set up, we can then compute our interest rate: Again, note how we have now sorted the dates into order.

Checking our work

If we want, we can now calculate the value of each of these payments if they were invested with the calculated interest rate, to check our working: In the sums shown at the bottom of the table, we see £3,700 (the absolute increase in the value of the investment) and £2.46 (the difference between the calculated result and the actual result; XIRR is an iterative function and won't produce an exact result).

If you want to explore these functions yourself, or use the template I've produced, you can download it from the bottom of this post.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.