How to calculate future value in Excel? Let’s play around with various future value examples.
⏱️TIMESTAMPS⏱️
00:00 How to calculate future value
00:07 Future value with compound returns
03:37 Future value with simple returns
04:27 Future value compound varying returns
06:05 Future value with multiple cash flows
08:05 Excel PMT function
09:20 Multiple cash flows of different value
First future value scenario: we invest an amount of $1000 today, and want to calculate the future value in year 4, using a 20% expected return (or interest rate, whatever your case situation is). There are three ways to calculate the future value in Excel. The first way is to construct a formula and calculate the future value year by year. The formula for future value in year 1 is B3 times (open parentheses) 1 + B4 (close parentheses). We lock the column reference by putting a $ sign in front of B4. The outcome of the calculation is $1200. We can copy the formula from C3 to D3. $1200 multiplied by 1.2 equals $1440. Due to the compounding effect, we get growth on top of growth. The absolute amount of the annual return increases. Copy the formula from D3 to E3 and F3. Here’s your answer: $1000 today, at 20% annual compound return, grows to $2074 by year 4.
The second way to get to that same #futurevalue is by using a formula that skips the intermediate steps, and goes straight from start point to end point. F4 equals B3 times (open parentheses) 1 + B4 (close parentheses) to the power 4.
The third way to get to that same number uses the Future Value function in Excel. Go to the insert function dialog box on the home tab of your #excel sheet, type “future value”, click Go, and select FV. As the description suggests, this is normally used to return the future value of an investment based on periodic, constant payments and a constant interest rate. But we can also use it for a lump sum situation like we have here. Click OK and you see the function arguments showing up. First, we need to specify the rate to be used. Let’s have this reference cell B4 rather than typing a number in here. Then, we specify the number of periods involved: 4 in our case. We leave the next row empty, as there is no recurring payment. In the fourth row, we reference cell B3 for the initial lump sum amount that we invest. Then we hit OK, and put a minus sign in front of the formula. The reason for this minus sign is that the FV formula assumes we are looking at cash outflows and cash inflows for loans or investments, while we are in this scenario just looking at how the future value of an initial amount of $1000 grows over time. The cool thing of using cell references in each of the formulas is that we can vary the amount of the initial investment in B3, or the rate of return in B4, and have an automatic calculation of the future values based on the new inputs. As you see in cells F3, F4 and F5, each of the three methods gives you the same result. Use whatever feels most logical or comfortable to you.
Let’s go to a future value scenario with multiple amounts: we invest $1000 each year, at 20% compound returns, and want to know what the total future value is of the sum of these amounts. In order to do this using our first method, we need to “layer” the investments: treat each of the $1000 investments as a separate line item and calculate its individual future value. In other words, investment 1, 2, 3, 4, and the sum of these is the value of the portfolio. In cell C22, we type equals B22 times (open parentheses) 1 + $B$27 (close parentheses). With $ signs in front of column B as well as in front of row 27, we make this an absolute reference to cell B27. We can now copy the formula for each of the cells of investments 1, 2, 3 and 4. Then we sum the future value of each of the line items, to a total of $6442. Now that’s a LONG way of doing the future value calculation. Let’s use the magic of the future value function to make that a lot easier. Insert function. FV. Link the rate to cell B27. Number of periods is 4. The payment is a constant amount of $1000 per year, which is in cell B22. We don’t have any lump sum. And in the last row, “type”, we put a 1 as the payment is made at the beginning of the period. Click OK, and then put a minus in front of the formula. Same outcome of $6442, but much faster.
Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!
Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through https://www.youtube.com/channel/UCQQJnyU8fALcOqqpyyIN4sg/join