Do you want to know how to calculate the future value of money with inflation in MS Excel? Want to calculate an inflation-adjusted return from your investment?

You’re in the right place. In this article we will demonstrate how you can calculate the future value with inflation in Excel with elaborate explanations.

**Table of Contents**Expand

**What Is Inflation and How It Affects Our Lives?**

Before going into the calculations, I will introduce you to several terms like:

- Inflation
- Future value
- Nominal Interest Rate
- Real Rate of Return

The prices of things go up and this is called inflation. Deflation is the antonym of inflation. The prices of things go down in the deflation period.

In the following image, we are seeing the inflation and deflation picture of the USA for the last around 100 years.

From the year 1920 to 1940 (20 years), deflation occurred more than inflation. From there, inflation dominated. So, most of the time, we see the prices of things are going up.

Suppose, you have $100 cash today. And the projected inflation for the next 1 year is 4%. If you still hold the cash ($100), after 1 year, your purchase power will be lower ($96) with that $100 cash.

If we see the general pricing of things, the $100 product will be priced now at $104. So, with your holding of $100 cash, you cannot buy the same product after 1 year that you could buy 1 year before.

So, inflation devalues the cash and increases the price of the product.

This is why holding cash is a bad idea in the investment world.

## Future Value of Money

The future value of money can be thought of in two ways:

- The future purchase power of your money. With inflation, the same amount of money will lose its value in the future.
- Return of your money when compounded with annual percentage return. If you invest your money with a fixed annual return, we can calculate the future value of your money with this formula: FV = PV(1+r)^n. Here, FV is the future value, PV is the present value, r is the annual return, and n is the number of years. If you deposit a small amount of money every month, your future value can be calculated using Excel’s FV function. We shall discuss both methods in this tutorial.

## Nominal Interest Rate

If you deposit your money with a bank, the bank provides you interest in your deposits. The rate, the bank provides your interest is called the Nominal Interest Rate. For example, if your bank provides 6% per year, then the nominal interest rate is 6%.

## Real Rate of Return

You can use this simplified formula to calculate the real rate of return:

**Nominal Interest Rate – Inflation Rate = Real Rate of Return**

To get a Real Rate of Return, you have to deduct the Inflation Rate from the Nominal Interest Rate (or your yearly return).

But the accurate formula is shown below:

Let me explain this concept with an example. Suppose, you have invested $1000 in the money market and a got 5% return from there. The inflation rate is 3% for this period.

So, your total money is now: $1000 + $1000 x 5% = $1050.

But do your purchase power the same as before? Say, you could buy a product for $1000, now its price is $1030 (with 3% inflation).

How many of these products you can buy today?

$1050/$1030 = 1.019417476.

So, your real purchase power has increased from 1 to 1.019417476.

In % it is: ((1.019417476 – 1)/1)*100% = 0.019417476*100% = 1.9417%

We can reach this percentage also using this formula:

(1.05/1.03)-1 = 1.019417 – 1 = 0.019417 * 100% = 1.9417%.

## Calculating Future Value with Inflation in Excel: 2 Suitable Example

We shall calculate the future value with inflation in more than one way:

### Example 1: Start with an Initial Investment and No Recurring Deposits

You have some investible money, and you want to invest the money with the following details:

- Investible money: $10,000
- Annual return from investment (fixed): 8.5% per year
- Inflation rate (approx.) over the investment time: 3.5%
- Investment period: 10 years
- What will be your inflation-adjusted return?

**Steps**

- We will input the following information in the range of cell
**C4:C7**. - This is the return you will get (following image).

- Don’t misunderstand one thing. In real life, you will get actually a return of the amount of $22,609.83 with the following formula (inflation is zero):

- But the purchasing power of your value will be: $16,288.95
- You will also come out with the same value if you use the following universal formula. For the value of r, you will use the real rate of return (
**real rate of return = annual return – inflation rate**).

**Read More: **How to Apply Future Value of an Annuity Formula in Excel

### Example 2: Start with An Initial Investment and Make Regular Deposits

In the next step, we are going to implement a method incorporated with a regular deposit. Because of the deposit, the future value calculation will be slightly modified compared to the previous method.

In this example, I am showing a scenario with the following details:

- Your initial investment: $50,000
- You’re paying a regular monthly deposit: $2500
- Interest rate (yearly): 8.5%
- Inflation rate (yearly): 3%
- Payment Frequency/Year: 12
- Total Time (Years): 10
- Payment per Period, pmt: $2,500.00
- Present Value, PV: 50000
- Payment is done at the beginning of the period

**Steps:**

- To begin, we need to calculate the investment per period. For this select cell
**C7**and enter the following formula:

`=(C5-C6)/C7`

- Observe that in cell
**C7**, we have calculated the Interest per Period by subtracting the Yearly Inflation Rate from the Yearly Interest Rate and then dividing the value by the Number of Payments per Year. - The following image shows the output.

- Then we enter the total time period of depositing money in cell
**C9**. - Select cell
**C10**and enter the following formula:

`=C9*C7`

- Then enter the Payment per period that you are going to use in the cell
**C11**. - Also, enter the present value of the money or one-time deposit in cell
**C12**. - After then enter
**1**in cell**C13**. Which denotes the payment due at the beginning of the payment period. - Finally, enter the following formula in cell
**C15**.

`=FV(C8,C10,C11,C12,C13)`

- Then select cell
**C18**and enter the following formula:

`=-C12+(-C11)*C10`

- Then select cell
**C19**and enter the following formula:

`=C15`

- Then enter the following formula in cell
**C20:**

`=C19-C18`

- After entering the formula, we get the Future value of the deposit made over the course of the payment period.

- Observe that in cell
**C7**, we have calculated the Interest per Period by subtracting the Yearly Inflation Rate from the Yearly Interest Rate and then dividing the value by the Number of Payments per Year. - What if the Yearly Return is lower than the Inflation Rate?
- See the below image. When the annual return is lower than the inflation rate, you will lose money.
- And that is the reason why it is showing in red color.

- This is how we calculate the future value of deposited money adjusted with inflation in Excel.

**Read More: **How to Calculate Future Value of Growing Annuity in Excel

**Download Practice Workbook**

Download this practice workbook below.

## Conclusion

Calculating the future value of your investment is important. But if you are calculating it without considering the Inflation Rate, you’re neglecting a very important thing. If you earn 8% annually from your investment in an economy where the inflation rate is 10%, you’re actually losing the value of your money.

## Related Articles

- How to Apply Present Value of Annuity Formula in Excel
- How to Calculate Present Value of Uneven Cash Flows in Excel
- How to Calculate Future Value of Uneven Cash Flows in Excel
- How to Calculate Present Value of Future Cash Flows in Excel
- How to Calculate Future Value in Excel with Different Payments
- How to Calculate Present Value of Lump Sum in Excel
- How to Make a Time Value of Money Calculator in Excel
- How to Calculate Present Value in Excel with Different Payments
- Calculate NPV for Monthly Cash Flows with Formula in Excel

**<< Go Back to ****Time Value Of Money In Excel ****|** **Excel for Finance**** | ****Learn Excel**

Hi, I like your website it is very easy to understand (for a simple mind like mine).

There is one example that nobody seems to show (I have been surfing to web for a while trying to find a solution to this problem), may be you might be able to help me please.

Here is the problem as an example;

1. I receive net $2000 p.m. from rent.

2. I invest this in an investment account that gives me 10% interest p.a.

3. Every year the rental is increase of 2.5% per year

4. Using excel FV calc., FV=(rate=2.083%,nper=120,PMT=?,PV=0,1)

5. The PMT has to be indexed somehow to take into consideration the 2.5% increase in yearly deposits in-line with inflation.

6. I know the answer at the end should be $453,624.65 (I printed something off a while back but I’ve lost the website) but I cannot come anywhere near this figure.

So, the question is how do I configure this FV formula?

If you can help me, I would be extremely grateful. Thank you in advance.

if i deposit 1000 each year to a pension bank for 41 years with an inflation of 1.40% and an interest of 4.40%, then how am i able to get the answer in one FV formula. I’ve already made a table for each year but am unable to get the answer in one formula.

Hi, ADRIAAN!

Thank you for your query.

The FV formula that you need for your solution will be like this:

`=FV([(Interest Rate-Inflation Rate)/Frequency of Payment Per Year],[Frequency of Payment Per Year * Total Years],[-Payment Per Period], Present Value,0)`

So, with your given values, the formula would be:

`=FV(3%,41,-1000,0,0)`

Regards,

Tanjim Reza

if i deposit 1000 each year to a pension bank for 41 years with an inflation of 1.40% and an interest of 4.40%, then how am i able to get the answer in one FV formula. I’ve already made a table for each year but am unable to get the answer in one formula.

Hi, A!

Thank you for your query.

The FV formula that you need for your solution will be like this:

`=FV([(Interest Rate-Inflation Rate)/Frequency of Payment Per Year],[Frequency of Payment Per Year * Total Years],[-Payment Per Period], Present Value,0)`

So, with your given values, the formula would be:

`=FV(3%,41,-1000,0,0)`

Regards,

Tanjim Reza

Hi, thank you for this information

How can you incorporate ‘fees’ (such as pension fund fees e.g per annum fee of 0.7%) into the rate function?

Thanks,

Jack

Hi

JackThank you for your query.

It would be very kind of you if explain your problem in detail. Perhaps, you can send us your problem at

[email protected]to get better assistance.From your query, it seems like you want to calculate in Excel using

the RATE functionand insert a certain fee as a percentage in the function.The RATE functionreturns the interest rate per period of an annuity. The syntax ofthe RATE functionis as follows.RATE(nper, pmt, pv, [fv], [type], [guess])Where,

●

Nper:Required. The total number of payment periods in an annuity.●

Pmt:Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.●

Pv:Required. The present value — the total amount that a series of future payments is worth now.●

Fv:Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument.●

Type:Optional. The number 0 or 1 and indicates when payments are due.●

Guess:Optional.As you can see, you cannot directly incorporate any fee as a percentage in

the RATE function. However, you can indirectly use a percentage in a formula that hasthe RATE function.I am going to discuss

2such examples.Example 1:Let’s assume that you have borrowed

$8000for4years. The monthly payment is$200. Now, if you usethe RATE functionhere, you will get a9.24%annual loan rate. But in practice, you may need to adjust your loan. So in that case, you can incorporate a percentage.The formula in

C8is`=RATE(C3*12, (-1)*C4, C5)*12-C7`

In the image above, we have subtracted a percentage (

0.07%, our adjusting factor inC7) from the annual rate of the loan. In a similar fashion, you can manipulate your dataset the way you want.Example 2:Suppose you got a loan of

20%on the value of your certain asset. Now, you can incorporate this20%in the RATE function. The formula inC7is=RATE(C3*12, (-1)*C4, C5*20%)*12Here,

C5*20%represents the loan amount ($8000).This is how you can incorporate any value indirectly in

the RATE function. To learn more, have a look at this article.How to Use RATE Function in Excel (3 Examples)