Excel help please - inflation

gallygirl

I am trying to do a simple spreadsheet to show compounded interest growth over 30 years and also what the real value of the final sum will be in today's terms, using flexible inflation rates. I've done the first part to an accuracy I'm happy with, but can't do the second, and can't get any search results to work. I could look the results up every time but want to build it in as I'm doing it for someone else to use.

An example:

After 30 years savings the total including compounded interest @5% is £50,000. What is that worth in today's money if there was an average inflation rate of 3%* over the period?

* to be variable.

Thanks.

bod1467

A quick search suggests the basic formula would be:

=PV*(1+R)^N

where PV is present value, R is the interest rate, and N is the number of investment periods.

I've done a quick check and the answer I get is £242.7262 for an initial value of £100, 3% interest and 30 investment periods (years).

If A1=£100, B1=3% and C1=30, then the formula below in D1 gives the above result:

=A1*(1+B1)^C1

Le_Kirk

Not sure you can do it if it is variable. I have always used FV (also check out PV), which is the formula for compound interest and paying back a loan or saving. It is:-
=FV(rate,nper,pmt,[pv],[type]) explained here: -
The FV function syntax has the following arguments:

• Rate Required. The interest rate per period.
• Nper Required. The total number of payment periods in an annuity.
• Pmt Required. The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
• Pv Optional. The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
• Type Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

You might have to play around to get what you want. I believe there are also on-line tools for calculating value based on historic interest rates.

tacpot12

Bod1467 has given you a hint.

If X is worth £50,000 after 30 years of growing at 5%, then X was £11,568.87. £11,568.87 would be worth (1+3%)^30 more in today's money. i.e. £28,080.69.

Chino

gallygirl wrote: »

What is that worth in today's money if there was an average inflation rate of 3%* over the period?

PV = FV / (1 + R/100)^N

FV = £50,000
R = 3%
N = 30

PV = £50,000 / (1 + 3/100)^30 = £50,000 / 1.03^30 = £50,000 / 2.4273 = £20,600 (rounded)

gallygirl

Thanks for all this, I've had a play and got Chino's formula to work