One specific feedback I received on my previous article on power of compounding is that the formula for compound rate of return is not obvious in first glance, and I could have done a better job of explaining two components of the formula: principal invested every year, and each of this principal compounding over different period of time. It is a valid point. In my defense, I didn't want the core message of that article to be drowned in the math. The message was that even a
small amount invested
every month at
small rate of return, over a
long period of time, becomes
pretty big; and that my finlosophy can be useful for
passive investor who are
disciplined but not necessarily financial savvy.
But the point is valid: basis of the formula is not obvious. I take it as an opportunity to not only explain the formula, but also explain common financial terms and Excel functions.
Formula for Compound Rate of Return
Say you invest a principal amount
P at the beginning of each year for the next
n years, and you earn
r rate of return. We want to develop the formula for computing the accumulated amount
A at the end of
n years.
Let's see first what happens to a single investment of amount
P. Say you had invested
P at the beginning of the year, so at the end of the first year, the
simple interest on that amount will be
P *
r, and the accumulated amount will be
P +
P *
r, or
P * (1+
r). The idea of
compound interest is that at the end of a year, the interest also earns interest for the next year just like principle. It is like accumulated amount of
P * (1+
r) has become principle for the next year. So at the end of second year, the accumulated amount will become
P * (1+
r) * (1+
r) or
P * (1+
r)
2. And at the end of
nth year, the accumulated amount will become (1+
r)
n.
Now, let's consider what happens when you invest
P every year for
n years. The amount invested at the beginning of first year remains invested for
n years, the amount invested in the second year remain invested for
n-1 years, and so on, till the amount invested in the last year remain invested for one year. So the the total accumulated amount is:
A = P * (1+r)n + P * (1+r)n-1 + ... + P * (1+r)2 + P * (1+r)
= P * (1 + r) * { (1+r)n-1 + (1+r)n-2 + ... + (1+r) + 1 }
= P * (1 + r) * ((1 + r)n - 1) / r
This is the formula I used in the previous article.
Present Value & Future Value
Basic concept is that the value of an amount depends upon when you receive or pay it. A thousand rupees today are worth more than a thousand rupees one year later. In its simplest form, relation between future value (FV) and present value (PV) is like the formula for compound interest:
FV = PV * (1+r)n
Typically a financial transaction has multiple payments or receivables over a period of time. Depending upon specific case, this basic formula can be applied for each payment to arrive at the formula for that specific case (like I did in previous section for a specific investment example). However you don’t need to work out these formulas, and instead the Excel functions of FV and PV suffice most of the common scenarios.
Another common term used is Compound Annual Growth Rate (CAGR), which is nothing but the
r in the formula above:
CAGR = r = (FV / PV)1/n - 1
Since we are at it, let’s also discuss Net Present Value (NPV) and Internal Rate of Return (IRR). In the formulas above for PV and FV, as well as in their Excel functions, principal amount P is constant. For example, amount P is assumed to be invested every year in formula A = P * (1 + r) * ((1 + r)n - 1) / r. However this assumption is not always valid. Say, you are making an investment and expect variable payment amounts in future, and you want to find out whether the invest makes sense. One way to compare is to find net present value of all expected future payments and compare it with the amount you are investing. For future payments (FV1, FV2, ..., FVn) at the end of each year, Net Present Value at r rate of return will be:
| NPV = | n
∑
i = 1 | FVi
——————
(1+r)i |
Again, you don’t need to encode this formula, and instead can use NPV Excel function.
However, most of the time, you know upfront investment and payouts over a period of time, and you need to compute what is the Internal Rate of Return (IRR). To me, IRR seems same as CAGR. I don’t know for sure, but I think term CAGR is typically used when returns are left untouched with the principal to accumulate over a period of time, while IRR is used when there intermediate payouts spread over the course of an investment. If payout amount is at regular interval, you can use IRR Excel function, otherwise you can use XIRR Excel function.
Hopefully this article has explained the math behind the compound rate of return, and various terms commonly used in financial discussions, personal finance planning, comparing asset classes, and evaluating investment strategies. It also introduces useful Excel function to compute these values. Do you use Excel for such computations? Do you have other useful functions that you commonly use? Please let me know through comments.
