An Excel macro to show the effect of inflation on spending-power
The simple solution
Calculating the affect of inflation on a sum of money is very simple in Excel - we can use Excel's FV function in a short formula to calculate the final value, if we give it the present value, inflation rate and number of years:
You can download a copy of that spreadsheet from here, but if you create a version for yourself from scratch, make sure you format cell B3 as 'percent' before you put a value in it.
The more fun solution
As neat as that little spreadsheet is, I wanted one that would calculate the yearly affect of inflation, with a helpful graph to illustrate the financial obliteration. Much like this:
As financial obliteration goes, that doesn't look too bad - so I googled "country with highest inflation rate" and got a winner.. Venezuela, at 28.2% (2010). Venezuela's graph looks like this:
Creating an Excel VBA macro to produce that chart's data is very simple, once you know how, because you're just deducting n% from the value, x number of times (where x is the number of years)
Whilst you can download the full Inflation-O-Meter spreadsheet here, it will make more sense to take a minute to read the overview, first.
The spreadsheet has three cells into which you type the values for initial value (the savings that we're going to deflate), the inflation rate (as a percentage) and the number of years:
The 'savings' (the initial value) go into cell C2, which is labelled 'savings' (you can't see it here, but this is how it's referenced in the VBA macro - an article on labelling cells is here), C3 holds the inflation rate ('inflation_rate') and C4 is has the number of years (labelled 'years').
Cell C7 contains a formula that calculates the percentage difference between initial and final values, '=((savings-final_value)/savings)', and C8 (labelled 'final_value') holds the final value. These last two figures are for informational purposes only, and are not relevant to the graph.
You can also see the 'Calculate' button form control, which calls the VBA macro.
Below all of this, we have a two-column table called 'balance' that holds the ever-deflating savings:
That table is controlled entirely by the VBA macro, and the table grows as required, depending on how many years' worth of inflation the VBA is calculating.
Then we have the chart itself, called 'chrtinflation'. The whole thing, then, looks like this:
If you want to know how the VBA macro works, it's well-annotated and the whole VBA macro is here. It won't actually put a Venezuelan flag on your charts, though; for that, you first need to find an image of a Venezuelan flag (or whatever other graphic you want to use as a background image), then left-click the chart area (the bit around the edge), click 'Chart Tools->Format' on the Ribbon..
..select Picture from 'shape fill':
.. and then select your flag's image file.