Future Value and the Magic of Compound Interest

The Future Value (FV) Formula is a staple of Finance 101 and Retirement Planning that allows us to estimate the value of current assets at a future date.

It is one of the basic formulas that allows us to build more complex personal financial models using elaborate spreadsheets or more sophisticated tools like NewRetirement and projection laboratory.

Future value is a paper-and-pencil math equation, but it’s most easily calculated with a function in Excel or Google Sheets.

Here is the basic example that we will use throughout this post to illustrate future value:

If I had $100,000 in a portfolio today that I expect to earn an average of 7% per year for the next 20 years, it would be worth $403,873.88 in 2043.

That is if I invest that money and never touch it. If you also invested an additional $200 per month at 7% for that same period, the portfolio would be worth $508,059.22.

Excel’s “FV” function works fine, but I prefer to manually build the month-to-month calculation with a table of future values. The month-to-month method is more tangible and shows how money grows every step of the way. I’ll share my spreadsheet below.

Why is future value important?

The Financial Independence Number is a general rule of thumb to help determine your “enough” number.

Future value estimates when you will reach it.

It also provides a framework for modifying your savings plan to reach the number faster (for example, if you invest another $300 per month, you will reach your goal 4 years sooner).

without knowing our enough number is like driving in a tunnel with no light at the end. We run the risk of locking ourselves into a career we don’t enjoy, ignoring the life we ​​prefer to live.

If your only goal is more, you will never get there. — Joshua Becker

Future Value Worksheet

Note: The example in this article is included in the free supplemental futures spreadsheet. download now and experiment with your numbers.

Get the spreadsheet

future value formula

Future value is the basic formula for compound interest. Merriam-Webster defines compound interest as:

Interest calculated on the sum of the original principal and accrued interest.

The future value formula works for specific assets, such as an individual investment (a stock) or broader assets like a retirement portfolio or net worth.

Here is the formula:

FV = PV(1+r)^n

Future Value = Present Value (1+ rate of return) ^ Number of periods (months or years)

Future Value (FV)

Future value is the estimated value of an asset at a future date, given a period of time and the expected return or interest rate.

Present Value (PV)

Consider the PV as your net worth or retirement portfolio for this example.

The FV formula allows us to calculate our net worth at a future date, given a set of variables and assumptions.

Start with present value and add some magic: investment returns (r) and time (n).

Return Rate (r)

The rate of return (r) is the expected average annual return on your investment.

For example, the expected average annual return for a US stock index fund over 20 years is around 9%. But a more conservative 60/40 stock/bond portfolio might be around 6%.

Use your specific anticipated rate of return for your calculation and be safe when underestimating. Consider adding an inflation adjustment (below).

If you are compounding the investment monthly, divide “r” by 12 and multiply the number of periods for “n” by 12.

Number of periods (n), time

How far do you want to project your net worth or investment portfolio?

In our example, we are using 20 years compounded monthly. That makes our n=240 (20 x 12).

Example Entries

We will use the following inputs for our example:

• PV = $100,000
• r = 7% (annual return, or 0.5833% per month)
• n = 240 (months)

Here is the solved mathematical equation:

FV = PV(1+r)^n
FV = 100,000(1+.0058333333)^240
FV = $403,873.88 

In Excel, it looks like this using the built-in FV formula:

I go a step further in my spreadsheet. It does the same calculation but one month at a time.

Using a futures table in Excel makes it easier to visualize and understand. Gets the same value as the formula.

here is the excel formula view to see how I calculate each field.

Incorporation of Payments (Portfolio Contributions)

The basic future value formula excludes contributions or payments (additions to or withdrawals from your portfolio).

In our example, we’ll use the payment as a contribution to a portfolio, adding $200 each month. This value is abbreviated as PMT in finance and the Excel formula. I call him the contributions column in my spreadsheet.

The mathematical formula for this scenario is more complicated. hat tip for calculator soup for help on the formula.

FV = PV(1+r)^n + ((PMT/r) * ((1+i)^n - 1))

The Excel formula and my spreadsheet are much easier to understand. I already added the fields to the example above. Now we just need to enter our value of $200, which fills the table.

Using Goal Seek to achieve savings goals

If you want to reach a specific goal by a certain date, you can use the Excel Find target functionality to calculate how much you need to invest each month to reach your goal.

For example, let’s say we want to save $450,000 by our 65th birthday. Given the above assumptions, how much would we need to save each month to reach that goal?

To do this, scroll down to the goal date. highlight the ending balance value, then go to Data/What-if Analysis/Goal Seeking.

The “Set Cell:” value will default to the highlighted cell. The “To Value:” value should be the target dollar amount. “On cell change:” should point to the Monthly Contributions value field in the inputs section.

When you press OK, the function finds the precise value to reach the target.

In this example, Goal Seek calculates that a monthly contribution of $344.53 would be needed to reach the target amount by the target age/date.

I use this function all the time. Sometimes he tells me that I need to invest more to reach my goals.

Other times it tells me that my goals are not realistic.

Inflation Accounting

Inflation affects our savings by reducing the purchasing power of our savings.

We can account for inflation in a number of ways using spreadsheet projections.

The easiest way is to modify the rate of return to be adjusted for inflation. Estimate the rate of return and reduce it based on the anticipated inflation rate. Inflation has been higher in the past year than it has been in the last 20 years, but economists generally use 2-3% as the long-term inflation rate.

If you expect returns of 7%, you can use 5% to account for inflation.

Another method is to add another column to the future value table that shows the value of $100,000 in today’s dollars compared to the target date.

For example, in 2043, $100,000 will only be worth $67,122 in today’s dollars with a long-term inflation rate of 2%. Therefore, you will need to save more money to achieve the same level of spending.

In 2043, $403,873.88 will be worth just $271,086.24 in today’s dollars.

These are all estimates, of course. But it’s useful to include inflation in the models to encourage us to save extra money so we don’t fall short.

meaningless and important

The Future Value Formula in the context of financial independence gave me the confidence to leave my career behind and become a full-time blogger.

Individual investors, especially younger ones, spend a lot of time thinking about portfolio allocations, investment options, tax-advantaged investments, etc.

These activities are important.

But if the present value of a current portfolio is zero or negligible, then “r” and “n” are meaningless.

I wrote about this recently in the humbledollar.

Early in my career, I spent my spare time at work playing around with spreadsheets, running financial models, and fitting various scenarios. I thought that modeling the next 30 years of my financial life was being smart with my money.

But in hindsight, my time would have been better spent networking, looking for the next promotion, or building a side business to grow my assets because earning 7% vs. 9% doesn’t move the needle much when there are only a few thousand. dollars to invest.

Higher returns are important when savings are large. That only happens once many of us are older, and that’s when we need to reduce portfolio risk and accept lower returns.

The magic of compound interest

Time is the most powerful factor when it comes to building wealth.

Look at the 20-year mark below. You can see how growth is starting to pay off, even at a conservative 6% rate of return.

But when you hit the 30 and 40 year marks, the total interest is separated from the total principal.

This is the magic of compound interest.

Unfortunately, it takes a long time and a lot of money to get up and running. We can create spreadsheets to project how saving and investing will eventually lead to significant wealth over time.

But we can’t make time go faster, and we can’t invest time to save more for our youth.

The best way to move the wealth needle today is to increase the gap between what we earn and what we spend, freeing up more money to invest, until there is enough.

Wizard photo via DepositPhotos used under license.

craig stephens

Craig is a former IT professional who left his 20-year career to pursue finance writing full-time. A DIY investor since 1995, he started Retire Before Dad in 2013 as a creative outlet to share his investment portfolios. Craig studied Finance at Michigan State University and lives in Northern Virginia with his wife and their three children. Read more here.