How to Do Continuous Compounding Annuity in Excel
Ever wanted to illustrate exactly how powerful compound interest can be? Wanted to have an Excel function to do it for you? This post by contributor Andy Shuler reveals the continuous compound interest formula and how a function built into Excel will calculate it for you.
"Compound Interest is the eighth wonder of the world. He who understands it earns it…he who doesn't, pays it"
– Albert Einstein
This is truly one of my favorite quotes ever. At first, I really didn't understand it. "yeah, you earn interest on your money…. who cares? That happens in a bank account too". Boy, was I wrong.
What's the difference between a quote and a saying? Looking at face value, not much really seems different – they're both just a couple sentences, or maybe even just a few words.
But there is one key difference – inspiration! Quotes INSPIRE you to do something…lose weight, save more, be a better person, anything at all!
That's why I love this quote from Einstein.
This quote is truly what inspired me to start investing in the stock market. The concept of compounding interest is pretty simple really – it's simply earning interest on your principal investment, but then continuing to earn interest on top of that principal and the previous interest that you've already earned.
For example, if you earn 6% on $1000, you will then have $1060. But, the next time you earn 6% interest, it will now be on $1060 instead of $1000, and that then will result in a new value of $1123.60.
Not a huge difference for this time, but it was $3.60 more than that $60 that your 6% interest on $1000 generated. To explain this a little bit better, I'll put it into a real-life scenario that I went through with my wife less than a week ago…
Convincing the Wife about Compound Interest
We're finally gotten to the point that we feel like we have a sufficient emergency fund, so now the thought process is, what do we do with it?
Previously, it had just been sitting in my bank account earning a whopping .01% interest. Yes, I said .01%.
I was shocked when I saw it at first, but I started doing more research and realized it was pretty common unfortunately.
I then started looking into some high interest savings accounts, and from hearing some recommendations of Ally from Andrew on the podcast, I decided that I thought that was the best way to go about holding our emergency fund.
Now, my wife is typically a very conservative person when it comes to money, as am I, but she gets skeptical of doing things that might seem out of the ordinary – like investing in an online-only bank such as Ally, but I was able to convince her to do it by showing her some Cold. Hard. Facts.
The truth is, if you're just holding onto cash in a normal bank account and not putting it in a high interest bank, you're essentially volunteering to lose money. As long as you're FDIC insured, the risk is 0.
So, the question is, how did I convince her?
It was easy really – I showed her the value of the compounding interest. But to share the results without first understanding the math, is like giving a man a fish instead of teaching him how to fish! So, here we go – welcome to the Continuous Compound Interest Formula:
Source: https://www.askforprogram.in/2018/07/program-to-calculate-compound-interest-in-kotlin.html
This formula might seem like a bunch of nonsense, but all you really need to know is your starting investment, the rate of return, amount of years it will grow, and then the frequency of the compounding each year.
For instance, below is the situation that I explained to my wife. If we took $10,000 and kept it in our current bank account earning .01% interest, for 30 years, and each month it was compounded, we would then have earned $30.04 over our initial investment.
BUT! If we put it in the Ally account, we would have earned $9,923.99 over our initial $10,000. I know that less than doubling your money in 30 years isn't exactly a staggering outcome, but that's not the point – the point is that by having that money compounding month after month on itself, we're realizing much greater returns than if we hadn't allowed it to do so.
Ok, I know what you're saying – "Andy, are you an employee of Ally? I opened this article to learn about the compound interest formula rather than savings account rates".
Well – lucky for us, Excel has an awesome function that allows you to do this on your own. And it's rather simple – you don't have to be a numbers nerd (like me) to run some situations! Look out below!
=FV(rate, nper, pmt, [pv], [type])
Simple, right? Let me explain:
- FV = Future Value
- Rate = Interest rate per period of compounding
- NPER = total number of payment periods
- PMT = The payment made each period
- PV = this is optional – but it is the present value of future payments.
- Type = this is also optional. If you select 0, it's that the payments are at the beginning of the period; 1 is that they're at the end. If you leave it blank, it's 0.
So, let's look at the picture below:
- FV = B5
- Rate = B2/B4. What this is doing is I'm putting the APR in cell B2 and then the compound frequency (once/month) to get a monthly interest rate. (.023/12).
- NPER = B3*B4. This then gives me the total number of payment periods (12 months * 30 Years).
- PMT = 0. I'm not adding any additional money each period.
- PV = -B1. This is just stating the investment of $10,000.
- Type = left blank. It will compound at the start of each month.
This might still seem confusing, but I guarantee that if you put this in excel in the format that I've laid out, you will be able to walk through step by step and get yourself a number. "What if I want to contribute money each month to this? Say, like $50/month…EASY! All you need to do is change the PMT to a negative of the contribution. For instance, in the below example, I'm putting -50 to show a contribution of $50/month.
You might be wondering why these numbers are negative, but it's because this function really is used as a payoff calculator rather than building interest, but they're the inverse of each other, so they work the exact same.
Now, I will admit these are somewhat boring examples, but, let's take a look at what if we did this in the stock market!
I have two examples below.
Both include making monthly contributions of $150, but one is if we realized an annual return of 6%, which is what I like to use as a conservative estimate when planning my future, and the other is looking at a 20-year average S&P 500 return of 9.95%, shown below:
The importance of this article is to get you excited about compound interest, and to teach you the ability to understand the continuous compound interest formula.
The formula truly is fairly simple to understand, and in my case, once I got it, I GOT IT. Like it just suddenly clicked.
This same concept can be used for DRIP, which Andrew has talked about many times. If you know your initial investment, interest rate, time frame, compounding frequency and contributions, then you are so close to understanding where you can be and helping you further prepare for your financial future… all it takes is a little bit of comprehension and willingness to open up that Excel spreadsheet!
Source: https://einvestingforbeginners.com/continuous-compound-interest-formula-ashul/