Mike Perris



home >> excel >> inflation-o-meter

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:

a spreadsheet showing a value after inflation

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:

a chart showing one hundred pounds decreasing by inflation

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:

a chart of Venezuela's inflation

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 sheet's input fields

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:

table of values

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:

a picture of the whole spreadsheet

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..

chart tools on Ribbon

..select Picture from 'shape fill':

shape-fill selection

.. and then select your flag's image file.

OTHER EXCEL ITEMS

Importing data from an Access Database into Microsoft Excel

Keeping track of your credit card and bank account balances with Excel

How to write a simple VBA macro that will read and write values to and from Excel cells

Excel's "What-if Analysis" Goal Seeker

Importing data from a text file into Microsoft Excel

Calculating the difference between two dates

How to add a button to an Excel worksheet, and link the button to a VBA macro

An Excel macro to show the effect of inflation on spending-power

An Excel spreadsheet that calculates how much you've spent on cigarettes

Password-protecting and encrypting Excel spreadsheets

How to record and modify an Excel macro

Naming cells and ranges in Excel

How to adjust the widths of columns in an Excel spreadsheet

Filtering data with Excel 2007/2010

Inverting Numbers in a range of cells

Getting a summary of numeric data in a range with Excel

Website design Copyright (C) 2007-2014 Mike Perris.