Evaluating Your Investing Plan
Sometimes it’s hard to know if you’re heading in the right direction.If you’re navigating your way through the physical world, modern technology offers lots of tools to help, and many of them fit in the palm of your hand.These tools are great, but you have to know how to use them. Some of my relatives still give directions with pen and paper!In investing, technology also offers many tools to help you determine if you’re on course.For most investors, the destination is retirement. Here’s a tool to help you see if your current investing plan is heading in the right direction.Excel has a really useful formula called future value. As investors, this allows you to estimate the future value of your portfolio, by inputting your current account value, current contribution rate, expected future rate of return, and how long you plan on working until you retire.Calculating Future Value Using the Excel FormulaLet’s calculate the future value of a portfolio for an investor who:
- Currently has $100,000 invested (Present Value, or PV);
- Expects to earn 5% on their investments annually (Rate of return, or rate);
- Is contributing an additional $6,000 per year to their accounts (Payment, or Pmt);
- And plans on working for another 10 years (Period, or Nper).
- If you are an Excel novice, don’t worry! We’re going to go step by step.
1. Open up a new workbook in Excel.2. Enter the following words and values in Columns A-B, rows 1-5.
3. In cell B5, type: =FV(You should see the future value formula pop up like this:
That formula is: FV(rate, nper, pmt, [pv], [type])Let’s pause and explain these acronyms for our example.FV – Future Value. What something could be worth in the future.Rate – Rate of return. The amount you expect your investments to grow each year, on average.Pmt – Payment. The amount of money you expect to save in a given period of time. You can choose any time period (year, month, week), but in this example we’ll use “year”.Nper – Number of periods. In our example, the number of years you expect to work and save money before you retire.PV – Present Value. The amount of money you currently have invested. You can omit this value and the formula will still work -- it will just assume that you don’t have any savings invested at the present time.Type – To keep it simple, we’re leaving this input blank in the example, meaning the formula will assume that you make your contributions at the end of each period. (If you make contributions at the beginning of each period, you can use “1” as your input.)Now, back in Excel, in cell B5, you had typed: =FV(4. With your cursor blinking after the open parenthesis in cell B5, take your mouse and click on cell B1, which has the value “5%” in it. Then type a comma.Your screen should look like this:
5. With your cursor blinking in cell B5 after the comma, click on cell B2 and type another comma.6. Click on cell B3 and type a comma.7. Click on cell B4 and type a closed parenthesis. Don’t hit enter yet.Your screen should now look like this:
If so, congratulations! You’ve just calculated a future value of $238,356.82 for the portfolio in our example.(The value is negative, but don’t worry about that for now. If you’re really curious about this, feel free to watch some YouTube videos about it or call me.)To estimate the future value of your own portfolio, leave the formula in cell B5 exactly as it is, and replace the values in cells B1, B2, B3 and B4 with the actual or expected values for your own portfolio: Your expected rate of return, the number of years you plan on working until you retire, your expected annual savings, and the present value of your invested savings.Of course all of the figures that you enter into your formula will be forecasts, except for the present value of your savings. There’s no way to know for certain what your rate of return will be in the future, especially if you’re investing in equities. You also can’t be sure of the number of years you’ll be working or how much you’ll be saving each year. You’ll have to get comfortable making educated guesses about an uncertain future.Regardless, using this formula can help you get a ballpark idea of where your investing plan is headed. After you run some calculations, you may find that you’re right on track for where you want to be when it comes time to retire.But you may find that you are not.If that’s the case, you’ll need to re-evaluate your plan, and consider making some changes. It might be hard, but it’s much easier to make a course-correction sooner rather than later. More on that next month.