An easier way to calculate compound interest in Excel

 Do you want to calculate how much your current savings might be worth in 10 years or in 30 years? It’s an easy calculation that doesn’t require any specific function in Excel. Some simple multiplication, addition and exponentiation is all you need.

Still, the answer you will most likely get if you search for “compound interest in Excel” on Google is the FV function. The FV function is difficult to use, and it actually calculates compound interest based on a monthly rate rather than a yearly rate, which gives a slightly different result.

If you have $1,000, the yearly return is 8% and you let it compound for 10 years, you would calculate it like this:

Of course, in Excel we don’t want to hardcode anything in the formula, so we’ll put the numbers in separate cells. This is how you can set it up:


Now you can change any of the variables in C2, C3 and C4, and the Future value will update instantly.

If you want to use the FV function instead, this is how it’s done. As mentioned above, the result is a little higher because it calculates the compound interest for every month


Popular posts from this blog

SUM across multiple sheets in Excel

Image
If you have a report in Excel with multiple identical sheets, for example one sheet per month, that you want to add together in a separate sheet, there are a few different ways to do it. The most common way is to simply type an equal sign, click on B2 in the ‘Jan’ sheet, type a plus sign, click on B2 in the ‘Feb’ sheet, and so on. Then copy down and across. This takes a long time, and it’s easy to make mistakes when you write formulas like that. A better way to do it is to use this formula: =SUM(Jan:Dec!B2) (and copy down and across) With this formula, Excel looks at all the tabs from Jan through Dec and picks up the value in B2 in all of them. Note that you only need an exclamation mark after the second sheet reference. You have to be careful when you use this approach, though. If you insert or move another sheet between the ones you want to refer to in the formula, Excel assumes that you want to include that too. On the picture below you can see what might happen if you move a phone ...
Read more