I have $10,000 to invest over the next six years. I have a chance to buy equipment that will save me $2000 a year for five years and after that I could sell it for $3000. Looks like $3000 ahead! But before I jump into this, I should compare it with other investment opportunities. Maybe I have a chance to invest it at 6% for the six years, and I figure the risks are comparable for the two choices.
Here’s a spreadsheet to look at investments like this. Fill in the interest rate you’re using for comparison and the amount you’re investing. Then fill in how much you will get each of years 1 through 6. (If you have to put in more money any year, enter that as a negative.) The cumulative column shows how much you’ll have total (positive in black or negative in red) at the end of each year, taking into account what you’ll give up by not investing the money.
The Cumulative column shows how much I’ll be ahead (black) or behind (red) relative to investing the money at 6%. I’m in the hole until Year 6, but I end up $765.45 ahead. All else being equal, this is a better than the 6% investment.
But how much better is it? Try various rates. Try 7%. The bottom line is still positive, so this is better than a 7% investment. Try 8%. Negative. Not as good. Now I can use Goal Seek to find the rate that gives comparable results. I tell Goal Seek to make the bottom line exactly zero by changing the rate. Here’s what I get.
The answer is that this investment is exactly as good as investing the money at 7.6%.
Here’s another way I could use this spreadsheet and Goal Seek. I’m looking for a 10% return on my investment since I think it’s risky. How much should I offer to pay for the equipment?
If I could buy the equipment for $9,275, I could make 10% on the investment.
The value of 7.6% that we got is called the internal rate of return, often abbreviated as IRR. This is a useful measure of how good the investment is. Take, for example, a similar investment that pays more each year, but has no salvage value at the end, as represented in this spreadsheet.
The internal rate of return here is 7.9%, a better investment.
Now let’s compare it to a completely different type of investment. Suppose someone borrows $10,000 and in six years (without any interest payments) pays you $15,000. That’s more than you’d get back from the other investments. But if we find the internal rate of return, it’s only 7.0%. Money sooner is more valuable than money later.
If you’re interested in how you’d build a spreadsheet like this, it’s the same techniques used in others—(1) create names for the inputs, (2) click on cells to get Excel to write a formula for you using only the current and preceding line and named inputs, and (3) AutoFill.
Take a look at the key formula for the cumulative in Year 1.
You’ll get this if you click on the cells of interest while thinking through what you’d compute. Start with the cumulative you had the year before (the $10,000 in the cell just above, C5) and add in a year of lost interest on it (Rate*C5). On top of that, you get back the Amount in/out, so add in the $2000 in the cell to the left (B6). Click on the cells to build this formula rather than worrying about cell numbers. Once again, Excel formulas are easier to write than to read. You may notice that this formula is exactly the same as the one we used to look at investment growth. It works just fine even if some of the numbers are negative. This formula uses only the current and previous rows and named inputs, so we can AutoFill it down to complete the spreadsheet.
By the way, Excel has a function for calculating the internal rate of return directly, IRR. In this spreadsheet, =IRR(B5:B11) gives you the same 7.6% we got before.