Spreadsheets

Inflation-adjusted future cost sheet: what a price may become

Project a future cost with inflation in Excel using FV or a power formula, shown for a college fee as a what-if across a range of rates.

Editorial illustration for Inflation-adjusted future cost sheet: what a price may become

To estimate what a price may become, multiply today's cost by (1 + inflation) for each year: =Cost*(1+Rate)^Years, or the equivalent =FV(Rate, Years, 0, -Cost). A fee of ₹2,00,000 a year today, at an assumed 6% a year for 10 years, becomes about ₹3,58,170. The 6% is your assumption, not a forecast, so run a range.

The two formulas that do the same job

  • Power formula: =B1*(1+B2)^B3 with today's cost in B1, assumed yearly inflation in B2 and years in B3.
  • FV function: =FV(B2, B3, 0, -B1). The payment is 0, and the present value goes in with a minus sign so the answer is positive.
  • Both give the same figure in Excel and Google Sheets; the power version is easier to explain to someone else.

Setting up a college fee what-if

  1. A1 Fee per year today: 200000, in B1.
  2. A2 Years until the first payment: 10, in B2.
  3. A3 Assumed inflation: type a rate in B3. Do not rely on a single figure.
  4. A4 Fee then: =B1*(1+B3)^B2.

Then copy the row three times with a low, middle and high rate. A table of three scenarios is far more useful than one number:

Assumed yearly riseFee after 10 yearsExtra over today's fee
4%₹2,96,049₹96,049
6%₹3,58,170₹1,58,170
8%₹4,31,785₹2,31,785

What about four years of fees, not one?

If the fee keeps rising during the course, add one more row per year. At an assumed 6%, the fees in years 10, 11, 12 and 13 from now add up to about ₹15,66,854, against ₹8,00,000 if you paid today's amount four times. In a sheet put the year numbers 10 to 13 in A5:A8, the fee in B5 with =$B$1*(1+$B$3)^A5, copy down, and sum.

Can I do the reverse and see today's value of a future sum?

Yes, divide instead of multiply: =Future/(1+Rate)^Years. A sum of ₹10,00,000 ten years from now, at an assumed 6%, buys about what ₹5,58,395 buys today. This helps when someone quotes you a large future figure, such as a maturity amount, and you want a feel for what it is worth in today's money.

How much do I need to save to reach the inflated figure?

Once you have the future cost, feed it into a monthly saving formula as the target: =-PMT(return/12, months, 0, future cost). The inflation sheet gives the target and the PMT formula gives the monthly amount. Keep the two assumptions, inflation and return, in separate labelled cells, because they are separate guesses and each deserves its own range.

Where can I get a rate for my own household?

Use your own history. If the yearly fee was ₹1,60,000 four years ago and is ₹2,00,000 now, the steady yearly rise is =(200000/160000)^(1/4)-1, about 5.7%. That is the CAGR of your own bill, and it is a better starting point than a figure from a news report, because it covers the school or college you actually deal with. Still run a range around it, since a fee that rose slowly can jump when a new course year begins.

Checks before you use the figure

  • The rate is a percentage and years is a plain number.
  • Years counts from today to the date of the payment.
  • You have tried at least a low and a high rate.
  • The cost today is the real current figure, taken from a prospectus, a fee letter or a receipt, not from memory.

For a current figure, the quickest source is your own spending record. If the school or college fee already went through your bank account, SpendixAI can pick it up from an imported statement and keep the receipt with the transaction, so the base number in B1 comes from a record.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports