0

I have an excel sheet like below -

enter image description here

In one cell (B1), I would mention the principal value, PV (for which I want to know future value at different point of time). And inflation rate, r, in another cell (B2) assuming it is going to be 5% every year. And in one column listing number of months from today (A6 to A29) and in another column against each month I will have to calculate the future value of my PV (B6 to B29).

So what should be the formula that I should use in the columns (B6 to B29)?

Improve this question

1 Answer 1

Reset to default
1

If the annual inflation rate is 5%, then after a year the value of the money needs to be divided by 1.05.

But you want the monthly rate, which is 1.05 to the power 1/12. This works out about 1.0040741. If you want a more precise figure, use your spreadsheet program to calculate it as (1 + (B2 / 100)) to the power 1/12.

So there's two ways to do the calculation...

Method 1

Cell B6 is B1 / 1.0040741
Cell B7 is B6 / 1.0040741
Cell B8 is B7 / 1.0040741
Cell B9 is B8 / 1.0040741

and so on.

Method 2

Cell B6 is B1 / 1.0040741
Cell B7 is B1 / (1.0040741 squared)
Cell B8 is B1 / (1.0040741 cubed)
Cell B9 is B1 / (1.0040741 to the power 4)

and so on.

Improve this answer
2
  • Thank you. But I saw considering the inflation for each month was making a significant different in longer run like 10 years (assuming it is due to compounding effect). So finally used it as annual inflation rate only on the lines of first statement you mentioned and mix of method 1.
    – Darshan L
    Commented Aug 6, 2021 at 14:38
  • rl360 here is a inflation impact calculator to check your work
    – rhavelka
    Commented Aug 6, 2021 at 15:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .