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:
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.
Previous post - Revisiting roundingTOTW index Next post - Rounding with totals
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.